Customer Support / CX Analytics data model
Kimball warehouse for help-desk/ticketing support: an accumulating-snapshot ticket fact (open → first response → resolved → closed) with SLA-breach and FCR flags, a transaction-grain ticket-activity fact, and a CSAT survey fact, over conformed customer/agent/channel/priority/category/product dimensions on BigQuery. For Salesforce Service Cloud (Case, Entitlement, Milestone, Asset), use the Salesforce Service Cloud Analytics template.
Vendor-neutral customer-support and CX analytics for help-desk/ticketing platforms (Zendesk, Freshdesk, ServiceNow, Intercom): the ticket lifecycle, conversation activity, and CSAT/CES/NPS survey responses over a conformed SCD2 customer dimension with agent, channel, priority, category, and product dimensions for SLA, FCR, and satisfaction analytics. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 10
- tables
- 98
- columns
- 15
- relationships
- 6
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: BigQuery). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Accumulating snapshot for tickets
A support ticket has a clear lifecycle (open → first response → resolved → closed); an accumulating snapshot delivers response, resolution, and handle-time lags from one evolving row.
Separate activity fact at touchpoint grain
SLA and FCR live on the ticket, but conversation flow, reassignments, and per-reply latency need their own transaction-grain fact.
Dedicated CSAT fact rather than a ticket attribute
Surveys are optional, can have multiple responses, and carry CES/NPS alongside CSAT, so a separate fact keeps the ticket grain clean.
Masking of customer and agent PII under GDPR/CCPA
Support records contain personal contact data subject to access and erasure rights; email is hashed and names classified confidential.
Tables in the Customer Support / CX Analytics model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Fact tables (3)
fct_ticket
Grain: one row per support ticket (accumulating snapshot open → resolved → closed)
Accumulating-snapshot fact — one row per ticket; milestone date keys and durations fill as the ticket progresses.
| Column | Type | Keys |
|---|---|---|
| ticket_key | bigint | PK |
| customer_key | bigint | |
| agent_key | bigint | |
| channel_key | bigint | |
| priority_key | bigint | |
| category_key | bigint | |
| product_key | bigint | |
| opened_date_key | bigint | |
| first_response_date_key | bigint | |
| resolved_date_key | bigint | |
| closed_date_key | bigint | |
| ticket_id | varchar | |
| ticket_status | varchar | |
| first_response_minutes | integer | |
| resolution_minutes | integer | |
| handle_time_minutes | integer | |
| reopen_count | smallint | |
| reassignment_count | smallint | |
| is_first_contact_resolution | boolean | |
| is_sla_response_breached | boolean | |
| is_sla_resolution_breached | boolean | |
| is_escalated | boolean |
fct_ticket_activity
Grain: one row per ticket activity (reply, status change, note)
Transaction-grain ticket-activity fact for conversation flow and agent touchpoint analytics.
| Column | Type | Keys |
|---|---|---|
| ticket_activity_key | bigint | PK |
| date_key | bigint | |
| customer_key | bigint | |
| agent_key | bigint | |
| channel_key | bigint | |
| ticket_id | varchar | |
| activity_id | varchar | |
| activity_type | varchar | |
| actor_type | varchar | |
| activity_count | smallint | |
| response_lag_minutes | integer |
fct_csat
Grain: one row per CSAT/CES survey response
Transaction-grain CSAT survey fact tying satisfaction back to the ticket, agent, and channel.
| Column | Type | Keys |
|---|---|---|
| csat_key | bigint | PK |
| date_key | bigint | |
| customer_key | bigint | |
| agent_key | bigint | |
| channel_key | bigint | |
| category_key | bigint | |
| ticket_id | varchar | |
| survey_id | varchar | |
| csat_score | smallint | |
| ces_score | smallint | |
| nps_score | smallint | |
| is_satisfied | boolean | |
| is_detractor | boolean | |
| has_verbatim | boolean | |
| response_count | smallint |
Dimension tables (4)
dim_date
Grain: one row per calendar day
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| day_of_week | smallint | |
| month_number | smallint | |
| quarter | smallint | |
| year | smallint | |
| is_weekend | boolean | |
| is_business_day | boolean |
dim_customer
Grain: one row per customer per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| customer_key | bigint | PK |
| customer_id | varchar | BK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| varchar | PII | |
| phone | varchar | PII |
| company_name | varchar | |
| customer_segment | varchar | |
| support_tier | varchar | |
| account_country | varchar | |
| preferred_language | varchar | |
| lifetime_ticket_count | integer | |
| is_at_risk | boolean |
dim_agent
Grain: one row per support agent per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| agent_key | bigint | PK |
| agent_id | varchar | BK |
| agent_name | varchar | PII |
| agent_email | varchar | PII |
| support_team | varchar | |
| agent_tier | varchar | |
| queue | varchar | |
| site_location | varchar | |
| is_outsourced | boolean |
dim_product
Grain: one row per product
| Column | Type | Keys |
|---|---|---|
| product_key | bigint | PK |
| product_id | varchar | BK |
| product_name | varchar | |
| product_line | varchar | |
| product_version | varchar |
Lookup tables (3)
dim_channel
Grain: one row per support channel
| Column | Type | Keys |
|---|---|---|
| channel_key | bigint | PK |
| channel_code | varchar | BK |
| channel_name | varchar | |
| is_synchronous | boolean |
dim_priority
Grain: one row per ticket priority
| Column | Type | Keys |
|---|---|---|
| priority_key | bigint | PK |
| priority_code | varchar | BK |
| priority_name | varchar | |
| priority_rank | smallint | |
| target_response_minutes | integer | |
| target_resolution_minutes | integer |
dim_category
Grain: one row per ticket category
| Column | Type | Keys |
|---|---|---|
| category_key | bigint | PK |
| category_code | varchar | BK |
| category_name | varchar | |
| subcategory_name | varchar | |
| issue_type | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_ticket.customer_key → dim_customer (many-to-one)
- fct_ticket.agent_key → dim_agent (many-to-one)
- fct_ticket.channel_key → dim_channel (many-to-one)
- fct_ticket.priority_key → dim_priority (many-to-one)
- fct_ticket.category_key → dim_category (many-to-one)
- fct_ticket.product_key → dim_product (many-to-one)
- fct_ticket_activity.date_key → dim_date (many-to-one)
- fct_ticket_activity.customer_key → dim_customer (many-to-one)
- fct_ticket_activity.agent_key → dim_agent (many-to-one)
- fct_ticket_activity.channel_key → dim_channel (many-to-one)
- fct_csat.date_key → dim_date (many-to-one)
- fct_csat.customer_key → dim_customer (many-to-one)
- fct_csat.agent_key → dim_agent (many-to-one)
- fct_csat.channel_key → dim_channel (many-to-one)
- fct_csat.category_key → dim_category (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.