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

MeasureFormulaUse Case
Gross Revenuesum(gross_amount)Total sales volume
Net Revenuesum(net_amount)Actual revenue
Gross ProfitNet Revenue - COGSProfitability
Gross Margin %Gross Profit / Net RevenueMargin analysis
ATVNet Revenue / TransactionsBasket size
ASPNet Revenue / Units SoldPricing analysis
Discount RateDiscounts / Gross RevenuePromotion 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

  1. Separate gross and net measures: Track discounts and returns explicitly
  2. Use compound measures for ratios: Prevents division errors
  3. Include cost data: Enable profitability analysis
  4. Add date grains: Support flexible time grouping
  5. Use LEFT join for optional dimensions: Handle anonymous sales

Next Steps