Membuat Dashboard Metrics dengan SQL: KPI yang Wajib Ditrack

Dashboard Metrics Itu Bikin Hidup Data Analyst Lebih Gampang

Pernah ditanya sama manager: "Gimana performa minggu ini?" terus kamu harus buka 10 query berbeda buat jawab? Capek banget kan.

Dashboard metrics yang bagus nampilin semua angka penting di satu tempat. Sekali liat, langsung ketauan mana yang perlu perhatian. Masalahnya, banyak data analyst yang struggle waktu disuruh bikin query buat dashboard.

Aku kumpulin template query lengkap buat 15+ metrics yang wajib ada di dashboard startup. Semua pakai konteks e-commerce dan fintech Indonesia, jadi tinggal adjust nama kolom aja.

Daftar Metrics

• Revenue Metrics: GMV, AOV, Revenue Growth
• User Metrics: DAU, WAU, MAU, dan rasionya
• Retention Metrics: D1, D7, D30 retention rate
• Engagement Metrics: Session frequency, feature adoption
• Acquisition Metrics: CAC, conversion rate
• Operational Metrics: order fulfillment, response time
• Cara bikin summary dashboard dengan satu query

Kategori 1: Revenue Metrics

Revenue metrics itu yang pertama ditanya investor dan C-level.

Gross Merchandise Value (GMV)

GMV adalah total nilai transaksi sebelum dipotong diskon, refund, dan biaya lainnya.

Sample Data - Tabel orders:

-- GMV Harian
SELECT
    DATE(order_date) AS tanggal,
    SUM(gross_amount) AS gmv_harian,
    SUM(CASE WHEN status = 'completed' THEN gross_amount ELSE 0 END) AS gmv_completed,
    COUNT(DISTINCT order_id) AS total_orders
FROM orders
WHERE order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
GROUP BY DATE(order_date)
ORDER BY tanggal;

Hasil:

Kenapa pisahin GMV total dan GMV completed? Karena kamu perlu tau gap-nya. Kalo gap-nya gede, berarti banyak order yang cancel atau pending. Itu red flag nih.

Average Order Value (AOV)

AOV ngasih tau rata-rata nilai belanja per transaksi.

-- AOV dengan Trend Mingguan
SELECT
    DATE_TRUNC('week', order_date) AS minggu,
    COUNT(order_id) AS total_orders,
    SUM(net_amount) AS total_revenue,
    ROUND(SUM(net_amount) / COUNT(order_id), 0) AS aov,
    ROUND(
        (SUM(net_amount) / COUNT(order_id) -
         LAG(SUM(net_amount) / COUNT(order_id)) OVER (ORDER BY DATE_TRUNC('week', order_date)))
        / LAG(SUM(net_amount) / COUNT(order_id)) OVER (ORDER BY DATE_TRUNC('week', order_date)) * 100,
        2
    ) AS aov_growth_pct
FROM orders
WHERE status = 'completed'
    AND order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 12 WEEK)
GROUP BY DATE_TRUNC('week', order_date)
ORDER BY minggu;

Tips nih: AOV yang naik belum tentu bagus kalo ternyata jumlah order-nya turun drastis. Makanya selalu tampilkan total_orders bareng AOV.

Revenue Growth Rate

-- Month-over-Month Revenue Growth
WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', order_date) AS bulan,
        SUM(net_amount) AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
    bulan,
    revenue,
    LAG(revenue) OVER (ORDER BY bulan) AS revenue_bulan_lalu,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY bulan))
        / LAG(revenue) OVER (ORDER BY bulan) * 100,
        2
    ) AS mom_growth_pct
FROM monthly_revenue
ORDER BY bulan;

Kategori 2: User Metrics

User metrics nunjukin seberapa sehat user base kamu. Ini penting banget buat ngerti growth trajectory.

DAU, WAU, MAU

Sample Data - Tabel user_activities:

-- DAU (Daily Active Users)
SELECT
    activity_date AS tanggal,
    COUNT(DISTINCT user_id) AS dau
FROM user_activities
WHERE activity_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
GROUP BY activity_date
ORDER BY tanggal;

-- WAU (Weekly Active Users)
SELECT
    DATE_TRUNC('week', activity_date) AS minggu,
    COUNT(DISTINCT user_id) AS wau
