user_acquisition.sql

SELECT
    CAST(DATE_TRUNC('month', signup_ts) AS DATE) AS cohort_month,
    acquisition_channel,
    country,
    COUNT(DISTINCT user_id) AS signups,
    COUNT(DISTINCT CASE
        WHEN latest_stage IN ('activated_account', 'first_purchase', 'repeat_purchase')
            THEN user_id
    END) AS activated_users,
    COUNT(DISTINCT CASE WHEN is_observed_customer THEN user_id END) AS purchasing_users,
    ROUND(
        CAST(COUNT(DISTINCT CASE
            WHEN latest_stage IN ('activated_account', 'first_purchase', 'repeat_purchase')
                THEN user_id
        END) AS DOUBLE) / NULLIF(COUNT(DISTINCT user_id), 0),
        4
    ) AS activation_rate,
    ROUND(
        CAST(COUNT(DISTINCT CASE WHEN is_observed_customer THEN user_id END) AS DOUBLE)
            / NULLIF(COUNT(DISTINCT user_id), 0),
        4
    ) AS purchase_conversion_rate
FROM modeled_users
WHERE signup_ts IS NOT NULL
GROUP BY 1, 2, 3;