# HeatWave Data Quality Report — `keywords.adv_clicks_{advId}_new`

**Prepared for:** the DB → HeatWave migration owner
**Date:** 2026-08-17
**Server:** `10.100.12.165`, MySQL 9.6.1-u1-cloud, schema `keywords`
**Context:** Breakdown View in adcenter now reads HeatWave as its system of record. Every
gap below surfaces directly in the advertiser-facing UI as a blank column or a zero metric.

---

## How this was measured

One aggregate pass over **every** `adv_clicks_*_new` table (137 tables, 0 query errors),
counting non-empty values and the last populated date per column, restricted to the
**last 45 days**. 98 of the 137 tables had rows in that window; percentages below are
against those 98 "active" tables.

Reproduce with the query shape:

```sql
SELECT COUNT(*),
       MAX(date),
       SUM(<col> IS NOT NULL AND <col> <> '')                       AS filled,
       MAX(CASE WHEN <col> IS NOT NULL AND <col> <> '' THEN date END) AS last_populated
FROM keywords.adv_clicks_<advId>_new
WHERE date >= DATE_SUB(CURDATE(), INTERVAL 45 DAY);
```

Schema itself is **clean**: all 137 tables have an identical 39-column set, and it is a
strict superset of the legacy `adv_clicks_{advId}_YYYYMM` shards (24 columns, none
dropped). No column is missing. Every issue below is a *population* problem.

---

## Severity summary

| # | Issue | Scope | Impact |
| --- | --- | --- | --- |
| 1 | Geo enrichment (`state`/`city`/`zip`) stopped **2026-07-29** | 74 advertisers (58 on that exact date) | P0 — Locations tab blank for all recent dates |
| 2 | `est_revenue` unusable | 61 of 98 empty; populated ones are wrong by ~1000× | P0 — Est. Revenue and ROAS read 0 everywhere |
| 3 | `conversions` missing or stopped | 45 never populated + 18 stopped mid-window | P1 — Conversions, CVR, CPA read 0 |
| 4 | Real landing page absent — `landing_page_url` mirrors `creative_url` (display domain); `destination_url` empty | 1 distinct value for most advertisers | P1 — populate `destination_url` from `Admin.creatives` |
| 5 | `search_query` near-empty | 89 of 98 empty | P2 — Search Term column falls back to keyword |
| 6 | `conversion_type` sparse | 45 of 98 empty | P3 — Conversion Type tab hidden from nav |
| 7 | `{keywordID}` macro leaking into `keyword` | adv 13421 (22 rows), adv 15325 (1 row) | P3 — trafficking bug, not migration |

---

## 1. Geo enrichment stopped on 2026-07-29 — P0

`state`, `city` and `zip` stop populating on the same date across the estate. Day-level
detail for adv 21610 shows a clean cliff, not a decay:

```
2026-07-29   9008 rows   city 95%   zip 99%   state 97%
2026-07-30   8182 rows   city  0%   zip  0%   state  0%
   ... still 0% every day through 2026-08-16
```

- **74 advertisers** affected; **58** stop on exactly `2026-07-29`.
- Includes every large advertiser: 20378, 13421, 21610, 17258, 21795, 16652, 21244,
  20905, 15325, 20932, 18769, 19775.
- Historical data before the cliff is fine (adv 21610 has 10.4M non-empty `state` rows;
  adv 15325 is 80% populated).

**Impact:** the Locations breakdown is the worst hit — Country still resolves, but State
and City are blank for every date after 2026-07-29.

**Ask:** find what changed in the enrichment step on 2026-07-29/30 and backfill from
that date forward.

> Note: `state` *does* exist and *was* populated. adcenter previously assumed the column
> was absent and substituted `zip`, which labelled ZIP codes as states. That has been
> fixed on our side — we now read the real column, so the fix will show through as soon
> as the feed resumes.

---

## 2. `est_revenue` is unusable — P0

Two distinct failures:

**a) Not populated at all** — 61 of 98 active tables. Includes adv 16652, 20378, 21610,
13421.

**b) Populated but wrong by orders of magnitude** — where values do exist they are
implausibly small:

```
adv 17258, HeatWave:
  date         conversions   est_revenue   spend
  2026-08-11       190          1.00      5207.93
  2026-08-13       183          1.00      4730.60
  2026-08-14       179          1.00      5063.69
  2026-08-15       123          1.00      3631.44
```

