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 line of shorthand or a JSON spec; 0sql returns the SQL and your application runs it.

zsql sql --expr "customer tier, total revenue, order count, average order value"
zsql sql --expr "customer name, customer tier, ticket count, avg resolution hours"
zsql sql --expr "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