Handle Time Metrics in RPT_EMAIL_DAILY¶

Reference document explaining the three AHT perspectives, the bug fix in work_session_handle_time, and how to interpret agent vs queue performance.

1. Contact Lifecycle: Working vs Ending¶

Two fundamentally different relationships exist between an agent and a contact:

Working on a contact — An agent has a work session: the contact was routed to them and they spent time on it. This does not mean they resolved it. An agent can be routed a contact, spend 25 seconds reading it, and it gets reassigned to someone else.
Ending a contact — The agent who actually resolved/closed the contact. Only one agent is credited as the ender (AGENT_NAME in WARBYPARKER_CONTACTS_MODEL).

Real Example¶

Contact HL0qZpHUSwun2e33uP1EFw on 2026-02-10:

Agent	Session Duration	Status	Role
Sanshe Surmieda	179s	UNANSWERED	Ender (credited)
Aubrey Sombilon	25s	UNANSWERED	Worked on, did not end

On 2026-02-10, Aubrey had work sessions across 54 contacts but only ended 12. The other contacts were ended by other agents.

2. The Bug and Fix¶

Before (Incorrect)¶

The work_session_handle_time CTE in R__warbyparker_rpt_email_daily.sql queried WORK_SESSIONS_HISTORICAL filtered only by agent + date. This counted ALL of Aubrey's sessions (347.63 min total), not just the 12 contacts she ended (49.91 min).

-- BEFORE: Counted every session the agent had that day
SELECT agent_name, date, SUM(handle_time_sec) ...
FROM WORK_SESSIONS_HISTORICAL
WHERE date = ...
GROUP BY agent_name, date

After (Correct)¶

The fix joins through WARBYPARKER_CONTACTS_MODEL to scope sessions to only the contacts the agent ended. Aubrey's agent handle time dropped from 348 min to 50 min.

-- AFTER: Only sessions on contacts this agent ended
SELECT ws.agent_name, ws.date, SUM(ws.handle_time_sec) ...
FROM WORK_SESSIONS_HISTORICAL ws
JOIN WARBYPARKER_CONTACTS_MODEL cm
  ON ws.conversation_id = cm.conversation_id
  AND ws.agent_name = cm.agent_name  -- agent must be the ender
WHERE ws.date = ...
GROUP BY ws.agent_name, ws.date

3. Three AHT Perspectives¶

Using Aubrey Sombilon on 2026-02-10 (12 contacts ended, 54 contacts touched) as the running example:

Contact AHT (5.09 min) — Queue/Client Performance¶


Formula	`SUM(total_contact_handle_time_sec) / contacts_ended`
What it measures	Total time all agents invested per contact (including transfers)
Who cares	The client — this is the actual cost of servicing a contact in their queue
Column	`contact_aht_min` in RPT_EMAIL_DAILY

This is the "all-in" cost per contact. If a contact was touched by 3 agents before resolution, all their time counts.

Agent Ended AHT (4.16 min) — Agent Efficiency¶


Formula	`SUM(agent's own sessions on ended contacts) / contacts_ended`
What it measures	The agent's personal effort on contacts they resolved
Who cares	Operations managers — measures agent speed/efficiency
Column	`agent_aht_min` in RPT_EMAIL_DAILY (the one fixed by the bug)

This is the metric that was broken. It now correctly reflects only the agent's own time on the contacts they actually closed.

Agent Overall AHT (6.41 min) — Agent Utilization¶


Formula	`SUM(handle_time_in_sec WHERE agent_accepted_at IS NOT NULL) / contacts_touched`
What it measures	Average time the agent spent per contact they actually worked on, regardless of who ended it
Who cares	Workforce management — shows how much effort each contact costs this agent on average
Column	Not currently exposed in RPT_EMAIL_DAILY

contacts_touched = COUNT(DISTINCT contact_id) from work sessions where AGENT_ACCEPTED_AT IS NOT NULL. This filters to contacts the agent actually accepted and worked on. The metric answers: "On average, how long does this agent spend per contact they touch?"

4. Team Comparison (2026-02-10)¶

Agent	Ended	Touched	Contact AHT	Agent Ended AHT	Agent Overall AHT
Aubrey Sombilon	12	54	5.09	4.16	6.41
Kyna Montorio	5	47	3.82	3.54	6.53
Rodmer De Los Reyes	5	49	2.89	1.55	6.20
Sanshe Surmieda	9	53	1.65	1.56	5.18

5. Performance Interpretation¶

Contact AHT vs Agent Ended AHT Gap¶

When contact_aht > agent_ended_aht, it means other agents also worked the contact (transfers, shared work). A larger gap indicates more collaboration or transfer overhead.

Agent	Contact AHT	Ended AHT	Gap	Interpretation
Aubrey	5.09	4.16	+0.93	Moderate transfer overhead
Rodmer	2.89	1.55	+1.34	Higher transfer overhead
Sanshe	1.65	1.56	+0.09	Almost no shared work

Agent Overall AHT vs Agent Ended AHT¶

All four agents show overall AHT in the 5-7 min range, which is moderately higher than their ended AHT. This is expected — agents spend some time on contacts they don't end up closing. The overall AHT is comparable across the team (5.18-6.53 min), even though their ended AHT varies more widely (1.55-4.16 min). This suggests the team handles similar per-contact workloads, but some agents close fewer of the contacts they touch.

Agent	Ended AHT	Overall AHT	Ratio	Interpretation
Aubrey	4.16	6.41	1.5x	Closes many, moderate extra effort on transfers
Kyna	3.54	6.53	1.8x	Similar effort per contact, ends fewer
Rodmer	1.55	6.20	4.0x	Fast closer but spends comparable time per touched contact
Sanshe	1.56	5.18	3.3x	Efficient across the board

6. Invariants¶

These relationships always hold:

contact_aht >= agent_ended_aht — The gap represents transferred/shared work
contacts_touched >= contacts_ended — You can work on more contacts than you close

Note: agent_overall_aht is not guaranteed to be >= agent_ended_aht. An agent could have many short sessions on touched contacts (low overall AHT) while spending more time on the few they actually close (higher ended AHT). In practice, overall AHT tends to be higher, but it's not an invariant.

Data sourced from Snowflake WBP_DB on 2026-02-12. Examples from 2026-02-10 Warby Parker email queue.