$1/day of revenue against $5,000/day of spend, on 180 conversions. For comparison,
`Shorty.conv_tracking_20260810` holds **$349,520.59** for adv 16652 on a single day —
the same advertiser whose HeatWave `est_revenue` is NULL.

22 tables also show `est_revenue` stopping mid-window (9 of them on 2026-07-29, same
cliff as the geo columns).

**Impact:** Est. Revenue and ROAS render as 0 on every breakdown tab. We deliberately did
**not** add a Shorty fallback — that would hide the gap from this migration and would
reintroduce two different revenue figures on different tabs (before this was tightened,
Devices showed $348,942 and Search Terms $3 for the same advertiser and period).

**Ask:** confirm the intended source and scale for `est_revenue`, then backfill. If the
unit differs from `conv_value` in `Shorty.conv_tracking` (dollars), tell us and we will
convert on read.

---

## 3. `conversions` missing or stopped — P1

- **45 of 98** active tables have no conversions at all in the last 45 days
  (adv 20378, 21610, 13421 among them).
- **18 more** stopped mid-window while the table kept receiving impression/click rows:

| Advertiser | Last conversion | Table has data to |
| --- | --- | --- |
| 21640 | 2026-07-05 | 2026-08-16 |
| 21244 | 2026-07-07 | 2026-08-16 |
| 20696 | 2026-07-17 | 2026-08-16 |
| 16652 | 2026-07-21 | 2026-08-16 |
| 21893 | 2026-07-21 | 2026-08-16 |
| 16302 | 2026-07-29 | 2026-08-16 |
| 19656 | 2026-07-29 | 2026-08-16 |
| 20170 | 2026-07-29 | 2026-08-16 |
| 20805 | 2026-07-29 | 2026-08-16 |
| 21189 | 2026-07-29 | 2026-08-16 |

adv 16652 is the clearest case of divergence between the two systems: HeatWave stops at
2026-07-21, while `Shorty.conv_tracking` is still receiving its conversions (270 rows /
$349,520 on 2026-08-10 — it is the only advertiser with rows in that table that day).

**Impact:** Conversions, CVR and CPA read 0 for those advertisers.

---

## 4. The real landing page is missing — `landing_page_url` carries the display domain — P1

This is a source-column choice, not a corrupted migration. `landing_page_url` is a
faithful copy of **`Admin.creatives.creative_url`**, verified per creative:

| Advertiser | creatives compared | `landing_page_url` == `creative_url` | == `destination_url` |
| --- | --- | --- | --- |
| 16652 | 65 | 65 (100%) | 0 (0%) |
| 17258 | 20 | 20 (100%) | 0 (0%) |
| 21610 | 15 | 15 (100%) | 0 (0%) |
| 20378 | 494 | 480 (97%) | 0 (0%) |
| 13421 | 479 | 463 (97%) | 0 (0%) |
| 15325 | 76 | 63 (83%) | 0 (0%) |

The problem is that `creative_url` is the **display URL**, not the landing page. adcenter
derives it in `Creative::backfillDisplayUrl()` as:

```php
$creativeData['creative_url'] = parse_url($creativeData['destination_url'], PHP_URL_HOST);
```

— i.e. the host only. So the migrated column holds a bare domain and collapses to a
**single distinct value** for most advertisers:

| Advertiser | creatives active (30d) | distinct `landing_page_url` | distinct `Admin.creatives.destination_url` |
| --- | --- | --- | --- |
| 16652 | 65 | **1** | 62 |
| 17258 | 20 | **1** | 20 |
| 20378 | 710 | **1** | 45 |
| 15325 | 76 | **1** | 76 |
| 21610 | 15 | **1** | 2 |
| 13421 | 572 | 280 | 544 |

```
adv 20378, creative 258987
  Admin creative_url        : amazon.com                                       <- what HeatWave got
  Admin destination_url     : https://www.amazon.com/s?k={keyword}&tag=…       <- the actual landing page
```

Formatting is also inconsistent between advertisers, inherited from `creative_url`:
16652/15325 hold a full `https://…`, 17258/20378/21610 hold a bare domain, 13421 mixes
both (315,886 rows with a scheme, 106,102 without).

**What we changed on our side:** Breakdown View now resolves the landing page for *both*
engines by joining `creative_id → Admin.creatives.destination_url`. That took adv 13421
from one row to real pages (`chipotle.com`, `lookfantastic.com`,
`storystudio.chron.com/…`), unwrapped out of their dartsearch redirect wrappers.

