Tickets created
How many tickets were created in the period?
COUNT(*) WHERE created_at IN period
created_at as period filter.
Adapt: Use the Zendesk instance timezone.
Service
Customer service: tickets, users, CSAT — load, PII and support measures.
Templates only — grain, filters, custom fields and ownership are firm-specific.
Copy formulas into the KPI Definition Card, then adapt grain and filters.
How many tickets were created in the period?
COUNT(*) WHERE created_at IN period
created_at as period filter.
Adapt: Use the Zendesk instance timezone.
How many tickets were solved in the period?
COUNT(*) WHERE solved_at IN period
solved_at vs status=Solved — stay consistent.
Adapt: Account for reopened tickets and backlog shift.
How satisfied are customers with support?
AVG(score) WHERE score IS NOT NULL
Only when satisfaction_ratings is loaded.
Adapt: Lock good/bad vs scale scoring model.
How quickly does support respond to new tickets?
AVG(reply_time_in_minutes) FROM ticket_metrics
Prefer ticket_metrics — more robust than manual calculation.
Adapt: Clarify business hours vs calendar hours.
How many tickets are currently open?
COUNT(*) WHERE status NOT IN (solved, closed)
Lock snapshot vs flow metric once.
Adapt: Count pending status as open depending on process.
Same formulas as a table — copy into KPI cards or SQL drafts.
| Label | Formula | Grain | Fields |
|---|---|---|---|
| Tickets created |
COUNT(*) WHERE created_at IN period
|
Ticket | Tickets.created_at |
| Tickets solved |
COUNT(*) WHERE solved_at IN period
|
Ticket | Tickets.solved_at, Tickets.status |
| CSAT score |
AVG(score) WHERE score IS NOT NULL
|
Satisfaction rating | Satisfaction Ratings.score, Satisfaction Ratings.ticket_id |
| First reply time |
AVG(reply_time_in_minutes) FROM ticket_metrics
|
Ticket | Ticket Metrics.reply_time_in_minutes |
| Open tickets |
COUNT(*) WHERE status NOT IN (solved, closed)
|
Ticket | Tickets.status |
| Id | Label | Grain | Notes |
|---|---|---|---|
status |
Status | New / open / pending / solved / closed | Lock open vs solved definition — pending often counts as open. |
priority |
Priority | Low / normal / high / urgent | Harmonize priority values before the mart. |
group |
Group | Support team | Respect group hierarchy and escalation paths. |
channel |
Channel | Email / chat / phone / web | Use via.channel for channel dimension. |
Inventory direct and quasi identifiers for this source — tag, restrict RAW, hash or drop before marts.
| Entity | Fields | Classification | Stage treatment | Treatment |
|---|---|---|---|---|
| users (end-users) | email, name, phone, details, notes |
Direct | RAW: restrict access; Curated: hash/tokenize; Mart: no cleartext | End-user PII — primary surface. |
| users (agents) | email, name, phone |
Workforce | RAW: workforce policy; Curated: separate from customer PII; Mart: aggregates only | Agents separate from end users. |
| tickets | subject, description, requester_id, submitter_id |
Sensitive | RAW: rarely load; Curated: drop/redact; Mart: never | Ticket free text + requester join. |
| ticket comments | body, html_body, plain_body, author_id |
Sensitive | RAW: rarely load; Curated: drop/redact; Mart: never | Skip or redact comments by default. |
| ticket attachments | file_name, content_url |
Sensitive | RAW: rarely load; Curated: drop/redact; Mart: never | Do not load attachments by default. |
| organizations | name, details, domain_names |
Quasi | RAW: tag; Curated: generalize (region vs postal); Mart: aggregated | Org details can be personal. |
| side conversations | subject, participants, messages |
Sensitive | RAW: rarely load; Curated: drop/redact; Mart: never | Side conversations — second PII surface. |
| custom fields | ticket/user custom fields mit Email/Phone |
Direct | RAW: restrict access; Curated: hash/tokenize; Mart: no cleartext | Inventory custom field definitions. |
Where personal data can hide beyond obvious contact fields — warehouse copies included.
| Focus | Notes |
|---|---|
| Match keys | email, external_id, user id, phone, social identities. |
| Primary objects | users, tickets, comments, organizations. |
| Comments & attachments | Largest free-text/file surface. |
| Warehouse copies | Export API dumps, Sunshine, BI connectors. |
| Side conversations | External participants and mail threads. |
| Help Center identities | End-user accounts via HC and messaging. |
| Deleted / redacted tickets | Redaction must propagate to warehouse. |
| Integration tokens | Apps often store email in their own stores. |
| Sandbox | Sandbox tickets not in prod marts. |
| Satisfaction comments | CSAT free text with PII. |
Source-specific guidance: what goes wrong, how to prevent it, what staff must know — plus where metadata lives and how to access it.
Prevent issues in the source first. Warehouse checks are a safety net — not a substitute for staff habits.
requester_id IS NULLstatus IN ('solved','closed') AND solved_at IS NULLcustom_status not in open/pending/solved setsticket.brand_id inconsistent with form/orgstatus open AND assignee_id IS NULL AND group_id IS NULLusers.email null or invalid for end-userstags not in controlled vocabularysatisfaction_rating comment present score nullusers (end-users)
End-user duplicates
email, external_id, phoneorganizations
Organization duplicates
domain_names, name, external_idusers (agents)
Agent vs end-user mix-up
role, emailside conversations
Side conversation identities
participant emailsSource-native catalogs for schema, picklists/properties and access paths — not a full dump.
Object overview with short descriptions and load guidance — not a full schema dump.
| Table | Description | Grain | Load |
|---|---|---|---|
tickets
Tickets
|
Support tickets — core fact for service KPIs. | One ticket | Load |
users
Users
|
End customers and agents — PII core and DSDR entry point. | One user | Load |
organizations
Organizations
|
Customer organizations — account-style dimension. | One organization | Load |
groups
Groups
|
Support groups for team dimension. | One group | Load |
ticket_metrics
Ticket metrics
|
Pre-aggregated SLA metrics — first reply, resolution time. | Metrics per ticket | Load |
satisfaction_ratings
Satisfaction ratings
|
CSAT/NPS ratings — optional but central for quality KPIs. | One rating | Optional |
ticket_comments
Ticket comments
|
Comments — metadata yes, bodies only when needed (PII). | One comment | Optional |
Most important fields to load early — not a full schema dump.
| Entity | Field | Role | Why |
|---|---|---|---|
| Tickets |
Tickets.id
|
Key | Join / lineage |
| Tickets |
Tickets.status
|
Dimension | Open / pending / solved |
| Tickets |
Tickets.priority
|
Dimension | Priority |
| Tickets |
Tickets.group_id
|
Dimension | Team |
| Tickets |
Tickets.assignee_id
|
Dimension | Assignee |
| Tickets |
Tickets.requester_id
|
Dimension | Requester (PII link) |
| Tickets |
Tickets.organization_id
|
Dimension | Organization |
| Tickets |
Tickets.via.channel
|
Dimension | Channel |
| Tickets |
Tickets.created_at
|
Measure | Created / period grain |
| Tickets |
Tickets.updated_at
|
Key | Incremental sync |
| Tickets |
Tickets.solved_at
|
Measure | Resolution time |
| Tickets |
Tickets.type
|
Dimension | Ticket type |
| Ticket Metrics |
Ticket Metrics.ticket_id
|
Key | Ticket join |
| Ticket Metrics |
Ticket Metrics.reply_time_in_minutes
|
Measure | First reply time |
| Ticket Metrics |
Ticket Metrics.full_resolution_time_in_minutes
|
Measure | Resolution time |
| Satisfaction Ratings |
Satisfaction Ratings.score
|
Measure | CSAT score |
| Satisfaction Ratings |
Satisfaction Ratings.ticket_id
|
Key | Ticket join |
| Users |
Users.id
|
Key | User join |
| Users |
Users.email
|
PII | Person key / PII |
| Users |
Users.name
|
PII | PII |
| Users |
Users.phone
|
PII | PII |
| Users |
Users.role
|
Dimension | End user vs agent |
| Organizations |
Organizations.id
|
Key | Join |
| Organizations |
Organizations.name
|
Dimension | Org label |
| Groups |
Groups.id
|
Key | Group join |
| Groups |
Groups.name
|
Dimension | Team label |
System and noise objects/fields you typically should not sync into the warehouse by default.
| Table | Category | Why |
|---|---|---|
ticket comment bodies (default) |
System | Free-text PII — only with a use case. |
ticket attachments / binaries |
System | Files — expensive and sensitive. |
side conversation message bodies |
System | Additional mail PII. |
audit events bulk (ohne Bedarf) |
System | Noise — selectively. |
chat / messaging transcripts (ohne Bedarf) |
System | Highly sensitive. |
unused custom object records |
System | Schema ballast. |
macro / trigger definition dumps |
System | Ops metadata. |
Warehouse-neutral examples for landing (RAW) and curated models — copy and adapt to your dialect. Not vendor SOQL/API DDL.
Tickets API shape — status, priority, requester_id, assignee_id, created_at/solved_at.
-- Warehouse-neutral RAW from Zendesk Tickets API export
CREATE TABLE raw_zd_ticket (
ticket_id BIGINT,
subject VARCHAR,
status VARCHAR, -- new/open/pending/hold/solved/closed
priority VARCHAR,
type VARCHAR,
requester_id BIGINT,
submitter_id BIGINT,
assignee_id BIGINT,
organization_id BIGINT,
group_id BIGINT,
created_at TIMESTAMP,
updated_at TIMESTAMP,
solved_at TIMESTAMP,
_loaded_at TIMESTAMP
);
End-user email = PII; ticket_metrics for first reply / full resolution time.
CREATE TABLE raw_zd_user (
user_id BIGINT,
name VARCHAR,
email VARCHAR, -- end-user / agent PII
role VARCHAR, -- end-user / agent / admin
organization_id BIGINT,
active BOOLEAN,
_loaded_at TIMESTAMP
);
CREATE TABLE raw_zd_ticket_metric (
ticket_id BIGINT,
reply_time_in_minutes_calendar INT,
reply_time_in_minutes_business INT,
full_resolution_time_in_minutes_calendar INT,
requester_wait_time_in_minutes_calendar INT,
_loaded_at TIMESTAMP
);
CREATE TABLE raw_zd_satisfaction_rating (
rating_id BIGINT,
ticket_id BIGINT,
score VARCHAR, -- good/bad/offered
created_at TIMESTAMP,
_loaded_at TIMESTAMP
);
Open = status in (new, open, pending, hold); solved/closed for throughput.
CREATE TABLE curated_fct_zd_ticket AS
SELECT
t.ticket_id,
t.status,
t.priority,
t.type,
t.requester_id,
t.assignee_id,
t.organization_id,
t.group_id,
t.created_at,
t.solved_at,
m.reply_time_in_minutes_calendar AS first_reply_minutes,
m.full_resolution_time_in_minutes_calendar AS full_resolution_minutes,
CASE WHEN t.status IN ('new', 'open', 'pending', 'hold') THEN TRUE ELSE FALSE END AS is_open,
CASE WHEN t.status IN ('solved', 'closed') THEN TRUE ELSE FALSE END AS is_solved
FROM raw_zd_ticket t
LEFT JOIN raw_zd_ticket_metric m ON m.ticket_id = t.ticket_id;
User dims without email; CSAT as fact or periodic aggregation.
CREATE TABLE curated_dim_zd_user AS
SELECT
user_id,
name,
role,
organization_id
-- omit email from default analytics dims
FROM raw_zd_user
WHERE active = TRUE;
CREATE TABLE curated_fct_zd_csat AS
SELECT
rating_id,
ticket_id,
score,
created_at,
CASE WHEN score = 'good' THEN 1 WHEN score = 'bad' THEN 0 END AS csat_flag
FROM raw_zd_satisfaction_rating
WHERE score IN ('good', 'bad');
Created/solved, open, first reply, CSAT — choose business vs calendar minutes.
-- Tickets created
SELECT COUNT(*) AS tickets_created
FROM curated_fct_zd_ticket
WHERE created_at BETWEEN :period_start AND :period_end;
-- Tickets solved
SELECT COUNT(*) AS tickets_solved
FROM curated_fct_zd_ticket
WHERE is_solved = TRUE
AND solved_at BETWEEN :period_start AND :period_end;
-- Open tickets
SELECT COUNT(*) AS open_tickets
FROM curated_fct_zd_ticket
WHERE is_open = TRUE;
-- First reply time (avg minutes)
SELECT AVG(first_reply_minutes) AS avg_first_reply_minutes
FROM curated_fct_zd_ticket
WHERE created_at BETWEEN :period_start AND :period_end
AND first_reply_minutes IS NOT NULL;
-- CSAT score
SELECT AVG(csat_flag) AS csat_score
FROM curated_fct_zd_csat
WHERE created_at BETWEEN :period_start AND :period_end;
Open related Binom-Tools workflows to adapt formulas, PII and measures for this source.
Related governance stories for modelling, PII and measure design around this source.