Foodie의 현재 상황 파악

현재 사용자 기반이 어떻게 구성되어 있는지, 앱을 얼마나 자주 사용하는지, 그리고 이탈한 사용자가 얼마나 되는지에 대한 파악

1. 사용자 상태: Foodie 앱 사용자는 활성 사용자, 휴면 사용자, 이탈 사용자 중 어떤 상태에 속해 있는가?
2. 사용자 활동 현황: 앱 사용 빈도는 어떤 패턴을 보이고 있는가?
3. 플랫폼별 비교: Android와 iOS 사용자 간 활동 패턴에 차이가 있는가?

1. Foodie의 활성 사용자는 전체 사용자 중 약 60%를 차지할 것이다.
2. 이탈 사용자의 비율은 약 30%로 예상되며, 장기적으로 감소세를 보여야 한다.
3. Android 사용자가 iOS 사용자에 비해 더 높은 활성 비율을 보일 가능성이 있다.

1. 사용자 정의:
   • 활성 사용자(Active): 최근 7일 내 이벤트를 기록한 사용자.
   • 휴면 사용자(Dormant): 마지막 이벤트 발생 후 7~30일 사이의 사용자.
   • 이탈 사용자(Churned): 마지막 이벤트 이후 30일 이상 활동이 없는 사용자.

• user_pseudo_id: 사용자 식별자.
• event_timestamp: 이벤트 발생 시간.
• platform: 사용자가 앱에 접속한 플랫폼(Android 또는 iOS).

쿼리

WITH base AS (
  SELECT
    DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date,
    CAST(user_pseudo_id AS STRING) AS user_pseudo_id,
    platform
  FROM advanced.app_logs
),
user_activity AS (
  SELECT
    user_pseudo_id,
    MIN(event_date) AS first_event_date,
    MAX(event_date) AS last_event_date,
    platform
  FROM base
  GROUP BY user_pseudo_id, platform
),
latest_event_date AS (
  SELECT MAX(event_date) AS latest_date FROM base
)
SELECT
  platform,
  CASE
    WHEN DATE_DIFF((SELECT latest_date FROM latest_event_date), last_event_date, DAY) <= 7 THEN 'Active'
    WHEN DATE_DIFF((SELECT latest_date FROM latest_event_date), last_event_date, DAY) > 7 AND DATE_DIFF((SELECT latest_date FROM latest_event_date), last_event_date, DAY) <= 30 THEN 'Dormant'
    ELSE 'Churned'
  END AS user_status,
  COUNT(*) AS user_count
FROM user_activity
GROUP BY platform, user_status
ORDER BY platform, user_status;

• 전체 사용자 상태 분포: