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 tables to accelerate common queries
CREATE TABLE IF NOT EXISTS 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;