# Phase 4: ad_metadata for All Campaigns (2026-08-01)

## Goal
107/117 campaigns missing ad creative metadata. Without it, we can't show which ad creative drove which conversions.

## Backfill Approach
For each campaign_id in `campaign_daily_metrics`:
1. `GET /<campaign_id>/ads?fields=id,name,status,effective_status,creative{id,name,title,body,image_url,video_id,thumbnail_url,call_to_action_type,link_url}`
2. Store each ad in `ad_metadata` with all creative fields

## Results

| Metric | Before | After |
|--------|--------|-------|
| Total ad rows | 10 | **417** |
| Distinct campaigns with meta | 8 | **116** |
| Coverage of daily-metrics campaigns | 7% | **99%** |

**By status** (after backfill):
- ACTIVE: 262 (62.8%)
- PAUSED: 154 (36.9%)
- NULL: 1

**Creative fields populated**:
- `creative_id`: 416/417 (99.8%)
- `creative_image_url`: 416/417 (99.8%)
- `creative_video_id`: 416/417 (99.8%)
- `creative_body`: 416/417 (99.8%)

## 7 Missing Campaigns
Campaigns that returned HTTP 400 Bad Request from Meta API:
- `23857953364070589`
- `23858064552020589`
- `23858943252790589`
- `23858946473440589`
- `23861043581080589`
- `23861726438890589`
- `23861794350820589`

These are likely from a different ad account or have been permanently deleted. Cannot retrieve their creatives.

## SQL Update Strategy
`INSERT ... ON DUPLICATE KEY UPDATE` — safe to re-run. The 7 missing campaigns are the only ones without metadata; all others are now fully covered.

## What's Now Possible
- Show creative preview in campaign drilldowns (image_url)
- Group conversions by creative (not just campaign)
- A/B test analysis at the ad level
- Detect creative fatigue (declining ROAS on same creative)
- Auto-pause low-CTR creatives

## File
`/opt/adsfb/bf_meta3.py` — re-runnable backfill script