Condé Nast GPP Scorecard — Metric Logic Explained¶

Reference document explaining how OPERATIONS.AGGREGATE.CONDENAST_SCORECARD_GPP turns raw daily agent activity into the GPP score, and what each of the metrics/columns that compose it actually means.

Model: OPERATIONS.AGGREGATE.CONDENAST_SCORECARD_GPP (dynamic table) Source file: apps/snowflake-schema/operations/aggregate/R__condenast_scorecard_gpp.sql Grain: one row per ROLE × MONTH × agent-or-leader × METRIC × CHANNEL

Inputs:

Source	Provides
`ods.condenast_scorecard_goals`	Per-metric/channel weights (`METRIC_WEIGHT`, `BEFORE_SALES_WEIGHT`, `CHANNEL_WEIGHT`) and the 5-tier rank thresholds (`RANK1`–`RANK5`)
`aggregate.condenast_agent_metrics_daily`	Daily per-agent, per-channel activity (calls, handle time, QA Maestro evaluations, FCR, sales topics)
`bamboohr.roster_condenast`	`AGENT_STATUS` and `hire_date` (for tenure)

1. Two roles, two build paths¶

The final table is a UNION ALL of two CTEs:

CSR (agent_scorecard_prep) — one agent, keyed by EID. Rows only exist for a (month, eid, channel) combination where the agent actually had inbound volume that month, because this CTE does an INNER JOIN to missing_agent_metrics (which requires has_interactions = TRUE). An agent who never touched Chat that month simply has no Chat rows.
Team Lead (team_leader_scorecard_prep) — keyed by direct_leader, built by summing the raw daily metrics across everyone reporting to that leader for the channel/metric. It is not an average of the CSRs' individual scorecards, and it skips the missing_agent_metrics gating entirely (there is no INNER JOIN), so leader rows always exist wherever the team had any volume.

