Data Liquidity — Your Unfair Advantage

-- Central Customer Database Schema
-- Cost: $15/month PostgreSQL instance

CREATE TABLE customers (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email           VARCHAR(255) UNIQUE NOT NULL,
    name            VARCHAR(255),
    company         VARCHAR(255),
    created_at      TIMESTAMP DEFAULT NOW(),
    updated_at      TIMESTAMP DEFAULT NOW(),

    -- Source system IDs (for sync reconciliation)
    stripe_id       VARCHAR(100),
    salesforce_id   VARCHAR(100),
    intercom_id     VARCHAR(100),
    zendesk_id      VARCHAR(100),

    -- Computed fields (updated by sync jobs)
    lifetime_value  DECIMAL(10,2) DEFAULT 0,
    health_score    INTEGER DEFAULT 50,  -- 0-100
    last_activity   TIMESTAMP,
    tier            VARCHAR(50),         -- free, starter, pro, enterprise
    churn_risk      VARCHAR(20)          -- low, medium, high
);

CREATE TABLE customer_events (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id     UUID REFERENCES customers(id),
    event_type      VARCHAR(100) NOT NULL,  -- payment, support_ticket, login, etc.
    source_system   VARCHAR(50) NOT NULL,   -- stripe, zendesk, mixpanel, etc.
    event_data      JSONB,
    occurred_at     TIMESTAMP NOT NULL,
    synced_at       TIMESTAMP DEFAULT NOW()
);

CREATE TABLE customer_communications (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id     UUID REFERENCES customers(id),
    channel         VARCHAR(50) NOT NULL,   -- email, chat, slack, phone
    direction       VARCHAR(10) NOT NULL,   -- inbound, outbound
    subject         TEXT,
    summary         TEXT,                   -- AI-generated summary
    sentiment       VARCHAR(20),            -- positive, neutral, negative
    occurred_at     TIMESTAMP NOT NULL
);

-- Indexes for agent queries (fast lookups)
CREATE INDEX idx_events_customer ON customer_events(customer_id, occurred_at DESC);
CREATE INDEX idx_events_type ON customer_events(event_type, occurred_at DESC);
CREATE INDEX idx_comms_customer ON customer_communications(customer_id, occurred_at DESC);
CREATE INDEX idx_customers_health ON customers(health_score, churn_risk);

Your Assignment

1. Map your data sources: List every system where customer data lives. For each, note the data types, access method (API, export, scraping), and approximate record count.
2. Identify your top 3 sources: Rank by value to agent operations. Which three systems, if unified, would enable the most impactful automations?
3. Design your schema: Adapt the template above to your specific data model. Add fields unique to your business. Remove fields you do not need.
4. Estimate your ROI: Calculate hours currently spent on cross-system data queries. Multiply by your hourly rate. That is your baseline annual savings.
5. Set your Week 1 goal: Choose your database provider, finalize your schema, and schedule your first 6-hour work block.

Target outcome: A complete data liquidity plan with schema design, source prioritization, sync schedule, and ROI projection -- ready to execute in the next 4 weeks.