What this dashboard is
A fair, interactive view of CX performance for the DECI main program.
The support system used to measure response and resolution time by the wall clock — every passing hour counted, including nights and days off. That made the team look far slower than it was, and punished agents most when tickets arrived late in the evening.
This dashboard rebuilds those numbers using business hours only, converts everything to Cairo time, and credits the agent who actually closed each ticket (not just whoever it was assigned to). It separates the real CX team from Sales helpers and agents who left, and exposes four live filters — Team (multi-select), People, Period, and SLA target — that recompute every page instantly.
The data & cleaning
Source, scope, and what was done before any calculation.
Cleaning pipeline
- Removed system accounts — “cs” and “Mustafa CEO” are not real agents.
- Converted timestamps to Cairo time — the export was UTC; conversion respects daylight-saving (UTC+2 in Mar/Apr, UTC+3 from late April).
- Validated durations — no negative times; genuinely long outliers kept, not treated as errors.
- Tagged every agent’s team — CX, Sales, or Left.
- Pre-computed per-ticket fields embedded in the dashboard so all filters run instantly and offline.
How response & resolution time are measured
The starting point of every duration — and one honest limitation.
Both clocks start at ticket creation
Response time = Created → First reply. Resolution time = Created → Resolved. Both are then passed through the business-hours filter (section 04) so only working time counts.
Measuring from creation reflects the customer’s experience — how long the student actually waited from the moment they wrote in.
Known limitation (for the future): the export has no “assigned-to-agent” timestamp. So if a ticket sat unassigned in a queue, that wait is counted from creation and attributed to the agent — even though they hadn’t received it yet. To measure pure agent handling time (from the moment they were assigned), a future export needs an Assigned At column. See the roadmap (section 12).
Who gets the credit (attribution rule)
A later export added a “Closed By” column — the agent who actually closed each ticket, which is often not the assigned agent. The dashboard now credits work by this rule, per ticket:
| Case | Condition | Credited to |
|---|---|---|
| Closed by self | Closed By = Assigned Agent | the assigned agent (normal) |
| Colleague helped | Closed By is a different agent name, ticket not escalated | the colleague who closed it |
| Escalated (ops lead) | ticket escalated & closed by lead/ops (any id) | the assigned agent (they escalated it) |
| Email worked as agent | closed by an ops/tech email, not escalated | that email identity (acted as an agent) |
| Blank but answered | Closed By blank, but a first reply exists | the assigned agent (fallback) |
| Tech-only (excluded) | Closed By blank and no first reply | nobody — removed from all CX metrics |
Two known emails were unified to their real agents (Sarah Ibrahim, Fareeda Ahmed had a CX id and an ops-lead id). This materially changes per-agent numbers — e.g. one agent was assigned ~9,000 tickets but actually closed ~6,000; the rest were closed by colleagues, who now get the credit. The Data Quality page shows each case week by week.
The Main Analysis Method filter
A header control lets you layer optional exclusions on top of the base attribution rule:
| Option | What it does |
|---|---|
| 1 · Attribution rule (base) | Always on. The 6-case rule above. Tech-only tickets are always excluded. |
| 2 · Exclude escalated | Drops every escalated ticket from agent metrics entirely (not even credited to the assigned agent). |
| 3 · Exclude blank closed-by | Drops every ticket whose Closed By is blank, even if it had a first reply. |
Options 2 and 3 are independent toggles and stack: turning both on leaves only tickets with an explicit closer that were never escalated — the strictest, cleanest sample.
The business-hours engine
The single most important correction in the whole analysis.
The rule
Count only the time inside the working window; discard everything outside it — nights above all.
Working window 9:00 AM → 6:00 PM, seven days a week (the team worked all week). Aya Hassan’s window is 8:00 AM → 5:00 PM, matching her consistent early start.
Worked example
Why one uniform window instead of each weekly roster? The rosters rotate weekly and were only partly available; reading every cell off phone screenshots risks mis-scoring a real person. A single fair ruler removes the night inflation (≈90% of the distortion) and keeps every agent comparable.
The SLA — now a live filter
There was no official SLA before. We derived options from the data — and built all three into the dashboard.
Reading the distribution (percentiles)
Sort every ticket’s response time fastest→slowest. A percentile marks where a share falls below. The distribution is bimodal — fast for most, with a long slow tail (worst in peak weeks):
The three targets — selectable in the header
Balanced default
Resolution ≤ 8h
CX compliance: 62% / 67%
Standard ambitious
Resolution ≤ 8h
CX compliance: 51% / 67%
Data-driven loose
Resolution ≤ 14h
CX compliance: 73% / 74%
The SLA TARGET control in the header switches between them, and the whole dashboard recomputes. The Overview also shows an “SLA methods compared” card so you see all three side by side at once. Compliance % = tickets meeting the target ÷ tickets with a recorded time, for the current team and period.
Team segmentation
Not everyone handling tickets was CX — separate them cleanly.
Every agent is tagged CX, Sales, or Left. The TEAM filter is now multi-select — view any combination (e.g. CX + Sales) or All; default is My CX team. A separate PEOPLE filter lets you pick specific individuals to compare side by side across every page (it overrides the team filter while active).
This matters: with Sales included, the overall median response looked like ~1.8h because Sales replied extremely fast on a small load. CX alone is ~2.1h — the honest number for your team.
Ranking & Performance Score
Rank by any criterion — plus one balanced score that's fair to heavy loads.
Rank by
The Rank by selector reorders the leaderboard: response speed, resolution speed, tickets handled, response-SLA, resolution-SLA, customer rating, or Performance Score. Column headers are also clickable.
Why speed alone misleads: the heaviest-loaded agents look slow because of volume, not skill (one CX agent handled ~9,000 tickets).
Performance Score (0–100)
Score = 0.30 × Response-SLA% + 0.25 × Resolution-SLA% + 0.20 × (Rating÷5×100) + 0.25 × (workload vs busiest agent)
Normalised within the team and period you’re viewing. Because it uses the SLA-compliance terms, the score also moves when you change the SLA target. It rewards meeting the SLA, satisfaction, and shouldering volume.
What changes when you switch the SLA
A key mental model — so the numbers are read correctly.
Reacts to the SLA
- Response & resolution compliance %
- Breach counts (Agent Detail) and breach rate (Peak Times)
- “% late” beside each category (Problems)
- Performance Score and SLA-based ranking
- The colour of median times (blue within target, red above)
Stays fixed (it’s a fact)
- The median response / resolution values themselves
- Ticket counts and volumes
- Category and sub-category breakdowns
- Peak hours and the heatmap shape
The SLA is a goal line, not a measurement of speed. How fast an agent actually was doesn’t change; what changes is whether that speed clears the line you set.
Every page, explained
All three filters (Team · Period · SLA) apply on every page.
- Headline KPIs, the Old-vs-Corrected comparison, a weekly volume chart, and the SLA methods compared card.
- Sortable leaderboard with a Performance Score column and team tag per name.
- Median time cells turn blue within target, red above it; SLA% and Score recompute with the SLA filter.
- Per-agent KPIs coloured against the target, with a breach count (“X of N breached”), the unfairness removed, and a daily response trend.
- A Response SLA breach rate KPI plus tickets by hour, by day of week, and a day × hour heatmap — all follow the filters.
- Amber bars mark hours outside the shift, where tickets pile up for the morning.
- Category & sub-category breakdowns with a “% late” figure per category, plus phase notes (full-program reference) and a suggested fix per wave.
- How every ticket was closed — by self, by a colleague, by escalation, or not logged — shown as four KPIs and a week-by-week stacked chart.
- Callouts for the “Closed-By blank” period and the “closed by someone else” period, and what they mean for trusting per-agent numbers.
- Everyone who closed an escalated ticket (the ops-lead role), ranked by escalations handled with median resolution in business hours.
- A compare picker to focus on specific leads. The originating CX agent keeps the performance credit; this page measures escalation handling separately.
- For each agent: how many tickets they closed for colleagues (credited here) and how many of their own were closed by others.
Findings & limits
What the corrected, CX-only numbers reveal — and what to keep in mind.
| Finding | Detail |
|---|---|
| The team is fast | CX median response ≈ 2.1h in business hours, not the 11h the old system implied. |
| Volume ≠ inefficiency | Heaviest-loaded agents look slow purely from volume; the Performance Score corrects for it. |
| Sales skewed the average | Sales helpers replied very fast on small loads, flattering the all-team median. |
| Night backlog | Tickets arrive heavily after shifts end until midnight, then queue for morning. |
| Predictable problem waves | Registration → virtual-session links → in-person location & rescheduling. |
- Working window is uniform (9–6); individual days off aren’t modelled. Accuracy is highest from late April onward.
- Time is measured from creation, not assignment (no assignment timestamp in the export).
- Priority is almost all “medium”, so the SLA is single-tier.
- No re-open field; ratings missing on ~22% of tickets (averages use rated tickets only).
- All tickets are from the main program; the summer program hasn’t started.
The three filters
Independent controls in the header — live on every page.
🟣 Main Analysis Method
Base attribution rule, plus optional stacking exclusions: exclude escalated, exclude blank closed-by.
🟢 Team (multi)
CX / Sales / Left / Ops, any combination or All. Default: My CX team.
👥 People
Pick specific individuals to compare; overrides the team filter while active.
📅 Period
From–To dates or a quick preset.
🟠 SLA target
Balanced 4/8 · Standard 2/8 · Data-driven 9/14.
Changing one never resets the others — every page recomputes for the exact combination you choose.
Roadmap — what we build next
This is a living dashboard. The natural next steps:
Measure pure agent handling time (from assignment, not creation) and reveal how fast tickets are routed — separating agent speed from queue delay.
Feed each week’s real shift times and days off to refine the business-hours window per agent, especially for March–mid-April.
If the system can flag re-opened tickets, add a quality metric — fast closes that bounce back aren’t real resolutions.
Load summer tickets when they start and reuse the phase knowledge base to staff each wave proactively.
If priority data improves, split the SLA by priority (urgent vs normal) instead of a single tier.
Turn the top recurring problems into ready replies linked from the Problems page.
Data Quality & the periods that passed
An honest record of how ticket handling and logging changed over time.
Two distinct periods
Mid-March → mid-April (collaborative & under-logged): tickets were closed by whoever was free — cross-closing hit 18–39% per week — and the “Closed By” field was often blank (~43% in the week of 22 Mar, ~27% the week of 5 Apr).
From 19 April onward (stable): tickets were mostly closed by their owner, cross-closing dropped to 4–6%, and logging became near-complete (0–3% blank).
How to read the numbers with this in mind: team-level totals, medians and SLA are robust across the whole period. Per-agent attribution is most precise from late April onward; in the early weeks the rule still credits the actual closer wherever it was recorded, and only falls back to the assigned agent when it wasn’t. The Data Quality page lets you see this for any team or people selection.