Quorum Household Architecture

0

HOUSEHOLD GRAPH — Source of Truth

BASE_TABLES

HOUSEHOLD_MAPPING

2.3B rows · 1.67B MAIDs (US) → 44M H3_10 HHs
The core household graph. Built from segment captures + Digital Element.
Cols: MAIDS, IP, H3_10, CLUSTER_KEY, IS_US
15.7x better impression match rate than MAID_HOUSEHOLD_LOOKUP

BASE_TABLES

MAID_CENTROID_DATA

5.68B rows · 4.57B MAIDs (US) → 42M H3_10 HHs
Raw centroid output. Nighttime-weighted home location per MAID.
Cols: AD_ID (=MAID), H3_10, IP_ADDRESS, OCCURENCE_COUNT, IS_US

BASE_TABLES

IP_MAID_MAPPING

1.57B rows · Digital Element feed
IP → real MAIDs. Fresh through Mar 9.
The bridge for SYS- fake MAIDs: IP → DE → real MAID → household

1

PRE-COMPUTED LOOKUP TABLES — What SPs Actually Read

↓ SP_REFRESH_HOUSEHOLD_LOOKUP builds from HOUSEHOLD_MAPPING (PATH 1: MAID→H3_10, PATH 2: IP→H3_10)

DERIVED_TABLES

IMPRESSION_HOUSEHOLD_LOOKUP DEPLOYED

80.2M rows · Refreshed Mar 15
Pre-computed DEVICE_ID_RAW → H3_10 for impression MAIDs
Cols: DEVICE_ID_RAW, HOUSEHOLD_ID_BY_MAID, HOUSEHOLD_ID_BY_IP
19.8% coverage on March impressions
★ JOIN ON DEVICE_ID_RAW ONLY (not IP)

HOUSEHOLD_CORE

MAID_HOUSEHOLD_LOOKUP ⚠️

200M rows · 36M HHs
Built from MAID_CENTROID_DATA with 30-MAID-per-cell cap
0.42% impression match rate — 95% of MAIDs filtered out
Feeds HOUSEHOLD_ID_CORE enrichment. Too weak on its own.

HOUSEHOLD_CORE

IP_HOUSEHOLD_LOOKUP

19.4M rows
IP → best H3_10 by observation count
Feeds HOUSEHOLD_ID_CORE enrichment (fallback path)

2

ENRICHMENT — Writes HOUSEHOLD_ID onto event tables

IMPRESSIONS

SP_ENRICH_V2_HOUSEHOLD_CORE

Writes V2.HOUSEHOLD_ID_CORE (NUMBER)
Uses MAID_HOUSEHOLD_LOOKUP + IP_HOUSEHOLD_LOOKUP
3.8% coverage (weak — MAID_HOUSEHOLD_LOOKUP is filtered)

WEB PIXELS

SP_ENRICH_WEBPIXEL_HOUSEHOLD

Writes WEBPIXEL_EVENTS.HOUSEHOLD_ID (NUMBER)
IP-based resolution
25-37% coverage (web IPs match well)

SEGMENTS

SP_BUILD_TSHD

Writes TSHD.HOUSEHOLD_ID (NUMBER)
MAID-resolved from segment captures
100% coverage (every capture has a MAID)

3

ATTRIBUTION — Household matching (impression ↔ visit)

★ THE CORRECT IMPRESSION HOUSEHOLD RESOLUTION PATTERN

SELECT
    COALESCE(
        v2.HOUSEHOLD_ID_CORE,                                         -- 3.8% (from enrichment)
        COALESCE(hhl.HOUSEHOLD_ID_BY_MAID, hhl.HOUSEHOLD_ID_BY_IP),  -- 19.8% (from IHL bridge)
        TRY_TO_NUMBER(v2.HOUSEHOLD_ID)                                -- legacy fallback
    ) AS HOUSEHOLD_ID
FROM AD_IMPRESSION_LOG_V2 v2
LEFT JOIN IMPRESSION_HOUSEHOLD_LOOKUP hhl
    ON v2.DEVICE_ID_RAW = hhl.DEVICE_ID_RAW                         -- JOIN ON DEVICE_ID_RAW ONLY

DERIVED_TABLES

HH_STORE_VISIT_ATTRIBUTION v3 DEPLOYING

~40M rows · Weekly Tue task
TSHD.HOUSEHOLD_ID ↔ imp.HOUSEHOLD_ID
+ same advertiser + imp before visit + 28-day window
v3: Added IHL join (same as web fix)

DERIVED_TABLES

HH_WEB_VISIT_ATTRIBUTION v6e DEPLOYED

14.8M rows · Was 37K before fix
WEBPIXEL.HOUSEHOLD_ID ↔ imp.HOUSEHOLD_ID
+ domain→advertiser resolution + 28-day window
v6e: Added IHL join → 400x improvement

4

REPORTING — PERF_BY Dimensional Facts → Optimizer API

SP_POPULATE_DIMENSIONAL_FACTS (run74)

PERF_BY_PUBLISHER / GEO / TRAFFIC / CREATIVE DEPLOYED

Uses IHL for HOUSEHOLD_REACH ✓
37M / 19M / 12M / 1.1M rows

SP_PERF_BY_VISIT_ENRICHMENT

VISITORS + WEB_VISITORS → PERF_BY_* DEPLOYED

Reads from attribution tables, distributes into PERF_BY
Store: 12.2M visitors · Web: 487K visitors

app.quorum.live

Optimizer API v7

Reads PERF_BY_* tables
Summary, timeseries, campaigns, publisher, geo, traffic, creative

✓

ID Space Verification — All H3 res-10 NUMBER, prefix 622xxx

HOUSEHOLD_MAPPING.H3_10: 622172167138934783

IHL.HOUSEHOLD_ID_BY_MAID: 622230593427505151

V2.HOUSEHOLD_ID_CORE: 622188498325635071

TSHD.HOUSEHOLD_ID: 622708318162780159

WEBPIXEL.HOUSEHOLD_ID: 622699280980213759

MAID_HH_LOOKUP.HOUSEHOLD_ID: 622188178506088447

MISTAKES THAT COST 40+ HOURS — DO NOT REPEAT

Using only HOUSEHOLD_ID_CORE for impression HH resolution (3.8%). Always add IMPRESSION_HOUSEHOLD_LOOKUP join (19.8%).

Joining IHL on DEVICE_ID_RAW AND IP_ADDRESS. PATH 1 stores NULL for IP. Join on DEVICE_ID_RAW ONLY.

Concluding low match rate is a "data procurement problem." HOUSEHOLD_MAPPING has the data. IHL bridges it.

Writing new tables instead of using deployed infrastructure. IHL, HOUSEHOLD_MAPPING, SP_REFRESH_HOUSEHOLD_LOOKUP all exist.

Confusing H3_10 (622xxx, NUMBER) with HH_ hash (TEXT, legacy) or H3_12 (631xxx, finer res). Pipeline uses H3_10 only.

Attribution window of 14 days. Config says 28. Always 28 unless advertiser specifies otherwise (some use 90).

Quorum Household Resolution Architecture ✓ ALL IDs VERIFIED H3 res-10