LTV vs CAC

Paid CPS

₩4,826

Total CPS

₩1,584

CVR

1.87%

Paid CAC

₩258,075

Total CAC

₩84,706

Monthly Plan - AMRPC

₩31,907

Yearly Plan - AYRPC

₩195,116

Monthly Plan - M1 Retention Rate

44%

Monthly Plan - Plateau Retention Rate

76%

12 Month Ratio

71.43%

Yearly Plan - Y1 Retention Rate

26.67%

LTV

₩304,571

Payback Period (Months)

Table

LTV vs CAC Cohort History

Name

LTV

Total CAC

Payback Period (Months)

Jan 2023

Open

₩171,231

₩54,810

5

Feb 2023

Open

₩167,025

₩52,277

4

Mar 2023

Open

₩199,995

₩57,600

4

Apr 2023

Open

₩326,191

₩172,174

7

May 2023

Open

₩368,591

₩89,492

3

June 2023

Open

₩304,571

₩84,706

3

Calculation Ref

CAC

Cost\ per\ sign-up \over Conversion\ rate

LTV

Total LTV

Total LTV = \left[ (Proportion_{1\_month} \times LTV_{1\_month}) + (Proportion_{12\_month} \times LTV_{12\_month}) \right]

LTV for 1 month plan

Monthly Plan Total LTV

LTV_{1\_month} = LTV\_M1\_Churn + LTV\_M1\_and\_after

LTV_M1_Churn

LTV\_M1\_Churn = AMRPC\_M1 * Churn Rate\_M1

LTV_afterM1

LTV\_afterM1 ={ ARPA\_afterM1 \over Churn\ Rate\_afterM1}

LTV_M1_and_after

LTV\_M1\_and\_after =\\ (AMRPC\_M1 * Retention\ Rate\_M1)\\ + (LTV\_afterM1* Retention\ Rate\_M1))

LTV for 12 month plan

Yearly Plan Total LTV

LTV_{12\_month} = AYRPC_{12\_month} \times \left(1 + \frac{Renewal Rate_{12\_month}}{1 - Churn Rate_{12\_month\_after\_1\_year}}\right)

AMRPC

* AMRPC for new customers

SET @target_month = '2023-06-01';

SELECT AVG(
  CASE 
    WHEN lang = 'en' THEN ROUND(amount_remaining*1200/ROUND(DATEDIFF(end_date, start_date)/30))
	  WHEN payment_key LIKE '%premium_12%' AND lang = 'ko' THEN 17491
    ELSE ROUND(amount_remaining/ROUND(DATEDIFF(end_date, start_date)/30))
  END
) 
FROM payment
JOIN slid_user ON payment.user_id = slid_user.id
WHERE amount_remaining > 0
AND (lang = 'ko' OR lang = 'en')
AND start_date > @target_month
AND end_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 slid_user.created_time > @target_month;
SQL
복사

Monthly

SET @target_month = '2023-06-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-06-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
복사

1month vs 12month ratio

SET @target_month = '2023-06-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
복사

퍼포먼스 마케팅 컨설팅