MySQL+Metabase技术需求：统计当月注册且下单的累计新用户

Hey there! Let's work through this cumulative new user report you're building in Metabase. You're already on the right track with joining the tables and grouping by date—let's refine that into a complete solution, whether you want to use SQL directly or tweak the Metabase GUI to get there.

Core Logic Breakdown #

First, let's clarify what we need:

• We want to count unique users who registered this month AND made a purchase this month, and show the cumulative total of these users day-by-day throughout the month.
• Cumulative means each day's number should be the total number of eligible users from the start of the month up to that day.

SQL Solution (Perfect for Metabase) #

Here's a robust SQL query tailored to your tables. I'll break down each part so you understand what's happening:

WITH eligible_users AS (
    -- Step 1: Grab users who registered AND purchased in the current month
    SELECT 
        u.user_id,
        DATE(u.registered_at) AS register_date,
        -- Get the user's first purchase date this month to avoid duplicate counts
        MIN(DATE(p.purchased_at)) AS first_purchase_date
    FROM "User" u
    JOIN "Purchase" p ON u.user_id = p.user_id
    WHERE 
        -- Filter for current month registration
        DATE_TRUNC('month', u.registered_at) = DATE_TRUNC('month', CURRENT_DATE)
        -- Filter for current month purchase
        AND DATE_TRUNC('month', p.purchased_at) = DATE_TRUNC('month', CURRENT_DATE)
    GROUP BY u.user_id, register_date
),
daily_dates AS (
    -- Step 2: Generate every date in the current month (so no days are missing)
    SELECT generate_series(
        DATE_TRUNC('month', CURRENT_DATE)::DATE,
        CURRENT_DATE::DATE,
        '1 day'::INTERVAL
    ) AS report_date
),
daily_user_counts AS (
    -- Step 3: Match eligible users to all dates they should be counted in
    SELECT 
        dd.report_date,
        eu.user_id
    FROM daily_dates dd
    LEFT JOIN eligible_users eu 
        -- Count the user starting from the later of their registration or first purchase date
        ON GREATEST(eu.register_date, eu.first_purchase_date) <= dd.report_date
)
-- Step 4: Calculate cumulative unique users per day
SELECT 
    report_date,
    COUNT(DISTINCT user_id) AS cumulative_eligible_users
FROM daily_user_counts
GROUP BY report_date
ORDER BY report_date;

What Each Part Does: #

• eligible_users: Filters down to users who meet both criteria (monthly registration + monthly purchase) and captures their key dates. Using MIN(p.purchased_at) ensures we only count each user once, even if they bought multiple times.
• daily_dates: Creates a full list of days in the current month—this ensures your report has no gaps (e.g., weekends with no activity still show the cumulative total from the last active day).
• daily_user_counts: Links each eligible user to every date from their first qualifying day (either registration or purchase, whichever is later) up to today.
• Final SELECT: Computes the cumulative unique user count for each day.

Metabase GUI Alternative #

If you prefer sticking to the GUI, you can build this step-by-step:

1. Join Tables: Start a new question, select the User table, then join it with Purchase on user_id.
2. Add Filters:
   • Filter User.registered_at to "This Month"
   • Filter Purchase.purchased_at to "This Month"
3. Group & Aggregate:
   • Group by User.registered_at (convert it to a "Date" type instead of timestamp first)
   • Add a metric: "Distinct count" of user_id
4. Enable Cumulative Calculation:
   • In the visualization tab (after running the query), click on the metric column header.
   • Select "Summarize" → "Cumulative sum"
   • This will turn your daily unique count into a running total.

Note for GUI Users: #

If you notice gaps in dates (days with no users missing from the report), you can use Metabase's "Add a date dimension" option to include all days in the month, or switch to the SQL query above for more control.

Quick Adjustments #

• If you want to target a specific month instead of the current one, replace CURRENT_DATE with a date from that month (e.g., '2024-03-15').
• If you want to count users starting from their registration date (even if they purchased later), remove the GREATEST() function in the daily_user_counts CTE and just use eu.register_date <= dd.report_date.

内容的提问来源于stack exchange，提问作者Osoba Osaze