Building a Startup Analytics Dashboard with DuckDB: Complete Growth Metrics Guide

-- Create main database and attach data sources
CREATE DATABASE IF NOT EXISTS startup_analytics.duckdb;
ATTACH 'payments.db' AS payments;
ATTACH 'users.db' AS users;
ATTACH 'events.db' AS events;

-- Unified user view
CREATE VIEW v_users AS
SELECT 
    user_id,
    signup_date,
    plan_type,
    subscription_status,
    country,
    acquisition_channel
FROM users.users
UNION ALL
SELECT 
    user_id,
    signup_date,
    plan_type,
    subscription_status,
    country,
    acquisition_channel
FROM payments.subscriptions;

-- Revenue table (integrating multiple payment channels)
CREATE TABLE revenue (
    transaction_id VARCHAR,
    user_id BIGINT,
    amount DECIMAL(10,2),
    currency VARCHAR(3),
    transaction_date TIMESTAMP,
    payment_method VARCHAR(50),
    subscription_id VARCHAR,
    plan_type VARCHAR(50)
);

-- User table
CREATE TABLE user_master (
    user_id BIGINT PRIMARY KEY,
    email VARCHAR(255),
    signup_date DATE,
    plan_type VARCHAR(50),
    mrr DECIMAL(10,2),
    ltv DECIMAL(10,2),
    country VARCHAR(50),
    acquisition_channel VARCHAR(100),
    is_active BOOLEAN,
    churn_date DATE
);

-- Event table (user behavior)
CREATE TABLE user_events (
    event_id VARCHAR PRIMARY KEY,
    user_id BIGINT,
    event_type VARCHAR(50),
    event_properties JSON,
    created_at TIMESTAMP,
    session_id VARCHAR
);

-- Calculate monthly MRR
WITH monthly_revenue AS (
    SELECT 
        DATE_TRUNC('month', transaction_date) AS month,
        plan_type,
        SUM(amount) AS total_revenue,
        COUNT(DISTINCT user_id) AS active_subscribers
    FROM revenue
    WHERE transaction_date >= DATE '2025-01-01'
    GROUP BY DATE_TRUNC('month', transaction_date), plan_type
),
mrr_calculation AS (
    SELECT 
        month,
        plan_type,
        total_revenue AS mrr,
        active_subscribers,
        ROUND(total_revenue / NULLIF(active_subscribers, 0), 2) AS arpu
    FROM monthly_revenue
)
SELECT 
    month,
    plan_type,
    mrr,
    active_subscribers,
    arpu,
    SUM(mrr) OVER (PARTITION BY month) AS total_mrr,
    SUM(mrr) OVER (ORDER BY month) AS cumulative_mrr
FROM mrr_calculation
ORDER BY month, plan_type;

-- LTV calculation (simplified version)
WITH user_metrics AS (
    SELECT 
        user_id,
        plan_type,
        SUM(amount) AS total_spent,
        MIN(transaction_date) AS first_purchase,
        MAX(transaction_date) AS last_purchase,
        COUNT(DISTINCT DATE_TRUNC('month', transaction_date)) AS active_months
    FROM revenue
    GROUP BY user_id, plan_type
),
ltv_calculation AS (
    SELECT 
        user_id,
        plan_type,
        total_spent,
        active_months,
        ROUND(total_spent / NULLIF(active_months, 0), 2) AS avg_monthly_spend,
        ROUND(total_spent / NULLIF(DATEDIFF('month', first_purchase, last_purchase) + 1, 0), 2) AS monthly_ltv
    FROM user_metrics
)
SELECT 
    plan_type,
    COUNT(*) AS user_count,
    ROUND(AVG(monthly_ltv), 2) AS avg_monthly_ltv,
    ROUND(AVG(total_spent), 2) AS avg_total_revenue,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY monthly_ltv) AS median_ltv
FROM ltv_calculation
GROUP BY plan_type
ORDER BY avg_monthly_ltv DESC;