FROM user_activities
WHERE activity_date >= DATE_SUB(CURRENT_DATE, INTERVAL 12 WEEK)
GROUP BY DATE_TRUNC('week', activity_date)
ORDER BY minggu;

-- MAU (Monthly Active Users)
SELECT
    DATE_TRUNC('month', activity_date) AS bulan,
    COUNT(DISTINCT user_id) AS mau
FROM user_activities
WHERE activity_date >= DATE_SUB(CURRENT_DATE, INTERVAL 12 MONTH)
GROUP BY DATE_TRUNC('month', activity_date)
ORDER BY bulan;

DAU/MAU Ratio (Stickiness)

Ratio ini sering disebut "stickiness" karena nunjukin seberapa sering user balik ke platform kamu dalam sebulan. Benchmark-nya: 20% udah bagus, 50%+ itu excellent.

-- DAU/MAU Ratio per Bulan
WITH daily_users AS (
    SELECT
        DATE_TRUNC('month', activity_date) AS bulan,
        activity_date,
        COUNT(DISTINCT user_id) AS dau
    FROM user_activities
    GROUP BY DATE_TRUNC('month', activity_date), activity_date
),
monthly_users AS (
    SELECT
        DATE_TRUNC('month', activity_date) AS bulan,
        COUNT(DISTINCT user_id) AS mau
    FROM user_activities
    GROUP BY DATE_TRUNC('month', activity_date)
),
avg_dau AS (
    SELECT
        bulan,
        ROUND(AVG(dau), 0) AS avg_dau
    FROM daily_users
    GROUP BY bulan
)
SELECT
    m.bulan,
    a.avg_dau,
    m.mau,
    ROUND(a.avg_dau * 100.0 / m.mau, 2) AS stickiness_pct
FROM monthly_users m
JOIN avg_dau a ON m.bulan = a.bulan
ORDER BY m.bulan;

Hasil:

Kalo stickiness naik, artinya user makin engaged. Bagus tuh!

New vs Returning Users

-- New vs Returning Users per Hari
WITH first_activity AS (
    SELECT
        user_id,
        MIN(activity_date) AS first_date
    FROM user_activities
    GROUP BY user_id
)
SELECT
    ua.activity_date AS tanggal,
    COUNT(DISTINCT CASE
        WHEN ua.activity_date = fa.first_date THEN ua.user_id
    END) AS new_users,
    COUNT(DISTINCT CASE
        WHEN ua.activity_date > fa.first_date THEN ua.user_id
    END) AS returning_users,
    COUNT(DISTINCT ua.user_id) AS total_users,
    ROUND(
        COUNT(DISTINCT CASE WHEN ua.activity_date > fa.first_date THEN ua.user_id END) * 100.0
        / COUNT(DISTINCT ua.user_id),
        2
    ) AS returning_rate_pct
FROM user_activities ua
JOIN first_activity fa ON ua.user_id = fa.user_id
WHERE ua.activity_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
GROUP BY ua.activity_date
ORDER BY tanggal;

Healthy ratio itu sekitar 30% new, 70% returning. Kalo new users terus dominan, berarti retention kamu bermasalah.

Kategori 3: Retention Metrics

Retain existing user lebih murah daripada acquire yang baru. Makanya retention wajib kamu pantau tiap minggu.

Classic Retention (D1, D7, D30)

Sample Data - Tabel users:

