Skip to content

Marketing Scorecard + Attribution Completion

Status: WS1–WS4 shipped (PR #852, merged + deployed to prod). WS2 Phase 2 (ad columns on the card) built on branch feat/scorecard-ad-columns. Owner: Aaron Related: fix-missing-attribution-rows (shipped, #727) · stripe-data-pipeline (ADR-007 sync pattern) · docs/marketing/system/4-learning-system/scorecard.md · channels.md

Problem

The cold-start scorecard is settled as a documented query set (scorecard.md) but has two weaknesses that block reading the middle of the funnel by channel:

  1. It spans two stores. Funnel rows (occupancy, move-ins, tours, inquiries) are in Postgres; ad spend and Meta lead volume are in the PostHog warehouse (metaads/googleads). No single queryable, testable object exists.
  2. Channel attribution starts empty upstream. The attribution chain is already wired and reasonably complete (see "What already exists" below), but it produces almost nothing for Salem because the upstream tour carries no channel for the DM/text/walk-in slice, so there is nothing to carry forward to the move-in. Of ~25 recorded Cherry City tours, 1 has an attribution; MONTHLY attribution is 3 rows ever.

Net: spend and lead volume are readable by channel; a channel's leads cannot be traced through a tour to a signed lockout. That linkage is the prerequisite to settle the Google-vs-Meta verdict from data instead of move-in timing.

What already exists (do NOT rebuild)

Verified in code. carryForwardAttributionUnchecked in apps/api/src/services/shared/AttributionService.ts already tries, in order:

  1. findPriorAttributionForUser — most recent prior attributed inquiry/reservation/promo/credit with a real UTM or click ID (broadened by #727).
  2. findMetaLeadAttributionForUser — matches the user's email/phone against meta_leads (Meta Instant Forms) and synthesizes utmSource='fb', utmMedium='paid', utmContent='instant-form:<form>' (#820/#821). This is the "auto-match" — already shipped.
  3. userFbclidFirstTouch rescue.
  4. Else emits a Sentry signal (reason: no_prior_attribution) and writes nothing.

This chain runs at tour creation (ReservationCreationService.createTourReservation, apps/api/src/services/scheduling/ReservationCreationService.ts:755-765: writeConversionAttribution('TOUR', …) if explicit attribution else carryForwardAttribution(…, 'TOUR', …)) and at move-in (FulfillmentService lines 562 and 719 call writeOrCarryForwardAttribution(…, 'MONTHLY', …)). PaymentIntentHandler (235, 337) carries HOURLY and CREDIT_PURCHASE, not MONTHLY. On-site UTM capture already populates INQUIRY rows via /api/contact, /api/leads/email-capture, /api/tours.

So the only genuinely missing capture is the truly-untrackable slice: a tour for someone with no prior on-site attribution, no meta_leads match, and no fbclid (came via DM/text/walk-in). Only the community manager knows that channel.

Goals

  • One queryable, integration-tested analytics.cold_start_scorecard object covering the full card once ad data is local.
  • Channel attribution that survives lead → tour → move-in for the untrackable slice too, via a coarse CM label, without disturbing the existing higher-confidence signals.

Non-goals

  • Rebuilding the carry-forward chain or the Meta lead auto-match (shipped).
  • Perfect attribution. Coarse, mostly-complete is enough at our volume.
  • Ad-account changes (Aaron runs those); the flywheel North Star.

Design

WS3 — Coarse CM lead-source on tours (the lever; do this first)

Status: built. One correction from the spec as written: the field lives on the staff path (staffTourQuickSignupSchema → /api/staff/tour/quick-signup), not the public createTourBookingSchema. The CM-reported source is staff-entered; the public/self-serve path carries real attribution and is untouched (.strict() rejects the key there). createReservationSchema also carries it so it threads through commitReservation. Precedence/UNKNOWN/alert coverage unit-tested in AttributionService.test.ts; end-to-end row assertion in tour-quick-signup.api.test.ts. The staff dropdown is wired into CreateTourModal as a required Select (no pre-selected default — the CM must choose; Unknown stays an explicit option for genuine no-signal). The API schema keeps the field optional (back-compat for other callers / existing quick-signup tests); the requirement is enforced in the UI.

Add a community-manager-entered lead source to the create-tour flow, applied only as the final fallback so it never overrides a real signal.

Schema / encoding. No DB migration to conversion_attributions (reuse UTM fields; #730 removed sourceCode). Encode a CM-reported source as utmMedium = 'cm-reported' plus utmSource = the channel. Mapping:

CM choice utmSource utmMedium
Google google cm-reported
Facebook fb cm-reported
Instagram ig cm-reported
Referral referral cm-reported
Walk-in walk-in cm-reported
Other other cm-reported
Unknown (write nothing — let the chain run)

Meta is split into Facebook (fb) and Instagram (ig) at entry; both roll up to "Meta" in the scorecard (utmSource IN ('fb','ig')). fb stays consistent with the Meta-lead auto-match synth.

hasRealAdAttribution returns true when utmSource/utmMedium is set, so a CM-reported row is treated as real and carries forward to the move-in. utmMedium='cm-reported' keeps it distinguishable from genuine ad clicks in analysis.

Code changes:

  1. packages/shared-schemas/src/api/common.ts — add the cmLeadSource enum (GOOGLE|FACEBOOK|INSTAGRAM|REFERRAL|WALK_IN|OTHER|UNKNOWN), wired onto staffTourQuickSignupSchema (tours.ts) and createReservationSchema (scheduling.ts). Optional on the API; required in the staff UI. Omitted from the public createTourBookingSchema (those already carry real attribution). (As-built: the enum landed on the staff path, not the public createTourBookingSchema the original draft named.)
  2. apps/api/src/services/shared/AttributionService.ts — add an optional cmLeadSource param to carryForwardAttribution / carryForwardAttributionUnchecked. After the fbclid-rescue step and before the no_prior_attribution return, if cmLeadSource is set and not UNKNOWN, synthesize { utmSource: <map>, utmMedium: 'cm-reported' }, write it via buildConversionAttributionData, and emit a signal (reason: cm_reported_source). This preserves precedence: prior attribution → meta-lead → fbclid → CM label.
  3. apps/api/src/services/scheduling/ReservationCreationService.ts — thread cmLeadSource from the tour-create params into the carryForwardAttribution call at line 759. (Leave the explicit-params.attribution branch unchanged; the public path still wins with real attribution.)
  4. Staff UI — surface the dropdown in the staff create-tour flow (the flow noted outstanding in staff-ui-redesign). Required field, default UNKNOWN, so it is never skipped silently.

Acceptance: a staff-created tour with cmLeadSource=GOOGLE and no other signal produces a TOUR conversion_attribution with utmSource='google', utmMedium='cm-reported'; a tour for a user already in meta_leads still gets the Instant-Form attribution (CM label does not override it); the subsequent move-in inherits the tour's channel via the existing carry-forward. Unit tests in AttributionService.test.ts cover precedence (prior > meta-lead > fbclid > CM) and the UNKNOWN no-op.

WS4 — Move-in coverage audit (mostly verification)

Status: audited — no code change needed. Every member lockout MONTHLY path converges on LockoutFinalizeService.finalizeLockout, which owns the single writeOrCarryForwardAttribution(…, 'MONTHLY', …) call site: public lockout checkout (checkout/fulfill-lockout/route.ts), invitation fulfill (lockout-invitation/[id]/fulfill/route.ts), and the subscription webhook (SubscriptionCreateHandler.ts, StripeWebhookService.ts) all return the idempotent lockoutFinalize marker that dispatches to it. The StripeWebhookService MONTHLY references are PM-sync queries, not creation sites. Lone exception: fulfillOrganizationDedicatedRoom does not carry forward, but that is the B2B org path, outside the cold-start member funnel the scorecard measures — deliberately left. (At the time this audit was written, the carry-forward lived in two FulfillmentService call sites, fulfillLockout/finalizeLockoutPostPayment; both were later retired in favor of the single LockoutFinalizeService.finalizeLockout site — see [[../consolidate-lockout-side-effects/spec|consolidate-lockout-side-effects]].)

The move-in path already carries forward; verify completeness, add nothing unless a path bypasses it.

  • The MONTHLY carry-forward lives in LockoutFinalizeService.finalizeLockout. Audit that every MONTHLY-creating entry routes through it: the public lockout checkout (public-lockout-checkout, #822), invitation fulfill (apps/api/src/app/api/checkout/lockout-invitation/[id]/fulfill/route.ts), and any staff-created lockout path. Add a writeOrCarryForwardAttribution(…, 'MONTHLY', …) call to any that create the reservation outside finalizeLockout.
  • No new matching logic (the meta-lead match is already inside carry-forward).

Acceptance: every move-in variant invokes carry-forward; with WS3 live, MONTHLY attribution coverage rises materially week-over-week on the scorecard.

WS1 — Ad-data sync ETL (prerequisite for the unified view)

Status: built + verified against live APIs. Cron GET/POST /api/cron/sync-ad-data (daily 09:00 UTC; 7-day lookback to absorb platform spend revisions) → AdDataSyncService.syncAdData(). Meta via lib/meta/insights.ts (campaign/day spend + lead action), Google via lib/google-ads/{client,insights}.ts (REST + service-account JWT, adwords scope, per non-manager client under the MCC). Idempotent upsert by (platform, campaignId, date) into ad_spend_daily / ad_lead_daily; per-platform failures captured to Sentry, not thrown. Smoke run pulled real Meta + Google spend and Meta lead counts with zero errors. Google Ads decision: we have API access (service-account key + developer token + login customer ID), so option (a) — our own Google Ads REST client, no PostHog dependency. Deploy note: the client reads the SA key from GOOGLE_ADS_SERVICE_ACCOUNT_KEY (inline JSON, for Vercel) or GOOGLE_ADS_SERVICE_ACCOUNT_KEY_PATH (file, local); the inline env var must be set in Vercel before the cron runs there. AdLeadDaily carries Meta (the lead action) and, since the google-capi work (2026-07-08), Google (all_conversions where conversion_action_category = SUBMIT_LEAD_FORM — populated by the server-side Lead uploads; see google-capi).

Pull ad spend and lead volume into Postgres, modeled on the existing Meta lead sync: apps/api/src/app/api/cron/sync-meta-leads/route.ts + apps/api/src/services/meta/MetaLeadSyncService.ts + apps/api/src/lib/meta/leads.ts.

  • New Prisma models (public schema, Prisma-managed writer): AdSpendDaily (date, platform, campaignId, campaignName, spendCents, impressions, clicks) and AdLeadDaily (date, platform, campaignId, campaignName, leads). Upsert idempotently by (platform, campaignId, date).
  • Meta: extend the Meta Graph client to pull Insights at campaign/day granularity (spend, impressions, clicks, and the lead action from the actions breakdown — the authoritative lead count; the meta_leads table only syncs ~recent).
  • Google: ~~needs a Google Ads API client with a developer token + OAuth. Risk: we may not have one today.~~ Resolved — we have API access. Built our own Google Ads REST client (service-account JWT + developer token + login customer ID, adwords scope); no PostHog runtime dependency. Verified pulling real spend.
  • Register the cron alongside the existing marketing crons (same vercel.json pattern as sync-meta-leads).

Acceptance: both tables populate daily; a sample month's Meta spend and lead counts match the warehouse extraction in scorecard.md; Google spend present once credentials exist.

WS2 — analytics.cold_start_scorecard view

Status: Phase 1 built. analytics.cold_start_scorecard (migration 20260625112529_analytics_cold_start_scorecard) selects FROM analytics.occupancy_monthly as its backbone — so occupied / total_studios / occupancy_pct are literally that view's numbers — and left-joins funnel counts: move_ins / move_outs (qualified-MONTHLY, same COALESCE(start_time, billing_start_date) / COALESCE(cancel_at_period_end, cancelled_at) + recurring-payment filter as occupancy_monthly), tours and hourly_bookings (by start_time month), inquiries (by created_at month). One row per (location_id, month_start). Integration test inserts a full move-in + tour + inquiry fixture and asserts the row plus occupancy parity. Scoping note: the grain is occupancy_monthly's grid (locations with STUDIO_MONTHLY resources, months from the first monthly reservation onward), so tours/inquiries outside that grid aren't counted — correct for the monthly-lockout funnel the card measures. Timezone note: month bucketing is date_trunc('month', <timestamptz>) in the DB session timezone (same as occupancy_monthly); prod Postgres is UTC, but local/test sessions may not be, so test fixtures use mid-month dates to stay boundary-safe.

Phase 2 (ad spend/leads) — built (migration 20260625124041_scorecard_ad_columns, branch feat/scorecard-ad-columns). Adds meta_spend_cents, google_spend_cents, meta_ad_leads to the card. A campaign_location CTE resolves each campaign to one location by matching campaign_name against the location name or mg_id (ILIKE '%…%', mgId match preferred), so "Cherry City - Leads" and "MG10 Cherry City" both attribute; only Aaron names campaigns, so no naming-discipline or tiebreak risk. Unmatched campaigns (general/regional, e.g. "General Platform") resolve to NULL and are excluded — never split across locations. Surfacing that unallocated/region spend is a deferred someday item. No mapping table to maintain; matching lives in the view. Integration test covers name-match, mgId-match, and general-campaign exclusion. (Ad-table date is a DATE, so the month bucketing here has no session-timezone caveat — unlike the reservation columns.)

A SQL view in the analytics schema (same migration style as analytics.occupancy_monthly), one row per (location_id, month_start), reusing occupancy_monthly's occupied/total logic so the flow stays consistent.

  • Phase 1 (now, no WS1 dependency): funnel columns — move_ins, move_outs, tours, inquiries, hourly_bookings, occupied, total_studios, occupancy_pct. Move-in/move-out use COALESCE(start_time, billing_start_date) / COALESCE(cancel_at_period_end, cancelled_at) with the same qualifier as occupancy_monthly (MONTHLY + stripe_subscription_id + a recurring payment); tours = reservations type='TOUR'; inquiries from inquiries.
  • Phase 2 (after WS1): left-join AdSpendDaily/AdLeadDaily aggregated to month for spend_* and leads_* columns, making the whole card one object.

Acceptance: the view's occupancy/move-in numbers match occupancy_monthly exactly; an integration test inserts fixture reservations/payments and asserts the row (the analytics schema is insertable per ADR-007); the Salem weekly snapshot can be produced from a single SELECT post-WS1.

Sequencing

  1. WS3 — the lever; cheap; unblocks the verdict. Pairs with the already-wired move-in carry-forward.
  2. WS4 audit — alongside WS3.
  3. WS1 + WS2 — infra for the unified, testable scorecard; parallelizable; WS2 Phase 1 can land before WS1.

Risks

  • CM data-entry discipline. Coarse source is only as good as Matador's entry; required field, default UNKNOWN, short option list.
  • Precedence bug. The CM label must be the last fallback — a misorder would overwrite real Instant-Form/UTM attribution. Covered by the precedence unit test in WS3.
  • Google Ads API access may not exist (WS1); decide source vs PostHog-warehouse pull at build time.
  • Don't redo #727 / #820 / #821. Carry-forward, fbp-only removal, and the Meta lead match are shipped; build on them.