-- Calculate monthly churn rate
WITH monthly_churn AS (
    SELECT 
        DATE_TRUNC('month', signup_date) AS month,
        COUNT(*) AS new_users,
        COUNT(*) FILTER (WHERE churn_date IS NOT NULL 
            AND DATE_TRUNC('month', churn_date) = DATE_TRUNC('month', signup_date)) AS churned_in_first_month
    FROM user_master
    WHERE signup_date >= DATE '2025-01-01'
    GROUP BY DATE_TRUNC('month', signup_date)
),
cohort_churn AS (
    SELECT 
        month,
        new_users,
        churned_in_first_month,
        ROUND(churned_in_first_month * 100.0 / NULLIF(new_users, 0), 2) AS month_1_churn_rate,
        COUNT(*) FILTER (WHERE churn_date IS NOT NULL 
            AND churn_date <= month + INTERVAL '2 months') AS churned_by_month_2,
        ROUND(COUNT(*) FILTER (WHERE churn_date IS NOT NULL 
            AND churn_date <= month + INTERVAL '2 months') * 100.0 / NULLIF(new_users, 0), 2) AS month_2_churn_rate
    FROM monthly_churn
    GROUP BY month, new_users, churned_in_first_month
)
SELECT 
    month,
    new_users,
    month_1_churn_rate,
    month_2_churn_rate
FROM cohort_churn
ORDER BY month;

-- Cohort retention analysis
WITH cohort_data AS (
    SELECT 
        DATE_TRUNC('month', signup_date) AS cohort_month,
        DATE_TRUNC('month', transaction_date) AS activity_month,
        user_id
    FROM user_master u
    JOIN revenue r ON u.user_id = r.user_id
    WHERE signup_date >= DATE '2025-01-01'
),
cohort_sizes AS (
    SELECT 
        cohort_month,
        COUNT(DISTINCT user_id) AS cohort_size
    FROM cohort_data
    GROUP BY cohort_month
),
retention_rates AS (
    SELECT 
        c.cohort_month,
        c.activity_month,
        COUNT(DISTINCT c.user_id) AS active_users,
        cs.cohort_size,
        ROUND(COUNT(DISTINCT c.user_id) * 100.0 / cs.cohort_size, 2) AS retention_rate,
        EXTRACT(MONTH FROM c.activity_month) - EXTRACT(MONTH FROM c.cohort_month) AS months_since_signup
    FROM cohort_data c
    JOIN cohort_sizes cs ON c.cohort_month = cs.cohort_month
    GROUP BY c.cohort_month, c.activity_month, cs.cohort_size
)
SELECT 
    cohort_month,
    activity_month,
    retention_rate,
    months_since_signup
FROM retention_rates
ORDER BY cohort_month, months_since_signup;

-- Daily core metrics snapshot
CREATE TABLE daily_metrics (
    report_date DATE PRIMARY KEY,
    mrr DECIMAL(12,2),
    arr DECIMAL(12,2),
    new_users BIGINT,
    churned_users BIGINT,
    net_new_users BIGINT,
    avg_revenue_per_user DECIMAL(10,2),
    cac DECIMAL(10,2),
    ltv_ratio DECIMAL(5,2)
);

-- Insert daily data
INSERT INTO daily_metrics
SELECT 
    CURRENT_DATE AS report_date,
    SUM(CASE WHEN DATE_TRUNC('month', transaction_date) = DATE_TRUNC('month', CURRENT_DATE) 
        THEN amount ELSE 0 END) AS mrr,
    SUM(CASE WHEN DATE_TRUNC('month', transaction_date) = DATE_TRUNC('month', CURRENT_DATE) 
        THEN amount ELSE 0 END) * 12 AS arr,
    COUNT(DISTINCT CASE WHEN DATE(signup_date) = CURRENT_DATE 
        THEN user_id END) AS new_users,
    COUNT(DISTINCT CASE WHEN DATE(churn_date) = CURRENT_DATE 
        THEN user_id END) AS churned_users,
    COUNT(DISTINCT CASE WHEN DATE(signup_date) = CURRENT_DATE 
        THEN user_id END) - COUNT(DISTINCT CASE WHEN DATE(churn_date) = CURRENT_DATE 
        THEN user_id END) AS net_new_users,
    ROUND(AVG(amount), 2) AS avg_revenue_per_user,
    0 AS cac,  -- Requires marketing data calculation
    0 AS ltv_ratio  -- Requires LTV calculation
