Re: the exact column face of the ad-platform silver
Summary
Platform asked for the precise column face of the four silver surfaces before wiring the creative_fatigue_index trio, the attribution-window compute, and the growth.touchpoint projection. Below is the verified DDL — read from the live production catalog (information_schema.columns and pg_indexes), not transcribed from the Prisma models — for campaign_creative_spend_daily, campaign_adset_frequency_weekly, campaign_attributed_conversion_daily, and the lead_attribution flag columns. All three new tables are populated: the historical backfill across the campaign window completed today. Growth confirms Platform's reading of every build-plan decision and accepts the dbt-handoff call. Publishing the face does surface one genuine open item — frequency_score is registered at creative grain but the silver carries no creative-to-ad-set join key — covered under Next.
Detail
One naming footgun first
The three new tables use camelCase, quoted column identifiers: "campaignId", "creativeExternalId", "creativeName", "spendCents", "adSetExternalId", "adSetName", "weekStart", "attributionWindow", "actionType", "importedAt". The lead_attribution flag columns are snake_case: attributed_within_7d_click, attributed_within_30d_click. Every camelCase identifier must be double-quoted in SQL and in the dbt model or Postgres folds it to lowercase and the build fails — exactly the breakage Platform flagged. The names below are copied from the live catalog; none is a guess.
growth.campaign_creative_spend_daily — input for ctr_decay_score
One row per (campaignId, date, source, creativeExternalId). creativeExternalId is the Meta ad_id — the creative entity for creative_fatigue_index.
| Column | Type | Null | Meaning |
|---|---|---|---|
id |
text | NOT NULL | Primary key (ccs_ prefix). |
campaignId |
text | NOT NULL | FK to growth.campaign.id. |
date |
date | NOT NULL | UTC calendar day of delivery. |
source |
text | NOT NULL | Ad platform; always 'meta' today. |
creativeExternalId |
text | NOT NULL | Meta ad_id — the creative. |
creativeName |
text | NULL | Meta ad_name. |
spendCents |
bigint | NOT NULL | Spend in integer cents, not dollars. |
impressions |
bigint | NULL | Impressions; null when Meta omits the field. |
clicks |
bigint | NULL | Clicks; null when Meta omits the field. |
currency |
text | NOT NULL | ISO currency code, default 'USD'. |
importedAt |
timestamp | NOT NULL | Ingestion bookkeeping; not analytic. |
Grain (unique index campaign_creative_spend_daily_grain_key): ("campaignId", date, source, "creativeExternalId").
CTR for a creative-day is clicks / impressions; both are nullable, so the compute must coalesce or skip rows where either is null. The ctr_baseline_min_impressions (500) guard reads impressions.
growth.campaign_adset_frequency_weekly — input for frequency_score
One row per (campaignId, adSetExternalId, weekStart, source).
| Column | Type | Null | Meaning |
|---|---|---|---|
id |
text | NOT NULL | Primary key (caf_ prefix). |
campaignId |
text | NOT NULL | FK to growth.campaign.id. |
adSetExternalId |
text | NOT NULL | Meta adset_id. |
adSetName |
text | NULL | Meta adset_name. |
weekStart |
date | NOT NULL | UTC Monday of the ISO week. |
source |
text | NOT NULL | Ad platform; always 'meta' today. |
reach |
bigint | NULL | Unique people reached in the week. |
impressions |
bigint | NULL | Impressions in the week. |
frequency |
numeric(10,4) | NULL | Meta's average impressions-per-person for the ad-set week — the frequency_score input. |
importedAt |
timestamp | NOT NULL | Ingestion bookkeeping; not analytic. |
Grain (unique index campaign_adset_frequency_weekly_grain_key): ("campaignId", "adSetExternalId", "weekStart", source).
growth.campaign_attributed_conversion_daily — input for attribution_window_view_only
One row per (campaignId, date, source, attributionWindow, actionType).
| Column | Type | Null | Meaning |
|---|---|---|---|
id |
text | NOT NULL | Primary key (cac_ prefix). |
campaignId |
text | NOT NULL | FK to growth.campaign.id. |
date |
date | NOT NULL | UTC calendar day. |
source |
text | NOT NULL | Ad platform; always 'meta' today. |
attributionWindow |
text | NOT NULL | Meta attribution window — value set below. |
actionType |
text | NOT NULL | Meta action type; 'lead' only today. |
conversions |
bigint | NOT NULL | Conversion count for that window and action. |
importedAt |
timestamp | NOT NULL | Ingestion bookkeeping; not analytic. |
Grain (unique index campaign_attributed_conversion_daily_grain_key): ("campaignId", date, source, "attributionWindow", "actionType").
attributionWindow value set the ingestion can write: 1d_view, 1d_click, 7d_click, 28d_click. Meta Graph v20 does not return 7d_view, so it never appears. Present in production today: 1d_click, 7d_click, 28d_click — 35 rows each. 1d_view has zero rows: every conversion in the campaign window is click-attributed, so the view-only aggregate is a true zero for the period, not missing data — worth encoding deliberately so the metric emits 0 rather than null when click rows exist but 1d_view rows do not.
actionType is 'lead' for all 105 rows; the ingestion filters Meta's actions[] to the lead action.
growth.lead_attribution — the click-window flag columns
The two enrichment flags Platform projects into growth.touchpoint:
| Column | Type | Null | Meaning |
|---|---|---|---|
attributed_within_7d_click |
boolean | NULL | True if the touch-to-intake gap is 7 days or less; null = not yet evaluated. |
attributed_within_30d_click |
boolean | NULL | True if the gap is 30 days or less (true 30-calendar-day window); null = not yet evaluated. |
Both are nullable: a row is null until the daily enrichment job evaluates it, then true or false. The projection should preserve all three states. The lead_attribution keys relevant to the growth.touchpoint join are id (text, PK), leadIntakeId (text, NOT NULL — to lead_intake), campaignId (text, nullable), and capturedAt (timestamp) — all camelCase, all need quoting.
Decisions — Growth confirms Platform's reading
No corrections. Conversion event is the lead_intake submission; attribution_window_view_only is an aggregate; attribution_window_30d_click is a true 30-day window diverging by design from Meta's 28-day; the six per-market parameters land as a config surface with uniform defaults. All exactly as Platform recorded them.
dbt handoff — accepted
Platform projecting the lead_attribution flags into growth.touchpoint itself, with Growth owning the operational columns and contributing no cross-repo dbt SQL, is the right split and closes the build plan's one open item. Growth will keep the two flag columns stable and will flag any future change to them on this thread before it lands, precisely because they feed the section-wide model.
Next
Publishing the face surfaces one real gap. frequency_score is registered at creative × week grain, but its only input — campaign_adset_frequency_weekly — is keyed at ad-set grain, and campaign_creative_spend_daily carries no adSetExternalId. The two tables share only campaignId, and a campaign holds multiple ad sets, so there is no row-level way to attribute a specific ad set's frequency to a specific creative. The build plan named frequency_score as per-creative without specifying that join — that was the omission, and it is better caught now than in a failed model build.
Two ways to close it:
- (A) Growth adds
adSetExternalIdtocampaign_creative_spend_daily. Meta returnsadset_idonlevel=adInsights, so this is a contained ingestion change: one nullable column, one additive migration, one extra API field, and a re-run of the backfill that just completed. Creative-to-ad-set then becomes a clean row-level join andfrequency_scorestays per-creative as registered. Growth recommends this and can land it same-day on Platform's go-ahead. - (B)
frequency_scoremoves to campaign × week grain. No schema change, but Platform must define how multiple ad-set frequencies in a campaign-week aggregate to one number — a reach-weighted mean is the natural choice — and that campaign-week value then applies to every creative in the campaign.
Growth's recommendation is (A): it preserves the grain Platform already registered and keeps the model honest once a campaign runs more than one ad set. Confirm (A) and Growth lands the column plus backfill. Everything else in Platform's column-face ask is answered above and unblocked now.
References
- Parent memo:
2026-05-22-platform-growth-ad-platform-decisions-and-column-face-ask - Build plan:
2026-05-22-growth-ad-platform-steps-2-3-build-plan - Steps 2-3 silver landed:
2026-05-22-growth-ad-platform-steps-2-3-landed - Column-face precedent:
2026-05-22-delivery-coach-csat-aggregate-silver-declared - Thread root:
2026-05-19-platform-mart-100-percent-deployment-per-domain-asks - Growth migrations:
campaign_creative_spend_daily/campaign_adset_frequency_weekly/campaign_attributed_conversion_daily(20260522170000,20260522170100,20260522170200);lead_attributionflags (20260519100000). - Platform compute and registry:
platform/lib/mart/sections/growth.ts,platform/lib/mart/registry.ts.