---
title: Add QB EPA data view column from play-by-play data
type: task
status: completed
description: >-
  Add a player_quarterback_epa_from_plays data view column that aggregates the nflfastR qb_epa field
  per quarterback, requiring a new derived qb_pid column on nfl_plays to identify the responsible QB
  on every play type
base_uri: user:task/league/add-qb-epa-data-view-column.md
created_at: '2026-03-29T17:44:38.044Z'
entity_id: ece302b9-0bef-4e46-bd21-fa992524993a
finished_at: '2026-04-05T04:09:19.843Z'
observations:
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
owner_identity_uri: █████████████████████████
priority: Medium
public_read: true
started_at: '2026-04-05T03:57:32.241Z'
tags:
  - user:tag/league-xo-football.md
updated_at: '2026-05-25T19:22:53.595Z'
visibility_analyzed_at: '2026-11-05T23:48:16.207Z'
---

# Implementation Plan: Add QB EPA Data View Column

## Overview

- Add a `player_quarterback_epa_from_plays` data view column that aggregates the nflfastR `qb_epa` field per quarterback from play-by-play data
- Requires adding a derived `qb_pid` column to the `nfl_plays` table to identify the responsible QB on every play (pass, rush, and special teams), since no existing pid column universally identifies the QB
- The `qb_epa` field is already imported and stored on every play; the gap is player-level attribution

## Background

- The `nfl_plays` table has a `qb_epa` field (numeric, nflfastR-exclusive) set on all plays -- pass, rush, and special teams
- Existing EPA data view columns use the `epa` field with role-based pid columns:
  - `player_total_expected_points_added_from_plays` -- `SUM(epa)` via `psr_pid`, `bc_pid`, `trg_pid`
  - `player_passing_expected_points_added_from_plays` -- `SUM(epa)` via `psr_pid`
  - `player_rushing_and_receiving_expected_points_added_from_plays` -- `SUM(epa)` via `bc_pid`, `trg_pid`
- The `qb_epa` field differs from `epa` on ~62 pass plays per season (turnover attribution adjustments, avg difference ~4.7 EPA per play)
- No existing pid column identifies the QB on all play types:
  - Pass plays (20k/yr): `psr_pid` identifies QB
  - QB scramble/kneel (1.5k/yr): `bc_pid` identifies QB
  - Non-QB rush plays (14k/yr): no pid column references the QB -- these represent -736 total `qb_epa` in 2024
  - Special teams (13k/yr): no pid column references the QB
- The `player_stat_from_plays` factory and `add_player_stats_play_by_play_with_statement` CTE builder support arbitrary pid columns via the `pid_columns` array and `COALESCE(pid_columns) as pid` pattern
- The `qb_epa` column param is already defined in `nfl-plays-column-params.mjs` (range -80 to 80, EXPECTED_POINTS group)

## Design

- Add a `qb_pid` column (`varchar(25)`) to the `nfl_plays` table to identify the responsible quarterback on every play
- Create a standalone backfill script (`scripts/populate-qb-pid.mjs`) that derives `qb_pid` using SQL:
  - Pass plays: `psr_pid`
  - QB scramble/kneel plays: `bc_pid`
  - All other plays: the most frequent `psr_pid` for the same `esbid` + `pos_team` (the primary QB for that team in that game)
- Integrate `qb_pid` derivation into the existing play processing pipeline so new plays get `qb_pid` set automatically
- Add a new data view column `player_quarterback_epa_from_plays` using the `player_stat_from_plays` factory with `pid_columns: ['qb_pid']` and `with_select_string: 'SUM(qb_epa)'`
- Register the field in `data-view-fields-index.mjs`
- Schema changes applied directly to production database per project conventions, then exported via `yarn export:schema`

## Notes

- The `qb_pid` derivation using most-frequent-passer-per-game handles mid-game QB changes by attributing non-pass plays to the primary QB, consistent with how nflfastR calculates `qb_epa` itself
- The backfill script follows the existing `populate-*.mjs` pattern with year-based processing and dry-run support
- The `qb_pid` column could also be useful for future QB-specific data view columns beyond EPA (e.g., QB win probability added)
- `qb_pid` should be indexed for query performance since it will be used in data view CTE GROUP BY clauses
- On special teams plays, `qb_pid` will still be set (to the primary QB) but `qb_epa` values on these plays are generally not meaningful for QB evaluation -- column params can filter these out

## Plan

### Database Schema

- [ ] **Add `qb_pid` column to `nfl_plays` table**
  - Run ALTER TABLE to add `qb_pid varchar(25)` to the `nfl_plays` table and all year partitions
  - Add index on `qb_pid` matching the pattern of existing pid column indexes
  - Export updated schema via `yarn export:schema`
  - File: `db/schema.postgres.sql` (updated via export)

### Backfill Script

- [ ] **Create `scripts/populate-qb-pid.mjs`**
  - File: `scripts/populate-qb-pid.mjs`
  - Purpose: Backfill `qb_pid` for all historical play data
  - Changes:
    - Accept `--year` parameter (default current season), `--dry-run` flag
    - For each year, run a single UPDATE query that derives `qb_pid` using a CTE:
      - CASE WHEN pass = true AND psr_pid IS NOT NULL THEN psr_pid
      - WHEN (qb_scramble = true OR qb_kneel = true) AND bc_pid IS NOT NULL THEN bc_pid
      - ELSE subquery for most frequent psr_pid per esbid + pos_team
    - Log rows updated per year
    - Follow `is_main` pattern and snake_case naming conventions

### Play Processing Integration

- [ ] **Add `qb_pid` to play enrichment pipeline**
  - File: `scripts/process-plays.mjs`
  - Purpose: Ensure newly processed plays get `qb_pid` set
  - Changes:
    - Add `qb_pid` to `ENRICHED_FIELD_NAMES` array
    - Derive `qb_pid` in the enrichment logic using the same rules as the backfill script
  - Alternative: Could be handled as a post-processing step called after `process_plays` completes, using the same SQL approach as the backfill script scoped to the processed game(s)

### Data View Column Definition

- [ ] **Add `player_quarterback_epa_from_plays` column definition**
  - File: `libs-server/data-views-column-definitions/player-stats-from-plays-column-definitions.mjs`
  - Purpose: Define the QB EPA data view column
  - Changes:
    - Add new entry after the existing EPA column definitions (after line 879):
      ```
      player_quarterback_epa_from_plays: player_stat_from_plays({
        pid_columns: ['qb_pid'],
        with_select_string: 'SUM(qb_epa)',
        stat_name: 'quarterback_epa_from_plays'
      })
      ```

### Field Registration

- [ ] **Register field in data view fields index**
  - File: `libs-shared/data-view-fields-index.mjs`
  - Purpose: Add human-readable description for the new column
  - Changes:
    - Add entry after the existing EPA field descriptions (after line 493):
      ```
      player_quarterback_epa_from_plays:
        'The total QB EPA attributed to the quarterback across all plays in the game, using the nflfastR qb_epa metric which adjusts for turnover-worthy plays (calculated from play-by-play data)'
      ```

### Testing and Validation

- [ ] **Validate backfill results**
  - Run backfill for 2024 season with `--dry-run` first
  - Verify `qb_pid` is set on all plays where `qb_epa` is not null
  - Spot-check that `qb_pid` matches expected QBs for known games
  - Compare `SUM(qb_epa)` grouped by `qb_pid` against known QB EPA rankings

- [ ] **Validate data view column**
  - Query the data view API with the new column
  - Verify results are reasonable and match expected QB EPA values
  - Test with year splits and rate types (per_game, per_team_play)