weekly retention 구하기 과제

BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)

3-7. 리텐션 SQL 쿼리 작성하기 : Weekly Retention

해결된 질문

작성

weekly retention 구하기 과제 해 봤습니다.

처음에 알려주신대로 두 가지 방법

1) EXTRACT 과 2) DATA_TRUNC 을 써서 각각 해 봤는데 일을 자르는 기준이 달라서 그런지 첫 데이터는 값이 같은데 1주가 차이나는 시점부터 데이터들의 값이 미세하게 차이가 나더라구요.

그리고 차이를 계산할 때도 INT 타입이라 DIFF_DATE 대신 그냥 컬럼끼리 빼는 방법으로 계산해야 됐습니다.

만약 이게 동일 년도 내에서만 해야하는 분석이라면 어떤 방법이 더 좋은지 궁금합니다.

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
    `advance.app_logs`
  WHERE 
    event_date BETWEEN '2022-08-01' AND '2022-11-03'
)
, first_week_data AS(

SELECT
  *,
  MIN(week_date) OVER(PARTITION BY user_pseudo_id) AS first_week
FROM (
  SELECT 
    user_pseudo_id,
    event_date,
    -- DATETIME_TRUNC(DATE(event_date), WEEK(MONDAY)) AS week_date,
    EXTRACT(WEEK FROM event_date) AS week_date

  FROM 
    base
  )
), week_diff_data AS (

SELECT 
  *,
  -- DATE_DIFF(week_date, first_week, WEEK) AS week_diff
  (week_date-first_week) AS week_diff
FROM first_week_data
), first_week_value_data AS(
SELECT
  *,
  FIRST_VALUE(user_cnt) OVER (ORDER BY week_diff) AS first_week_value
FROM(  
SELECT
  week_diff,
  COUNT(DISTINCT user_pseudo_id) AS user_cnt, 

FROM week_diff_data

GROUP BY ALL)
ORDER BY 
  week_diff
)
SELECT
  *,
  ROUND(SAFE_DIVIDE(user_cnt, first_week_value),3) AS retention

FROM first_week_value_data

sql Google-Analytics firebase google-sheets bigquery

답변 2

카일스쿨

지식공유자

안녕하세요! 두가지 방법 모두 하시면서 차이점 찾으신 점 박수드리고 싶네요!

2가지 방법이 있는데, 저는 DATE_TRUNC를 한 후, DATE_DIFF를 하는 것을 선호합니다.

그 이유는 데이터를 분석하다보면 연도가 넘어가는 상황이 있는데 그럴 때는 DATE_TRUNC하고 DATE_DIFF를 하는 것이 더 장기적으로 유연하게 활용할 수 있습니다. EXTRACT를 사용하면 YEAR도 항상 가지고 가야하고, 빼다가 실수가 생기는 경우를 많이 봤습니다.

동일 연도 내에서 한다고 하면 EXTRACT가 편하긴 한데, 그러는 경우에 EXTRACT(WEEK FROM date)보다 EXTRACT(ISOWEEK FROM date)를 하는 것이 좋습니다.

ISOWEEK은 ISO 6701의 표준인데, 월요일부터 시작하고 목요일이 포함된 주가 해당 연도의 1주차가 됩니다. 1~53범위의 값이 반환되고 더 일관성 있는 주차가 계산되게 됩니다.

저는 동일 연도에서도 DATE_TRUNC + DATE_DIFF를 추천하긴 해요..!

인프런 AI 인턴

안녕하세요, 인프런 AI 인턴입니다.

Min님, Weekly Retention 쿼리를 두 가지 방법(EXTRACT와 DATE_TRUNC)으로 구현하신 점 훌륭합니다. 이 두 방법은 주간 데이터를 다룰 때 서로 다른 방식으로 작동할 수 있으며, 따라서 특정 요일을 기준으로 주로 나눴을 경우 미세한 차이가 발생할 수 있습니다.

차이점 설명:

DATE_TRUNC:
주어진 날짜를 특정 주, 월, 또는 연도로 자릅니다. 예를 들어, 주간 데이터를 자를 경우 주 초일(보통 월요일)을 기준으로 주를 시작합니다.
EXTRACT:
날짜에서 특정 필드를 추출합니다. 예를 들어 EXTRACT(WEEK FROM date)는 해당 날짜가 속한 주의 주 번호를 추출하며, 이는 특정 시작 요일과 무관하게 작동할 수 있습니다.

동일 년도 내 분석:

동일 년도 내의 분석을 수행할 때 DATE_TRUNC가 특정 요일을 기준으로 주를 나누므로 명시적이며 일관성 있는 결과를 제공합니다.
EXTRACT는 간단하게 주 번호를 추출하므로 간편하지만 구체적인 주의 시작일에 따라 결과가 다르게 나올 수 있습니다.

따라서, 일관된 주의 경계를 필요로 하는 분석이라면 DATE_TRUNC를 사용하는 것이 적합할 수 있습니다. 데이터의 시작일이나 요일에 따라 다르다면 EXTRACT를 사용할 수도 있지만, 그럴 경우 EXTRACT의 동작 방식을 확실히 이해해야 합니다.

반면에, 차이를 계산할 때 DATE_DIFF를 사용하지 않은 점은 각각의 방법을 시도하면서 INT 타입의 컬럼간 연산으로도 충분히 차이를 산출하셨기에 문제 없다고 판단됩니다.

유사 질문 및 참고 자료:

아래 두 링크를 통해 리텐션 쿼리 작성 시 주의해야 할 점과 개선점에 대한 지식을 더 쌓으실 수 있습니다:

이 자료가 Min님의 리텐션 분석 및 쿼리 최적화에 더 많은 도움을 드리기를 바랍니다.

저는 질문자님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해드리고 있습니다.
현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏
추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.

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