BI Lead Journey: Product-Led Growth Analytics Platform

  Current State                         Target State
  ┌────────────────────────┐            ┌────────────────────────┐
  │ 5 analytics tools      │            │ 1 governed platform    │
  │ 3 different ARR numbers│            │ 1 canonical ARR number │
  │ Manual board decks     │            │ Auto-generated reports │
  │                        │            │                        │
  │ PM self-service: 0%    │  ────▶     │ PM self-service: 80%   │
  │ Deck prep: 3 days      │            │ Deck prep: 15 minutes  │
  └────────────────────────┘            └────────────────────────┘

{
  "source": "stripe_webhooks",
  "connector": "airbyte/source-stripe",
  "config": {
    "streams": [
      "subscriptions", "invoices", "charges",
      "customers", "plans", "products"
    ],
    "sync_mode": "incremental",
    "cursor_field": "created",
    "frequency": "every_hour",
    "webhook_events": [
      "customer.subscription.created",
      "customer.subscription.updated",
      "customer.subscription.deleted",
      "invoice.paid",
      "invoice.payment_failed"
    ]
  }
}

{
  "source": "aws_cost_explorer",
  "connector": "airbyte/source-aws-cost-explorer",
  "config": {
    "streams": ["cost_and_usage"],
    "granularity": "DAILY",
    "group_by": ["SERVICE", "REGION", "USAGE_TYPE"],
    "sync_mode": "full_refresh",
    "frequency": "daily_at_0600_utc",
    "filter": {
      "not": {
        "dimensions": {
          "key": "RECORD_TYPE",
          "values": ["Credit", "Refund"]
        }
      }
    }
  }
}

-- Detect timezone mismatches between Stripe and product DB
SELECT
    s.subscription_id,
    s.created                                    AS stripe_created_utc,
    w.created_at                                 AS product_created_local,
    -- If product DB stores Pacific time, there is a gap
    s.created - w.created_at                     AS time_difference
FROM stripe.subscriptions s
JOIN postgresql.workspaces w
    ON s.metadata->>'workspace_id' = CAST(w.workspace_id AS VARCHAR)
WHERE ABS(EXTRACT(EPOCH FROM (s.created - w.created_at))) > 28800
LIMIT 10;

{
  "governance_tags": [
    {
      "table": "users",
      "column": "email",
      "classification": "PII",
      "masking_policy": "hash_sha256",
      "applies_to_roles": ["analyst", "pm"],
      "exempt_roles": ["admin", "compliance"]
    },
    {
      "table": "stripe.customers",
      "column": "payment_method",
      "classification": "PCI",
      "masking_policy": "redact",
      "applies_to_roles": ["ALL"],
      "exempt_roles": ["finance_admin"]
    },
    {
      "table": "subscriptions",
      "column": "mrr",
      "classification": "FINANCIAL",
      "masking_policy": "none",
      "access_audit": true
    }
  ]
}

Finance team (Stripe dashboard):
  invoices.amount_paid → SUM(last 12 months) → manual annualization

Sales team (Salesforce):
  opportunities.amount → SUM(closed-won, current year)

Product team (internal dashboard):
  subscriptions.mrr → SUM(active) × 12

Emily's canonical definition:
  subscriptions.mrr (active status, exclude trials)
    → SUM by month → × 12
    → validated against Stripe invoices ± 2%

-- SEMANTIC LAYER: Canonical SaaS Metric Definitions
-- These are registered in the BI Workbench and available to all users
 
-- ============================================================
-- METRIC: Monthly Recurring Revenue (MRR)
-- Owner: Emily Park | Validated against: Stripe Dashboard
-- ============================================================
-- Definition: Sum of monthly recurring subscription amounts for
-- active subscriptions, excluding trials and one-time charges.
SELECT
    DATE_TRUNC('month', s.current_period_start AT TIME ZONE 'UTC')
                                                 AS month,
    SUM(CASE
        WHEN s.status = 'active' AND s.trial_end < CURRENT_TIMESTAMP
        THEN s.mrr
        ELSE 0
    END)                                         AS mrr,
    COUNT(DISTINCT CASE
        WHEN s.status = 'active' AND s.trial_end < CURRENT_TIMESTAMP
        THEN s.workspace_id
    END)                                         AS paying_workspaces
FROM subscriptions s
GROUP BY 1;
 