-- D1, D7, D30 Retention Rate
WITH cohort AS (
    SELECT
        user_id,
        DATE(registration_date) AS signup_date
    FROM users
    WHERE registration_date >= DATE_SUB(CURRENT_DATE, INTERVAL 60 DAY)
),
activity_flags AS (
    SELECT
        c.signup_date,
        c.user_id,
        MAX(CASE
            WHEN ua.activity_date = DATE_ADD(c.signup_date, INTERVAL 1 DAY)
            THEN 1 ELSE 0
        END) AS active_d1,
        MAX(CASE
            WHEN ua.activity_date = DATE_ADD(c.signup_date, INTERVAL 7 DAY)
            THEN 1 ELSE 0
        END) AS active_d7,
        MAX(CASE
            WHEN ua.activity_date = DATE_ADD(c.signup_date, INTERVAL 30 DAY)
            THEN 1 ELSE 0
        END) AS active_d30
    FROM cohort c
    LEFT JOIN user_activities ua
        ON c.user_id = ua.user_id
    GROUP BY c.signup_date, c.user_id
)
SELECT
    signup_date,
    COUNT(DISTINCT user_id) AS cohort_size,
    SUM(active_d1) AS retained_d1,
    SUM(active_d7) AS retained_d7,
    SUM(active_d30) AS retained_d30,
    ROUND(SUM(active_d1) * 100.0 / COUNT(DISTINCT user_id), 2) AS d1_retention_pct,
    ROUND(SUM(active_d7) * 100.0 / COUNT(DISTINCT user_id), 2) AS d7_retention_pct,
    ROUND(SUM(active_d30) * 100.0 / COUNT(DISTINCT user_id), 2) AS d30_retention_pct
FROM activity_flags
GROUP BY signup_date
ORDER BY signup_date;

Hasil:

Benchmark retention untuk e-commerce Indonesia:
- D1: 40-50% (bagus)
- D7: 25-35%
- D30: 10-20%

Rolling Retention

Rolling retention lebih forgiving. User dihitung retained kalo aktif kapan aja setelah day N, bukan harus exactly di day N.

-- Rolling Retention (aktif di hari N atau setelahnya)
WITH cohort AS (
    SELECT
        user_id,
        DATE(registration_date) AS signup_date
    FROM users
    WHERE registration_date BETWEEN '2024-01-01' AND '2024-01-31'
),
retention_flags AS (
    SELECT
        c.signup_date,
        c.user_id,
        MAX(CASE
            WHEN ua.activity_date >= DATE_ADD(c.signup_date, INTERVAL 7 DAY)
            THEN 1 ELSE 0
        END) AS retained_d7_plus,
        MAX(CASE
            WHEN ua.activity_date >= DATE_ADD(c.signup_date, INTERVAL 30 DAY)
            THEN 1 ELSE 0
        END) AS retained_d30_plus
    FROM cohort c
    LEFT JOIN user_activities ua
        ON c.user_id = ua.user_id
    GROUP BY c.signup_date, c.user_id
)
SELECT
    DATE_TRUNC('week', signup_date) AS cohort_week,
    COUNT(DISTINCT user_id) AS cohort_size,
    ROUND(SUM(retained_d7_plus) * 100.0 / COUNT(DISTINCT user_id), 2) AS rolling_d7_pct,
    ROUND(SUM(retained_d30_plus) * 100.0 / COUNT(DISTINCT user_id), 2) AS rolling_d30_pct
FROM retention_flags
GROUP BY DATE_TRUNC('week', signup_date)
ORDER BY cohort_week;

Kategori 4: Engagement Metrics

Engagement metrics nunjukin seberapa deep user berinteraksi dengan produk kamu.

Session Frequency

-- Rata-rata Sessions per User per Minggu
WITH weekly_sessions AS (
    SELECT
        DATE_TRUNC('week', activity_date) AS minggu,
        user_id,
        COUNT(DISTINCT DATE(activity_date)) AS active_days
    FROM user_activities
    WHERE activity_date >= DATE_SUB(CURRENT_DATE, INTERVAL 12 WEEK)
    GROUP BY DATE_TRUNC('week', activity_date), user_id
)
SELECT
    minggu,
    COUNT(DISTINCT user_id) AS total_users,
    ROUND(AVG(active_days), 2) AS avg_active_days_per_user,
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY active_days), 2) AS median_active_days
FROM weekly_sessions
GROUP BY minggu
ORDER BY minggu;

Feature Adoption Rate

