BI Lead Journey: Real-Time Revenue Command Center

{
  "connector": "source-stripe",
  "config": {
    "account_id": "${STRIPE_ACCOUNT_ID}",
    "client_secret": "${STRIPE_SECRET_KEY}",
    "lookback_window_days": 3
  },
  "streams": [
    { "name": "charges", "sync_mode": "incremental", "cursor_field": "created" },
    { "name": "refunds", "sync_mode": "incremental", "cursor_field": "created" },
    { "name": "disputes", "sync_mode": "incremental", "cursor_field": "created" },
    { "name": "balance_transactions", "sync_mode": "incremental", "cursor_field": "created" }
  ],
  "schedule": { "type": "cron", "expression": "*/15 * * * *" }
}

Revenue Data Map
────────────────
Shopify Orders ──────────────┐
  order_id, total_price,     │
  currency, financial_status │
                             ├──▶ Revenue Fact Table (Sofia creates)
Stripe Charges ──────────────┤         │
  charge_id, amount,         │         │     ┌──▶ GMV
  currency, status           │         ├─────┼──▶ Net Revenue
                             │         │     ├──▶ Refund Rate
Stripe Refunds ──────────────┤         │     └──▶ ROAS
  refund_id, amount,         │         │
  reason                     │         │
                             │         │
Google Ads ──────────────────┘         │
  campaign_id, spend,                  │
  clicks, conversions                  │
                                       │
CRM Segments ──────────────────────────┘
  customer_id, segment,
  acquisition_channel

Column Profiling: stripe.public.charges
────────────────────────────────────────
Column          Type       Nulls    Notes
────────────────────────────────────────
charge_id       VARCHAR    0.0%     Unique identifier
amount          INTEGER    0.0%     Amount in cents (divide by 100)
currency        VARCHAR    0.0%     97 distinct values
status          VARCHAR    0.0%     [succeeded, pending, failed]
customer_id     VARCHAR    1.2%     Links to Shopify customer
created         TIMESTAMP  0.0%     -

ISSUE FOUND: Currency Distribution
  USD:    96.8%
  CAD:     1.9%
  GBP:     0.8%
  EUR:     0.3%
  Other:   0.2%  (12 currencies)

3.2% of transactions are in non-USD currencies.
Must normalize to USD for accurate revenue reporting.

-- Semantic layer definitions (configured in BI Workbench > Semantic Layer > Metrics)
 
-- Gross Merchandise Value: total order value before any deductions
METRIC gmv AS (
    SELECT SUM(
        CASE
            WHEN currency = 'USD' THEN total_price
            ELSE total_price * er.rate_to_usd
        END
    )
    FROM shopify.orders o
    LEFT JOIN analytics.reference.exchange_rates er
        ON o.currency = er.currency AND DATE(o.order_date) = er.rate_date
    WHERE o.financial_status NOT IN ('voided', 'pending')
    DIMENSIONS: order_date, channel, customer_segment, product_category, geography
);
 
-- Net Revenue: GMV minus refunds, returns, and discounts
METRIC net_revenue AS (
    gmv
    - SUM(COALESCE(r.refund_amount_usd, 0))
    - SUM(COALESCE(o.discount_amount_usd, 0))
    DIMENSIONS: order_date, channel, customer_segment, product_category, geography
);
 
-- Refund Rate: percentage of orders that result in a refund
METRIC refund_rate AS (
    CAST(COUNT(DISTINCT r.refund_id) AS DOUBLE) /
    NULLIF(COUNT(DISTINCT o.order_id), 0)
    DIMENSIONS: order_date, channel, product_category
);
 
-- Customer Acquisition Cost
METRIC cac AS (
    SUM(ga.spend) / NULLIF(COUNT(DISTINCT new_customers.customer_id), 0)
    DIMENSIONS: date, campaign, channel
);
 
-- Return on Ad Spend
METRIC roas AS (
    net_revenue / NULLIF(SUM(ga.spend), 0)
    DIMENSIONS: date, campaign, channel
);

