Pipeline to Dashboardnihalsulaiman.com

Week 5 · Model it in SQL › Day 34

Day 34. Metrics, additivity and the semantic layer

⏱ 4h · learn 75m · lab 130m · ship 20m · review 15m🏷 restaurants, hotels, retail, marketingBefore: Day 33

By the end of today you can

  • Write a metric dictionary with one definition, formula and owner per metric
  • Classify metrics as additive, semi-additive or non-additive and aggregate each correctly
  • Build menu engineering and same-store sales metrics
  • Explain what a semantic layer is and when it's worth it

1. Why this matters

Two dashboards showing two different "revenue" numbers destroy more trust than any pipeline failure. Senior BI people own definitions: what each metric means, how it's computed, and where it's computed (once). Today you write the dictionary for all four sectors.

2. The metric dictionary

One entry per metric, kept in the repo (dbt YAML or a Markdown table) and linked from every dashboard:

Field Example
Name Net sales
Definition Gross line revenue minus discounts and refunds, excluding tax and tips, by business date
Formula sum(line_revenue) - sum(discount) - sum(refund_amount)
Grain / additivity Additive across all dimensions
Source model fct_order_lines
Owner Finance (sign-off), BI (implementation)
Caveats Delivery-platform orders net of platform fees from Oct 2026

3. Additivity rules

Class Examples Aggregate by
Additive Revenue, quantity, spend, clicks, room nights sum over anything
Semi-additive Stock on hand, OTB, active loyalty members, cash balance sum across entities; last / average over time
Non-additive ADR, RevPAR, ROAS, CTR, margin %, average check, food-cost % ratio of sums at the display grain
Distinct counts Unique customers, unique guests recompute count(distinct); never sum daily uniques

4. Sector metrics worth knowing cold

  • QSR: net sales, transactions (orders), average check = net sales / orders, items per order, channel mix, speed of service (if timestamps allow), food cost % = food cost / net sales, labour % (with a schedule source), same-store sales growth (only restaurants open in both periods).
  • Menu engineering (Kasavana–Smith): classify each item by popularity (share of items sold vs a threshold of $0.7 / n$ for \(n\) items) and contribution margin (price − food cost) vs the average: Stars (high/high), Plowhorses (popular, low margin), Puzzles (low popularity, high margin), Dogs (low/low).
  • Hotels: occupancy, ADR, RevPAR, TRevPAR, pickup, pace, cancellation rate, length of stay, channel cost.
  • Retail: sales per sq ft, basket size, units per transaction, sell-through, refund rate, gross margin %.
  • Marketing: CTR, CPC, CPA, ROAS, conversion rate, blended CAC.
Maths and principle

Principle: same-store sales growth must compare the same set of stores in both periods, or new openings fake growth.

Equation: let \(S\) be stores open for all of both periods. \(\text{SSS} = \dfrac{\sum_{s \in S} R_{s,t}}{\sum_{s \in S} R_{s,t-1}} - 1\). Total growth \(= \dfrac{\sum_{\text{all}} R_t}{\sum_{\text{all}} R_{t-1}} - 1\) mixes in new and closed stores.

Analogy: a controlled experiment: hold the sample fixed to measure the effect. Where it breaks: remodels, temporary closures and calendar shifts (an extra Saturday). Adjust or exclude, and state the rule.

Try it: last year 10 stores sold $10M. This year those 10 sold $10.3M and 2 new stores sold $1.2M. SSS? Total growth? (SSS 3%; total 15%.)

5. The semantic layer

A semantic layer stores metric definitions once, and every tool asks it for "net sales by brand by week" instead of writing its own SQL. Options: dbt Semantic Layer / MetricFlow, Cube (open source), Power BI semantic models (the dataset with DAX measures is itself a semantic layer for Microsoft shops), and LookML in Looker.

For a medium company with one BI tool, the Power BI model is the semantic layer and a dictionary in git is enough. Add a separate layer when several tools (Power BI + Metabase + Python notebooks + an LLM chat) must agree.

The 5-year lens

When finance and operations disagree on a definition, don't pick a winner in code. Write both, name them differently (net_sales_finance, net_sales_ops), show the bridge between them on one page, and get the owners to agree which one goes on the executive dashboard. Your job is to make the disagreement visible and small, not to hide it.

Lab

  1. Write docs/metrics.md (or dbt semantic_models/metrics YAML) with 20 metrics across the four sectors: definition, formula, additivity, model, owner, caveats.
  2. Build mart.fct_menu_engineering (dbt model) for each brand and month: quantity, mix %, contribution margin, classification. Test with a hand-computed fixture.
  3. Build fct_restaurant_daily with net sales, orders, average check. Show that averaging daily average checks for a month gives a different answer than the ratio of sums. Put both in the docs with the explanation.
  4. Add is_comparable to dim_restaurant (open for ≥ 13 months) and a same-store sales model. (Generate an extra 400 days of data with an earlier start date and add two "new" restaurants by editing the reference file.)
  5. Optional: define 5 metrics in Cube or MetricFlow and query them.

Ship

feat(metrics): metric dictionary, menu engineering, same-store sales.

Check yourself

1. Daily unique customers Mon–Sun are 300, 280, 310, 290, 350, 400, 380. Unique customers for the week?

2. A menu item is very popular with below-average contribution margin. Its menu-engineering class?

3. Why exclude new stores from same-store sales?

My notes