SQL 코호트 유지 분석

Retention은 정의된 행위자가 시작 마일스톤 이후에 복귀하는지 여부를 묻습니다. 유용한 보존 쿼리에는 행위자, 집단 이벤트, 적격 반품 활동, 간격 및 관찰 기간이 명시되어 있습니다. 5가지 선택 사항이 명확해질 때까지 "주간 보존"은 모호합니다.

코호트 및 활동 테이블을 별도로 구축

WITH cohorts AS (
  SELECT
    account_id,
    date_trunc('week', MIN(timestamp_utc)) AS cohort_week
  FROM product_events
  WHERE event_name = 'activation_completed'
  GROUP BY account_id
),
activity AS (
  SELECT DISTINCT
    account_id,
    date_trunc('week', timestamp_utc) AS activity_week
  FROM product_events
  WHERE event_name IN ('query_completed', 'dashboard_viewed')
)
SELECT
  cohort_week,
  activity_week,
  COUNT(DISTINCT cohorts.account_id) AS retained_accounts
FROM cohorts
JOIN activity
  ON cohorts.account_id = activity.account_id
 AND activity.activity_week >= cohorts.cohort_week
GROUP BY cohort_week, activity_week
ORDER BY cohort_week, activity_week;

이 결과는 코호트 및 활동 주를 보존합니다. 보고 계층은 선택한 SQL 엔진에서 지원하는 날짜 산술을 확인한 후 간격을 0주, 1주 및 이후 기간으로 변환할 수 있습니다. 중간 날짜를 표시하면 경계 실수를 더 쉽게 잡을 수 있습니다.

분모를 한 번 정의하십시오.

분모는 나중에 활동 이벤트를 생성한 숫자가 아니라 코호트의 적격 행위자 수입니다. 코호트 크기를 별도로 계산하고 이를 유지된 수에 결합합니다. NULLIF로 백분율을 보호하고 출력에서 ​​두 개수를 모두 유지합니다.

최근 코호트는 이후 간격에 도달할 시간이 없었습니다. 해당 셀을 0 보존으로 처리하는 대신 불완전한 것으로 표시하십시오. 또한 0주차가 활성화 자체를 의미하는지 아니면 활성화 후 별도의 반환을 의미하는지 결정하세요.

백그라운드 하트비트가 아닌 지속성 값을 나타내는 이벤트를 사용하십시오. 제어된 필드가 포함된 테스트 계정 및 자동화된 활동을 제외합니다. 작은 장치를 사용하여 신원 병합, 이벤트 중복, 지각 및 시간대 경계를 검증합니다.

다른 날짜 계산을 적용하기 전에 마일스톤 진행에 대한 퍼널 분석, 간격 규칙에 대한 시간 버킷팅 및 DataFusion SQL 참조를 읽어보세요. SQL 방언.