-- Validation: compare semantic layer GMV vs manual calculation
WITH semantic_gmv AS (
    SELECT DATE_TRUNC('month', order_date) AS month, SUM(gmv) AS semantic_value
    FROM semantic.metrics
    WHERE order_date >= DATE '2025-10-01'
    GROUP BY 1
),
raw_gmv AS (
    SELECT DATE_TRUNC('month', o.order_date) AS month,
           SUM(CASE
               WHEN o.currency = 'USD' THEN o.total_price
               ELSE o.total_price * er.rate_to_usd
           END) AS raw_value
    FROM shopify.orders o
    LEFT JOIN analytics.reference.exchange_rates er
        ON o.currency = er.currency AND DATE(o.order_date) = er.rate_date
    WHERE o.financial_status NOT IN ('voided', 'pending')
      AND o.order_date >= DATE '2025-10-01'
    GROUP BY 1
)
SELECT s.month, s.semantic_value, r.raw_value,
       ABS(s.semantic_value - r.raw_value) AS difference
FROM semantic_gmv s JOIN raw_gmv r ON s.month = r.month;

month       | semantic_value  | raw_value       | difference
────────────┼─────────────────┼─────────────────┼───────────
2025-10-01  | $15,234,891.42  | $15,234,891.42  | $0.00
2025-11-01  | $18,472,103.87  | $18,472,103.87  | $0.00
2025-12-01  | $22,891,447.23  | $22,891,447.23  | $0.00

User (Sofia): "What percentage of our revenue comes from customers who made
               their first purchase in the last 90 days?"

AI Agent: I'll query new customer revenue contribution.

Generated SQL:
  SELECT
      ROUND(100.0 * SUM(CASE WHEN c.signup_date >= CURRENT_DATE - INTERVAL '90' DAY
                              THEN o.total_price ELSE 0 END)
            / SUM(o.total_price), 1) AS new_customer_revenue_pct
  FROM shopify.orders o
  JOIN postgresql.novamart.customers c ON o.customer_id = c.customer_id
  WHERE o.order_date >= CURRENT_DATE - INTERVAL '90' DAY
    AND o.financial_status = 'paid';

Result: 14.3% of revenue in the last 90 days came from new customers.

{
  "pipeline_id": "revenue-fact-table",
  "name": "Revenue Fact Table Builder",
  "schedule": "0 * * * *",
  "tasks": [
    {
      "task_id": "build_hourly_revenue",
      "type": "sql_execute",
      "config": {
        "query_ref": "analytics.queries.revenue_fact_hourly",
        "target_table": "analytics.facts.revenue_hourly",
        "write_mode": "merge",
        "merge_key": ["order_id"],
        "description": "Merge latest order/payment/refund data into hourly fact table"
      }
    },
    {
      "task_id": "build_daily_aggregates",
      "type": "sql_execute",
      "depends_on": ["build_hourly_revenue"],
      "config": {
        "query_ref": "analytics.queries.revenue_daily_agg",
        "target_table": "analytics.facts.revenue_daily",
        "write_mode": "merge",
        "merge_key": ["date", "channel", "product_category", "geography"]
      }
    },
    {
      "task_id": "quality_check",
      "type": "data_quality_check",
      "depends_on": ["build_daily_aggregates"],
      "config": {
        "suite": "revenue_fact_validation",
        "fail_pipeline_on_error": true
      }
    },
    {
      "task_id": "refresh_dashboard_cache",
      "type": "bi_cache_refresh",
      "depends_on": ["quality_check"],
      "config": {
        "dashboards": [
          "executive-revenue-overview",
          "channel-performance",
          "product-mix"
        ]
      }
    }
  ]
}

{
  "suite_name": "revenue_fact_validation",
  "expectations": [
    {
      "type": "expect_column_pair_values_a_to_be_greater_than_b",
      "kwargs": {
        "column_A": "gmv",
        "column_B": "net_revenue",
        "or_equal": true
      },
      "notes": "GMV must always be >= Net Revenue (refunds reduce net)"
    },
    {
      "type": "expect_column_values_to_be_between",
      "kwargs": { "column": "refund_rate", "min_value": 0, "max_value": 1 }
    },
    {
      "type": "expect_column_values_to_not_be_null",
      "kwargs": { "column": "currency_normalized" }
    },
    {
      "type": "custom_sql_expectation",
      "kwargs": {
        "sql": "SELECT COUNT(*) FROM analytics.facts.revenue_hourly WHERE ABS(order_total - payment_total) > 0.01 AND refund_amount = 0",
        "expected_value": 0,
        "description": "Order totals must match payment totals (tolerance: $0.01) for non-refunded orders"
      }
    },
    {
      "type": "custom_sql_expectation",
      "kwargs": {
        "sql": "SELECT COUNT(*) FROM (SELECT order_id, COUNT(*) c FROM analytics.facts.revenue_hourly GROUP BY order_id HAVING c > 1)",
        "expected_value": 0,
        "description": "No duplicate order_ids in the fact table"
      }
    }
  ]
}

