인프런 커뮤니티 질문&답변

SELECT DISTINCT
  LEFT(FORMAT_DATE('%Y%m%d', event_date), 6) AS login_month,
  COUNT(DISTINCT user_pseudo_id) as count
FROM advanced.app_logs
GROUP BY LEFT(FORMAT_DATE('%Y%m%d', event_date), 6)

2022년 8월부터 2023년 1월까지의 MAU를 살펴보니(MAU기준은 로그인 기록),

첫 달인 2022년 8월에는 미진했지만 그 후로 점차 증가하는 것을 볼 수 있습니다. 특히 9월은 8월 대비 2배에 가까운 성장률을 보였고 10월에는 1.5배 이상의 성장률을 보이면서 점차 사용자가 확대되고 있는 것을 알 수 있습니다.

WITH base AS (
  SELECT
    DISTINCT
      user_pseudo_id,
      DATE_TRUNC(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul'), WEEK(MONDAY)) AS event_week,
      DATE_TRUNC(MIN(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) OVER(PARTITION BY user_pseudo_id), WEEK(MONDAY)) AS first_week
  FROM `advanced.app_logs`
),
first_week_diff AS (
  SELECT
    user_pseudo_id,
    DATE_DIFF(event_week, first_week, WEEK) AS diff_of_week
  FROM base
),
user_counts AS (
  SELECT
    diff_of_week,
    COUNT(DISTINCT user_pseudo_id) AS user_count
  FROM first_week_diff
  GROUP BY diff_of_week
)
SELECT
  diff_of_week,
  user_count,
  ROUND(SAFE_DIVIDE(user_count, FIRST_VALUE(user_count) OVER (ORDER BY diff_of_week ASC)), 2) AS retention_rate
FROM user_counts
ORDER BY diff_of_week;

주 리텐션율을 살펴보면, 첫 주에는 당연히 1%이지만 그 후로 확연히 주마다 접속하는 사용자가 줄고 있습니다. Foodie Express는 아직 Product Market Fit을 발견하지 못한 것으로 보입니다.

WITH base AS (
SELECT 
    DISTINCT
        user_id,
        user_pseudo_id,
        event_name,
        DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date,
        DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime
FROM advanced.app_logs
WHERE 
    event_date BETWEEN '2022-08-01' AND '2022-12-31' -- 데이터 길게보기 
), first_week_and_diff AS (
    SELECT 
        *,
        DATE_DIFF(event_week,first_week, WEEK) AS weeks_after_first_week
    FROM (
        SELECT
            DISTINCT
                user_pseudo_id,
                DATE_TRUNC(MIN(event_date) OVER(PARTITION BY user_pseudo_id), WEEK(MONDAY)) AS first_week,
                DATE_TRUNC(event_date, WEEK(MONDAY)) AS event_week
        FROM base
    )
), active_users AS(
     SELECT
        first_week,
        weeks_after_first_week,
        COUNT(DISTINCT user_pseudo_id) AS avtive_users
  FROM first_week_and_diff
  GROUP BY first_week, weeks_after_first_week
)

SELECT
            first_week,
            weeks_after_first_week,
            avtive_users,
            FIRST_VALUE(avtive_users) OVER(PARTITION BY first_week ORDER BY weeks_after_first_week) AS cohort_users 
         FROM active_users AS a
ORDER BY first_week, weeks_after_first_week;

첫 주에 1,211의 사용자가 있었으나 1주차에 32명으로 급감했습니다 (약 2.6% 유지)

2-8주차에는 비교적으로 안정적이었고, 9주차에서 20주에는 사용자가 좀 더 증가하였습니다.

특히 14주차에 142명으로 가장 많은 사용자가 있었습니다.

이를 살펴보면, 초기에 감소 후에 오히려 사용자 수가 증가하는 패턴을 보이고

오히려 앱을 사용하면 사용할 수록 더 활발하게 앱을 사용하는 것을 알 수 있습니다.

따라서 어떻게든 사용자들을 앱에 유입시켜서 사용하게 만들어야합니다.

특히 주말, 공휴일 또는 음식 주문이 많은 근무 시간 이후에 비활성 사용자에게 정기적인 푸시 알림이나 메시지를 보내면 좋을 것 같습니다.

(음식을 많이 시켜먹는 주말이나, 공휴일 또는 퇴근시간때쯤 맞춰서 푸시알림을 준다)

공휴일에 좀 더 사용자가 증가하는 경향을 보이는 것 같은데, 이 때는 사용자 유입이 많으므로 소액 할인 쿠폰을 제공하는 것이 효과적일 것이라고 판단됩니다.

무엇보다도 정기적인 알림을 통해 앱 사용 습관을 들이는 것이 중요해 보입니다!