110,041
Desk tickets in the dataset the dashboard reads
Case Study
The Support Dashboard That Counted Every Unanswered Ticket as "Instant"
How a digital marketing agency's first-response-time dashboard was wrong from the first formula, and how we rebuilt it from raw Zoho Desk threads and proved it row by row.
A digital marketing agency runs client support through Zoho Desk and wanted one view of first response time per agent against a 24-hour target, across four SLA policies. The Zoho Desk connector exports no first-response timestamp, three lookalike columns were traps, and duplicate tickets inflated volume. We rebuilt the logic from the raw reply threads and proved it against the source.
Marketing Agency · US·Zoho AnalyticsZoho DeskSQL Query TablesData ValidationSLA Reporting
D
Digital Marketing AgencyZoho DeskZoho Analytics
Validated · 0 mismatches
Desk tickets
110,041
in the dashboard dataset
Row mismatches
0
105,704 rows · 7 columns
Reply threads captured
99.84%
12,591 of 12,611
SLA policies
4
24h first-response target
Go-ahead to validated
17 days
assembled dashboard
Median First Response by Agent
24h targetAgent 1
4.2h97% in SLA
Agent 2
7.8h94% in SLA
Agent 3
12.5h88% in SLA
Agent 4
19.4h79% in SLA
Agent 5
26.1h61% in SLA
Inside target Near limit Over 24h
Median FRT — Weekly Trend
hoursDashed line: internal 12h goal
By SLA Policy
4 policiesStandard
61% of tickets94%
Priority
22% of tickets96%
Enterprise
11% of tickets91%
After-hours
6% of tickets78%
Unanswered Tickets
the trapBefore
No reply = "0 hrs", counted inside SLA
After
Built from raw reply threads; no reply flagged
Row-Level Validation
105,704 rowsFirst response time0 diff
SLA status0 diff
Agent0 diff
Channel0 diff
Duplicate Tickets
channel fix7
personal mailboxes acting as inbound channels
unique ticketsduplicates removed
At a glance
Results That Speak for Themselves
0
row-level mismatches across 7 value columns (105,704 rows)
99.84%
of agent reply threads captured in the export (12,591 of 12,611)
17 days
client go-ahead to a validated, assembled dashboard
From challenge to outcome
The Problem & Our Approach
A Response-Time Dashboard That Counts Silence as Speed
The brief sounded simple: show first response time by agent, by week, by SLA. The data said otherwise. Every metric on the existing columns looked plausible, which is what made it dangerous.
- The connector exports no first-response timestamp. The Zoho Desk to Zoho Analytics connector carries no first-response DateTime column, so the dashboard could not be built as specified.
- Three lookalike columns were traps. One holds the last agent reply, not the first. One holds "0 hrs" for tickets nobody ever answered, so any formula that does not check for a reply counts every unanswered ticket as answered instantly and inside SLA. A third set of pre-built formula columns sat on top of that second trap and was wrong.
- Duplicate tickets inflated volume. Seven personal agent mailboxes were configured as active inbound email channels, so one customer email could become several tickets.
First-Response Logic Built From the Raw Reply Threads
We built the first-response logic ourselves, inside the client's existing Zoho workspace, and did not trust the number until it matched the raw threads, ticket by ticket.
- First response is the earliest outgoing agent reply at or after ticket creation, joined to agents and departments. Unanswered tickets are flagged, never counted as zero.
- A Status Group column rolls the client's 5 custom in-progress statuses into one group so the Awaiting Response KPI covers all of them.
- Six reports: FRT by agent, share within 24h by agent, weekly trend, agent performance summary, breach detail, average by department.
- A 10-section builder spec, KPI definitions and a dashboard mockup for the UI assembly step.
- A written diagnosis and instructions to disable fetching on the 7 personal agent channels while keeping one support address.
What We Achieved
A first-response metric proven to zero row-level mismatches against raw Zoho Desk threads.
Unanswered tickets counted separately instead of as instant, in-SLA responses.
Six reports and a dashboard that each agent can filter to their own numbers, with a 10-section spec for handoff.
A written diagnosis of the duplicate-ticket loop and the 7 mailboxes to switch off.
Definition decisions (assignee vs responder, calendar vs business hours) documented for the client to choose.
What the Rebuild Proved
| Check | Result |
|---|---|
| Live query table vs temp-table recomputation | 0 mismatches across 105,704 rows and 7 value columns |
| Agent reply threads in the export vs Desk's own counters | 12,591 of 12,611 (99.84%), no history cliff across 2024 to 2026 |
| Agreement with Desk's own first-response metric | 86% (3,128 of 3,650 responded tickets); every divergence traced to stale Desk data |
| Re-check against raw Desk data 5 weeks later | Total 110,041, responded 3,781, within SLA 2,216, awaiting 113: exact match |
| First response sent by someone other than the assignee | 12.6% of answered tickets (459 of 3,650), surfaced as a definition decision for the client |
| Awaiting-response backlog between the July and August checks | 209 to 113 (data drift, not a bug) |
What Changed After Delivery
The dashboard was assembled and validated: headline counts on the assembled dashboard matched a recomputation from the raw Desk tables exactly, and the top five agents' ticket counts and averages matched too.
- Unanswered no longer looks fast. Tickets without a reply are excluded from FRT and counted in a separate Awaiting Response KPI.
- The audit trail is documented. The refresh cadence (Desk connector every 3 hours), the calculation and the known data caveats are written down for handoff.
- Calendar hours match the client's target. The 24-hour target is measured in calendar hours, independent of Desk's SLA settings, so no Desk configuration had to change.
The Pattern, Not the Industry
This was not a marketing problem. It was a trust problem: a metric built on a default field that quietly means something else. It appears in any team that reports SLAs from a helpdesk, CRM or ticketing export.
- Check what a "zero" means before you average it. Null and zero are different facts.
- Prove a rebuilt metric against raw records, not against the previous dashboard.
- Measure in the unit the target is written in (calendar hours vs business hours).
- Write down the definition decisions (assignee vs responder) so the client chooses them, not the formula.
- If your response-time number has never been reconciled to raw tickets, treat it as a draft.
Discuss a similar Marketing Agency project
Lets Viz runs a paid discovery audit ($500–1,000, credited toward the project) to scope your requirements, data model, and architecture before writing a line of code.
From Lets Viz
Helping companies make data-driven decisions to achieve their Goals
Experience superior data analysis with our custom dashboards. Specialising in all tools and technologies, we turn data into visually compelling, actionable insights.
NDA-safe · No obligation · Clear next steps