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 shorthand:

zsql sql --expr "month(sale date), net revenue, yoy(net revenue) as Net Revenue LY, mom_pct(transaction count)"
zsql sql --expr "day(sale date), net revenue, moving_avg(net revenue, 7)"

Common Queries

zsql sql --expr "sale channel, month(sale date), net revenue, transaction count, average transaction value"
zsql sql --expr "product category, brand, net revenue, units sold, gross margin percent"
zsql sql --expr "store name, store region, net revenue, transaction count, discount rate"
zsql sql --expr "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