Lab: Map the model on paper

Step 1 — Draw the join graph

1. 1List the six tables. Draw an edge wherever a foreign key connects two, and label it with the join condition (e.g. order_items.order_id = orders.order_id).
2. 2For each fact table (order_items, subscriptions, marketing_spend), trace the path to customers and to products. Which are reachable? Which aren't?
3. 3Note any table that stands alone (marketing_spend) — its metrics can only be sliced by the dimensions it physically contains (channel, date).

Step 2 — List dimensions and their columns

1. 1Write each dimension and its default column (e.g. region → customers.region).
2. 2For date and channel, note the per-model overrides — which column each fact uses.
3. 3Mark date as a time dimension that supports day/week/month/quarter/year grains.

Step 3 — Define every metric in one sentence

This is the most important step. For each metric, write the definition a CFO would sign off on:

• revenue = sum of (quantity × unit_price − discount) for completed orders only.
• orders_count = number of distinct completed orders.
• aov = revenue ÷ orders_count.
• new_customers = distinct customers by signup date.
• cac = marketing_spend ÷ new_customers.
• mrr = sum of mrr_amount for currently-active subscriptions.
• gross_margin = (revenue − cost of goods) ÷ revenue.