Real-Time Blended ROAS Without the Spreadsheet Tax

-- AI Agent Generated: Daily Blended ROAS & CAC Calculation
-- Dialect: DuckDB (Arcli Embedded Compute Engine)
-- Semantic Governance: strictly joins spend against ledger-verified Net Revenue.

WITH daily_spend AS (
    -- Normalize and union spend schemas from Meta and Google
    SELECT DATE_TRUNC('day', date_start) AS metric_date, 'Meta Ads' AS platform, SUM(spend) AS daily_cost
    FROM tenant.meta_ads.campaign_insights GROUP BY 1, 2
    UNION ALL
    SELECT DATE_TRUNC('day', segments_date) AS metric_date, 'Google Ads' AS platform, SUM(metrics_cost_micros / 1000000.0) AS daily_cost
    FROM tenant.google_ads.campaign_stats GROUP BY 1, 2
),
daily_revenue AS (
    -- Calculate true net sales (excluding refunds, taxes, and shipping) directly from the transactional ledger
    SELECT 
        DATE_TRUNC('day', created_at) AS metric_date,
        COUNT(DISTINCT customer_id) AS unique_purchasers,
        SUM(total_price - total_tax - total_discounts - total_shipping) AS net_sales
    FROM tenant.shopify.orders
    WHERE financial_status IN ('paid', 'partially_refunded') AND cancelled_at IS NULL
    GROUP BY 1
)
SELECT 
    s.metric_date,
    SUM(s.daily_cost) AS total_ad_spend,
    COALESCE(r.net_sales, 0) AS total_net_revenue,
    CASE 
        WHEN SUM(s.daily_cost) = 0 THEN 0 
        ELSE ROUND((COALESCE(r.net_sales, 0) / SUM(s.daily_cost)), 2) 
    END AS true_blended_roas
FROM daily_spend s
LEFT JOIN daily_revenue r ON s.metric_date = r.metric_date
WHERE s.metric_date >= CURRENT_DATE - INTERVAL 30 DAY
GROUP BY 1, r.net_sales
ORDER BY s.metric_date DESC;