Scorecard (cold-start)

The standing instrument for a building's fill. Read weekly. It is a documented query set, not an automated dashboard, because it spans two sources (the prod DB and the PostHog warehouse). Run the queries below, record the result as a dated snapshot in runs/, and read it against the prior week and the North Star.

The card is now a live view, analytics.cold_start_scorecard (deployed 2026-06-25; PR 852 + 853). One row per location/month gives the funnel rows (move-ins, move-outs, tours, inquiries, hourly, occupancy, built on analytics.occupancy_monthly so occupancy matches exactly) plus ad columns (meta_spend_cents, google_spend_cents, meta_ad_leads). The ad columns come from a daily sync cron (/api/cron/sync-ad-data) into prod ad_spend_daily / ad_lead_daily, joined to a location by campaign-name or mgId match, so the whole card is one prod SELECT. The full ad history is backfilled (Meta from 2025-09, Google from 2026-01 when Google campaigns began; March 2026 Meta $0 is real data, ads were paused, not a gap), and the daily cron keeps it current. One thing to keep straight: the card surfaces less than what is stored. It shows spend only where a campaign name-matches a location (its name or mgId) and that location/month sits in the occupancy grid, so general, regional, and any campaign whose name does not contain the location's name or mgId stays in the raw tables (a deferred "unallocated" bucket) rather than on any location's row. Naming discipline (the operational rule): name every ad campaign with its location's name or mgId so it grids. Meta's "Cherry City …" campaigns already do. The Google search campaign was originally named "Salem" (matching neither "Cherry City" nor "MG10"), so its spend sat unallocated and read google_spend_cents = 0; on 2026-06-25 it was renamed to "Cherry City - Salem Search" and its 42 historical rows relabeled, so Google now grids onto Cherry City (Feb $551, May $27, June $72). Any campaign whose name carries no location token still lands in the unallocated bucket by design. For totals including unmatched spend, or campaign-level detail, query ad_spend_daily / ad_lead_daily (or the warehouse) directly. meta_ad_leads is Meta-only; Google leads arrive via on-site/gclid attribution.

The live instance is Salem; its latest snapshot and parameters are in the Salem instance.

Low-volume note

At a handful of tours and move-ins a month, weekly counts are mostly noise. Read weekly for spend and lead pace; judge move-ins and tours on the trailing 4 weeks and month-to-date, not single weeks. Use decision rules and step-changes, not week-over-week deltas.

Rows, sources, and how far to trust each

Row	Source	Trust
Spend by channel	warehouse `googleads.campaign_stats`, `metaads.campaign_stats`	Solid (platform-reported)
Leads by channel	Meta: warehouse `lead` action (NOT the `meta_leads` table — it only syncs ~recent). On-site: prod `inquiries`	Volume solid; not traceable to move-ins
Tours	prod `reservations` `type='TOUR'` at the location	Count is a floor (tours ≥ move-ins); undercounted; channel unknown
Tour close rate	Matador report; DB only once tours log reliably	~80%, forward-only
Move-ins	prod occupancy flow (see queries); net-new only, excludes migrated tenants	Solid
Migrations (transfers in)	qualified MONTHLY whose subscription matches an APPROVED `migration_submissions` row	Solid
Occupancy %	`analytics.occupancy_monthly`	Solid
Hourly (distinct)	prod `reservations` `type='HOURLY'`	Solid

Move-ins count net-new acquisition only. Migrated legacy tenants (the Yardi/SecureCafe onboarding) are transfers into the system, not demand we generated, so they are split out into their own Migrations row. Occupancy still reconciles: occupied = cumulative(move-ins + migrations) − cumulative(move-outs). Move-outs stay inclusive (a migrated member who cancels is real churn). Cherry City (Salem) has zero migrations, so its move-in numbers are unchanged; Portland is mostly migrations (e.g. MG3 was 7 net-new of 63).

What the middle can and cannot show (the real gap)

Read this before drawing any channel conclusion. The attribution stack rolled out in waves (first row 2026-04-12; CAPI ~Apr 27; tour attribution ~May 10; Meta Instant Form sync ~Jun 15), so thin history before mid-April is by design, not breakage. Past that, two gaps are structural, not rollout:

Lead volume is captured going forward: Meta lead actions (warehouse, real history back to February) plus on-site inquiries. Two silos, not unified, no dedup.
Tour → channel is dark (the upstream cause). Of ~25 recorded tour reservations, exactly 1 carries a channel attribution. Tours arrive through several capture points (on-site inquiry, tour form, Instant Lead Form, DM/text); the DM/text/walk-in slice is the unattributable one, backfilled by the CM with no channel, so those tours write nothing. The fix is live as of 2026-06-25 (PR 852): a required coarse CM lead-source dropdown on staff-created tours (GOOGLE | FACEBOOK | INSTAGRAM | REFERRAL | WALK_IN | OTHER | UNKNOWN, fb/ig roll up to Meta), written as a TOUR attribution (utmMedium='cm-reported') only as the last fallback so it never overrides a real signal; the email/phone auto-match to meta_leads was already shipped (#820/#821). It closes the gap going forward only, as CMs enter the source from here, not retroactively, and only as well as Matador's entry discipline holds.
Move-in → channel is near-empty, but the wiring exists. Only 3 MONTHLY rows ever. The move-in path already calls carry-forward (FulfillmentService/PaymentIntentHandler, on top of #727); it inherits nothing because the upstream tour carries nothing. Populate the tour and the move-in inherits automatically. PR 852's WS4 audit confirmed every member MONTHLY path routes through FulfillmentService carry-forward (public checkout, invitation fulfill, subscription webhook); the lone non-carrying path is the B2B org room, outside this funnel.

Net: we can read spend and lead volume by channel, and move-in/occupancy counts, but we cannot trace a channel's leads through tours to a signed lockout. That linkage is the blocker on the Google-vs-Meta verdict (channels.md) and the top measurement priority. The fix is live (PR 852, 2026-06-25) but only fills going forward; until enough CM-entered tours accumulate, judge channels on move-in timing against spend changes (as the Salem diagnosis did), not on attributed conversions.

The queries

Prod (psql via `DIRECT_URL`; substitute the location `mg_id`)

Occupancy + the validated move-in/move-out flow (mirrors analytics.occupancy_monthly):

WITH migrated_subs AS (
  SELECT DISTINCT subscription_id FROM migration_submissions
  WHERE status='APPROVED' AND subscription_id IS NOT NULL
), q AS (
  SELECT r.id,
         COALESCE(r.start_time, r.billing_start_date) AS eff_start,
         COALESCE(r.cancel_at_period_end, r.cancelled_at) AS eff_end,
         (r.stripe_subscription_id IN (SELECT subscription_id FROM migrated_subs)) AS is_migration
  FROM reservations r JOIN locations l ON l.id = r.location_id
  WHERE l.mg_id = 'MG10' AND r.type = 'MONTHLY' AND r.stripe_subscription_id IS NOT NULL
    AND EXISTS (SELECT 1 FROM payments p WHERE p.reservation_id = r.id AND p.recurring_amount_cents IS NOT NULL)
    AND (r.cancelled_at IS NULL OR r.cancelled_at >= COALESCE(r.start_time, r.billing_start_date))
), mo AS (
  SELECT generate_series(date_trunc('month',(SELECT min(eff_start) FROM q)),
                         date_trunc('month',current_date)+interval '1 month', interval '1 month')::date AS m)
SELECT mo.m,
  (SELECT count(*) FROM q WHERE date_trunc('month',q.eff_start)=mo.m AND NOT q.is_migration) AS movein,
  (SELECT count(*) FROM q WHERE date_trunc('month',q.eff_start)=mo.m AND q.is_migration) AS migration,
  (SELECT count(*) FROM q WHERE q.eff_end IS NOT NULL AND date_trunc('month',q.eff_end)=mo.m) AS moveout,
  (SELECT count(*) FROM q WHERE q.eff_start <= (mo.m+interval '1 month'-interval '1 day')
        AND (q.eff_end IS NULL OR q.eff_end > (mo.m+interval '1 month'-interval '1 day'))) AS occupied
FROM mo ORDER BY mo.m;

Tours, on-site inquiries, hourly (by month):

-- tours (floor; channel unknown)
SELECT date_trunc('month',coalesce(start_time,created_at))::date m, count(*)
FROM reservations WHERE location_id=(SELECT id FROM locations WHERE mg_id='MG10')
  AND type='TOUR' GROUP BY 1 ORDER BY 1;
-- on-site inquiries by channel
SELECT date_trunc('month',created_at)::date m, channel, count(*)
FROM inquiries WHERE location_id=(SELECT id FROM locations WHERE mg_id='MG10')
  AND deleted_at IS NULL GROUP BY 1,2 ORDER BY 1;
-- attribution coverage check (how many tours/move-ins carry a channel)
SELECT source_type, count(*), count(*) FILTER (WHERE utm_source IS NOT NULL) has_utm
FROM conversion_attributions GROUP BY 1 ORDER BY 2 DESC;

Warehouse (PostHog `execute-sql`)

Spend by channel/month (filter campaigns to the market):

SELECT toStartOfMonth(toDate(s.date_start)) AS mo, c.name AS campaign,
  round(sum(toFloatOrZero(s.spend)),0) AS spend
FROM metaads.campaign_stats s LEFT JOIN metaads.campaigns c ON c.id=s.campaign_id
WHERE toDate(s.date_start) >= '2026-01-01' GROUP BY mo,campaign ORDER BY mo,spend DESC;
-- Google: same shape on googleads.campaign_stats, cost = sum(metrics_cost_micros)/1e6, filter campaign_name='Salem'

Meta lead volume (authoritative; the meta_leads table lags):

SELECT toStartOfMonth(toDate(s.date_start)) AS mo, c.name AS campaign,
  round(sum(arraySum(x -> if(x.1='lead', toFloatOrZero(x.2), 0),
    JSONExtract(ifNull(s.actions,'[]'),'Array(Tuple(String,String))')))) AS leads
FROM metaads.campaign_stats s LEFT JOIN metaads.campaigns c ON c.id=s.campaign_id
WHERE toDate(s.date_start) >= '2026-01-01' GROUP BY mo,campaign HAVING leads>0 ORDER BY mo;