-- ============================================================
-- METRIC: Net Dollar Retention (NDR) - Trailing 12 Months
-- Formula: (Starting MRR + Expansion - Contraction - Churn) / Starting MRR
-- ============================================================
WITH monthly_mrr AS (
    SELECT
        workspace_id,
        DATE_TRUNC('month', current_period_start AT TIME ZONE 'UTC') AS month,
        SUM(mrr)                                 AS mrr
    FROM subscriptions
    WHERE status = 'active'
      AND trial_end < CURRENT_TIMESTAMP
    GROUP BY 1, 2
),
mrr_changes AS (
    SELECT
        COALESCE(curr.workspace_id, prev.workspace_id) AS workspace_id,
        curr.month,
        COALESCE(curr.mrr, 0)                    AS current_mrr,
        COALESCE(prev.mrr, 0)                    AS previous_mrr,
        CASE
            WHEN prev.mrr IS NULL THEN 'new'
            WHEN curr.mrr IS NULL THEN 'churned'
            WHEN curr.mrr > prev.mrr THEN 'expansion'
            WHEN curr.mrr < prev.mrr THEN 'contraction'
            ELSE 'retained'
        END                                      AS change_type
    FROM monthly_mrr curr
    FULL OUTER JOIN monthly_mrr prev
        ON curr.workspace_id = prev.workspace_id
        AND curr.month = prev.month + INTERVAL '1' MONTH
)
SELECT
    month,
    SUM(CASE WHEN change_type = 'new' THEN current_mrr ELSE 0 END)        AS new_mrr,
    SUM(CASE WHEN change_type = 'expansion'
         THEN current_mrr - previous_mrr ELSE 0 END)                      AS expansion_mrr,
    SUM(CASE WHEN change_type = 'contraction'
         THEN previous_mrr - current_mrr ELSE 0 END)                      AS contraction_mrr,
    SUM(CASE WHEN change_type = 'churned' THEN previous_mrr ELSE 0 END)   AS churned_mrr,
    -- NDR = (retained + expansion) / starting MRR
    ROUND((SUM(CASE WHEN change_type IN ('retained', 'expansion')
               THEN current_mrr ELSE 0 END) * 1.0) /
          NULLIF(SUM(CASE WHEN change_type != 'new'
               THEN previous_mrr ELSE 0 END), 0), 3)                      AS ndr
FROM mrr_changes
GROUP BY month
ORDER BY month;
 
-- ============================================================
-- METRIC: Churn Rate (Logo and Revenue)
-- ============================================================
SELECT
    DATE_TRUNC('month', cancelled_at AT TIME ZONE 'UTC')  AS month,
    -- Logo churn: workspaces cancelled / starting workspaces
    COUNT(DISTINCT CASE WHEN status = 'cancelled'
          THEN workspace_id END) * 1.0 /
    NULLIF(COUNT(DISTINCT workspace_id), 0)               AS logo_churn_rate,
    -- Revenue churn: MRR lost / starting MRR
    SUM(CASE WHEN status = 'cancelled' THEN mrr ELSE 0 END) /
    NULLIF(SUM(mrr), 0)                                   AS revenue_churn_rate
FROM subscriptions
GROUP BY 1;

-- Signup cohort retention analysis (monthly cohorts)
WITH cohorts AS (
    SELECT
        workspace_id,
        DATE_TRUNC('month', created_at AT TIME ZONE 'UTC') AS cohort_month
    FROM workspaces
    WHERE plan != 'free'
),
monthly_activity AS (
    SELECT
        e.workspace_id,
        DATE_TRUNC('month', e.timestamp)         AS activity_month
    FROM events e
    WHERE e.event_type NOT IN ('session_start', 'session_end', 'heartbeat')
    GROUP BY 1, 2
)
SELECT
    c.cohort_month,
    DATE_DIFF('month', c.cohort_month, a.activity_month) AS months_since_signup,
    COUNT(DISTINCT a.workspace_id)               AS active_workspaces,
    COUNT(DISTINCT c.workspace_id)               AS cohort_size,
    ROUND(COUNT(DISTINCT a.workspace_id) * 100.0 /
          COUNT(DISTINCT c.workspace_id), 1)     AS retention_pct
FROM cohorts c
LEFT JOIN monthly_activity a ON c.workspace_id = a.workspace_id
    AND a.activity_month >= c.cohort_month
GROUP BY c.cohort_month, DATE_DIFF('month', c.cohort_month, a.activity_month)
ORDER BY c.cohort_month, months_since_signup;