Both CTEs share the same formulas from METRIC_SCORE onward; the difference is purely in the population being summed and how the "no activity" fallback is applied (see HAS_INTERACTIONS_IN_PERIOD below — it's always NULL at the Team Lead grain).

2. The four KPI metrics (`METRIC_SCORE`)¶

Metric	Formula (`METRIC_SCORE`)	Target (`TARGET_SCORE`)	Explanation
AHT	`SUM(total_handle_sec) / SUM(total_calls)`	Phone 450s · Chat 450s · Email 315s	Average handle time in seconds per inbound contact. Lower is better — the only metric ranked on an inverted scale.
QA MANUAL	`SUM(qa_maestro_score) / SUM(qa_maestro_evaluations)`	0.90 (all channels)	Average score from QA Maestro evaluations. Note this reads `qa_maestro_evaluations`/`qa_maestro_score`, not the legacy Medallia `QA_EVALUATIONS`/`QA_SCORE` columns on the same table. If the agent had zero QA Maestro evaluations that channel/month, the row falls back to a neutral rank of 3 (see `CHANNEL_METRIC_RANK`) instead of being penalized or left unranked.
FCR	`SUM(fcr_success) / SUM(fcr_eligible)`	Phone 0.95 · Chat 0.85 · Email 0.75	First-contact-resolution rate. Same "no eligible contacts → neutral rank 3" fallback as QA MANUAL.
SALES	`ZEROIFNULL( (SUM(upsells)+SUM(xsells)) / NULLIFZERO(SUM(total_opportunities)+SUM(upsells)+SUM(xsells)) + SUM(saved_members) / NULLIFZERO(SUM(cancellation_requests)+SUM(saved_members)) )`	0.18 (all channels)	The only metric that adds two independent rates together — an upsell/cross-sell conversion rate plus a cancellation-save rate. This means `METRIC_SCORE` for SALES isn't bounded at 1.0; it can range 0–2. SALES is never computed for the Email channel — every SALES column (`INTERACTIONS`, `SALES_VOLUME`, `CHANNEL_METRIC_RANK`, …) is forced `NULL` there.

TARGET_SCORE is hardcoded in this file as a CASE on metric + channel, not sourced from ods.condenast_scorecard_goals.CHANNEL_GOAL even though that column exists. Any channel other than Phone/Chat/Email (SMS, Voicemail, SoMe - DM, SoMe - Comments, Training) gets TARGET_SCORE = NULL for every metric, which cascades PTG and CHANNEL_METRIC_RANK to NULL as well.

3. From raw activity to the final score¶

Column	Formula	Explanation
`HAS_INTERACTIONS_IN_PERIOD`	Metric-mapped boolean from `missing_agent_metrics`: AHT→`has_interactions`, QA MANUAL→`has_qa_maestro_evaluations`, FCR→`has_fcr_eligible`, SALES→`has_sales_topics`	Whether the agent had any volume for this metric type in this channel/month. Only used to trigger the QA MANUAL / FCR fallback below. Always `NULL` for Team Lead rows.
`INTERACTIONS`	AHT: `SUM(total_calls)` · QA MANUAL: `SUM(qa_maestro_evaluations)` (or `1` if no evaluations) · FCR: `SUM(fcr_eligible)` (or `1` if none eligible) · SALES: `SUM(total_opportunities + xsells + upsells + cancellation_requests + saved_members)`, `NULL` for Email	The "volume" for this metric/channel row. The `1` fallback for QA MANUAL/FCR exists so a metric with zero real volume still gets a non-zero weight downstream instead of dividing by zero.
`SALES_VOLUME`	`SUM(xsells + upsells + saved_members)`, `NULL` for Email or non-SALES metrics	Count of successful sales actions only (excludes cancellations and lost opportunities). Passed straight through to the output — not consumed anywhere else in the ranking math.
`TOTAL_INTERACTIONS`	`SUM(interactions)` windowed over `(month, agent/leader, direction, metric)`	This metric's total volume across all channels for that agent/leader that month.
`INTERACTIONS_RATIO`	`ROUND(interactions / NULLIFZERO(total_interactions), 4)`	This channel's share of the agent's total volume for that metric (e.g. 0.80 if 80% of QA-evaluated contacts were on Phone). Used twice downstream — once to blend channels into `METRIC_RANK`, again to weight `METRIC_RANK` into `WEIGHTED_RANK`.
`METRIC_SCORE`	See §2	The raw KPI value.
`TARGET_SCORE`	See §2	The goal `METRIC_SCORE` is compared against.
`PTG`	`metric_score / target_score`	"Percent to goal." For AHT, lower `PTG` is good; for the other three, higher is good.
`CHANNEL_METRIC_RANK`	Buckets `PTG` against `RANK1`–`RANK5` from `ods.condenast_scorecard_goals`. AHT uses `<=` comparisons (lower is better); the other three use `>=`. SALES+Email is forced `NULL`. QA MANUAL/FCR with `HAS_INTERACTIONS_IN_PERIOD = FALSE` is forced to `3`.	Converts the continuous `PTG` into a discrete 1 (worst) – 5 (best) tier using client-defined thresholds. Boundary note: the `CASE` ladder has a gap — a `PTG` strictly between `RANK1` and `RANK2` (or, for AHT, strictly between `RANK2` and `RANK1`) matches none of the branches and falls to `ELSE NULL`, unless the goal thresholds happen to be contiguous.
`METRIC_WEIGHT`	`CASE WHEN month < '2025-05-01' THEN sg.before_sales_weight ELSE sg.metric_weight END`	Importance weight for this metric. Scorecards before May 2025 use a different weight column (`BEFORE_SALES_WEIGHT`) than scorecards from May 2025 onward, reflecting a rebalancing when SALES weighting changed.
`CHANNEL_WEIGHT`	`sg.channel_weight`	Importance weight for this channel. No date-based variant — same value regardless of month.
`WEIGHTED_METRIC_RANK`	`ROUND(channel_metric_rank * interactions_ratio, 2)`	Scales this channel's tier by how much of the agent's volume for that metric happened on this channel.
`METRIC_RANK`	`SUM(weighted_metric_rank)` windowed over `(month, agent/leader, metric)`	Collapses the volume-weighted per-channel tiers into one blended rank per metric (e.g. a single AHT rank blending Phone/Chat/Email). Repeated on every row for that metric.
`WEIGHTED_CHANNEL_RANK`	`ROUND(channel_metric_rank * channel_weight, 2)`	Same idea as `WEIGHTED_METRIC_RANK`, but weighted by the channel's static importance (`CHANNEL_WEIGHT`) instead of the agent's own volume share.
`CHANNEL_RANK`	`SUM(weighted_channel_rank)` windowed over `(month, agent/leader, channel_formatted)`	Collapses the four metrics into one composite rank per channel per agent/month. This column feeds nothing downstream — it isn't referenced in `WEIGHTED_RANK` or `FINAL_RANK`; it's an informational "how's this agent doing on this channel overall" figure only.
`WEIGHTED_RANK`	`ROUND(metric_rank * metric_weight * interactions_ratio, 2)`	This row's contribution to the agent's overall score: the metric-level blended rank (`METRIC_RANK`, identical across all of that metric's channel rows) × the metric's importance (`METRIC_WEIGHT`) × this channel's volume share (`INTERACTIONS_RATIO`). The volume-share multiplication re-splits the already-blended metric rank back across channels so that summing `WEIGHTED_RANK` over all rows reconstructs `metric_rank * metric_weight` per metric without double-counting.
`FINAL_RANK`	`SUM(weighted_rank)` windowed over `(role, month, name)`	The GPP score — sum of every metric × channel row's `WEIGHTED_RANK` for that agent/leader that month.
`AGENT_STATUS`, `TENURE_DAYS`	`LEFT JOIN bamboohr.roster_condenast hr ON hr.eid = data.eid`; `TENURE_DAYS = DATEDIFF('day', hr.hire_date, month + INTERVAL '1 month')`	Roster status and tenure as of month-end. `NULL` for Team Lead rows since those have no `EID` to join on.

4. Quick mental model¶

condenast_agent_metrics_daily  ──┐
                                  ├─► METRIC_SCORE vs TARGET_SCORE ─► PTG ─► CHANNEL_METRIC_RANK (1-5)
condenast_scorecard_goals ───────┘
                                        │                         │
                                        │ × interactions_ratio    │ × channel_weight
                                        ▼                         ▼
                                  WEIGHTED_METRIC_RANK       WEIGHTED_CHANNEL_RANK
                                        │ Σ over channels         │ Σ over metrics
                                        ▼                         ▼
                                   METRIC_RANK                CHANNEL_RANK (informational only)
                                        │ × metric_weight × interactions_ratio
                                        ▼
                                  WEIGHTED_RANK
                                        │ Σ over all metric × channel rows
                                        ▼
                                  FINAL_RANK  ◄── the GPP score

Condé Nast GPP Scorecard — Metric Logic Explained¶

1. Two roles, two build paths¶

2. The four KPI metrics (METRIC_SCORE)¶

3. From raw activity to the final score¶

4. Quick mental model¶

2. The four KPI metrics (`METRIC_SCORE`)¶