-- Feature Adoption Rate
SELECT
    DATE_TRUNC('month', activity_date) AS bulan,
    COUNT(DISTINCT user_id) AS total_active_users,
    COUNT(DISTINCT CASE WHEN activity_type = 'wishlist_add' THEN user_id END) AS used_wishlist,
    COUNT(DISTINCT CASE WHEN activity_type = 'review_submit' THEN user_id END) AS used_review,
    COUNT(DISTINCT CASE WHEN activity_type = 'chat_seller' THEN user_id END) AS used_chat,
    COUNT(DISTINCT CASE WHEN activity_type = 'promo_redeem' THEN user_id END) AS used_promo,

    -- Adoption rates
    ROUND(COUNT(DISTINCT CASE WHEN activity_type = 'wishlist_add' THEN user_id END) * 100.0
        / COUNT(DISTINCT user_id), 2) AS wishlist_adoption_pct,
    ROUND(COUNT(DISTINCT CASE WHEN activity_type = 'review_submit' THEN user_id END) * 100.0
        / COUNT(DISTINCT user_id), 2) AS review_adoption_pct,
    ROUND(COUNT(DISTINCT CASE WHEN activity_type = 'chat_seller' THEN user_id END) * 100.0
        / COUNT(DISTINCT user_id), 2) AS chat_adoption_pct
FROM user_activities
GROUP BY DATE_TRUNC('month', activity_date)
ORDER BY bulan;

Power Users Identification

Power users biasanya top 10% dari user base yang contribute 50%+ revenue. Penting banget buat diidentifikasi.

-- Identifikasi Power Users (Top 10% by Transaction)
WITH user_stats AS (
    SELECT
        user_id,
        COUNT(order_id) AS total_orders,
        SUM(net_amount) AS total_spend,
        COUNT(DISTINCT DATE_TRUNC('month', order_date)) AS active_months
    FROM orders
    WHERE status = 'completed'
        AND order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH)
    GROUP BY user_id
),
percentiles AS (
    SELECT
        PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY total_spend) AS p90_spend
    FROM user_stats
)
SELECT
    us.*,
    CASE
        WHEN us.total_spend >= p.p90_spend THEN 'Power User'
        WHEN us.total_orders >= 5 THEN 'Regular'
        ELSE 'Casual'
    END AS user_segment
FROM user_stats us
CROSS JOIN percentiles p
ORDER BY total_spend DESC;

Kategori 5: Acquisition Metrics

Acquisition metrics penting buat tau efisiensi marketing spend.

Customer Acquisition Cost (CAC)

Sample Data - Tabel marketing_spend:

Sample Data - Tabel user_attribution:

-- CAC per Channel per Bulan
WITH monthly_spend AS (
    SELECT
        DATE_TRUNC('month', spend_date) AS bulan,
        channel,
        SUM(amount) AS total_spend
    FROM marketing_spend
    GROUP BY DATE_TRUNC('month', spend_date), channel
),
monthly_acquisitions AS (
    SELECT
        DATE_TRUNC('month', registration_date) AS bulan,
        acquisition_channel AS channel,
        COUNT(DISTINCT user_id) AS new_users
    FROM user_attribution
    WHERE acquisition_channel != 'organic'
    GROUP BY DATE_TRUNC('month', registration_date), acquisition_channel
)
SELECT
    ms.bulan,
    ms.channel,
    ms.total_spend,
    COALESCE(ma.new_users, 0) AS new_users,
    CASE
        WHEN COALESCE(ma.new_users, 0) > 0
        THEN ROUND(ms.total_spend / ma.new_users, 0)
        ELSE NULL
    END AS cac
FROM monthly_spend ms
LEFT JOIN monthly_acquisitions ma
    ON ms.bulan = ma.bulan
    AND ms.channel = ma.channel
ORDER BY ms.bulan, ms.channel;

Hasil:

TikTok CAC-nya paling murah nih. Tapi jangan lupa cek LTV juga, karena user murah belum tentu berkualitas.

CAC Payback Period

-- CAC Payback Period (berapa bulan untuk recover CAC)
WITH user_cac AS (
    SELECT
        u.user_id,
        u.registration_date,
        u.acquisition_channel,
        COALESCE(c.cac, 50000) AS cac  -- default CAC kalo ga ada data
    FROM user_attribution u
    LEFT JOIN (
        SELECT
            DATE_TRUNC('month', spend_date) AS bulan,
            channel,
            SUM(amount) / NULLIF(COUNT(*), 0) AS cac
        FROM marketing_spend
        GROUP BY DATE_TRUNC('month', spend_date), channel
    ) c ON DATE_TRUNC('month', u.registration_date) = c.bulan
        AND u.acquisition_channel = c.channel
),
cumulative_revenue AS (
    SELECT
        uc.user_id,
        uc.registration_date,
        uc.acquisition_channel,
        uc.cac,
        o.order_date,
        SUM(o.net_amount) OVER (
            PARTITION BY uc.user_id
            ORDER BY o.order_date
        ) AS cumulative_spend
    FROM user_cac uc
    LEFT JOIN orders o ON uc.user_id = o.user_id AND o.status = 'completed'
)
SELECT
    acquisition_channel,
    COUNT(DISTINCT user_id) AS total_users,
    AVG(cac) AS avg_cac,
    COUNT(DISTINCT CASE
        WHEN cumulative_spend >= cac THEN user_id
    END) AS users_paid_back,
    ROUND(
        COUNT(DISTINCT CASE WHEN cumulative_spend >= cac THEN user_id END) * 100.0
        / COUNT(DISTINCT user_id),
        2
    ) AS payback_rate_pct
