Monthly Membership Retention Rate

SET @cohort_start_date = '2022-11-01';
SET @cohort_end_date = '2022-12-01';
SET @target_start_date = '2023-06-01';
SET @target_end_date = '2023-07-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
복사

Yearly Plan

Table

Retention by Cohorts

Name

Year 1

2022 Mar

Open

36.36%

2022 Apr

Open

47.37%

2022 May

Open

66.67%

2022 June

Open

26.67%

AVERAGE44.2675%

Retention by Cohort

SET @cohort_start_date = '2022-06-01';
SET @cohort_end_date = '2022-07-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) > 364) 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) > 364
        ) 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 >= DATE_ADD(@cohort_start_date, INTERVAL 1 YEAR)
        AND start_date < DATE_ADD(@cohort_end_date, INTERVAL 1 YEAR)
        AND DATEDIFF(end_date, start_date) > 364) AS retained_count
  ) AS data
SQL
복사

Average Subscription Length

Average\ Subscription\ Length = \\(Churn\ Rate\_M1 * 1)\\ + ((1 - Churn\ Rate\_M1) * (1 + (1 / Churn\ Rate\_afterM1)))

0.5484 0.6531 0.589 0.6837000000000001 0.5932 0.68