ADR 022 — WBP Topic-Level Metric Aggregates¶

Date: 2026-05-04 Status: Accepted Deciders: Data Engineering

Context¶

Warby Parker uses Gladly's conversation topic system to tag interactions with one or more topics (e.g. "Return | Label", "Exchange"). Operations requested metric breakdowns at the topic level — handle time, QA scores, CSAT, and FCR — to understand how performance varies by contact reason.

A legacy implementation existed in OPERATIONS.AGGREGATE as a set of dynamic tables (WARBYPARKER_AHT_BY_TOPIC, WARBYPARKER_QA_BY_TOPIC, WARBYPARKER_CSAT_BY_TOPIC, WARBYPARKER_FCR_BY_TOPIC) and a rollup view (WARBYPARKER_METRICS_BY_TOPIC). These had several limitations:

• Topics were stored as up to three denormalized topic_1/topic_2/topic_3 columns, requiring UNPIVOT at query time
• CSAT used weekly granularity for all metrics, losing daily precision for non-CSAT metrics
• CSAT join matched on customer + week (not conversation) — a fuzzy approximation
• QA topics were resolved by applying the topic list from the conversation export to the QA evaluation date, rather than parsing the actual Gladly URL embedded in the QA record
• No topic_id — topics stored as name strings only, vulnerable to renames

This ADR documents the redesign in the Gold medallion layer.

Decision¶

Build five Gold models that replace the legacy topic tables:

Design Decisions¶

1. Fan-out grain — one row per topic, not per conversation¶

Each aggregate produces one row per (date × EID × channel × topic_id). A conversation with N active topics produces N rows — this is intentional. Cross-topic rollups (totaling metrics across all topics for an agent-day) belong in rpt_ views or BI tooling via GROUP BY.

Storing the fan-out in the agg is correct: it preserves the full dimensionality for downstream aggregation at any level.

2. Topic source — fct_wbp_conversation_topics with IS_ACTIVE filter¶

All four aggregates join to fct_wbp_conversation_topics, which has one row per TOPIC_ADDED event and an IS_ACTIVE flag derived from subsequent TOPIC_REMOVED events. Only active topics are included (IS_ACTIVE = TRUE). Removed topics are excluded from all topic aggregates.

Topics are stored as both TOPIC_ID (Gladly UUID) and TOPIC_NAME (display string). Using TOPIC_ID as the join key protects against topic renames in Gladly.

3. Handle time — direct join via CONVERSATION_ID¶

fct_wbp_contacts carries CONVERSATION_ID. The handle time aggregate inner-joins contacts to topics on this key directly — no URL parsing or fuzzy matching required. One contact belongs to one conversation; a conversation may have N active topics, producing N handle time rows.

The join is INNER (contacts with no active topics are excluded) — this is the correct behavior for a topic aggregate.

4. FCR attribution — last-closed agent¶

FCR is attributed to the agent who last closed the conversation (INITIATOR_AGENT_ID_LAST_CLOSED), consistent with agg_wbp_fcr_channel_daily. This requires a re-join to bi_roster_warbyparker in the agg itself (rather than using the EID from fct_wbp_conversations, which joins on the first-close agent). The distinction matters when a conversation is reopened and closed by a different agent.

5. QA — URL parsing to resolve conversation ID¶

The QA Google Sheets source (wbp_gsheet_qa, wbp_gsheet_qa_partner) stores a link column containing a Gladly URL manually entered by the auditor. Two URL patterns are in use:

The Gold QA fact models (fct_wbp_qa_internal, fct_wbp_qa_partner) extract three IDs from the link using REGEXP_SUBSTR capture groups:

REGEXP_SUBSTR(data.link, '/customer/([^/]+)',       1, 1, 'e', 1) AS qa_customer_id,
REGEXP_SUBSTR(data.link, '/conversation/([^/?#]+)', 1, 1, 'e', 1) AS qa_conversation_id,
REGEXP_SUBSTR(data.link, '/item/([^/?#]+)',          1, 1, 'e', 1) AS qa_item_id,

The aggregate then resolves the conversation ID via:

COALESCE(qi.qa_conversation_id, c.CONVERSATION_ID) AS conversation_id

Where c is a LEFT JOIN to fct_wbp_contacts on qa_item_id = c.CONTACT_ID — bridging the item (contact) URL pattern through to its parent conversation. The COALESCE handles both patterns: direct conversation ID first, contact bridge as fallback.

QA evaluations where neither pattern resolves a conversation (conversation_id IS NULL) are excluded from the topic aggregate — they cannot be matched to topics.

The QA aggregate includes both internal and external evaluations, distinguished by an evaluation_type column ('internal' / 'external'). The rpt_ view collapses both into combined totals.

6. CSAT — fuzzy join via customer ID extracted from INTERACTION_ID¶

The CSAT Google Sheet does not expose a conversation ID. The INTERACTION_ID field contains a Gladly URL similar to the QA link, but the conversation segment is present in only a small fraction of records — too few to justify a direct join. Instead, the customer ID is extracted:

REGEXP_SUBSTR(data.INTERACTION_ID, '/customer/([^/]+)', 1, 1, 'e', 1) AS gladly_customer_id

The aggregate then fuzzy-joins CSAT to conversations using:

fct_wbp_csat.gladly_customer_id = fct_wbp_conversations.CUSTOMER_ID
AND DATE_TRUNC('week', csat.date) = DATE_TRUNC('week', conv.LAST_CLOSED_AT)
AND csat.channel_norm = UPPER(conv.LAST_CHANNEL)

Why weekly, not daily: CSAT surveys are submitted by customers after the interaction, sometimes 1–2 days later. Weekly granularity accommodates this submission lag. This matches the legacy approach.

Known limitation: A single CSAT response may match multiple conversations if the same customer contacted via the same channel more than once in the same week. This produces inflated counts. This was also true in the legacy system and is accepted given the absence of a direct conversation join key in the CSAT source.

CSAT records where gladly_customer_id is NULL (INTERACTION_ID is not a Gladly URL) are excluded from the topic aggregate.

7. Channel normalization¶

All aggregates normalize channel to the same display format for consistent joining in the rpt_ spine:

8. inbox_when_queued — deferred¶

The legacy WARBYPARKER_METRICS_BY_TOPIC view included an inbox_when_queued dimension. The Gold topic aggregates intentionally omit it: the Gold fact models do not expose inbox/queue at the per-topic grain in a way that aligns across all four metrics. This dimension can be added in a future iteration if needed.

9. rpt_ spine pattern¶

rpt_wbp_metrics_topic_daily joins all four topic aggregates via a spine CTE (UNION of distinct grain keys from all four sources) with LEFT JOINs from the spine to each metric. This ensures rows appearing in any one metric appear in the output even when absent in others — e.g. a day with CSAT data but no contacts still appears with NULL handle time columns.

Consequences¶

Improvements over legacy: - Topics stored as normalized rows (one per topic) instead of denormalized topic_1/2/3 columns — no UNPIVOT needed at query time - TOPIC_ID included for rename-safe joins - Daily grain for all metrics, including CSAT (previously weekly-only) - QA conversation resolution uses actual URL parsing rather than the conversation export's topic list - FCR correctly attributed to last-closed agent

Inherited limitations: - CSAT topic attribution is inherently approximate (fuzzy join) — accepted given source constraints - QA evaluations with unresolvable links (<1%) are excluded - inbox_when_queued not included (deferred)

Related¶

• ADR 008 — Gold Layer Architecture
• ADR 019 — Gold Layer Standardization
• apps/snowflake-schema/operations/aggregate/R__warbyparker_metrics_per_topic.sql — legacy view being replaced