AI Agents Durations Daily¶

Table: WBP_DB.GOLD.RPT_AI_AGENTS_DURATIONS_DAILY Materialization: View (zero storage) Replaces: OPERATIONS.AGGREGATE.WARBYPARKER_RPT_AI_AGENTS_DURATIONS_DAILY

Overview¶

Daily duration report for AI-enabled Warby Parker agents. Tracks time spent in each status type (ACTIVE, ACW, AWAY, etc.) broken down by agent, date, type, and context. Includes CONTEXT_COUNT to enable average duration per context calculations (e.g. ACW by context type).

Audience: Operations analytics, Tableau dashboards, QA/coaching workflows.

Data Sources¶

#	Source	Purpose	Join Key
1	Gladly API `AgentDurationsReportV2`	Primary — raw status span records	—
2	`CORE_DB.SILVER.ROSTER_WARBYPARKER`	BambooHR roster — provides `DIRECT_LEADER`	`GLADLY_NAME = AGENT`
3	dbt seed `ai_agents.csv`	Static AI agent list — provides `WAVE` assignment	`AGENT_NAME = AGENT`

Pipeline / Data Flow¶

flowchart LR
    A[Gladly API\nAgentDurationsReportV2] -->|Airflow extract| B[S3 Ingest\nagent_durations_DATE.csv]
    B -->|Copy-then-Peek| C[S3 Stage\nstandardized headers]
    C -->|COPY INTO\nbatch_replace| D[WBP_DB.BRONZE\nGLADLY_AGENT_DURATIONS]
    D -->|dbt incremental| E[WBP_DB.SILVER\nAGENT_DURATIONS]
    E -->|dbt view| F[WBP_DB.GOLD\nRPT_AI_AGENTS_DURATIONS_DAILY]
    G[CORE_DB.SILVER\nROSTER_WARBYPARKER] -->|LEFT JOIN| F
    H[dbt seed\nai_agents.csv] -->|INNER JOIN| F

Schedule & Refresh¶

Property	Value
Airflow DAG	`warbyparker_gladly_daily`
Schedule	`50 6 * * *` — 6:50 AM EST, every day
Time window	Previous day's data (lag: 1 day)
Timezone	`America/New_York`
Retries	2 × 5 min
Bronze strategy	`batch_replace` (DELETE WHERE BATCH_ID + COPY INTO)

Target Endpoint¶

WBP_DB.GOLD.RPT_AI_AGENTS_DURATIONS_DAILY

Materialized as a view — no storage cost; queries compute on demand from Silver. Part of the Gold report layer (rpt_ prefix convention).

Column Reference¶

Column	Type	Source	Description
`DATE`	DATE	Silver (`start_time::DATE`)	Date of agent duration activity
`AGENT`	VARCHAR	Gladly API	Agent name from status span record
`WAVE`	VARCHAR	dbt seed `ai_agents.csv`	AI wave assignment (`Wave 1` / `Wave 2`)
`DIRECT_LEADER`	VARCHAR	`ROSTER_WARBYPARKER.GLADLY_NAME`	Agent's direct leader from BambooHR (nullable)
`TYPE`	VARCHAR	Gladly `TYPE_FIELD`	Duration type — `ACTIVE`, `ACW`, `AWAY`, etc.
`CONTEXT`	VARCHAR	Gladly `CONTEXT`	ACW sub-type context; `null` for non-ACW types
`TOTAL_DURATION_MIN`	FLOAT	Computed	Sum of `duration_mins` per agent/date/type/context group
`CONTEXT_COUNT`	INTEGER	Computed	Row count per group; used for average: `total_duration_min / context_count`

Metrics Computed¶

Metric	Formula	Use Case
`TOTAL_DURATION_MIN`	`SUM(duration_mins)` per group	Total time in a status type per day
`CONTEXT_COUNT`	`COUNT(*)` per group	Denominator for avg duration per context
Average duration per context	`TOTAL_DURATION_MIN / CONTEXT_COUNT`	Compute in BI layer (Tableau)

Duration types (from Gladly TYPE_FIELD): ACTIVE, ACW, AWAY, and others as returned by the API.

Context is populated only for ACW rows, providing the sub-type breakdown (e.g. wrap-up reason codes).

AI Agents in Scope¶

The view uses an INNER JOIN on the ai_agents seed — only agents in this table appear in results.

Wave 1 — Enabled 2026-01-21¶

Agent
Angelica Madrid
Aubrey Sombilon
Dianne Grace Emar
Kyna Montorio
Mariel Perez
Michael Louis Cavile
Myrna Gabion
Rodmer De Los Reyes
Sanshe Surmieda
Terence John Haro

Wave 2 — Enabled 2026-02-02¶

Agent
Anjanette Baytos
Carlo Gian Chin
Fritz Joshua Fernandez
Jeffrey Sormillo
Joseph Torrena
Raffy Tampos
Shaina Rica Palmares

Known Limitations / Notes¶

Non-AI agents excluded: The INNER JOIN on ai_agents.csv means any agent not in the seed will not appear in this view, even if they have duration data in Silver.
DIRECT_LEADER can be null: The roster join is a LEFT JOIN — agents not matched in ROSTER_WARBYPARKER will have null for DIRECT_LEADER.
Wave assignment is static: Wave and enablement date come from the dbt seed file, not a live HR system field. Updates require a seed file change and dbt re-run.
Context is sparse: CONTEXT is only populated for ACW type rows. All other types will show null for context.
View latency: This is a view — data reflects the most recent Silver state. Silver is updated by the daily Airflow DAG at ~6:50 AM EST (previous day's data).