> For the complete documentation index, see [llms.txt](https://v2.dataos.info/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://v2.dataos.info/consume/consume-with-ai/cookbook/answer-a-quarterly-kpi-question.md).
# Answer a KPI Question
**Goal:** You have a vetted Data Product and a business question — "what was revenue by customer segment, and what does the quarterly trend look like?" This recipe turns that question into a governed answer, with the metric and source model cited, so you can trust and reproduce the number.
**Journey:** Inspect schema → run the governed query → slice it further → confirm it was governed.
**Tools used:** `vulcan_semantic_schema`, `vulcan_query`.
**Example product:** Orders360 (`orders360`).
{% hint style="info" %}
This recipe assumes you have a [connected client](/consume/consume-with-ai/connect-clients.md) and a Data Product you already trust. If you have not vetted one yet, start with [Find a Data Product](/consume/consume-with-ai/cookbook/find-a-trustworthy-data-product.md). Each step shows an example prompt as a toggle - phrase it however you like; the assistant works from your intent, not exact wording. Expand a toggle to see the full response. The prompts and responses shown are illustrative examples from a sample catalog; the data products, metrics, and results in your environment will differ.
{% endhint %}
{% stepper %}
{% step %}
### Inspect the semantic schema
Before asking for numbers, find out what the product can actually answer so you ask for measures that exist and slice by dimensions that are supported. This calls `vulcan_semantic_schema` and returns the queryable surface: measures, dimensions, time dimensions, and segments. Think of it as the menu before you order.
*Claude Desktop returning the orders360 semantic surface*
What metrics and measures does orders360 expose?
Here's the full semantic layer for Orders360 - **4 models, 22 measures, 56 dimensions**:
***
**Orders model - revenue & order measures** *(the core fact table for revenue reporting)*
| Measure | Type | Definition |
| ------------------------------ | --------------- | -------------------------------------------- |
| `orders.TOTAL_REVENUE` | sum | Sum of `TOTAL_AMOUNT` (incl. tax & shipping) |
| `orders.AVERAGE_ORDER_VALUE` | avg | Average of `TOTAL_AMOUNT` |
| `orders.TOTAL_ORDERS` | count\_distinct | Distinct order count |
| `orders.TOTAL_QUANTITY_SOLD` | sum | Sum of `QUANTITY` |
| `orders.TOTAL_DISCOUNT_AMOUNT` | sum | `UNIT_PRICE × QUANTITY × DISCOUNT` |
| `orders.TOTAL_SHIPPING_COST` | sum | Sum of `SHIPPING_COST` |
| `orders.TOTAL_TAX_COLLECTED` | sum | Sum of `TAX` |
Key dimensions for slicing: `ORDER_DATE`, `CUSTOMER_ID`, `PRODUCT_ID`, `QUANTITY`, `DISCOUNT`, `UNIT_PRICE`.
**Customer model - customer measures**
| Measure | Type | Definition |
| -------------------------------- | ----- | ----------------------- |
| `customer.TOTAL_CUSTOMERS` | count | All customers |
| `customer.ACTIVE_CUSTOMERS` | count | Active customers |
| `customer.PLATINUM_CUSTOMERS` | count | Platinum-tier customers |
| `customer.AVERAGE_LOYALTY_SCORE` | avg | Avg loyalty score |
Key dimensions for slicing: `CUSTOMER_SEGMENT` (Platinum/Gold/Silver/Bronze), `ACCOUNT_STATUS`, `SIGNUP_DATE`, `LOYALTY_SCORE`.
**Customer Orders Enriched model - enriched measures**
| Measure | Type | Definition |
| -------------------------------------------------- | --------------- | ----------------------- |
| `customer_orders_enriched.TOTAL_ORDERS_METRIC` | sum | Total orders |
| `customer_orders_enriched.AVG_ORDERS_PER_CUSTOMER` | avg | Avg orders per customer |
| `customer_orders_enriched.MAX_CREDIT_SCORE` | max | Max credit score |
| `customer_orders_enriched.MIN_CREDIT_SCORE` | min | Min credit score |
| `customer_orders_enriched.M_COUNT` | count | Order count |
| `customer_orders_enriched.M_COUNT_DISTINCT` | count\_distinct | Distinct customers |
Rich dimensions include: `COUNTRY`, `LOYALTY_TIER`, `CURRENCY_CODE`, `ORDER_YEAR/MONTH/DAY`, `IS_FRAUD`, `IS_RETURNED`, `DISCOUNT_PERCENT`, `CONVERSION_RATE`.
**Products model - catalogue measures**
| Measure | Type | Definition |
| -------------------------- | ----- | ---------------------------------- |
| `products.TOTAL_PRODUCTS` | count | All products |
| `products.ACTIVE_PRODUCTS` | count | Active (non-discontinued) products |
Key dimensions for slicing: `CATEGORY`, `SUBCATEGORY`, `BRAND`, `PRICE`, `RATING`, `STOCK_QUANTITY`.
***
**For your quarterly revenue report**, the measures you'll want are:
* `orders.TOTAL_REVENUE` - primary revenue figure
* `orders.TOTAL_ORDERS` - order volume
* `orders.AVERAGE_ORDER_VALUE` - AOV trend
* `orders.TOTAL_DISCOUNT_AMOUNT` - discount impact
Slice by `orders.ORDER_DATE` (quarterly), `customer.CUSTOMER_SEGMENT`, or `products.CATEGORY`. Note there are **no pre-built time dimensions or metrics configured** - you'll need to apply date filters manually using `ORDER_DATE`, or `ORDER_YEAR`/`ORDER_MONTH` from the enriched model.
What dimensions and time dimensions can I use with the revenue measure? Can I slice by customer segment and product category?
Based on the semantic schema we already retrieved, here's the full picture for `orders.TOTAL_REVENUE`. Revenue lives on the `orders` model, which joins to `customer` and `products`, so you can slice by dimensions from all three.
**From `orders` (direct)**
| Dimension | Description |
| ---------------------- | -------------------------------------------------- |
| `orders.ORDER_DATE` | Date the order was placed - your primary time axis |
| `orders.CUSTOMER_ID` | Join key to customer |
| `orders.PRODUCT_ID` | Join key to products |
| `orders.QUANTITY` | Items per order |
| `orders.DISCOUNT` | Discount rate (0.0–1.0) |
| `orders.UNIT_PRICE` | Price per unit at time of order |
| `orders.TAX` | Tax amount |
| `orders.SHIPPING_COST` | Shipping cost |
**From `customer`** (via `orders.CUSTOMER_ID = customer.CUSTOMER_ID`)
| Dimension | Description |
| --------------------------- | ---------------------------------------------- |
| `customer.CUSTOMER_SEGMENT` | ✅ Yes - Platinum, Gold, Silver, Bronze |
| `customer.ACCOUNT_STATUS` | Active, Inactive, Suspended |
| `customer.SIGNUP_DATE` | Useful for cohort analysis |
| `customer.LOYALTY_SCORE` | Numeric loyalty score |
| `customer.FULL_NAME` | Customer name |
| `customer.EMAIL` | ⚠️ Caution - this is the failing quality check |
**From `products`** (via `orders.PRODUCT_ID = products.PRODUCT_ID`)
| Dimension | Description |
| ----------------------- | ------------------------------------------------------- |
| `products.CATEGORY` | ✅ Yes - Electronics, Home, Clothing, Sports, Food, Toys |
| `products.SUBCATEGORY` | Sub-level category |
| `products.BRAND` | Brand name |
| `products.PRICE` | List price |
| `products.RATING` | Product rating (1.0–5.0) |
| `products.DISCONTINUED` | Active vs discontinued flag |
**Time dimensions ⚠️**
There are no pre-built time dimensions configured on this data product - the schema returned 0 time dimensions. You can approximate quarterly slicing using:
* `orders.ORDER_DATE` - pass this as a filter range (e.g. `2026-01-01` to `2026-03-31` for Q1)
* `customer_orders_enriched.ORDER_YEAR` / `ORDER_MONTH` / `ORDER_DAY` - pre-extracted date parts from the enriched model, useful for grouping without date arithmetic
**Answer: yes to both ✅**
You can slice `TOTAL_REVENUE` by both customer segment and product category in a single query, since the join graph connects `orders → customer` and `orders → products`. A query like revenue by `customer.CUSTOMER_SEGMENT` + `products.CATEGORY` + a quarterly `ORDER_DATE` range is fully supported.
{% endstep %}
{% step %}
### Ask the KPI question
Now ask the business question in plain language. This calls `vulcan_query`, which validates the question against the schema and returns preview rows, the row count, and source attribution.
What was total revenue by customer segment in orders360?
Here's all-time total revenue (`orders.TOTAL_REVENUE`) broken down by customer segment (`customer.CUSTOMER_SEGMENT`):
| # | Customer Segment | Total Revenue |
| - | ---------------- | ------------- |
| 1 | Platinum | $552.31 |
| 2 | Gold | $493.01 |
| 3 | Bronze | $293.90 |
| 4 | Silver | $195.27 |
| | **Total** | **$1,534.49** |
**Observations:**
* Platinum is the top revenue segment at $552.31 (36% of total), as expected for the highest-tier customers.
* Gold is close behind at $493.01 (32%).
* Bronze ($293.90) outperforms Silver ($195.27) - worth investigating whether Silver customers are under-engaged or the segment has fewer customers.
* Combined, Platinum + Gold account for 68% of total revenue.
⚠️ No time filter applied - this reflects all-time data. Want me to scope this to a specific quarter or year?
{% endstep %}
{% step %}
### Slice it further
Reuse the same governed metric to break the result down another way, or to see the trend.
Now break that same revenue down by product category.
Here's `orders.TOTAL_REVENUE` broken down by both `customer.CUSTOMER_SEGMENT` and `products.CATEGORY` - all-time, 14 combinations:
| Segment | Category | Revenue |
| -------- | ----------- | ------- |
| Gold | Toys | $183.56 |
| Platinum | Electronics | $150.19 |
| Gold | Sports | $138.69 |
| Silver | Clothing | $129.75 |
| Platinum | Clothing | $116.13 |
| Gold | Electronics | $113.48 |
| Bronze | Sports | $104.18 |
| Bronze | Electronics | $98.37 |
| Platinum | Home | $98.36 |
| Platinum | Food | $96.26 |
| Platinum | Sports | $91.37 |
| Bronze | Clothing | $91.35 |
| Silver | Food | $65.52 |
| Gold | Home | $57.28 |
**Notable patterns:**
* Gold × Toys is the single biggest combination at $183.56 - surprising given Toys isn't typically a premium category. Worth investigating whether this is a data anomaly or a genuine trend.
* Platinum revenue is spread across 5 categories (Electronics, Clothing, Home, Food, Sports) - no single dominant category, suggesting broad purchasing behaviour.
* Silver only appears in 2 categories (Clothing, Food) - either a small segment or heavily concentrated purchasing.
* Electronics appears across Gold, Platinum, and Bronze but not Silver - Silver customers may be price-sensitive.
* Toys and Home are absent from Platinum and Bronze respectively as top combinations, which could indicate category affinity differences by tier.
⚠️ No time filter applied - all-time data. Want me to visualise this as a heatmap or scope it to a specific period?
Show me the monthly revenue trend so I can see the quarterly shape.
The data covers Jan–May 2024 (5 months). Here's the trend visualised:
| Period | Revenue |
| -------------------------- | --------- |
| Q1 2024 (Jan–Mar) | $1,014.40 |
| Q2 2024 (Apr–May, partial) | $520.09 |
| All-time total | $1,534.49 |
*Monthly revenue trend across Jan–May 2024, coloured by quarter*
A few things stand out from the shape:
* **Q1 2024 was strong and building** - January ($259.93) → February ($322.59) → March ($431.88), a 66% rise peak-to-trough within the quarter. March is the clear high watermark across all available data.
* **Q2 2024 drops sharply** - April ($261.40) and May ($258.69) are both back near January levels, suggesting either a seasonal dip or a data coverage issue (the dataset only runs through May 2024, so Q2 is incomplete).
The data only covers 5 months, so there's no full quarterly comparison available yet. Want me to check whether earlier data exists, or break this monthly view down further by segment or category?
{% endstep %}
{% step %}
### Confirm it was governed
Confirm the answer came through the semantic layer rather than ad-hoc SQL.
Confirm this came through the semantic layer and not raw SQL.
Confirmed. Every query in this conversation used the Vulcan semantic REST API (`query_type: SEMANTIC_REST`), not raw SQL.
Here's what that means in practice:
* **Measures** like `orders.TOTAL_REVENUE` were resolved by the semantic layer - you referenced a named measure, and Vulcan generated the underlying `SUM(orders.TOTAL_AMOUNT)` SQL server-side.
* **Joins** (e.g. `orders → customer`, `orders → products`) were handled automatically by the semantic join graph, not written manually.
* **Filters and time dimensions** (e.g. `ORDER_DATE` granularity = month) were expressed as semantic parameters, not SQL `WHERE` clauses.
The raw SQL you can see in the query responses (the `WITH _wrapped AS (...)` blocks) is what Vulcan generated and executed against Snowflake - it was never written or passed in directly. The entry point was always the semantic layer.
{% endstep %}
{% endstepper %}
## Outcome
You answered a revenue KPI question in plain language and got back a governed result — validated against the semantic schema, with the metric and source model cited, and drilled down to quarterly trend. The number is reproducible and defensible, not a fabricated guess.
## Next steps
* [Find a Data Product](/consume/consume-with-ai/cookbook/find-a-trustworthy-data-product.md) - vet a product before you query it.
---
# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.
## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:
```
GET https://v2.dataos.info/consume/consume-with-ai/cookbook/answer-a-quarterly-kpi-question.md?ask=&goal=
```
`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.