FROM cumulative_revenue
GROUP BY acquisition_channel
ORDER BY payback_rate_pct DESC;

Kategori 6: Operational Metrics

Operational metrics penting buat tim operations dan customer experience.

Order Fulfillment Rate

-- Order Fulfillment Metrics
SELECT
    DATE_TRUNC('week', order_date) AS minggu,
    COUNT(order_id) AS total_orders,
    COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed,
    COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled,
    COUNT(CASE WHEN status = 'refunded' THEN 1 END) AS refunded,

    ROUND(COUNT(CASE WHEN status = 'completed' THEN 1 END) * 100.0
        / COUNT(order_id), 2) AS completion_rate_pct,
    ROUND(COUNT(CASE WHEN status = 'cancelled' THEN 1 END) * 100.0
        / COUNT(order_id), 2) AS cancellation_rate_pct
FROM orders
WHERE order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 12 WEEK)
GROUP BY DATE_TRUNC('week', order_date)
ORDER BY minggu;

Average Delivery Time

-- Average Delivery Time by Region
SELECT
    o.shipping_city,
    COUNT(o.order_id) AS total_orders,
    ROUND(AVG(
        DATEDIFF(d.delivered_at, o.order_date)
    ), 1) AS avg_delivery_days,
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (
        ORDER BY DATEDIFF(d.delivered_at, o.order_date)
    ), 1) AS median_delivery_days,
    ROUND(PERCENTILE_CONT(0.95) WITHIN GROUP (
        ORDER BY DATEDIFF(d.delivered_at, o.order_date)
    ), 1) AS p95_delivery_days
FROM orders o
JOIN deliveries d ON o.order_id = d.order_id
WHERE d.status = 'delivered'
    AND o.order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
GROUP BY o.shipping_city
HAVING COUNT(o.order_id) >= 50
ORDER BY avg_delivery_days;

Customer Support Metrics

-- CS Response Time dan Resolution Rate
SELECT
    DATE_TRUNC('week', created_at) AS minggu,
    COUNT(ticket_id) AS total_tickets,
    ROUND(AVG(
        TIMESTAMPDIFF(MINUTE, created_at, first_response_at)
    ), 0) AS avg_first_response_minutes,
    ROUND(AVG(
        TIMESTAMPDIFF(HOUR, created_at, resolved_at)
    ), 1) AS avg_resolution_hours,
    ROUND(
        COUNT(CASE WHEN status = 'resolved' THEN 1 END) * 100.0 / COUNT(ticket_id),
        2
    ) AS resolution_rate_pct
FROM support_tickets
WHERE created_at >= DATE_SUB(CURRENT_DATE, INTERVAL 12 WEEK)
GROUP BY DATE_TRUNC('week', created_at)
ORDER BY minggu;

Bonus: Executive Summary Dashboard

Ini query all-in-one yang bisa kamu jadiin foundation buat executive dashboard. Semua metrics penting ada di satu tempat.