-- Cross-validate MRR: Semantic Layer vs Stripe raw invoices
WITH semantic_mrr AS (
    SELECT month, mrr AS semantic_mrr
    FROM semantic_layer.mrr_monthly
),
stripe_mrr AS (
    SELECT
        DATE_TRUNC('month', i.period_start AT TIME ZONE 'UTC') AS month,
        SUM(i.amount_paid / 100.0)               AS stripe_mrr
    FROM stripe.invoices i
    WHERE i.status = 'paid'
      AND i.billing_reason = 'subscription_cycle'
    GROUP BY 1
)
SELECT
    s.month,
    s.semantic_mrr,
    st.stripe_mrr,
    ROUND(ABS(s.semantic_mrr - st.stripe_mrr), 2)  AS difference,
    ROUND(ABS(s.semantic_mrr - st.stripe_mrr) * 100.0
          / st.stripe_mrr, 2)                       AS pct_difference
FROM semantic_mrr s
JOIN stripe_mrr st ON s.month = st.month
ORDER BY s.month DESC;

{
  "pipeline": {
    "name": "saas-metrics-daily",
    "schedule": "0 7 * * *",
    "description": "Daily SaaS metrics computation, cohort analysis, and board data refresh",
    "stages": [
      {
        "name": "normalize_timezones",
        "type": "sql_transform",
        "description": "Convert all timestamps to UTC, fix product DB Pacific time",
        "query_ref": "timezone_normalization.sql",
        "output_table": "analytics.events_utc"
      },
      {
        "name": "compute_mrr",
        "type": "sql_transform",
        "depends_on": ["normalize_timezones"],
        "query_ref": "mrr_monthly.sql",
        "output_table": "analytics.mrr_monthly"
      },
      {
        "name": "compute_ndr",
        "type": "sql_transform",
        "depends_on": ["compute_mrr"],
        "query_ref": "ndr_calculation.sql",
        "output_table": "analytics.ndr_monthly"
      },
      {
        "name": "compute_cohorts",
        "type": "sql_transform",
        "depends_on": ["normalize_timezones"],
        "query_ref": "cohort_retention.sql",
        "output_table": "analytics.cohort_retention"
      },
      {
        "name": "compute_infrastructure_costs",
        "type": "sql_transform",
        "depends_on": ["normalize_timezones"],
        "query_ref": "infra_cost_per_user.sql",
        "output_table": "analytics.infrastructure_cost_daily"
      },
      {
        "name": "quality_gate",
        "type": "data_quality",
        "depends_on": ["compute_mrr", "compute_ndr", "compute_cohorts"],
        "checks": [
          {
            "name": "mrr_continuity",
            "type": "custom_sql",
            "query": "SELECT ABS(curr_mrr - prev_mrr) / prev_mrr FROM analytics.mrr_monthly ORDER BY month DESC LIMIT 1",
            "max_value": 0.10,
            "severity": "critical",
            "note": "MRR should not change more than 10% month-over-month"
          },
          {
            "name": "stripe_reconciliation",
            "type": "custom_sql",
            "query": "SELECT ABS(semantic_mrr - stripe_mrr) / stripe_mrr FROM analytics.mrr_reconciliation ORDER BY month DESC LIMIT 1",
            "max_value": 0.02,
            "severity": "warning"
          },
          {
            "name": "cohort_completeness",
            "type": "row_count",
            "table": "analytics.cohort_retention",
            "min_rows": 100,
            "severity": "critical"
          }
        ]
      },
      {
        "name": "refresh_board_deck",
        "type": "sql_transform",
        "depends_on": ["quality_gate"],
        "query_ref": "board_deck_metrics.sql",
        "output_table": "analytics.board_deck_latest",
        "trigger_dashboard_refresh": true
      }
    ]
  }
}

{
  "pipelines": [
    {
      "name": "saas-metrics-weekly",
      "schedule": "0 8 * * MON",
      "stages": [
        {
          "name": "weekly_cohort_analysis",
          "type": "sql_transform",
          "query_ref": "weekly_cohort_deep_dive.sql"
        },
        {
          "name": "feature_adoption_report",
          "type": "sql_transform",
          "query_ref": "feature_adoption_weekly.sql"
        }
      ]
    },
    {
      "name": "board-report-monthly",
      "schedule": "0 9 1 * *",
      "stages": [
        {
          "name": "generate_board_metrics",
          "type": "sql_transform",
          "query_ref": "board_metrics_monthly.sql"
        },
        {
          "name": "validate_against_finance",
          "type": "data_quality",
          "checks": [
            {
              "name": "arr_matches_finance",
              "type": "custom_sql",
              "query": "SELECT ABS(our_arr - finance_arr) / finance_arr FROM analytics.arr_reconciliation",
              "max_value": 0.005,
              "severity": "critical",
              "note": "Board-reported ARR must match finance within 0.5%"
            }
          ]
        }
      ]
    }
  ]
}