FROM (
    SELECT user_id, transaction_date, amount, signup_date, churn_date FROM revenue
    UNION ALL
    SELECT user_id, transaction_date, NULL, signup_date, churn_date FROM user_master
);

-- Weekly report data query
WITH weekly_metrics AS (
    SELECT 
        DATE_TRUNC('week', CURRENT_DATE) - INTERVAL '1 week' AS week_start,
        DATE_TRUNC('week', CURRENT_DATE) AS week_end,
        SUM(CASE WHEN transaction_date >= DATE_TRUNC('week', CURRENT_DATE) - INTERVAL '1 week'
            THEN amount ELSE 0 END) AS weekly_revenue,
        COUNT(DISTINCT user_id) FILTER (WHERE transaction_date >= DATE_TRUNC('week', CURRENT_DATE) - INTERVAL '1 week') AS weekly_active_payers,
        COUNT(*) FILTER (WHERE signup_date >= DATE_TRUNC('week', CURRENT_DATE) - INTERVAL '1 week') AS new_signups,
        COUNT(*) FILTER (WHERE churn_date >= DATE_TRUNC('week', CURRENT_DATE) - INTERVAL '1 week') AS churns
    FROM revenue r
    JOIN user_master u ON r.user_id = u.user_id
)
SELECT 
    week_start,
    week_end,
    weekly_revenue,
    weekly_active_payers,
    new_signups,
    churns,
    ROUND(weekly_revenue / NULLIF(weekly_active_payers, 0), 2) AS arpu_weekly,
    ROUND((new_signups - churns) * 100.0 / NULLIF(new_signups, 0), 2) AS retention_rate
FROM weekly_metrics;

-- Use materialized views to accelerate common queries
CREATE MATERIALIZED VIEW mv_monthly_mrr AS
SELECT 
    DATE_TRUNC('month', transaction_date) AS month,
    SUM(amount) AS mrr,
    COUNT(DISTINCT user_id) AS subscribers
FROM revenue
GROUP BY DATE_TRUNC('month', transaction_date);

-- Create indexes to optimize user queries
CREATE INDEX idx_revenue_user_date ON revenue(user_id, transaction_date);
CREATE INDEX idx_user_master_signup ON user_master(signup_date, plan_type);

-- Use partitioned tables to optimize large data volumes
CREATE TABLE revenue_partitioned (
    LIKE revenue INCLUDING ALL
) PARTITION BY RANGE (transaction_date);

-- Create monthly partitions
CREATE TABLE revenue_2025_01 PARTITION OF revenue_partitioned
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE revenue_2025_02 PARTITION OF revenue_partitioned
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

-- Adjust DuckDB memory configuration
PRAGMA memory_limit = '4GB';
PRAGMA threads = 4;
PRAGMA max_memory = 8GB;

-- Use temporary tables to optimize complex queries
CREATE TEMP TABLE temp_active_users AS
SELECT user_id, plan_type, mrr
FROM user_master
WHERE is_active = TRUE
AND signup_date >= DATE '2024-01-01';

-- Use WITH clause to improve readability
WITH base_metrics AS (
    SELECT 
        user_id,
        SUM(amount) AS total_revenue,
        COUNT(*) AS transaction_count
    FROM revenue
    GROUP BY user_id
)
SELECT 
    b.user_id,
    b.total_revenue,
    b.transaction_count,
    ROUND(b.total_revenue / NULLIF(b.transaction_count, 0), 2) AS avg_order_value
FROM base_metrics b
WHERE b.total_revenue > 1000
ORDER BY b.total_revenue DESC;