Suite: revenue_fact_validation
Status: PASSED (5/5)

  [PASS] GMV >= Net Revenue: all 2,847,312 rows valid
  [PASS] Refund rate range: all values in [0, 0.87]
  [PASS] Currency normalized: 0 nulls
  [PASS] Order-payment match: 0 mismatches (excluding refunds)
  [PASS] Duplicate orders: 0 duplicates found

┌─────────────────────────────────────────────────────────────────────┐
│  NovaMart Revenue Command Center          Last updated: 3 min ago  │
├─────────────┬─────────────┬─────────────┬─────────────┬────────────┤
│  GMV Today  │  Net Rev    │  Orders     │  AOV        │  Refund    │
│  $487,234   │  $421,891   │  7,234      │  $67.40     │  Rate      │
│  +12% DoD   │  +8% DoD    │  +15% DoD   │  -3% DoD    │  3.1%     │
├─────────────┴─────────────┴─────────────┴─────────────┴────────────┤
│                                                                     │
│  Revenue Trend (30 Days)                                            │
│  $600K ┤                                                            │
│  $500K ┤              ╭─╮     ╭──╮                    ╭──╮          │
│  $400K ┤  ╭──╮   ╭──╮│ │╭──╮╭╯  ╰╮  ╭──╮   ╭──╮  ╭─╯  │          │
│  $300K ┤╭─╯  ╰──╯  ╰╯ ╰╯  ╰╯    ╰──╯  ╰──╯  ╰──╯     │          │
│  $200K ┤╯                                                │          │
│        └─────────────────────────────────────────────────           │
│         Nov 15              Dec 01              Dec 15              │
│                                                                     │
├────────────────────────────┬────────────────────────────────────────┤
│  Channel Revenue Mix       │  Top 5 Products (Today)               │
│                            │                                        │
│  Web     ████████████ 61%  │  1. Wireless Buds    $18,234  +24%    │
│  Mobile  ██████      28%   │  2. Cotton T-Shirt   $14,891  +8%     │
│  Store   ███         11%   │  3. Water Bottle     $12,472  +15%    │
│                            │  4. Vitamin C Serum  $11,938  -2%     │
│                            │  5. Resistance Bands $10,234  +31%    │
├────────────────────────────┴────────────────────────────────────────┤
│  YoY Comparison                                                     │
│                                                                     │
│  Month     This Year    Last Year    Growth                         │
│  Oct       $15.2M       $12.8M       +18.7%                        │
│  Nov       $18.5M       $15.1M       +22.5%                        │
│  Dec (MTD) $14.3M       $11.9M       +20.2%                        │
└─────────────────────────────────────────────────────────────────────┘

{
  "dashboard": "executive-revenue-overview",
  "access_rules": [
    { "role": "executive", "access": "view", "filter": null },
    { "role": "regional_manager", "access": "view", "filter": "geography = user.region" },
    { "role": "marketing_analyst", "access": "view", "filter": "channel IN user.channels" },
    { "role": "data_team", "access": "edit", "filter": null }
  ]
}

{
  "report_name": "Weekly Revenue Summary",
  "dashboard": "executive-revenue-overview",
  "format": "pdf",
  "schedule": "0 8 * * MON",
  "recipients": [
    { "email": "david@novamart.com", "role": "VP Strategy" },
    { "email": "cfo@novamart.com", "role": "CFO" },
    { "email": "ceo@novamart.com", "role": "CEO" }
  ],
  "filters": {
    "date_range": "last_7_days",
    "comparison": "previous_7_days"
  },
  "sections": [
    "revenue_summary",
    "channel_breakdown",
    "top_movers",
    "anomaly_alerts"
  ]
}

