Base Snapshot Data Mart & Persona Reporting Architecture

Base is the customer intelligence engine of the Social Signal platform. It transforms raw customer lists (emails, ZIP codes, and monetary values) into strategic audience personas, demographic rollups, and autonomous AI marketing briefs.

This document describes the materialized data mart architecture, entity separation model, query mechanics, and lifecycle automation powering Base.

---

1. System Topology & Separation of Concerns

Base decouples long-term brand narrative, per-upload campaign intent, point-in-time customer records, and pre-aggregated analytical metrics into four distinct layers:

graph TD
    subgraph Layer1 ["Tier 1: Standing Brand Profile (Set Once)"]
        A[Client Onboarding / Settings Panel] -->|Upsert| B[(base_brand_profiles_raw)]
        B --> C[View: base_brand_profiles<br/>_id, domain, self_description, customer_insight]
    end

    subgraph Layer2 ["Tier 2: Snapshot Header & Intent (Per Upload)"]
        D[CSV / API Ingestion Payload] -->|Header Stream| E[(base_snapshots_raw)]
        E --> F[View: base_snapshots<br/>snapshot_id, jtbd, value_type, status]
    end

    subgraph Layer3 ["Tier 3: Customer Raw Materials & Enrichment"]
        D -->|Customer Rows| G[(base_customers_raw)]
        G --> H[Async Background Workers<br/>Spatial API & ZIP Array Resolution]
        H --> I[View: base_customers<br/>Enriched 1:1 Records with Segment & Value]
    end

    subgraph Layer4 ["Tier 4: Materialized 80-Persona Data Mart"]
        I -->|Completion Trigger| J[Materialization Step<br/>base_snapshot_personas_materialize]
        J --> K[(base_snapshot_personas_raw)]
        K --> L[View: base_snapshot_personas<br/>80 Pre-Aggregated Rows per snapshot_id]
    end

    subgraph Reporting ["Tier 5: Zero-Scan Fast Reporting & LLM Briefs"]
        L --> M[Query #1: get_brief_personas<br/>80 Rows + Canonical Taxonomy Catalog]
        C --> N[Query #2: get_brief_audience_stats<br/>Match Counts + Standing Story + jtbd]
        F --> N
        I --> N
        L --> O[Query #3: get_brief_demographics<br/>80 Shares x 70 Handles Instant Rollup]

        M --> P[Claude LLM Audience Brief<br/>base.generate.brief / Visual Dashboard]
        N --> P
        O --> P
    end

---

2. Entity Model & BigQuery Schema Reference

erDiagram
    base_brand_profiles ||--o{ base_snapshots : "applies to all snapshots for"
    base_snapshots ||--|{ base_customers : "contains N customer rows"
    base_snapshots ||--|{ base_snapshot_personas : "materializes 80 segment metrics"
    base_snapshot_personas }|--|| personas : "joins taxonomy metadata on (code, persona_version)"
    base_snapshot_personas }|--o{ persona_demographics : "rolls up demographic weights"

    base_brand_profiles {
        string _id PK
        string domain PK
        string brand_name
        string self_description
        string customer_insight
        timestamp event_timestamp
    }

    base_snapshots {
        string snapshot_id PK
        string pid
        string _id FK
        string domain FK
        string batch_type
        string value_type
        string jtbd
        string status
        timestamp uploaded_at
    }

    base_snapshot_personas {
        string snapshot_id PK
        string pid PK
        string _id PK
        string domain PK
        string persona_version PK
        string responseType PK
        string code PK
        int64 matched_count
        float64 audience_share_pct
        float64 national_share_pct
        float64 audience_index_score
        float64 ltv_vs_mean
        float64 family_share_pct
        string indexing_status
    }

    base_customers {
        string uuid PK
        string snapshot_id FK
        string _id FK
        string domain FK
        string email_hash
        string zip
        float64 value
        string segment
        string responseType
        string persona_version
    }

Table Dictionary

Table / View	Storage Type	Key / Partition	Description
base_brand_profiles_raw	Physical Table	PARTITION BY DATE(event_timestamp) CLUSTER BY _id, domain	Append-only log of standing brand context (brand_self_description, brand_customer_insight).
base_brand_profiles	SQL View	Deduplicated by (_id, domain)	Always projects the single latest narrative update for that brand.
base_snapshots_raw	Physical Table	PARTITION BY DATE(uploaded_at) CLUSTER BY pid, _id, domain, snapshot_id	Append-only lifecycle events for upload batches (ACTIVE, EXPEDITE, COMPLETED, DELETED) and jtbd.
base_snapshots	SQL View	Deduplicated by snapshot_id	Projects the single latest active operational state per snapshot batch.
base_snapshot_personas_raw	Physical Table	PARTITION BY DATE(snapshot_at) CLUSTER BY _id, domain, snapshot_id, responseType, code	Materialized extract table storing the 80 segment metrics per completed snapshot and responseType (xEmail, xZip).
base_snapshot_personas	SQL View	Deduplicated by (snapshot_id, responseType, code)	Active view over pre-computed persona distributions and LTV indices (160 rows max per snapshot: 80 xEmail + 80 xZip).
base_customers_raw	Physical Table	PARTITION BY DATE(snapshot_at) CLUSTER BY _id, domain, snapshot_id, email_hash	Raw staged individual customer rows.
base_customers	SQL View	Deduplicated by uuid	Enriched single source of truth for individual customer records.

---

3. End-to-End Lifecycle Sequence

The diagram below traces a customer batch from initial CSV submission through async background enrichment, completion detection, data mart materialization, and fast dashboard consumption:

sequenceDiagram
    autonumber
    actor User as Marketer / Client
    participant Flow as base.snapshot.submit
    participant BQ as BigQuery Storage
    participant Worker as base.enrichment.cron
    participant Monitor as base.snapshot.completion.cron
    participant Brief as base.generate.brief (LLM / Dashboard)

    User->>Flow: Upload CSV (emails, zips, LTV) + jtbd
    Flow->>BQ: Stream to base_snapshots_raw (ACTIVE) & base_customers_raw
    Flow-->>User: Return snapshot_id & upload confirmation

    loop Async Background Enrichment
        Worker->>BQ: Query pending records (WHERE segment IS NULL)
        Worker->>Worker: Resolve via lk_email_segments cache or Spatial API
        Worker->>BQ: Update customer rows with segment code (A01-Q08)
    end

    Note over Monitor,BQ: Polling for finished snapshots (0 pending remaining)
    Monitor->>BQ: Execute base_snapshots_check_completion
    BQ-->>Monitor: Return snapshot with 0 pending records

    rect rgb(240, 248, 255)
        Note over Monitor,BQ: Automated Materialization Phase
        Monitor->>BQ: Run base_snapshot_personas_materialize
        BQ->>BQ: Calculate shares, LTV ratios & INSERT 80 rows into base_snapshot_personas_raw
        Monitor->>BQ: Append status = 'COMPLETED' to base_snapshots_raw
        Monitor->>Monitor: Post Slack Completion Notification
    end

    User->>Brief: Request Audience Brief or open Dashboard
    rect rgb(245, 255, 245)
        Note over Brief,BQ: Zero-Scan Fast Read Phase (<50ms)
        Brief->>BQ: Run get_brief_personas (Reads 80 rows + joins taxonomy)
        Brief->>BQ: Run get_brief_audience_stats (Match stats + base_brand_profiles + jtbd)
        Brief->>BQ: Run get_brief_demographics (80 segment shares x 70 handles matrix)
        Brief->>Brief: Format prompt context & call Claude (Anthropic)
        Brief-->>User: Render Executive Strategy Brief & Visual Charts
    end

---

4. The 3 Core Reporting Queries

Query #1: Persona Distribution & Narrative (get_brief_personas)

• Role: Provides the ranked list of 80 personas with audience shares, national baseline indices, sample-gated LTV ratios, and rich copywriting copy.
• Scan Cost: Zero customer records scanned. Reads strictly from the 80 pre-aggregated rows in base_snapshot_personas and joins the canonical personas catalog.

WITH target_snapshot AS (
  SELECT snapshot_id
  FROM `socialsignal.beaconv2.base_snapshots`
  WHERE _id = '{{_id}}' AND domain = '{{domain}}'
    AND (status IS NULL OR status != 'DELETED')
    {{#snapshot_id}}AND snapshot_id = '{{snapshot_id}}'{{/snapshot_id}}
  ORDER BY uploaded_at DESC
  LIMIT 1
)
SELECT
  e.code,
  p.label,
  p.family_code,
  p.family_label,
  e.audience_share_pct,
  e.national_share_pct,
  IF(e.audience_index_score >= 0, '+', '-') AS audience_index_sign,
  ROUND(ABS(e.audience_index_score), 1) AS audience_index_val,
  e.ltv_vs_mean,
  e.matched_count,
  e.family_share_pct,
  p.creative_tag,
  p.description_p1,
  p.description_p2,
  p.signals_high,
  p.signals_low,
  e.indexing_status
FROM `socialsignal.beaconv2.base_snapshot_personas` e
JOIN `socialsignal.beaconv2.personas` p
  ON e.code = p.segment
 AND e.persona_version = p.persona_version
WHERE e.snapshot_id = (SELECT snapshot_id FROM target_snapshot)
ORDER BY e.audience_index_score DESC;

---

Query #2: Audience Statistics & Brand Context (get_brief_audience_stats)

• Role: Computes total records, email/zip match counts, overall match rates, and audience mean LTV from base_customers, while joining standing brand narrative from base_brand_profiles and upload intent (jtbd, value_type) from base_snapshots.

WITH target_snapshot AS (
  SELECT snapshot_id, pid, _id, domain, batch_type, value_type, jtbd, uploaded_at
  FROM `socialsignal.beaconv2.base_snapshots`
  WHERE _id = '{{_id}}' AND domain = '{{domain}}'
    AND (status IS NULL OR status != 'DELETED')
    {{#snapshot_id}}AND snapshot_id = '{{snapshot_id}}'{{/snapshot_id}}
  ORDER BY uploaded_at DESC
  LIMIT 1
),
brand_profile AS (
  SELECT _id, domain, brand_name, self_description, customer_insight
  FROM `socialsignal.beaconv2.base_brand_profiles`
  WHERE _id = '{{_id}}' AND domain = '{{domain}}'
),
base_stats AS (
  SELECT
    COUNT(1) AS total_records,
    COUNTIF(responseType IN ('xEmail', 'EMAIL')
            AND segment IS NOT NULL AND segment != 'Z00') AS email_matched_count,
    COUNTIF(responseType IN ('xZip', 'ZIP', 'ZIP-BQ')
            AND segment IS NOT NULL AND segment != 'Z00') AS zip_matched_count,
    COUNTIF(responseType IN ('xEmail', 'EMAIL', 'xZip', 'ZIP', 'ZIP-BQ', 'DIRECT')
            AND segment IS NOT NULL AND segment != 'Z00') AS matched_count,
    COUNTIF(NOT (responseType IN ('xEmail', 'EMAIL', 'xZip', 'ZIP', 'ZIP-BQ', 'DIRECT')
                 AND segment IS NOT NULL AND segment != 'Z00')) AS unmatched_count,
    ROUND(AVG(IF(responseType IN ('xEmail', 'EMAIL', 'xZip', 'ZIP', 'ZIP-BQ', 'DIRECT')
                 AND segment IS NOT NULL AND segment != 'Z00', value, NULL)), 0) AS audience_mean_ltv
  FROM `socialsignal.beaconv2.base_customers`
  WHERE _id = '{{_id}}' AND domain = '{{domain}}'
    AND snapshot_id = (SELECT snapshot_id FROM target_snapshot)
)
SELECT
  COALESCE(bp.brand_name, u.company, snap.domain) AS brand_name,
  snap.domain,
  bp.self_description AS brand_self_description,
  bp.customer_insight AS brand_customer_insight,
  snap.jtbd AS brand_jtbd,
  COALESCE(snap.value_type, 'LTV') AS value_column_name,
  snap.snapshot_id,
  FORMAT_TIMESTAMP('%Y-%m-%dT%H:%M:%SZ', snap.uploaded_at) AS snapshot_uploaded_at,
  stats.total_records,
  stats.email_matched_count,
  ROUND(SAFE_DIVIDE(stats.email_matched_count, stats.total_records) * 100, 1) AS email_matched_pct,
  stats.zip_matched_count,
  ROUND(SAFE_DIVIDE(stats.zip_matched_count, stats.total_records) * 100, 1) AS zip_matched_pct,
  stats.unmatched_count,
  ROUND(SAFE_DIVIDE(stats.unmatched_count, stats.total_records) * 100, 1) AS unmatched_pct,
  stats.matched_count,
  stats.matched_count AS records_used_count,
  ROUND(SAFE_DIVIDE(stats.matched_count, stats.total_records) * 100, 1) AS match_rate_pct,
  ROUND(SAFE_DIVIDE(stats.matched_count, stats.total_records) * 100, 1) AS combined_match_rate_pct,
  stats.audience_mean_ltv
FROM base_stats stats
CROSS JOIN target_snapshot snap
LEFT JOIN brand_profile bp ON snap._id = bp._id AND snap.domain = bp.domain
LEFT JOIN `socialsignal.beaconv2.users` u ON snap._id = u._id;

---

Query #3: Demographic Signals Rollup (get_brief_demographics)

Why Demographics is an Instant Math Rollup (The Nutrition Facts Principle):

Spatial.ai pre-calculates the fixed demographic properties for all 80 personas (e.g. Persona A01 is $98\%$ high-income with $\$2\text{M}+$ homes, stored in persona_demographics).

To calculate the demographic profile of a 100,000-person brand audience, BigQuery does not need to scan the 100,000 customer rows. It simply takes the 80 pre-aggregated segment percentages from base_snapshot_personas and calculates the weighted sum across the 70 demographic handles:

$$\text{Weighted Score}d = \sum}} \left( \text{Audience Shares \times \text{Demographic Score} \right)$$

graph LR
    subgraph Extract ["80 Snapshot Shares"]
        A["Segment A01: 10.9%<br/>Segment A02: 4.2%<br/>...<br/>Segment Q08: 0.7%"]
    end

    subgraph Catalog ["70 Demographic Handles"]
        B["Handle: $2M+ Homes<br/>Handle: Graduate Degree<br/>Handle: Business/Finance<br/>..."]
    end

    subgraph Rollup ["Dot Product Rollup (<50ms)"]
        C["Weighted Demographic Signals<br/>$2M+ Homes: +141.6%<br/>Graduate Degree: +51.2%<br/>Farm-Related: -40.7%"]
    end

    A -->|Multiply & Sum| C
    B -->|Multiply & Sum| C

WITH target_snapshot AS (
  SELECT snapshot_id
  FROM `socialsignal.beaconv2.base_snapshots`
  WHERE _id = '{{_id}}' AND domain = '{{domain}}'
    AND (status IS NULL OR status != 'DELETED')
    {{#snapshot_id}}AND snapshot_id = '{{snapshot_id}}'{{/snapshot_id}}
  ORDER BY uploaded_at DESC
  LIMIT 1
),
audience_shares AS (
  SELECT
    code AS segment,
    (audience_share_pct / 100.0) AS share
  FROM `socialsignal.beaconv2.base_snapshot_personas`
  WHERE snapshot_id = (SELECT snapshot_id FROM target_snapshot)
),
weighted_demographics AS (
  SELECT
    d.demographic_category AS dimension,
    d.title AS label,
    ROUND(SUM(a.share * d.score), 1) AS weighted_score
  FROM audience_shares a
  JOIN `socialsignal.beaconv2.persona_demographics` d
    ON a.segment = d.segment
  GROUP BY d.demographic_category, d.title
),
formatted_signals AS (
  SELECT
    dimension,
    label,
    IF(weighted_score >= 0, '+', '-') AS index_sign,
    ROUND(ABS(weighted_score), 1) AS index_val,
    weighted_score
  FROM weighted_demographics
  WHERE ABS(weighted_score) >= 10.0
)
SELECT
  dimension,
  ARRAY_AGG(
    STRUCT(label, index_sign, index_val)
    ORDER BY weighted_score DESC  -- ← Positive defining drivers first, negative under-indices last
  ) AS values
FROM formatted_signals
GROUP BY dimension
ORDER BY dimension;

---

5. Longitudinal Time-Series Analysis (Q1 vs Q2 Trend Reports)

Because metrics are pre-aggregated into 80 rows per snapshot, comparing customer base evolution over time (e.g. Quarter-over-Quarter or Month-over-Month) is virtually free in BigQuery.

graph TD
    subgraph Snapshots ["Snapshots in base_snapshot_personas"]
        Q1["Q1-2026 Snapshot<br/>(80 Rows)"]
        Q2["Q2-2026 Snapshot<br/>(80 Rows)"]
    end

    subgraph TrendQuery ["Lightweight Longitudinal Query (160 Rows Scanned)"]
        T["SELECT code, q1.audience_share_pct, q2.audience_share_pct,<br/>(q2.audience_share_pct - q1.audience_share_pct) AS share_delta<br/>FROM q1 JOIN q2 USING (code)"]
    end

    subgraph Output ["Client Trend Chart"]
        O["Segment A01: +3.3% share expansion<br/>Segment H02: +18% LTV growth<br/>Segment Q04: -2.1% share contraction"]
    end

    Q1 --> T
    Q2 --> T
    T --> Output

To compare two snapshots across all 80 personas, BigQuery reads only 160 rows total ($80 + 80 \approx 20\text{ KB}$), avoiding hundreds of thousands of customer row scans.

---

6. State Machine & Idempotency Rules

The background completion monitor base.snapshot.completion.cron uses a two-gate state machine to guarantee that snapshots are processed exactly once:

stateDiagram-v2
    [*] --> ACTIVE : base.snapshot.submit
    ACTIVE --> ACTIVE : Async Enrichment In-Progress (Pending > 0)
    ACTIVE --> COMPLETED : 0 Pending Records (Cron Triggers Materialization)
    COMPLETED --> [*] : Excluded from Future Cron Ticks (Status is not ACTIVE)
    COMPLETED --> DELETED : User Deletes Snapshot (Appends DELETED row)
    DELETED --> [*] : Excluded from Active Views

1. Gate 1: WHERE s.status IN ('ACTIVE', 'EXPEDITE') — Only uncompleted batches are inspected.
2. Gate 2: HAVING COUNTIF(c.segment IS NULL AND c.responseType IS NULL) = 0 — Requires 100% of customer rows to be resolved.
3. Transition: As soon as the materialization query finishes, base_snapshots_update_status appends status = 'COMPLETED' to base_snapshots_raw.
4. Idempotent View Deduplication: If a snapshot is ever manually re-materialized, the view base_snapshot_personas deduplicates by (snapshot_id, responseType, code) ordered by created_at DESC, guaranteeing zero duplicate rows while maintaining distinct 80-row segments for xEmail and xZip.

---

7. Annual Spatial.ai Taxonomy Release Runbook

When Spatial.ai releases an updated PersonaLive dataset (e.g. 26.05 $\longrightarrow$ 27.01):

graph LR
    A["1. ZCTA Counts CSV<br/>(PersonaLive_2027.csv)"] -->|calculate_percentages.ts| B["2. segment_percentages.csv<br/>(80 Exact Proportions)"]
    B -->|load-personas.ts| C["3. personas_load.json<br/>(NDJSON Batch)"]
    C -->|bq load| D[("4. BQ: personas_raw<br/>(personas View Auto-Updates)")]

1. Re-calculate National Baseline Proportions:

   npx tsx sscore/resources/spatial/calculate_percentages.ts --version 27.01
   

2. Generate Validated Persona Records:

   npx tsx sscore/scripts/load-personas.ts
   

3. Ingest to BigQuery:

   bq load --source_format=NEWLINE_DELIMITED_JSON socialsignal:beaconv2.personas_raw personas_load.json
   

Because snapshot extracts store persona_version and join personas on (code, persona_version), historical snapshots remain locked to their historical taxonomy while new snapshots seamlessly adopt the updated release.