-- Executive Summary Dashboard (Single Query)
WITH date_range AS (
    SELECT
        DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month') AS current_month,
        DATE_TRUNC('month', CURRENT_DATE - INTERVAL '2 month') AS previous_month
),
revenue_metrics AS (
    SELECT
        SUM(CASE
            WHEN DATE_TRUNC('month', order_date) = (SELECT current_month FROM date_range)
            THEN net_amount ELSE 0
        END) AS current_revenue,
        SUM(CASE
            WHEN DATE_TRUNC('month', order_date) = (SELECT previous_month FROM date_range)
            THEN net_amount ELSE 0
        END) AS previous_revenue,
        COUNT(DISTINCT CASE
            WHEN DATE_TRUNC('month', order_date) = (SELECT current_month FROM date_range)
            THEN order_id
        END) AS current_orders,
        COUNT(DISTINCT CASE
            WHEN DATE_TRUNC('month', order_date) = (SELECT current_month FROM date_range)
            THEN user_id
        END) AS current_buyers
    FROM orders
    WHERE status = 'completed'
),
user_metrics AS (
    SELECT
        COUNT(DISTINCT CASE
            WHEN DATE_TRUNC('month', activity_date) = (SELECT current_month FROM date_range)
            THEN user_id
        END) AS current_mau,
        COUNT(DISTINCT CASE
            WHEN DATE_TRUNC('month', activity_date) = (SELECT previous_month FROM date_range)
            THEN user_id
        END) AS previous_mau
    FROM user_activities
),
new_users AS (
    SELECT
        COUNT(DISTINCT CASE
            WHEN DATE_TRUNC('month', registration_date) = (SELECT current_month FROM date_range)
            THEN user_id
        END) AS current_new_users,
        COUNT(DISTINCT CASE
            WHEN DATE_TRUNC('month', registration_date) = (SELECT previous_month FROM date_range)
            THEN user_id
        END) AS previous_new_users
    FROM users
)
SELECT
    -- Revenue
    rm.current_revenue,
    rm.previous_revenue,
    ROUND((rm.current_revenue - rm.previous_revenue) * 100.0 / NULLIF(rm.previous_revenue, 0), 2) AS revenue_growth_pct,

    -- AOV
    ROUND(rm.current_revenue / NULLIF(rm.current_orders, 0), 0) AS current_aov,

    -- Orders
    rm.current_orders,
    rm.current_buyers,

    -- Users
    um.current_mau,
    um.previous_mau,
    ROUND((um.current_mau - um.previous_mau) * 100.0 / NULLIF(um.previous_mau, 0), 2) AS mau_growth_pct,

    -- New Users
    nu.current_new_users,
    nu.previous_new_users,
    ROUND((nu.current_new_users - nu.previous_new_users) * 100.0 / NULLIF(nu.previous_new_users, 0), 2) AS new_user_growth_pct,

    -- Conversion
    ROUND(rm.current_buyers * 100.0 / NULLIF(um.current_mau, 0), 2) AS conversion_rate_pct
FROM revenue_metrics rm
CROSS JOIN user_metrics um
CROSS JOIN new_users nu;

Hasil:

Satu query, semua metrics penting. Tinggal visualize di dashboard tool kayak Metabase atau Looker.

Common Mistakes yang Harus Dihindari

1. Ga Handle NULL Values

-- SALAH: Division by zero error
SELECT revenue / total_users AS arpu FROM metrics;

-- BENAR: Handle NULL dan zero
SELECT
    revenue / NULLIF(total_users, 0) AS arpu
FROM metrics;

2. Double Counting Users

-- SALAH: User yang login 10x dihitung 10x
SELECT COUNT(user_id) AS active_users FROM activities;

-- BENAR: Unique users
SELECT COUNT(DISTINCT user_id) AS active_users FROM activities;

3. Timezone Mismatch

-- SALAH: Server timezone beda sama business timezone
SELECT DATE(order_date) AS tanggal FROM orders;

-- BENAR: Convert ke WIB dulu
SELECT DATE(CONVERT_TZ(order_date, 'UTC', 'Asia/Jakarta')) AS tanggal
FROM orders;

4. Ga Filter Status dengan Benar

-- SALAH: Include cancelled orders di revenue
SELECT SUM(net_amount) AS total_revenue FROM orders;

-- BENAR: Filter completed orders aja
SELECT SUM(net_amount) AS total_revenue
FROM orders
WHERE status = 'completed';

Tips Bikin Dashboard yang Actionable

1. Limit to 7 Key Metrics: Jangan kebanyakan. Orang cuma bisa fokus ke 7 metrics sekaligus.

2. Show Comparison: Always tampilkan period-over-period comparison. "GMV 2.4M" ga se-meaningful "GMV 2.4M (+12% MoM)".

