Customer 360 Recipe
Build a comprehensive customer analytics model with multiple touchpoints.
Overview
Customer 360 provides a unified view of customer behavior across all touchpoints: orders, support interactions, marketing engagement, and lifetime value. This recipe demonstrates a production-ready model structure.
Architecture
flowchart TD
O[Orders Fact] -->|many_to_one| C[Customer]
S[Support Tickets] -->|many_to_one| C
E[Email Events] -->|many_to_one| C
C -->|many_to_one| CA[Customer Address]
C -->|many_to_one| CS[Customer Segment]
Complete Model Structure
Customer Dimension
name: Customer
physical_name: customers
datasource: warehouse
cost: 10
fields:
- type: dimension
name: Customer ID
data_type: string
expression:
primary_key: true
lookup: true
sql: customer_id
- type: dimension
name: Customer Name
data_type: string
expression:
lookup: true
sql: concat(first_name, ' ', last_name)
- type: dimension
name: Customer Email
data_type: string
expression:
lookup: true
sql: email
- type: dimension
name: Customer Since
data_type: date
grains: [day, month, quarter, year]
expression:
sql: created_at
- type: dimension
name: Customer Status
data_type: string
expression:
lookup: true
sql: status
- type: dimension
name: Customer Tier
description: Based on lifetime spend
data_type: string
expression:
lookup: true
sql: |
CASE
WHEN lifetime_value >= 10000 THEN 'Platinum'
WHEN lifetime_value >= 5000 THEN 'Gold'
WHEN lifetime_value >= 1000 THEN 'Silver'
ELSE 'Bronze'
END
Orders Fact Table
name: Orders
physical_name: orders
datasource: warehouse
cost: 100
fields:
- type: dimension
name: Order ID
data_type: string
expression:
primary_key: true
sql: order_id
- type: dimension
name: Order Date
data_type: date
grains: [day, week, month, quarter, year]
expression:
sql: order_date
- type: dimension
name: Order Channel
data_type: string
expression:
lookup: true
sql: channel
- type: measure
name: Total Revenue
data_type: decimal
format: currency:2
expression:
sql: sum(order_total)
- type: measure
name: Order Count
data_type: integer
expression:
sql: count(distinct order_id)
- type: measure
name: Average Order Value
description: Revenue per order
data_type: decimal
format: currency:2
expression:
sql: "[Total Revenue] / nullif([Order Count], 0)"
- type: measure
name: Items Per Order
data_type: decimal
expression:
sql: sum(item_count) / nullif(count(distinct order_id), 0)
Support Tickets Fact
name: Support Tickets
physical_name: support_tickets
datasource: warehouse
cost: 100
fields:
- type: dimension
name: Ticket ID
data_type: string
expression:
primary_key: true
sql: ticket_id
- type: dimension
name: Ticket Created Date
data_type: date
grains: [day, week, month]
expression:
sql: created_at
- type: dimension
name: Ticket Category
data_type: string
expression:
lookup: true
sql: category
- type: dimension
name: Ticket Priority
data_type: string
expression:
lookup: true
sql: priority
- type: measure
name: Ticket Count
data_type: integer
expression:
sql: count(distinct ticket_id)
- type: measure
name: Avg Resolution Hours
data_type: decimal
expression:
sql: avg(resolution_hours)
- type: measure
name: First Response Hours
data_type: decimal
expression:
sql: avg(first_response_hours)
Relationships
datasource: warehouse
# Orders to Customer
orders_customer:
left: Orders
right: Customer
sql: left.customer_id = right.customer_id
cardinality: many_to_one
# Support Tickets to Customer
tickets_customer:
left: Support Tickets
right: Customer
sql: left.customer_id = right.customer_id
cardinality: many_to_one
# Customer to Address (for geographic analysis)
customer_address:
left: Customer
right: Customer Address
sql: left.address_id = right.address_id
cardinality: many_to_one
allow_measure_expansion: true
Key Measures Explained
| Measure | Definition | Business Use |
|---|---|---|
| Total Revenue | sum(order_total) | Track overall sales |
| Order Count | count(distinct order_id) | Volume analysis |
| Average Order Value | Revenue / Orders | Basket size tracking |
| Customer Lifetime Value | Total Revenue grouped by Customer ID | Segment customers |
| Ticket Count | Support interactions | Support load |
| Avg Resolution Hours | Time to resolve | Support efficiency |
Advanced: Compound Measures
Lifetime value needs no new measure: project Total Revenue by Customer ID and the planner joins Orders to Customer. Compound measures are for formulas over other fields: their expression references measures and dimensions in brackets rather than warehouse columns. A formula built only from measure references needs no aggregate of its own, because each referenced measure carries one. Anything that needs a raw column goes in a plain measure first.
# In Orders: a plain measure over the fact's own columns
- type: measure
name: Active Days
description: Days between a customer's first and last order
data_type: integer
expression:
sql: datediff(day, min(order_date), max(order_date))
# Compound: other measures, referenced by name
- type: measure
name: Customer Order Frequency
description: Average days between orders
data_type: decimal
expression:
sql: "[Active Days] / nullif([Order Count] - 1, 0)"
# Compound across two facts: Support Tickets and Orders blend on Customer
- type: measure
name: Tickets per 1000 Orders
description: Support load relative to order volume
data_type: decimal
expression:
sql: "[Ticket Count] * 1000.0 / nullif([Order Count], 0)"
Query Examples
Each request is one expression line or a JSON spec; 0sql returns the SQL and your application runs it.
zsql sql "customer tier, total revenue, order count, average order value"
zsql sql "customer name, customer tier, ticket count, avg resolution hours"
zsql sql "month(customer since), total revenue, order count"
The third line, a monthly cohort view, as the spec your application would POST:
{
"spec": {
"projections": [
{"field": "Customer Since", "decorators": [{"type": "truncate", "grain": "month"}]},
{"field": "Total Revenue"},
{"field": "Order Count"}
]
}
}
Add {"type": "temporalize", "transform": "year_over_year"} to the Total Revenue projection’s decorators for year-over-year growth per cohort month. See Projections and decorators.
Best Practices
- Use Customer ID as primary grain: All customer-level analysis flows from here
- Add lookup: true on frequently filtered dimensions
- Use compound measures for cross-table calculations
- Include date dimensions with grains for time-series analysis
- Document business logic in descriptions
Next Steps
- Compound Measures: Cross-table calculations
- Extended Blending: one customer concept across fact tables
- Projections and decorators: YoY customer growth and other request-time transforms