WBP Agent Performance — Metric Sourcing Reference¶

Models covered: agg_wbp_agent_summary_channel_daily, fct_wbp_contacts, fct_wbp_agent_channel_summary Related report: rpt_wbp_agent_performance_channel, rpt_wbp_xoos_agent_performance

Overview¶

Agent performance metrics in WBP are sourced from three distinct Gladly APIs. Each API covers a different grain and attribution model. Mixing them up produces wrong per-agent numbers — particularly on transferred contacts.

Metric Sourcing Rules¶

Handle time (total_handle_time_sec, total_acw_sec)¶

Source: Gladly Agent Summary API → fct_wbp_agent_channel_summary → agg_wbp_agent_summary_channel_daily

Gladly pre-computes per-agent, per-channel handle time. Use it as-is. Do not compute handle time from work sessions — work sessions attribute to the accepting agent, not the fulfilling agent.

Accepted-to-fulfilled (total_accepted_to_fulfilled_sec) — non-phone only¶

Source: Gladly Agent Summary API (AVG_ACCEPTED_TO_FULFILLED_TIME_IN_SECONDS_* per channel)

The Bronze table exposes a per-agent per-channel average for this metric. To make it summable downstream, the Gold fact reconstructs a total as:

avg_accepted_to_fulfilled * COALESCE(contact_fulfilled, 0) AS TOTAL_ACCEPTED_TO_FULFILLED_SEC

This is computed in fct_wbp_agent_channel_summary per channel CTE, then summed in agg_wbp_agent_summary_channel_daily.

Phone is excluded — for phone calls, accepted_at and fulfilled_at coincide in Gladly (both map to when the agent picks up), so accepted_to_fulfilled ≈ 0 and the metric is meaningless. The column is NULL for the phone CTE.

Queued-to-fulfilled (total_queued_to_fulfilled_sec) — all channels¶

Source: fct_wbp_contacts.QUEUED_TO_FULFILLED_SEC, attributed to FULFILLED_EID

Available for all channels. The fulfilling agent is identified from the CONTACT/FULFILLED event's assigned_agent_name (roster-matched to FULFILLED_EID). On transferred phone calls the fulfilling agent differs from the accepting agent — sourcing from work sessions would attribute queue time to the wrong person.

The contact_queue_metrics CTE in agg_wbp_agent_summary_channel_daily covers all channels:

contact_queue_metrics AS (
    SELECT
        FULFILLED_AT::DATE   AS date,
        FULFILLED_EID        AS eid,
        CHANNEL_FORMATTED    AS channel,
        SUM(QUEUED_TO_FULFILLED_SEC) AS total_queued_to_fulfilled_sec
    FROM {{ ref('fct_wbp_contacts') }}
    WHERE FULFILLED_AT IS NOT NULL
      AND FULFILLED_EID IS NOT NULL
    GROUP BY FULFILLED_AT::DATE, FULFILLED_EID, CHANNEL_FORMATTED
)

Which channel's total_queued_to_fulfilled_sec is meaningful is up to the analyst. For phone it represents time in the call queue. For async channels (email, SMS) it represents time from contact creation to resolution, which has a different interpretation.

SLA (sla_met_count, sla_eligible_count)¶

Source: fct_wbp_contacts.SLA_MET, attributed to FULFILLED_EID (same CTE as total_queued_to_fulfilled_sec)

fct_wbp_work_sessions has one work session per agent who touched a contact. A transferred contact would stamp the same SLA result on every agent involved — double-counting SLA eligible/met counts across agents.

fct_wbp_contacts has one row per contact, so SLA is counted exactly once per contact and attributed to the fulfilling agent. SLA_MET is NULL when no agent_accepted_at was recorded (same eligibility condition as before), so sla_eligible_count = SUM(CASE WHEN sla_met IS NOT NULL THEN 1 ELSE 0 END).

fct_wbp_contacts Agent Attribution¶

COALESCE order (validated March 2026)¶

For both CONTACT/ENDED and CONTACT/FULFILLED events, assigned_agent_name is the canonical field:

-- ENDED
COALESCE(assigned_agent_id,   NULLIF(initiator_id, 'SYSTEM')) AS agent_id_when_ended,
COALESCE(assigned_agent_name, initiator_agent_name)           AS agent_name_when_ended,

-- FULFILLED
COALESCE(assigned_agent_id,   NULLIF(initiator_id, 'SYSTEM')) AS fulfilled_by_agent_id,
COALESCE(assigned_agent_name, initiator_agent_name)           AS fulfilled_by_agent,

Why assigned-first: For phone calls, initiator_agent_name is always SYSTEM (0% match against the real agent). assigned_agent_name matches ~99.5% of contacts across all channels. Using COALESCE(initiator, assigned) would produce the wrong agent for every phone contact.

Match rates against legacy contact export (March 2026, 99,545 contacts):

Remaining mismatches (~0.5–1.5%) are attributable to agent name changes in Gladly (name updated after legacy export captured the old value) or NULL agent_name in the legacy pipeline — not errors in fct_wbp_contacts.

Agent columns on fct_wbp_contacts¶

On most contacts AGENT_NAME_WHEN_ENDED = FULFILLED_BY_AGENT. They differ on transferred contacts where one agent fulfills (answers) and a different agent closes.

Why Not Work Sessions for Queue Timing and SLA?¶

fct_wbp_work_sessions has one row per agent who touched a contact (accepting agent attribution). On a transferred phone call:

1. Agent A receives the routing offer → agent_accepted_at stamped on Agent A's work session
2. Agent A transfers to Agent B
3. Agent B answers and fulfills the contact

Queue timing: work sessions would attribute queued_to_fulfilled to Agent A. Agent B is the one who actually handled the customer — queue time belongs on Agent B's performance.

SLA: the same seconds_within_or_over_sla value is written to every work session for the contact. Summing across work sessions double-counts SLA met/eligible on transferred contacts — one contact increments multiple agents' counters.

fct_wbp_contacts has exactly one row per contact and FULFILLED_EID captures the fulfilling agent (Agent B) correctly via the CONTACT/FULFILLED event. Both metrics are now sourced from the same CTE.