---
title: Repair the is_tackle_for_loss undercount, 2001 to 2024
type: task
status: planned
description: >-
  nfl_plays.is_tackle_for_loss is exactly has-stat-95 before 2025 and misses roughly 1,300 real
  tackles for loss per season that carry only stat 120. An operator-gated data repair over about 24
  seasons, derivable from stored play stats.
base_uri: user:task/league/repair-is-tackle-for-loss-undercount.md
created_at: '2026-09-21T15:40:02.173Z'
entity_id: 7db50442-610f-4766-b263-63263327c59a
observations:
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
owner_identity_uri: █████████████████████████
priority: Medium
public_read: true
relations:
  - blocked_by [[███████████████████████-████-████-███████-███████-████-████-████████]]
  - relates [[user:task/league/register-drift-check-for-play-stat-derived-columns.md]]
  - relates [[███████████████████████████████-█████████-████████]]
tags:
  - user:tag/league-xo-football.md
  - user:tag/software-task.md
updated_at: '2026-09-21T15:51:48.285Z'
visibility_analyzed_at: '2026-11-05T23:50:06.197Z'
---

# Implementation Plan: Repair the is_tackle_for_loss undercount, 2001 to 2024

## Overview

- `nfl_plays.is_tackle_for_loss` is missing roughly 1,300 real tackles for loss in every regular season before 2025. This is an UNDERCOUNT, not a rival definition, and the evidence to repair it has been in `nfl_play_stats` the whole time.
- Expected outcome: the column means the same thing in every season, and any cross-season rate, trend or per-defender ranking stops reading an artificial doubling at 2025.
- **This is an operator-gated DATA change over roughly 24 seasons.** It is deliberately separate from the derivation task, which only changes what gets written going forward.

## Background

Measured 2026-09-21 over REG, non-`NOPL` plays.

GSIS carries two tackle-for-loss stat ids, 95 and 120, and they are NEARLY DISJOINT — most plays carry one or the other, not both. Before 2025 the stored column is exactly "has stat 95":

| season | stored true | plays with stat 95 | plays with only stat 120 | only-120 stored true |
| --- | --- | --- | --- | --- |
| 2015 | 1,362 | 1,362 | 1,255 | 0 |
| 2022 | 1,228 | 1,228 | 1,297 | 0 |
| 2024 | 1,256 | 1,256 | 1,416 | 0 |
| 2025 | 2,590 | 1,196 | 1,396 | 1,393 |

The roughly 1,300 plays a season carrying only stat 120 are stored `false` before 2025. They are real tackles for loss: **1,274 of the 1,297 in 2022 and 1,407 of the 1,416 in 2024 carry a play description stating a loss**. In 2025 the vendor began honouring stat 120 and marks 1,393 of 1,396 of them true.

The FEED did not change. nflfastR started reading an id that was always present.

## Design

Repair by dated adhoc, not by re-import. The values are derivable from stored `nfl_play_stats`, so nothing needs to be fetched:

```
is_tackle_for_loss = bool_or(stat_id in (95, 120))  -- over is_valid = true rows
```

### 2005 to 2007 cannot be repaired and must be recorded as a permanent seam

Stat 120 is effectively missing from the feed in those three seasons — **155, 33 and 181 plays carry it, against 2,397 in 2004 and 2,330 in 2008**. Stat 95 is present and normal throughout at roughly 1,300. So the repair returns close to the narrow count for those three, and nothing in `nfl_play_stats` can recover the difference.

A repaired table will therefore carry 2005-2007 as a visible trough that is a collection gap rather than football. That is already on [[███████████████████████████████-█████████-████████]] and must survive the repair rather than be smoothed over.

### 2002 is a separate collection gap

898 plays with stat 95 and 1,158 with stat 120, against roughly 1,400 and 2,400 in the seasons either side. Both ids are down, so it is not 120-specific. Any repaired 2002 figure is still suspect.

## Plan

- [ ] **Confirm the derivation task has landed first** — `████████████████████████████████████████████████`. Repairing history before the live writer agrees with it would leave the two eras disagreeing again from the next game onward.
- [ ] **Read `docs/guides/schema.md`** before applying anything, per the repo's routing table.
- [ ] **Write the adhoc under `private/db/adhoc/`**, dated, gated on the populations it was validated against, following the precedent of `2026-09-19-derive-is-fumble-lost-from-gsis-stat-106.sql`.
- [ ] **Measure before and after, per season, and state both.** The expected shape is stored-true roughly doubling for every season except 2002 and 2005-2007.
- [ ] **Verify the repair is reversible.** `play_changelog` must carry a row per flip. Confirm the changelog is actually written by this path before running it at scale, not after.
- [ ] **Run a control season first** — one season, verify, then widen. Do not sweep 24 seasons on the first invocation.
- [ ] **Update the query-trap sheet** once repaired: the undercount section becomes historical, and the 2002 and 2005-2007 seams become the live warning.