Marketing Data Warehouse and Full Funnel Attribution Dashboard

What we are building, why, and the call we have to make first

SGA operates 260 practices. Roughly 50 to 60 actively run paid media. Today there is no automated way to see spend, leads, cost per lead, or ROAS by practice without logging into 50 different ad accounts and CRMs. This plan stands up a single warehouse, a daily refresh, and a dashboard inside SGA3P that the growth team can open on Monday morning and act from.

Where this plan deviates from the 2026-04-20 settled stack

The settled stack memo locked in ClickHouse for analytics, Power BI for dashboards, and AWS for hosting. This plan diverges on two of those choices, deliberately, because Dakota directed the change in the project-kickoff conversation. Calling it out so the deviation is conscious, not drift.

Phase boundaries and what ships when

Out of scope for Phase 1

• PHI or clinical data. No procedure detail, diagnosis, or chart data.
• Write back to Google Ads. Read only. No pause, no budget change, no creative edit from inside the dashboard.
• Meta Ads, TikTok, YouTube, Microsoft Ads. Smile Like Si Meta data ingests in Phase 2.
• GA4 web analytics, Google Business Profile, review platforms.
• Agency comparison scorecards. Politically sensitive, defer until data trust is established.
• Predictive forecasting, LTV models, propensity scoring, budget optimization.
• Practice manager login and per practice scoped RBAC.
• Data driven attribution. Insufficient per practice volume. Revisit at portfolio level in Phase 2.
• Mobile native app, scheduled email or SMS report exports.

End to end pipeline, ingestion service shape, dashboard data layer

Why a new dedicated worker, not endpoints on asset-registry

Asset registry is a low latency request response API powering the content engine UI. Ingestion is long running, bursty, OAuth bearing, and rate limited by upstream APIs. Mixing them violates single responsibility, lets a runaway ingest loop choke the API, and bleeds OAuth secrets into the wrong service. The duplication cost is small because both services share packages/shared-types for Zod schemas and Drizzle tables.

Dashboard data layer

