Customer dimension table with lifetime metrics
- File
- models/marts/customers.sql
Lineage
Upstream 2
- model orders
- model stg_customers
Downstream 2
- exposure how_are_sales_and_customers_developing
- exposure weekly_report
Columns 6
| Column | From | Used by |
|---|---|---|
| customer_id | stg_customers.customer_id | |
| first_name | stg_customers.first_name | |
| last_name | stg_customers.last_name | |
| stg_customers.email | ||
| order_count | orders.order_id, . | |
| lifetime_value | orders.total_amount, . |
SQL
SELECT
c.customer_id,
c.first_name,
c.last_name,
c.email,
COUNT(o.order_id) as order_count,
SUM(o.total_amount) as lifetime_value
FROM {{ ref('stg_customers') }} c
LEFT JOIN {{ ref('orders') }} o ON c.customer_id = o.customer_id
GROUP BY 1, 2, 3, 4