-- Segment-level SaaS metrics
SELECT
    CASE
        WHEN s.mrr >= 5000  THEN 'Enterprise'
        WHEN s.mrr >= 1000  THEN 'Mid-Market'
        WHEN s.mrr >= 200   THEN 'SMB'
        ELSE 'Self-Serve'
    END                                          AS segment,
    COUNT(DISTINCT w.workspace_id)               AS accounts,
    ROUND(SUM(s.mrr), 0)                         AS total_mrr,
    ROUND(AVG(s.mrr), 0)                         AS avg_mrr,
    ROUND(AVG(activity.dau), 0)                  AS avg_dau,
    ROUND(AVG(activity.features_adopted), 1)     AS avg_features,
    ROUND(AVG(CASE WHEN w.status = 'cancelled' THEN 1.0
              ELSE 0 END) * 100, 1)              AS churn_rate_pct,
    ROUND(AVG(scores.churn_probability), 2)      AS avg_churn_risk
FROM workspaces w
JOIN subscriptions s ON w.workspace_id = s.workspace_id AND s.status = 'active'
LEFT JOIN analytics.workspace_activity activity ON w.workspace_id = activity.workspace_id
LEFT JOIN ml.churn_scores_daily scores ON w.workspace_id = scores.workspace_id
GROUP BY 1
ORDER BY total_mrr DESC;

-- Leading indicators of expansion (upgrade/seat addition)
WITH expansion_events AS (
    SELECT
        workspace_id,
        DATE_TRUNC('month', event_date)          AS expansion_month,
        expansion_type,
        mrr_increase
    FROM analytics.subscription_changes
    WHERE expansion_type IN ('plan_upgrade', 'seat_addition')
),
pre_expansion_signals AS (
    SELECT
        e.workspace_id,
        e.expansion_month,
        -- Usage signals 30 days before expansion
        AVG(a.seat_utilization)                  AS avg_seat_util,
        AVG(a.dau)                               AS avg_dau,
        AVG(a.features_adopted)                  AS avg_features,
        MAX(a.hit_seat_limit)                    AS hit_seat_limit,
        MAX(a.hit_storage_limit)                 AS hit_storage_limit,
        COUNT(DISTINCT t.ticket_id)              AS support_tickets
    FROM expansion_events e
    JOIN analytics.workspace_activity_daily a
        ON e.workspace_id = a.workspace_id
        AND a.activity_date BETWEEN e.expansion_month - INTERVAL '30' DAY
            AND e.expansion_month
    LEFT JOIN support_tickets t
        ON e.workspace_id = t.workspace_id
        AND t.subject LIKE '%limit%' OR t.subject LIKE '%upgrade%'
    GROUP BY e.workspace_id, e.expansion_month
)
SELECT
    ROUND(AVG(avg_seat_util), 2)                 AS avg_seat_utilization,
    ROUND(AVG(avg_features), 1)                  AS avg_features_adopted,
    ROUND(AVG(CASE WHEN hit_seat_limit = 1 THEN 1.0 ELSE 0 END), 2)
                                                 AS pct_hit_seat_limit,
    ROUND(AVG(CASE WHEN hit_storage_limit = 1 THEN 1.0 ELSE 0 END), 2)
                                                 AS pct_hit_storage_limit
FROM pre_expansion_signals;

  CloudFlow Dashboard Suite
  ┌────────────────────────────────────────────────────────┐
  │                                                        │
  │  1. SaaS Metrics Overview (Executive)                  │
  │     MRR, ARR, NDR, Churn, ARPA -- real-time            │
  │                                                        │
  │  2. Product Usage (Product Team)                       │
  │     DAU/MAU, Feature Adoption, Activation Funnel       │
  │                                                        │
  │  3. Funnel Analytics (Growth Team)                     │
  │     Signup → Activation → Conversion → Expansion       │
  │                                                        │
  │  4. Customer Health (Customer Success)                 │
  │     Churn scores, Health trends, CSM workload          │
  │                                                        │
  │  5. Infrastructure Costs (Engineering)                 │
  │     Cost per user, Cost per service, Trend analysis    │
  │                                                        │
  │  6. Board Report (CEO/Board)                           │
  │     Auto-generated, finance-validated, exportable      │
  │                                                        │
  └────────────────────────────────────────────────────────┘

