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

MeasureDefinitionBusiness Use
Total Revenuesum(order_total)Track overall sales
Order Countcount(distinct order_id)Volume analysis
Average Order ValueRevenue / OrdersBasket size tracking
Customer Lifetime ValueTotal Revenue grouped by Customer IDSegment customers
Ticket CountSupport interactionsSupport load
Avg Resolution HoursTime to resolveSupport 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

  1. Use Customer ID as primary grain: All customer-level analysis flows from here
  2. Add lookup: true on frequently filtered dimensions
  3. Use compound measures for cross-table calculations
  4. Include date dimensions with grains for time-series analysis
  5. Document business logic in descriptions

Next Steps