**Ask — the useful one:** the `_new` tables already have an empty `destination_url`
column. Populating it from `Admin.creatives.destination_url` would put the real landing
page in HeatWave and let us drop the cross-DB join to Admin entirely. That is the single
change that makes Breakdown View self-sufficient on HeatWave for this dimension.

Keeping `landing_page_url` as the display domain is fine — it is just misleadingly
named. Consider `display_url` if it is being renamed anyway.

---

## 5. `search_query` near-empty — P2

Populated on only **9 of 98** active tables.

| Advertiser | non-empty `search_query` | total rows |
| --- | --- | --- |
| 17258 | 2,201,162 | 2,284,227 (96%) |
| 20378 | 673,133,253 | 1,744,872,567 (39%) |
| 16652, 21610, 15325, 13421 | 0 | — |

This may be correct — non-search advertisers legitimately have no search term. Worth
confirming which advertisers are *expected* to carry it, so we know whether 91% empty is
the intended state or a gap.

Where it is populated it is frequently **identical** to `keyword`, which makes the
design's separate "Search Term" and "Keyword" columns redundant in practice.

---

## 6. `conversion_type` sparse — P3

Populated on 53 of 98 tables; 18 stopped mid-window (8 on 2026-07-29). Low priority only
because the Conversion Type tab is currently hidden from the nav.

---

## 7. `{keywordID}` macro leaking into `keyword` — P3

Not a migration issue — flagging it because it travels with the data.

An unsubstituted tracking macro is being written into the click log as a literal:

| Advertiser | `{...}` macro rows | Impressions on those rows |
| --- | --- | --- |
| 13421 | 22 | **391,151** |
| 15325 | 1 | — |
| 17258, 16652, 21610 | 0 | — |

The ad tag should replace `{keywordID}` with the real keyword ID at click time. adcenter
displays it as-is deliberately: it carries real spend, so filtering it would break totals
reconciliation, and it is the only visible signal that the tag is misconfigured. Worth
routing to whoever owns Hearst's trafficking.

---

## Column health — all 98 active tables

| Column | Tables with data | Tables empty | % empty |
| --- | --- | --- | --- |
| `device_type` | 98 | 0 | 0% |
| `campaign_name` | 98 | 0 | 0% |
| `country` | 98 | 0 | 0% |
| `gender` | 97 | 1 | 1% |
| `age_group` | 97 | 1 | 1% |
| `domain` | 95 | 3 | 3% |
| `landing_page_url` | 95 | 3 | 3% — *populated, but see §4* |
| `creative_name` | 95 | 3 | 3% |
| `keyword` | 94 | 4 | 4% |
| `state` | 83 | 15 | 15% |
| `zip` | 83 | 15 | 15% |
| `city` | 82 | 16 | 16% |
| `conversions` | 53 | 45 | 46% |
| `conversion_type` | 53 | 45 | 46% |
| **`est_revenue`** | **37** | **61** | **62%** |
| **`search_query`** | **9** | **89** | **91%** |
| `destination_url` | 0 | 98 | 100% — *should carry the landing page, see §4* |

Healthy: `device_type`, `campaign_name`, `country`, `gender`, `age_group`, `domain`,
`landing_page_url`, `creative_name`, `keyword`. These need no attention.

---

## One non-data finding, for awareness

The HeatWave **RAPID accelerator is not loaded** — all 138 tables report
`AVAIL_RPDGSTABSTATE` in `performance_schema.rpd_tables`, meaning queries fall back to
InnoDB. Breakdown queries scan up to 18.8 GB (`adv_clicks_21610_new`) that way. If the
accelerator is meant to be on, loading it is likely a large win independent of any data
fix. If it is intentionally off for cost, ignore this.

Also worth knowing: `information_schema.table_rows` is wildly inaccurate on these tables
(it reported 7M for `adv_clicks_20378_new`, actual count is **1.74 billion**). Use
`COUNT(*)` when sizing anything.

---

## Priority order we would suggest

1. **Geo cliff (2026-07-29)** — biggest visible breakage, one event, 74 advertisers, and
   it looks like a single root cause shared with the conversions/est_revenue stoppages on
   the same date.
2. **`est_revenue`** — currently the single largest blocker to Breakdown View being
   trustworthy; every revenue and ROAS figure is 0.
3. **`conversions`** for the 18 advertisers that stopped mid-window.
4. **Populate `destination_url`** from `Admin.creatives.destination_url` — see §4. Lets
   us drop the cross-DB join to Admin for the Landing Pages dimension.
5. Confirm expected coverage for `search_query` and `conversion_type`.