Sales Analysis Recipe
Build a production-ready sales analytics model with multi-channel support.
Overview
This recipe creates a comprehensive sales model supporting:
- Multi-channel sales (store, web, catalog)
- Product hierarchy analysis
- Geographic breakdown
- Time-series comparisons
- Profitability measures
Architecture
flowchart TD
SS[Store Sales] -->|many_to_one| P[Product]
SS -->|many_to_one| S[Store]
SS -->|many_to_one| D[Date]
SS -->|many_to_one| C[Customer]
WS[Web Sales] -->|many_to_one| P
WS -->|many_to_one| D
WS -->|many_to_one| C
P -->|many_to_one| PC[Product Category]
S -->|many_to_one| R[Region]
Complete Model Structure
Sales Fact Table
name: Sales
physical_name: sales_fact
datasource: warehouse
cost: 100
fields:
# Keys and IDs
- type: dimension
name: Transaction ID
data_type: string
expression:
primary_key: true
sql: transaction_id
- type: dimension
name: Sale Date
data_type: date
grains: [day, week, month, quarter, year]
expression:
sql: sale_date
- type: dimension
name: Sale Channel
data_type: string
expression:
lookup: true
sql: channel
# Revenue measures
- type: measure
name: Gross Revenue
description: Total sales before returns and discounts
data_type: decimal
format: currency:2
expression:
sql: sum(gross_amount)
- type: measure
name: Net Revenue
description: Sales after returns and discounts
data_type: decimal
format: currency:2
expression:
sql: sum(net_amount)
- type: measure
name: Discount Amount
data_type: decimal
format: currency:2
expression:
sql: sum(discount_amount)
# Volume measures
- type: measure
name: Units Sold
data_type: integer
expression:
sql: sum(quantity)
- type: measure
name: Transaction Count
data_type: integer
expression:
sql: count(distinct transaction_id)
# Profitability
- type: measure
name: Cost of Goods Sold
data_type: decimal
format: currency:2
expression:
sql: sum(cogs)
- type: measure
name: Gross Profit
data_type: decimal
format: currency:2
expression:
sql: "[Net Revenue] - [Cost of Goods Sold]"
- type: measure
name: Gross Margin Percent
data_type: decimal
format: percent:2
expression:
sql: "[Gross Profit] / nullif([Net Revenue], 0)"
# Derived measures
- type: measure
name: Average Transaction Value
data_type: decimal
format: currency:2
expression:
sql: "[Net Revenue] / nullif([Transaction Count], 0)"
- type: measure
name: Average Selling Price
data_type: decimal
format: currency:2
expression:
sql: "[Net Revenue] / nullif([Units Sold], 0)"
- type: measure
name: Discount Rate
data_type: decimal
format: percent:2
expression:
sql: "[Discount Amount] / nullif([Gross Revenue], 0)"
Product Dimension
name: Product
physical_name: products
datasource: warehouse
cost: 10
fields:
- type: dimension
name: Product ID
data_type: string
expression:
primary_key: true
lookup: true
sql: product_id
- type: dimension
name: Product Name
data_type: string
expression:
lookup: true
sql: product_name
- type: dimension
name: Product Category
data_type: string
expression:
lookup: true
sql: category
- type: dimension
name: Product Subcategory
data_type: string
expression:
lookup: true
sql: subcategory
- type: dimension
name: Brand
data_type: string
expression:
lookup: true
sql: brand
- type: dimension
name: Product Status
description: Active, Discontinued, etc.
data_type: string
expression:
lookup: true
sql: status
Store Dimension
name: Store
physical_name: stores
datasource: warehouse
cost: 10
fields:
- type: dimension
name: Store ID
data_type: string
expression:
primary_key: true
lookup: true
sql: store_id
- type: dimension
name: Store Name
data_type: string
expression:
lookup: true
sql: store_name
- type: dimension
name: Store Type
data_type: string
expression:
lookup: true
sql: store_type
- type: dimension
name: Store City
data_type: string
expression:
lookup: true
sql: city
- type: dimension
name: Store State
data_type: string
expression:
lookup: true
sql: state
- type: dimension
name: Store Region
data_type: string
expression:
lookup: true
sql: region
- type: dimension
name: Store Open Date
data_type: date
expression:
sql: open_date
Date Dimension
name: Date
physical_name: date_dim
datasource: warehouse
cost: 10
fields:
- type: dimension
name: Date
data_type: date
grains: [day, week, month, quarter, year]
expression:
primary_key: true
sql: date_value
- type: dimension
name: Day of Week
data_type: string
expression:
lookup: true
sql: day_name
- type: dimension
name: Month Name
data_type: string
expression:
lookup: true
sql: month_name
- type: dimension
name: Quarter
data_type: string
expression:
lookup: true
sql: quarter_name
- type: dimension
name: Year
data_type: integer
expression:
lookup: true
sql: year_number
- type: dimension
name: Is Weekend
data_type: boolean
expression:
sql: is_weekend
- type: dimension
name: Is Holiday
data_type: boolean
expression:
sql: is_holiday
Relationships
datasource: warehouse
# Sales to Product
sales_product:
left: Sales
right: Product
sql: left.product_id = right.product_id
cardinality: many_to_one
# Sales to Store
sales_store:
left: Sales
right: Store
sql: left.store_id = right.store_id
cardinality: many_to_one
# Sales to Date
sales_date:
left: Sales
right: Date
sql: left.sale_date = right.date_value
cardinality: many_to_one
# Sales to Customer
sales_customer:
left: Sales
right: Customer
sql: left.customer_id = right.customer_id
cardinality: many_to_one
join: left # Not all sales have customer (e.g., anonymous)
Key Measures Reference
| Measure | Formula | Use Case |
|---|---|---|
| Gross Revenue | sum(gross_amount) | Total sales volume |
| Net Revenue | sum(net_amount) | Actual revenue |
| Gross Profit | Net Revenue - COGS | Profitability |
| Gross Margin % | Gross Profit / Net Revenue | Margin analysis |
| ATV | Net Revenue / Transactions | Basket size |
| ASP | Net Revenue / Units Sold | Pricing analysis |
| Discount Rate | Discounts / Gross Revenue | Promotion impact |
Time-Series Analysis
Period comparisons and windows are not modeled. They are decorators on a projection in the spec your application sends; the planner adds the comparison as a final pass over the aggregated result. See Projections and decorators.
{
"spec": {
"projections": [
{"field": "Sale Date", "decorators": [{"type": "truncate", "grain": "month"}]},
{"field": "Net Revenue"},
{"field": "Net Revenue", "alias": "Net Revenue LY",
"decorators": [{"type": "temporalize", "transform": "year_over_year"}]},
{"field": "Transaction Count", "alias": "Transactions MoM %",
"decorators": [{"type": "temporalize", "transform": "month_over_month", "percent_change": true}]}
]
}
}
At day grain, a moving average is {"type": "window", "mode": "moving", "function": "avg", "size": 7} on the measure. The same two requests as expression lines, where the frame is counted in rows rather than rows-before-current, so size: 7 is written 8:
zsql sql "month(sale date), net revenue, yoy(net revenue) as Net Revenue LY, mom_pct(transaction count)"
zsql sql "day(sale date), net revenue, moving_avg(net revenue, 8)"
Common Queries
zsql sql "sale channel, month(sale date), net revenue, transaction count, average transaction value"
zsql sql "product category, brand, net revenue, units sold, gross margin percent"
zsql sql "store name, store region, net revenue, transaction count, discount rate"
zsql sql "day of week, net revenue, transaction count, [is weekend] = true"
The last one, day-of-week analysis with a filter, as a spec:
{
"spec": {
"projections": [
{"field": "Day of Week"},
{"field": "Net Revenue"},
{"field": "Transaction Count"}
],
"filters": [
{"field": "Is Weekend", "predicate": "equals", "value": "true"}
]
}
}
Best Practices
- Separate gross and net measures: Track discounts and returns explicitly
- Use compound measures for ratios: Prevents division errors
- Include cost data: Enable profitability analysis
- Add date grains: Support flexible time grouping
- Use LEFT join for optional dimensions: Handle anonymous sales
Next Steps
- Projections and decorators: YoY and MoM comparisons, running totals, moving averages, percent of total
- Query spec: the full request shape
- Filters: predicates and relative dates