[빠짝스터디 4주차] 6-1 과제

개요

Domain : 배달음식 어플리케이션

Data set : app_logs 테이블

데이터기간: 2022.08~2022.12

목적: Foodie 현황 데이터분석

데이터분석

1. 월별 활성 사용자 수

• 목적: 월별 활성 고객 파악을 통해 서비스의 전반적인 흐름 파악

1-1 지표정의

• MAU (Monthly Active Users)

  • 한 달 동안 앱을 사용한 순수 고유 사용자 수

  • 중복을 제거한(COUNT DISTINCT) user_id 기준으로 집계

  • 서비스의 전반적인 성장과 규모를 보여주는 핵심 지표

• Cart Users

  • 한 달 동안 장바구니에 상품을 담은 순수 고유 사용자 수

  • event_name = 'click_cart'인 이벤트의 고유 사용자 수

  • 실제 구매 의도를 가진 잠재 고객의 규모를 파악할 수 있는 지표

• Payment Users

  • 한 달 동안 실제 결제를 진행한 순수 고유 사용자 수

  • event_name = 'click_payment'인 이벤트의 고유 사용자 수

  • 실제 매출 발생에 기여한 고객의 규모를 나타내는 지표

• Cart to Payment Rate

  • 장바구니 사용자 중 실제 결제로 이어진 비율 (%)

  • (Payment Users / Cart Users) * 100으로 계산

  • 주요 의미:

    • 구매 전환율을 나타내는 핵심 지표

    • 장바구니 단계에서의 이탈률 파악 가능

    • 높을수록 구매 전환이 잘 이루어짐을 의미

쿼리

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,
      event_params.key AS param_key,
      event_params.value.string_value AS string_value,
      event_params.value.int_value AS int_value
  FROM advanced.app_logs
  CROSS JOIN UNNEST(event_params) as event_params
  WHERE event_date BETWEEN "2022-01-01" AND "2022-12-31"
)
# 월별 활성 사용자 분석
,monthly_active AS (
  SELECT
    FORMAT_DATE('%Y-%m', event_date) as year_month,
    COUNT(DISTINCT user_id) as MAU,
    COUNT(DISTINCT CASE WHEN event_name = 'click_cart' THEN user_id END) as cart_users,
    COUNT(DISTINCT CASE WHEN event_name = 'click_payment' THEN user_id END) as payment_users,
    ROUND(COUNT(DISTINCT CASE WHEN event_name = 'click_payment' THEN user_id END) * 100.0 / 
          NULLIF(COUNT(DISTINCT CASE WHEN event_name = 'click_cart' THEN user_id END), 0), 2) as cart_to_payment_rate
  FROM base
  GROUP BY year_month
  ORDER BY year_month
)
SELECT * FROM monthly_active

시각화

결론

1. 8월~10월 사이 사용자 확대

2. 특시 10월에 MAU가 증가하여 서비스의 성장

3. 사용자 수가 크게 증가했음에도 구매전환율이 안정적으로 유지

4. MAU증가와 함께 실제 구매자 수도 증가

⇒ 서비스가 8~10월 사이 급격한 성장세를 통해 사용자가 증가하였으며 일시적인 효과가 아닌 안정적으로 성장세에 맞는 고객을 확보하고 있는 중

Action Item

• MAU 증가 원인 파악하기

  • 신규고객, 기존고객 중 어떤 고객이 활성화가 된건지 (신규, 기존에 따른 프로모션 등 확인하기)

    • 신규고객이라면 어떤 채널로 유입이 된건지 유입 근거 찾아보기

    • 기존고객이라면 기존고객 대상 프로모션을 한게 있는지, 특정페이지 개선이 되었거나, 변경사항이 있는지 확인해보기

       

2. Funnel 분석

• 쿼리

  WITH base AS (
    SELECT 
      DISTINCT
        user_id,
        event_name,
        event_timestamp,
        DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime,
        DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date,
        user_pseudo_id
    FROM advanced.app_logs
    WHERE
      event_date BETWEEN "2022-01-01" AND "2022-12-31"
  ), user_journey AS (
    SELECT 
      user_id,
      event_date,
      event_name,
      event_datetime,
      -- 사용자별 이벤트 발생 순서
      ROW_NUMBER() OVER (PARTITION BY user_id, event_date ORDER BY event_datetime) as event_sequence
    FROM base
  ),
  funnel_steps AS (
    SELECT 
      event_date,
      COUNT(DISTINCT CASE WHEN event_name = 'screen_view' THEN user_id END) as view_users,
      COUNT(DISTINCT CASE WHEN event_name = 'click_cart' THEN user_id END) as cart_users,
      COUNT(DISTINCT CASE WHEN event_name = 'click_payment' THEN user_id END) as payment_users
    FROM user_journey
    GROUP BY event_date
  ),
  funnel_rates AS (
    SELECT 
      event_date,
      view_users,
      cart_users,
      payment_users,
      ROUND(cart_users * 100.0 / NULLIF(view_users, 0), 2) as view_to_cart_rate,
      ROUND(payment_users * 100.0 / NULLIF(cart_users, 0), 2) as cart_to_payment_rate,
      ROUND(payment_users * 100.0 / NULLIF(view_users, 0), 2) as view_to_payment_rate
    FROM funnel_steps
  )
  #SELECT * FROM funnel_rates
  #ORDER BY event_date
  
  SELECT 
    event_date,
    view_users as view_users_count,
    cart_users as cart_users_count,
    payment_users as payment_users_count,
    ROUND((cart_users * 100.0 / view_users), 2) as view_to_cart_rate,
    ROUND((payment_users * 100.0 / cart_users), 2) as cart_to_payment_rate,
    ROUND((payment_users * 100.0 / view_users), 2) as total_conversion_rate
  FROM funnel_rates
  ORDER BY event_date

  시각화

⇒ 공휴일 등 특정 날짜에 고객 유입 변동이 큼

• 8월부터 10월까지 고객이 급격하게 늘었으나 이탈율은 9월에 높게 나타났음

• 그에 비해 10월은 고객이 크게 늘어났음에도 이탈율은 큰폭으로 보이지 않음

• view > cart는 20% 수준의 이탈율이나, cart > payment가 60% 수준의 큰 이탈율 발생

⇒ 구간별 고객수가 전체적으로 같이 확대되어 이탈율은 월별 특이점 없음

Action Item

• cart에서 payment 이탈 고객에게 쿠폰지급 프로모션 또는

• 결제단계 최소화를 통해 이탈율 개선 방법 모색 필요