Add a /api/v1/marketing/* route group to the existing asset-registry service. Same auth (Bearer demo password now, Cognito later), same Drizzle, separate route module. The React app does not hit Postgres directly. Phase 1 routes:

All response shapes live in packages/shared-types/src/marketing.ts as Zod schemas. The React app imports the same types. Fastify response cache holds summary endpoints for 5 minutes since materialized views refresh on the order of hours.

Postgres design: raw, staging, mart, materialized

All marketing objects live in a single marketing schema on the existing Railway Postgres, alongside shared (practices). The design separates four concerns: operational sync state, raw API ingest, conformed dimensions and facts, and materialized views for the dashboard.

Schema layout

Google Ads tables (Phase 1)

DDL: marketing.google_ads_campaign_daily_metrics (partitioned)

CREATE TABLE marketing.google_ads_campaign_daily_metrics (
  metric_date           DATE   NOT NULL,
  customer_id           BIGINT NOT NULL,
  campaign_id           BIGINT NOT NULL,
  practice_id           UUID   NOT NULL,
  impressions           BIGINT NOT NULL DEFAULT 0,
  clicks                BIGINT NOT NULL DEFAULT 0,
  cost_micros           BIGINT NOT NULL DEFAULT 0,
  conversions           NUMERIC(12,4) NOT NULL DEFAULT 0,
  conversion_value      NUMERIC(14,4) NOT NULL DEFAULT 0,
  view_through_conv     NUMERIC(12,4) NOT NULL DEFAULT 0,
  search_impression_share NUMERIC(6,4),
  avg_cpc_micros        BIGINT,
  ctr                   NUMERIC(8,6),
  ingested_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (metric_date, campaign_id),
  FOREIGN KEY (campaign_id) REFERENCES marketing.google_ads_campaigns(campaign_id),
  FOREIGN KEY (practice_id) REFERENCES shared.practices(id)
) PARTITION BY RANGE (metric_date);

CREATE INDEX idx_gads_metrics_practice_date
  ON marketing.google_ads_campaign_daily_metrics (practice_id, metric_date DESC);
CREATE INDEX idx_gads_metrics_campaign_date
  ON marketing.google_ads_campaign_daily_metrics (campaign_id, metric_date DESC);

GoHighLevel tables (Phase 1)

DDL: marketing.ghl_contacts (attribution carrier)

CREATE TABLE marketing.ghl_contacts (
  contact_id          TEXT        NOT NULL,
  location_id         TEXT        NOT NULL,
  practice_id         UUID        NOT NULL,
  first_name          TEXT,
  last_name           TEXT,
  email_hash          BYTEA,                  -- sha256(lower(trim(email)))
  phone_hash          BYTEA,                  -- sha256(e164(phone))
  email_encrypted     BYTEA,                  -- pgcrypto, key in app layer
  phone_encrypted     BYTEA,
  source              TEXT,                   -- 'Google Ads', 'Facebook', 'Direct'
  utm_source          TEXT,
  utm_medium          TEXT,
  utm_campaign        TEXT,
  utm_content         TEXT,
  utm_term            TEXT,
  gclid               TEXT,
  fbclid              TEXT,
  wbraid              TEXT,
  gbraid              TEXT,
  tags                TEXT[]      NOT NULL DEFAULT '{}',
  date_added          TIMESTAMPTZ NOT NULL,
  first_seen_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  last_activity_at    TIMESTAMPTZ,
  updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
  raw_payload         JSONB,
  PRIMARY KEY (contact_id),
  FOREIGN KEY (location_id) REFERENCES marketing.ghl_locations(location_id),
  FOREIGN KEY (practice_id) REFERENCES shared.practices(id)
);

CREATE INDEX idx_ghl_contacts_practice_added ON marketing.ghl_contacts (practice_id, date_added DESC);
CREATE INDEX idx_ghl_contacts_gclid          ON marketing.ghl_contacts (gclid) WHERE gclid IS NOT NULL;
CREATE INDEX idx_ghl_contacts_email_hash     ON marketing.ghl_contacts (email_hash) WHERE email_hash IS NOT NULL;
CREATE INDEX idx_ghl_contacts_phone_hash     ON marketing.ghl_contacts (phone_hash) WHERE phone_hash IS NOT NULL;

Identity resolution: how the funnel chains together

The chain is Google Click (GCLID) to GHL Contact to Opportunity to Appointment to Started Treatment to Revenue.

Attribution model (three table design)

Three tables let multiple attribution algorithms run over the same touchpoint history without rewriting facts.

• attribution_touchpoints: every marketing touch (click, organic visit, referral) tied to a contact, with channel, source, medium, campaign, gclid, occurred_at, position.
• attribution_conversions: events being attributed TO. Types: contact_created, opportunity_created, opportunity_won, appointment_booked, appointment_showed, case_started.
• attribution_assignments: many to many between touchpoints and conversions, parameterized by model_name and credit_fraction (sums to 1.0 per conversion per model).

This shape supports first touch, last touch, linear, time decay, position based, and (Phase 2) data driven without reshaping data.

Materialized views (dashboard tier)

Every MV requires a UNIQUE index on its grain key so REFRESH MATERIALIZED VIEW CONCURRENTLY works. Without this, refresh blocks dashboard reads.

Partitioning, retention, PII

Migration plan (Drizzle, drop in)

Every metric the dashboard must show, with formula and freshness

Stale threshold is the lag past which the dashboard surfaces a yellow data warning banner. If a source crosses its threshold, the page header shows the affected source with the last successful refresh timestamp.

Spend metrics

Acquisition metrics

Funnel metrics

Cost efficiency metrics

Attribution metrics

Five views inside app.sga3p.com

Global filter bar (sticky, persists across views)

What "insights across each marketing channel" actually means

An insight is a system generated, plain English statement about a measurable change or comparison that warrants action. Insights are not raw charts. They appear as cards in the Insights feed and as inline badges on Practice Detail tiles, with acknowledge and snooze (7 day) actions to keep the feed actionable.

Anomaly detection rules (Phase 1)

Comparative insight rules

• Vs portfolio: "Practice X CPL of $84 is 2.4x portfolio median of $35 over the last 30 days. Investigate creative, geo targeting, or landing page."
• Vs self: "Practice X CPL is up 38% week over week vs the prior 4 week average."
• Vs region: "Practice X is the worst performer in its region (Valdosta cluster) on cost per started case."

Recommendation templates (Phase 1.5)

Which models, when, and why

The attribution model selector is a single dropdown in the global filter bar. Changing it recomputes attribution dependent KPIs (assisted conversions, channel share, ROAS by channel) in place. The selected model is shown as a persistent badge on every attribution dependent tile.

What "live update on a consistent schedule" means concretely

Principle: webhooks update the warehouse within seconds, scheduled pulls are the safety net. Both write to the same raw_ghl_* tables. The dedupe step in stg_* resolves conflicts using updated_at from the source.

Every dashboard page shows three timestamps in the footer: "Google Ads last refreshed," "GHL last refreshed," "Derived metrics last computed." If any source is past its stale threshold, a yellow banner appears at the top of the page with the affected source named.

Secrets, scheduling, observability, backfill

Service topology on Railway

Deploy sga-marketing-ingest as a new Railway service in the same project as the existing Postgres service. Same private network (no egress cost on the Postgres connection), separate environment, isolated blast radius.

Scheduling: node-cron now, BullMQ when needed, Temporal eventually

// scheduler.ts -- the only file that changes when Temporal arrives
import cron from 'node-cron';
import { runGoogleAdsDailyPull } from './jobs/google-ads-daily';
import { runGhlReconciliation } from './jobs/ghl-reconciliation';
import { refreshMvSlow, refreshMvFast } from './jobs/mv-refresh';

export function startScheduler() {
  cron.schedule('0 9 * * *',  () => runGoogleAdsDailyPull({ window: 'T-2..T-1' }));
  cron.schedule('0 * * * *',  () => runGhlReconciliation());
  cron.schedule('30 9 * * *', () => refreshMvSlow());
  cron.schedule('0 * * * *',  () => refreshMvFast());
}

Every job body is a plain async function with no cron dependency. node-cron calls it today, BullMQ calls it tomorrow, Temporal Activity calls it the day after. The migration cost is one file.

Secrets: Railway env vars, layered with Doppler

# --- Google Ads ----------------------------------
GOOGLE_ADS_DEVELOPER_TOKEN=
GOOGLE_ADS_CLIENT_ID=
GOOGLE_ADS_CLIENT_SECRET=
GOOGLE_ADS_REFRESH_TOKEN_GEN4=
GOOGLE_ADS_REFRESH_TOKEN_SGA=          # add when SGA MCC exists
GOOGLE_ADS_MCC_CUSTOMER_ID_GEN4=
GOOGLE_ADS_MCC_CUSTOMER_ID_SGA=

# --- GoHighLevel ---------------------------------
GHL_AGENCY_API_TOKEN=                  # single agency token, not per location
GHL_WEBHOOK_SIGNING_SECRET=

# --- Database -----------------------------------
DATABASE_URL=                          # Railway internal

# --- Observability ------------------------------
SENTRY_DSN=
BETTERSTACK_SOURCE_TOKEN=

# --- Runtime ------------------------------------
NODE_ENV=production
TZ=UTC
LOG_LEVEL=info

Doppler is recommended on top of Railway env vars: single source of truth, audit log, per environment promotion. Do not introduce AWS Secrets Manager in Phase 1, that is a Phase 2 migration.

OAuth refresh and rate limits

• Google Ads: store one refresh token per MCC (Gen4, future SGA). On invalid_grant, write status = 'token_error' to sync_runs, fire Sentry fatal alert, skip remaining practices for that MCC. Do not retry in same run.
• Google Ads quotas: apply for Standard Access immediately (no daily op cap). Basic Access (15,000 ops/day) covers 60 practices comfortably as a fallback. Batch reports across the MCC hierarchy.
• GHL: single Agency API token with locationId query param per request. Do not store 260 location keys. v2 rate limit is 100 req per 10 sec per location, plenty of headroom.

GHL webhook receiver

POST /webhooks/ghl on the worker, exposed via a Railway public URL (separate hostname from api.sga3p.com). HMAC verification using x-ghl-signature. Synchronous insert into marketing.ghl_webhook_events (the durable queue). Downstream processor reads from that table on the hourly reconciliation job and marks rows processed = true. This gives replay capability without any new infrastructure.

Observability stack (Phase 1, lightweight)

• Structured JSON logs with pino, drained to Better Stack via Railway log drain
• sync_runs table: row per job per practice per source per day with status, rows ingested, rows expected, duration, error message, api ops consumed
• Sentry for error capture and alerting (free tier is enough)
• Better Stack for uptime monitoring of /webhooks/ghl/health and /healthz
• Phase 2: OpenTelemetry traces, Grafana via Railway addon

Three SLOs to commit to in Phase 1

Backfill workflow

POST /internal/backfill on the worker (static internal API key, not exposed). Body: { practiceId, source, startDate, endDate }. Writes one row per date-source combination to backfill_queue. A backfill runner processes oldest first, respects rate limits, marks rows done with row count. Idempotent on (practice_id, source, data_date). 90 days x 60 practices x 200ms delay ≈ 18 minutes wall time.

Cost projection (Phase 1)

6 weeks, week by week, with a walking skeleton in week 1

Walking skeleton first. Prove the full path end to end with one practice, one day, one number on screen, then replicate breadth and depth from there.

Phase 1 inclusion decisions

Given / When / Then scenarios for the highest value flows

Feature: Portfolio Overview load performance and content
  Scenario: Growth lead opens Portfolio Overview on Monday morning
    Given I am authenticated as the growth lead
    And there are 50 or more practices with active spend in the last 30 days
    When I navigate to Portfolio Overview
    Then I see total spend, total leads, blended CPL, total started cases, and blended ROAS within 2 seconds
    And each tile shows delta versus the previous 30 day period

Feature: Funnel waterfall on Practice Detail
  Scenario: Diagnosing where a practice is losing patients
    Given I have selected a single practice on Practice Detail
    When the funnel waterfall renders
    Then I see counts and conversion percentages at each stage from Impression through Started Case
    And the largest drop off stage is visually highlighted

Feature: Attribution model toggle
  Scenario: Switching from last touch to linear attribution
    Given I am on Funnel View with attribution set to Last Touch
    When I change the attribution selector to Linear
    Then ROAS, channel share, and assisted conversion tiles recompute within 3 seconds
    And every attribution dependent tile shows a "Linear" badge

Feature: Anomaly detection surfaces a spend spike
  Scenario: A practice spends 60 percent above trailing mean
    Given Practice X has a trailing 14 day mean daily spend of $100
    And Practice X spent $180 yesterday
    When the nightly insights job runs at 05:00 ET
    Then a High severity insight card appears in the Insights feed
    And the card states the practice name, the metric, the percentage change, and a suggested action

Feature: GHL webhook freshness
  Scenario: A new lead is created in GHL
    Given a contact is created in GHL with a marketing source tag at 14:00 ET
    When I refresh Portfolio Overview at 14:05 ET
    Then the lead count tile reflects the new lead
    And the GHL last refreshed timestamp is within 5 minutes of now

Feature: Google Ads stale data warning
  Scenario: Google Ads ingest fails overnight
    Given the Google Ads nightly refresh failed at 04:00 ET
    And it is now 14:00 ET
    When I open any dashboard page
    Then a yellow banner appears at the top of the page
    And the banner states "Google Ads data is stale, last refreshed [timestamp]"

Feature: Independent verification of agency numbers
  Scenario: QA reviewer cross checks LookSee monthly report
    Given LookSee reported a CPL of $42 for Practice Y in April
    When I open Practice Detail for Practice Y with time range April 1 to April 30
    Then the dashboard CPL is displayed and is within 2 percent of the agency reported figure
    And if the variance exceeds 5 percent, the tile shows a variance flag

Feature: Comparative insight against portfolio median
  Scenario: A practice is materially above portfolio CPL
    Given Practice X has a 30 day CPL of $84
    And the portfolio median 30 day CPL is $35
    When I open Practice Detail for Practice X
    Then a comparative insight card states "CPL is 2.4x portfolio median"
    And a recommendation suggests creative or targeting investigation

The five most consequential calls, captured

Top 6 ranked, what breaks and what we do about it

What to confirm and what to start, this week