신규 가입자 SQL
SET @target_month = '2023-05-01';
SELECT count(id) FROM slid_user
WHERE created_time >= @target_month
AND created_time < DATE_ADD(@target_month, INTERVAL 1 MONTH)
AND (locale = 'ko' or country = 'South Korea')
SQL
복사
신규 가입자 중 결제 전환 수 SQL
SET @target_month = '2023-05-01';
SELECT COUNT(DISTINCT p.user_id)
FROM slid_user u
JOIN payment p ON u.id = p.user_id
WHERE u.created_time >= @target_month
AND u.created_time < DATE_ADD(@target_month, INTERVAL 1 MONTH)
AND (u.locale = 'ko' OR u.country = 'South Korea')
AND p.amount_remaining > 0;
SQL
복사
1개월/12개월 비중 SQL
SET @target_month = '2023-05-01';
SELECT
count(*),
SUM(CASE WHEN DATEDIFF(end_date, start_date) < 32 THEN 1 ELSE 0 END) AS count_1_month,
SUM(CASE WHEN DATEDIFF(end_date, start_date) >= 365 THEN 1 ELSE 0 END) AS count_12_month,
SUM(CASE WHEN DATEDIFF(end_date, start_date) < 32 THEN 1 ELSE 0 END) / COUNT(*) AS portion_1_month,
SUM(CASE WHEN DATEDIFF(end_date, start_date) >= 365 THEN 1 ELSE 0 END) / COUNT(*) AS portion_12_month
FROM payment
WHERE payment_key NOT LIKE '%recurring%'
AND amount_remaining > 0
AND start_date > @target_month
ANd start_date < DATE_ADD(@target_month, INTERVAL 1 MONTH)
AND is_pending != 1
AND payment_key NOT LIKE '%daypass%'
AND payment_key NOT LIKE '%indian%'
AND payment_key LIKE '%payple%'
AND user_id IN (
SELECT id FROM slid_user
WHERE created_time >= @target_month
AND created_time < DATE_ADD(@target_month, INTERVAL 1 MONTH)
AND (locale = 'ko' or country = 'South Korea')
);
SQL
복사
*신규 유저 Only
1개월/12개월 ARPPU SQL
Monthly
SET @target_month = '2023-03-01';
SELECT AVG(amount_remaining)
FROM payment
WHERE payment_gateway = 'payple'
AND payment_key NOT LIKE '%recurring%'
AND amount_remaining > 0
AND amount_remaining != 3900
AND amount_remaining != 9900
AND start_date >= @target_month
AND start_date < DATE_ADD(@target_month, INTERVAL 1 MONTH)
AND DATEDIFF(end_date, start_date) < 32
AND is_pending != 1
SQL
복사
Yearly
SET @target_month = '2023-03-01';
SELECT AVG(
CASE
WHEN payment_key LIKE '%premium_12%' AND lang = 'ko' THEN 209900
ELSE amount_remaining
END
)
FROM payment
WHERE payment_gateway = 'payple'
AND payment_key NOT LIKE '%recurring%'
AND amount_remaining > 0
AND amount_remaining != 3900
AND amount_remaining != 9900
AND start_date >= @target_month
AND start_date < DATE_ADD(@target_month, INTERVAL 1 MONTH)
AND DATEDIFF(end_date, start_date) > 364
AND is_pending != 1
SQL
복사
* 신규 유저 Only
1개월 Cohort retention SQL
SET @cohort_start_date = '2023-01-01';
SET @cohort_end_date = '2023-02-01';
SET @target_start_date = '2023-02-01';
SET @target_end_date = '2023-03-01';
SELECT
cohort_customers_count,
retained_count,
retained_count / cohort_customers_count AS retention_rate
FROM
(
SELECT
(SELECT COUNT(DISTINCT user_id)
FROM payment
WHERE payment_gateway = 'payple'
AND payment_key NOT LIKE '%recurring%'
AND amount_remaining > 0
AND amount_remaining != 3900
AND amount_remaining != 9900
AND start_date >= @cohort_start_date
AND start_date < @cohort_end_date
AND DATEDIFF(end_date, start_date) < 32) AS cohort_customers_count,
(SELECT COUNT(DISTINCT payment.user_id)
FROM payment
INNER JOIN
(
SELECT DISTINCT user_id
FROM payment
WHERE payment_gateway = 'payple'
AND payment_key NOT LIKE '%recurring%'
AND amount_remaining > 0
AND amount_remaining != 3900
AND amount_remaining != 9900
AND start_date >= @cohort_start_date
AND start_date < @cohort_end_date
AND DATEDIFF(end_date, start_date) < 32
) AS cohort_customers
ON payment.user_id = cohort_customers.user_id
WHERE payment_gateway = 'payple'
AND payment_key LIKE '%recurring%'
AND amount_remaining > 0
AND start_date >= @target_start_date
AND start_date < @target_end_date
AND DATEDIFF(end_date, start_date) < 32) AS retained_count
) AS data
SQL
복사