3. Use Color Coding: Hijau buat naik/baik, merah buat turun/alert. Simple tapi efektif.

4. Add Context: "CAC Rp 45.000" itu mahal atau murah? Tambahin benchmark atau target.

5. Refresh Frequency yang Tepat:
   - Revenue metrics: Hourly atau daily
   - User metrics: Daily
   - Retention: Weekly
   - CAC/LTV: Monthly

Latihan

Soal 1: Revenue per Category

Bikin query buat ngitung GMV dan AOV per product category, dengan month-over-month growth.

WITH monthly_category AS (
    SELECT
        DATE_TRUNC('month', o.order_date) AS bulan,
        p.category,
        SUM(o.net_amount) AS gmv,
        COUNT(o.order_id) AS total_orders,
        ROUND(SUM(o.net_amount) / COUNT(o.order_id), 0) AS aov
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN products p ON oi.product_id = p.product_id
    WHERE o.status = 'completed'
    GROUP BY DATE_TRUNC('month', o.order_date), p.category
)
SELECT
    bulan,
    category,
    gmv,
    aov,
    LAG(gmv) OVER (PARTITION BY category ORDER BY bulan) AS prev_gmv,
    ROUND(
        (gmv - LAG(gmv) OVER (PARTITION BY category ORDER BY bulan)) * 100.0
        / LAG(gmv) OVER (PARTITION BY category ORDER BY bulan),
        2
    ) AS gmv_growth_pct
FROM monthly_category
ORDER BY bulan, gmv DESC;

Soal 2: Weekly Cohort Retention

Bikin retention table untuk cohort mingguan, dengan retention W1, W2, W3, W4.

WITH cohort AS (
    SELECT
        user_id,
        DATE_TRUNC('week', registration_date) AS cohort_week
    FROM users
    WHERE registration_date >= DATE_SUB(CURRENT_DATE, INTERVAL 8 WEEK)
),
retention AS (
    SELECT
        c.cohort_week,
        c.user_id,
        MAX(CASE
            WHEN DATE_TRUNC('week', a.activity_date) = DATE_ADD(c.cohort_week, INTERVAL 1 WEEK)
            THEN 1 ELSE 0
        END) AS w1,
        MAX(CASE
            WHEN DATE_TRUNC('week', a.activity_date) = DATE_ADD(c.cohort_week, INTERVAL 2 WEEK)
            THEN 1 ELSE 0
        END) AS w2,
        MAX(CASE
            WHEN DATE_TRUNC('week', a.activity_date) = DATE_ADD(c.cohort_week, INTERVAL 3 WEEK)
            THEN 1 ELSE 0
        END) AS w3,
        MAX(CASE
            WHEN DATE_TRUNC('week', a.activity_date) = DATE_ADD(c.cohort_week, INTERVAL 4 WEEK)
            THEN 1 ELSE 0
        END) AS w4
    FROM cohort c
    LEFT JOIN user_activities a ON c.user_id = a.user_id
    GROUP BY c.cohort_week, c.user_id
)
SELECT
    cohort_week,
    COUNT(DISTINCT user_id) AS cohort_size,
    ROUND(SUM(w1) * 100.0 / COUNT(DISTINCT user_id), 2) AS w1_retention,
    ROUND(SUM(w2) * 100.0 / COUNT(DISTINCT user_id), 2) AS w2_retention,
    ROUND(SUM(w3) * 100.0 / COUNT(DISTINCT user_id), 2) AS w3_retention,
    ROUND(SUM(w4) * 100.0 / COUNT(DISTINCT user_id), 2) AS w4_retention
FROM retention
GROUP BY cohort_week
ORDER BY cohort_week;

Soal 3: CAC vs LTV Analysis

Bikin query buat bandingin CAC dengan LTV (6-month revenue) per acquisition channel.