{
  "alerts": [
    {
      "name": "revenue-drop-alert",
      "description": "Revenue drops more than 10% compared to same day last week",
      "metric": "net_revenue",
      "condition": "current_day / same_day_last_week < 0.90",
      "evaluation_window": "1d",
      "severity": "critical",
      "channels": ["slack:#revenue-alerts", "email:david@novamart.com"]
    },
    {
      "name": "refund-spike-alert",
      "description": "Refund rate exceeds 5% (rolling 24h)",
      "metric": "refund_rate",
      "condition": "value > 0.05",
      "evaluation_window": "24h",
      "severity": "warning",
      "channels": ["slack:#revenue-alerts"]
    },
    {
      "name": "data-freshness-sla",
      "description": "Order data must be less than 1 hour old",
      "type": "freshness",
      "table": "analytics.facts.revenue_hourly",
      "max_age_minutes": 60,
      "severity": "critical",
      "channels": ["slack:#data-alerts", "pagerduty:data-oncall"]
    },
    {
      "name": "dashboard-performance",
      "description": "Dashboard load time exceeds 5 seconds",
      "type": "performance",
      "dashboard": "executive-revenue-overview",
      "threshold_seconds": 5,
      "severity": "warning",
      "channels": ["slack:#bi-team"]
    }
  ]
}

BI Platform Health - December 2025
──────────────────────────────────
Data Freshness SLA:
  Order data < 1 hour:    99.7%  (2 breaches -- Shopify API downtime)
  Ad spend data < 24h:    100%
  CRM data < 1 hour:      99.9%

Alert Summary:
  Revenue drop alerts:    1 triggered (Dec 3, Black Friday hangover -- expected)
  Refund spike alerts:    0 triggered
  Freshness breaches:     2 (resolved within 20 min each)

Dashboard Performance:
  Executive Overview:     avg 2.1s load time   [OK]
  Channel Performance:    avg 1.8s load time   [OK]
  Product Mix:            avg 3.4s load time   [OK]
  Geographic Breakdown:   avg 2.7s load time   [OK]
  Customer Cohorts:       avg 4.2s load time   [OK]

Usage Statistics:
  Weekly active viewers:  47 unique users
  Most viewed dashboard:  Executive Revenue Overview (312 views)
  Report downloads:       18 PDF exports
  Saved queries:          23 created by business users

A/B Test: Dashboard Layout Optimization
Duration: 2 weeks
Participants: 20 business users (random assignment)

Version A (Control): Current layout -- KPI cards on top, trend chart, tables
Version B (Treatment): Added trend sparklines in KPI cards, inline comparisons,
                        collapsible detail sections

Metrics Tracked:
  - Time to first insight (how quickly users find what they need)
  - Number of drill-downs per session
  - Session duration
  - User satisfaction survey (1-5 scale)

Metric Governance Audit Summary
────────────────────────────────
Total metrics defined:        67
Metrics with active consumers: 43
Metrics with no consumers:     12  --> RETIRED
Duplicate metrics found:        8  --> CONSOLIDATED to 4
Inconsistent definitions:       4  --> STANDARDIZED

Actions Taken:
  RETIRED (12 metrics):
    - daily_sessions_v2 (replaced by daily_sessions in Q3)
    - gmv_usd_old (pre-currency-normalization version)
    - temp_campaign_roi (one-time analysis, never used again)
    ... (9 more)

  CONSOLIDATED (8 -> 4 metrics):
    - "revenue" + "total_revenue" + "gross_revenue" --> "gmv"
    - "refund_pct" + "return_rate" --> "refund_rate"
    - "new_user_count" + "new_customers" --> "new_customers"
    - "cost_per_acq" + "cac_by_channel" --> "cac"

  STANDARDIZED (4 metrics):
    - "aov" -- now consistently uses net_revenue / orders (was gmv / orders in 2 dashboards)
    - "conversion_rate" -- now uses unique_sessions denominator (was total_pageviews in 1 report)
    - "churn_rate" -- aligned with Priya's ML definition (90-day no-purchase)
    - "cltv" -- standardized to 3-year horizon (was inconsistent across reports)