RPT Email Daily¶

View: OPERATIONS.AGGREGATE.WARBYPARKER_RPT_EMAIL_DAILY Materialization: View (zero storage) Database: OPERATIONS (legacy — not medallion)

Overview¶

Daily email performance metrics for AI-enabled Warby Parker agents. Each row represents one agent's aggregate email activity for a calendar day. The view is the direct data source for the wbp_xoos_analytics_push Airflow DAG, which queries it every morning to push daily and weekly metrics to the XOOS analytics ingest API.

Audience: XOOS platform, operations analytics, Tableau dashboards, QA/coaching workflows.

Data Sources¶

Dependency Flowchart¶

flowchart LR
    subgraph Bronze["Bronze Tables — OPERATIONS.STAGING"]
        B1["WARBYPARKER_CONTACT_TIMESTAMPS_HISTORICAL"]
        B2["WARBYPARKER_WORK_SESSIONS_HISTORICAL"]
        B3["WARBYPARKER_AGENT_DURATIONS_REPORT_HISTORICAL"]
    end

    subgraph StagingDT["Staging Dynamic Tables — OPERATIONS.STAGING"]
        S1["STAGE_WARBYPARKER_CONTACTS_QUEUED"]
        S2["STAGE_WARBYPARKER_CONTACTS_ENDED"]
        S3["STAGE_WARBYPARKER_CONTACTS_ACCEPTED"]
        S4["STAGE_WARBYPARKER_CONTACTS_WORK_SESSIONS"]
        S5["STAGE_WARBYPARKER_CONTACTS_HOLD"]
        S6["STAGE_WARBYPARKER_CONTACTS_MESSAGES"]
        S7["STAGE_WARBYPARKER_CONTACTS_EVENTS"]
        S8["STAGE_ROSTER_WARBYPARKER"]
    end

    subgraph AggLayer["Aggregate / BambooHR Layer"]
        A1["WARBYPARKER_CONTACTS_MODEL\n(OPERATIONS.AGGREGATE — Dynamic Table)"]
        A2["ROSTER_WARBYPARKER\n(OPERATIONS.BAMBOOHR — Dynamic Table)"]
    end

    subgraph Output["Output — OPERATIONS.AGGREGATE"]
        V["WARBYPARKER_RPT_EMAIL_DAILY\n(View)"]
    end

    B1 --> S1 & S2 & S3 & S5 & S6 & S7
    B2 --> S4
    S1 & S2 & S3 & S4 & S5 & S6 & S7 --> A1
    A2 --> A1
    S8 --> A2

    A1 -->|"email_contacts CTE\nwork_session_handle_time CTE"| V
    B1 -->|"email_messages CTE"| V
    B2 -->|"work_session_handle_time CTE\n(direct join)"| V
    B3 -->|"active_time CTE"| V
    A2 -->|"LEFT JOIN"| V

Dependency Reference¶

Schedule & Refresh¶

Dynamic Table latency note: WARBYPARKER_CONTACTS_MODEL and ROSTER_WARBYPARKER both use target_lag = DOWNSTREAM. This means they are refreshed lazily by Snowflake when the view is queried by the DAG, not on a fixed schedule. Freshness depends on when the upstream HISTORICAL Bronze tables were last loaded.

Column Reference¶

Metrics Computed¶

AHT sources: Two AHT columns exist because Gladly has two grains — contact-level handle time (summed across all agents who touched the contact) and work-session-level handle time (this agent's sessions only). AGENT_AHT_MIN is generally preferred for per-agent performance.

AI Agents in Scope¶

The view uses an INNER JOIN on a hardcoded ai_agents CTE — only the 17 agents below appear in results regardless of other activity.

Wave 1 — Enabled 2026-01-21¶

Wave 2 — Enabled 2026-02-02¶

Known Limitations¶

• Hardcoded agent list: The ai_agents CTE is a VALUES literal in the view SQL. Adding or removing agents requires editing R__warbyparker_rpt_email_daily.sql and redeploying via schemachange. A TODO in the SQL notes the future intent to replace this with a roster AA_STATUS = 'Enabled' filter once that field is reliably populated.
• HISTORICAL table latency: The three Bronze HISTORICAL tables (CONTACT_TIMESTAMPS, WORK_SESSIONS, AGENT_DURATIONS) are loaded by separate Airflow pipelines on their own schedules. The wbp_xoos_analytics_push DAG at 7 AM EST assumes upstream data for the previous day is already present.
• Dynamic Table DOWNSTREAM lag chain: WARBYPARKER_CONTACTS_MODEL and ROSTER_WARBYPARKER are both target_lag = DOWNSTREAM. They refresh lazily when queried. If Bronze tables are delayed, the Snowflake refresh triggered by the DAG query will read stale data silently — no error is raised.
• No AA_STATUS filtering yet: ROSTER_WARBYPARKER includes all active roster rows. The view currently uses the hardcoded agent list as a proxy for "AI-enabled." Future work (see SQL TODO) will filter by AA_STATUS.
• EID / ACCOUNT_EMAIL can be null: The roster join is a LEFT JOIN on GLADLY_NAME. Agents not matched in the roster will have null for these columns, which breaks the XOOS RECORD_KEY (EID || date || client_id). Monitor for null EIDs in the push task output.
• CONTACTS_WORKED ≠ CONTACTS_FULFILLED: CONTACTS_WORKED counts contacts where the agent sent at least one message. CONTACTS_FULFILLED counts contacts the agent was the fulfilling agent. An agent may message a contact without being the one to fulfill it.