WITH user_ltv AS (
    SELECT
        ua.user_id,
        ua.acquisition_channel,
        ua.registration_date,
        COALESCE(SUM(o.net_amount), 0) AS ltv_6m
    FROM user_attribution ua
    LEFT JOIN orders o
        ON ua.user_id = o.user_id
        AND o.status = 'completed'
        AND o.order_date BETWEEN ua.registration_date
            AND DATE_ADD(ua.registration_date, INTERVAL 6 MONTH)
    WHERE ua.registration_date <= DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH)
    GROUP BY ua.user_id, ua.acquisition_channel, ua.registration_date
),
channel_cac AS (
    SELECT
        channel,
        AVG(amount / NULLIF(new_users, 0)) AS avg_cac
    FROM (
        SELECT
            ms.channel,
            DATE_TRUNC('month', ms.spend_date) AS bulan,
            SUM(ms.amount) AS amount,
            COUNT(DISTINCT ua.user_id) AS new_users
        FROM marketing_spend ms
        LEFT JOIN user_attribution ua
            ON ms.channel = ua.acquisition_channel
            AND DATE_TRUNC('month', ms.spend_date) = DATE_TRUNC('month', ua.registration_date)
        GROUP BY ms.channel, DATE_TRUNC('month', ms.spend_date)
    ) sub
    GROUP BY channel
)
SELECT
    ul.acquisition_channel,
    COUNT(DISTINCT ul.user_id) AS total_users,
    ROUND(AVG(ul.ltv_6m), 0) AS avg_ltv,
    ROUND(cc.avg_cac, 0) AS avg_cac,
    ROUND(AVG(ul.ltv_6m) / NULLIF(cc.avg_cac, 0), 2) AS ltv_cac_ratio
FROM user_ltv ul
LEFT JOIN channel_cac cc ON ul.acquisition_channel = cc.channel
GROUP BY ul.acquisition_channel, cc.avg_cac
ORDER BY ltv_cac_ratio DESC;

FAQ

Query di artikel ini jalan di database apa aja?

Nggak semuanya langsung jalan, soalnya syntax-nya campuran. DATE_TRUNC dan PERCENTILE_CONT itu gaya PostgreSQL, sedangkan DATE_SUB dan CONVERT_TZ itu gaya MySQL. Konsep query-nya sama kok. Kamu cuma perlu ganti fungsi tanggalnya sesuai database yang kamu pakai. Biasanya beda 1-2 fungsi doang.

Harus track semua 15+ metrics ini sekaligus?

Nggak perlu. Mulai dari 5-7 metrics yang paling relevan sama fase bisnismu. Startup early-stage biasanya cukup GMV, MAU, dan D7 retention. Kalau udah mulai spend marketing gede, baru tambahin CAC dan LTV. Dashboard yang isinya 20 metrics malah bikin orang nggak fokus. Ujungnya nggak ada yang dibaca.

Bedanya GMV sama revenue apa?

GMV itu total nilai transaksi sebelum dipotong diskon, refund, dan cancel. Revenue itu uang yang beneran masuk setelah semua potongan. Contoh: order Rp500.000 dengan diskon Rp50.000. GMV-nya Rp500.000, tapi revenue-nya Rp450.000. Investor sering nanya dua-duanya, jadi track keduanya di dashboard kamu.

Tool apa yang cocok buat visualisasi query-query ini?

Buat mulai, Metabase paling gampang. Gratis, open source, dan bisa connect langsung ke database kamu. Google Looker Studio juga gratis kalau datamu udah di BigQuery. Tinggal paste query dari artikel ini, pilih tipe chart, selesai. Nggak perlu langsung beli Tableau yang harganya jutaan per bulan.

Kesimpulan

Dashboard metrics yang bagus itu:
- Actionable: Tiap metric punya implikasi "terus ngapain?"
- Timely: Data fresh, bukan basi seminggu lalu
- Comparative: Ada benchmark atau trend buat context
- Focused: Ga lebih dari 7-10 metrics utama

Query-query di atas bisa langsung kamu pakai sebagai foundation. Adjust nama kolom sesuai schema database kamu, dan kamu udah punya dashboard metrics yang solid.

Yang penting, jangan cuma bikin dashboard lalu ditinggal. Review weekly, adjust metrics kalo bisnis berubah, dan pastikan semua stakeholder paham cara baca angka-angkanya.

Selanjutnya

Udah punya dashboard? Sekarang waktunya deep dive ke area spesifik:
- A/B Testing dengan SQL buat validasi eksperimen
- Cohort Analysis buat retention deep dive
- Funnel Analysis buat optimasi conversion

Happy dashboarding!