{
  "self_service_config": {
    "role": "product_manager",
    "allowed_schemas": ["analytics", "product_events"],
    "denied_schemas": ["stripe", "salesforce"],
    "denied_columns": ["email", "payment_method", "ip_address"],
    "query_limits": {
      "max_scan_rows": 100000000,
      "max_runtime_seconds": 120,
      "max_concurrent_queries": 3
    },
    "pre_built_templates": [
      {
        "name": "Feature Adoption by Cohort",
        "description": "How quickly each signup cohort adopts a given feature",
        "parameters": ["feature_name", "date_range"]
      },
      {
        "name": "Funnel Drop-off Analysis",
        "description": "Identify where users drop off in a multi-step flow",
        "parameters": ["funnel_steps", "date_range", "segment"]
      },
      {
        "name": "A/B Test Results",
        "description": "Compare metrics between experiment variants",
        "parameters": ["experiment_id", "metric"]
      }
    ]
  }
}

{
  "alerts": [
    {
      "name": "mrr_drop_alert",
      "metric": "mrr_monthly",
      "condition": "month_over_month_change < -0.05",
      "severity": "critical",
      "notify": ["michael@cloudflow.io", "emily@cloudflow.io", "slack://exec-team"],
      "message": "MRR dropped more than 5% month-over-month. Current: ${current_mrr}, Previous: ${previous_mrr}"
    },
    {
      "name": "churn_spike_alert",
      "metric": "weekly_churn_rate",
      "condition": "value > 0.015",
      "severity": "high",
      "notify": ["slack://cs-team", "emily@cloudflow.io"],
      "message": "Weekly churn rate exceeded 1.5%. ${churned_count} accounts cancelled."
    },
    {
      "name": "infra_cost_per_user_alert",
      "metric": "infrastructure_cost_per_user",
      "condition": "month_over_month_change > 0.10",
      "severity": "warning",
      "notify": ["slack://eng-leads"],
      "message": "Infrastructure cost per user increased > 10%. Current: $${current_value}/user/month."
    },
    {
      "name": "stripe_reconciliation_alert",
      "metric": "mrr_reconciliation_diff_pct",
      "condition": "value > 0.02",
      "severity": "critical",
      "notify": ["emily@cloudflow.io", "finance@cloudflow.io"],
      "message": "MRR calculation diverges from Stripe by more than 2%. Investigate timezone normalization."
    }
  ]
}

-- Weekly data quality summary
SELECT
    source_name,
    MAX(last_sync_at)                            AS last_sync,
    DATE_DIFF('minute', MAX(last_sync_at), CURRENT_TIMESTAMP)
                                                 AS minutes_since_sync,
    COUNT(DISTINCT sync_date)                    AS days_with_data_7d,
    ROUND(AVG(row_count), 0)                     AS avg_daily_rows,
    SUM(CASE WHEN sync_status = 'failed' THEN 1 ELSE 0 END)
                                                 AS failed_syncs_7d,
    ROUND(AVG(null_rate), 3)                     AS avg_null_rate
FROM analytics.ingestion_health
WHERE sync_date >= CURRENT_DATE - INTERVAL '7' DAY
GROUP BY source_name
ORDER BY minutes_since_sync DESC;

{
  "experiment": {
    "name": "embedded-analytics-pilot",
    "hypothesis": "Giving workspace admins a usage dashboard will increase engagement and reduce churn",
    "variant": {
      "name": "usage_dashboard",
      "description": "In-workspace analytics showing team activity, project progress, and adoption metrics",
      "target_accounts": 200,
      "selection": "random_from_mid_market_segment"
    },
    "control": {
      "description": "No embedded analytics (existing experience)",
      "target_accounts": 200
    },
    "primary_metric": "90_day_retention",
    "secondary_metrics": ["dau_change", "feature_adoption_change", "nps_change"],
    "duration_weeks": 12
  }
}

-- PM self-service adoption over time
SELECT
    DATE_TRUNC('week', query_timestamp)          AS week,
    COUNT(DISTINCT user_id)                      AS unique_pms_querying,
    COUNT(*)                                     AS total_queries,
    ROUND(AVG(query_duration_seconds), 1)        AS avg_query_time,
    COUNT(CASE WHEN used_template = true THEN 1 END) AS template_queries,
    COUNT(CASE WHEN used_template = false THEN 1 END) AS custom_queries
FROM analytics.query_audit_log
WHERE user_role = 'product_manager'
  AND query_timestamp >= CURRENT_DATE - INTERVAL '12' WEEK
GROUP BY 1
ORDER BY 1;