When the Executive Dashboard and the Ticket Sample Disagree
Aster Retail's customer care group runs 500 agents on a service center. The executive dashboard shows average first response time of 2.1 hours for the last quarter, but a manual sample of 50 tickets shows a median of 45 minutes. The backlog trend line is flat even though case volume grew 28% year over year. The dashboard's open-cases metric uses case status not equal to Closed. Reporting was built by three different analysts over two years; one report filters on Case Owner, another on Created By, and a third uses last modified date. Case reassignment creates a new case history row, and the team added snapshot history only six weeks ago. The contact center spans four time zones, and the dashboard displays timestamps in the viewer's local time. The COO wants a real-time dashboard by next week and has asked why the numbers cannot be trusted.
1. Explain the likely causes of the discrepancy between the dashboard and the sample. Distinguish metric-definition problems from data-model problems.
Read the worked answer
The two numbers are almost certainly measuring different things:
Metric-definition problems
- The dashboard likely averages a timestamp that is overwritten or recomputed on reassignment, while the sample measures creation to first outbound agent reply. Reassignment creating a new case history row supports this: the report may be reading the first history row or the latest owner change.
- Open cases defined as status not equal to Closed includes pending customer, solved, and on-hold states. That inflates the open population and flattens the backlog trend.
- Population filters differ: Case Owner, Created By, and last modified date produce three different case sets. Case Owner excludes cases reassigned away, which is exactly the group with the worst response times.
- Average versus median: a handful of very old cases pulls the average up. The dashboard shows an average and the sample shows a median, so they are not comparable as presented.
Data-model problems
- No point-in-time snapshot until six weeks ago. Any backlog trend before that is reconstructed from current fields, which cannot reproduce historical state.
- Timestamps displayed in viewer-local time and averaged across four zones. Averaging local times is meaningless and shifts results by hours.
- Case history rows multiply on reassignment, so joins to history can duplicate cases and distort counts.
The flat backlog is the more alarming signal: volume grew 28% and the backlog did not move, which usually means the metric is broken rather than the operation being stable.
2. Propose the metric definitions and data model changes required before any dashboard is rebuilt.
Read the worked answer
Lock definitions first; rebuilding a dashboard on undefined metrics scales the error.
- First response: creation timestamp to first public outbound agent comment, excluding automated acknowledgments, stored as a dedicated field that reassignment never resets. Report median and 90th percentile, not only the average. Use milestone data where entitlements exist.
- Open cases: status in an explicitly enumerated open set, with pending customer and solved treated as separate categories. Do not use not equal to Closed.
- Backlog: end-of-day snapshot count of open cases by age bucket, priority, and queue, stored in a snapshot object. Do not reconstruct history from current records; accept that trend reporting starts when snapshots start and label the earlier period as unavailable.
- Population: define the reporting population as cases created in the period, regardless of current owner, and keep a separate view by current owner for workload analysis.
- Time: store UTC, convert once at the presentation layer, and attribute cases to a reporting time zone dimension rather than averaging local timestamps.
- Data model: a conformed case fact table with dimensions for region, priority, channel, queue, and intent, joined to history only through a de-duplicated event view.
- Governance: one certified dataset, one data dictionary, named ownership, and change control. Retire the three analyst-built reports rather than fixing them in parallel.
3. How would you respond to the COO's request for a real-time dashboard next week?
Read the worked answer
Push back on the timeline, not on the goal, and offer a staged path:
- Explain the risk: a real-time dashboard built on the current definitions would display the wrong number faster. The COO's real problem is trust, and trust comes from definitions and reconciliation, not refresh frequency.
- Separate the two audiences: operational leaders need near-real-time queue and SLA views inside the service center, refreshing every 5 to 15 minutes. Executives need daily or hourly trends. Building one dashboard for both produces a screen nobody trusts.
- Propose a two-week metric audit: reconcile the 50-ticket sample, document each definition, identify the report of record for each metric, and get executive sign-off on the definitions. Deliver a corrected static summary in that window so the COO has something usable.
- Then a four-to-six-week rebuild: certified dataset, snapshot-based backlog, corrected first-response field, parallel run against the old dashboard, and a documented reconciliation.
- Set expectations on history: backlog trend before snapshots began cannot be recovered reliably. State that clearly rather than interpolating.
- Commit to a review cadence: monthly metric review with the business, so definitions do not drift again when analysts change.
Give the COO a dated plan with deliverables each week. Refusing the date without an alternative is not credible; accepting it guarantees a second failed dashboard.