Correct population sizing counts: repoint mobile pool at nimbus_targeting_context, filter by channel and partition #17650

Population estimates on the Audience page are wrong on every platform.

Note: this issue mirrors a Jira ticket into a public repo, so population figures are deliberately kept out of it. Measurements are reproducible from the queries described below.

Impact

• iOS: every estimate written is zero.
• Fenix: nearly all are zero; the largest is a double-digit client count.
• Desktop: one rollout was sized at over a billion eligible clients.
• Estimates also drift upward daily with targeting unchanged, as pool partitions accumulate.

Root cause

Desktop and mobile read different sources with incompatible sampling:

-- DESKTOP
FROM `firefox_desktop.nimbus_targeting_context`           -- the Nimbus ping
WHERE sample_id < 10                                      -- uniform population sample

-- MOBILE
FROM `{app}_derived.nimbus_recorded_targeting_context_v1` -- derived off the metrics ping
WHERE ABS(MOD(FARM_FINGERPRINT(client_id), 100)) < 10     -- hand-rolled hash

The ETL multiplies both by 10. Correct for desktop. Not for mobile, because the metrics ping carries the Nimbus context object for only a small fraction of clients — the ×10 corrects for the hash and nothing else.

The hash exists because nimbus_recorded_targeting_context_v1 has no sample_id column. That is the tell: the dedicated ping has one, exactly like desktop.

Three defects

1. Desktop over-counts. build_query emits FROM <pool> with no partition filter. Each daily partition is an independently deduplicated 7-day window and the table keeps fourteen, so each client is counted once per partition it appears in — the pool holds roughly an order of magnitude more rows than distinct client_ids.
2. Mobile under-counts. Wrong source ping, as above. The sample is also geographically skewed relative to the true population, so no flat multiplier can correct it.
3. Channel is wrong. The mobile source filters normalized_channel = 'release', but most mobile Drafts target nightly, beta or developer.

Two compounding problems in the mobile query

• The sub-sample destroys scarce data. Sampling 10% is sensible on desktop. On mobile it saves nothing — the tables are tens of megabytes — and leaves so few clients per day that any real targeting lands on single-digit rows. This is why every iOS estimate is zero.
• Fenix and iOS disagree. Fenix reads fenix_derived (union of release/beta/nightly); iOS reads org_mozilla_ios_firefox_derived (release only — firefox_ios is the union). iOS nightly and beta experiments can never size correctly.

Fix

Point all three apps at {app}.nimbus_targeting_context with sample_id < 10. It is the union view, carries sample_id, and covers nearly all ping senders — including daysSinceUpdate and isPhone. This resolves the source, sampling and channel-scope problems in one change, and gives one code path instead of two. Then pin build_query to a single partition.

Notes

• Sequencing: the mobile source fix must land with or before the partition fix. Alone, the partition fix divides mobile further and makes the zeros worse.
• Desktop zeros are not a bug. Investigated and ruled out — the conversion is correct and pool fields are fully populated. Those experiments target an unshipped Firefox version or prefs nobody has enabled.

┆Issue is synchronized with this Jira Task