BigQueryで継続率を計算する

ある日に来た人のデータがあれば、継続率を計算できます。
BigQueryなので富豪的になってます。
適当に編集して使ってね。

継続率の計算

#standardSQL
WITH 
-- 日毎のユーザーアクセス
DUU_HISTORY AS (  
  SELECT 
  USER_ID, -- 数値型 
  DATE       -- 日付型
  FROM --  ユーザーIDとアクセス日が記録されたテーブルを書いてね
),
-- 継続日の定義
CONTINUATION_DAYS AS (
  SELECT 
    DAY -- 継続日  
  FROM  UNNEST(
  ARRAY[1,3,7,14,30] --　継続率を出したい継続日を書いてね
  ) DAY 
),
--初回訪問
FIRST_VISIT AS (
  SELECT 
    USER_ID,  --ユーザーID 
    MIN(DATE) DATE    -- 初回訪問日
  FROM DUU_HISTORY GROUP BY USER_ID 
),
-- 初回訪問日と継続日の表現
DATE_PIVOTS AS (
  SELECT 
    DATE FIRST_VISIT_DATE, --初回訪問日  
    CONTINUATION_DAYS,      --継続日数
    DATE_ADD(DATE, INTERVAL CONTINUATION_DAYS DAY) VISIT_DATE   --訪問日 
  FROM (
    SELECT DAYS.DATE DATE, CONTINUATION_DAYS.DAY CONTINUATION_DAYS FROM ( SELECT DATE FROM DUU_HISTORY GROUP BY DATE ) DAYS
    CROSS JOIN CONTINUATION_DAYS )
  WHERE DATE_DIFF(CURRENT_DATE(), DATE_ADD(DATE, INTERVAL CAST(CONTINUATION_DAYS AS INT64) DAY), DAY ) > 0 
),
--初回訪問したユーザーがある日に来訪したかの表現
CONTINUATION_HISTORY AS(
  SELECT
    DATE_PIVOTS.FIRST_VISIT_DATE FIRST_VISIT_DATE,   --初回訪問日  
    DATE_PIVOTS.CONTINUATION_DAYS CONTINUATION_DAYS,   --継続日数
    CASE
      WHEN FIRST_VISIT.USER_ID = DUU_HISTORY.USER_ID THEN FIRST_VISIT.USER_ID
      ELSE NULL
    END CONTINUATION_USER_ID,  --　継続ユーザーのID
    FIRST_VISIT.USER_ID FIRST_VISIT_USER_ID   -- 初回訪問ユーザーのID
  FROM
    DATE_PIVOTS
  LEFT OUTER JOIN
    FIRST_VISIT
  ON
    DATE_PIVOTS.FIRST_VISIT_DATE = FIRST_VISIT.DATE
  LEFT OUTER JOIN
    DUU_HISTORY
  ON
    DATE_PIVOTS.VISIT_DATE = DUU_HISTORY.DATE
  GROUP BY
    FIRST_VISIT_DATE,
    CONTINUATION_DAYS,
    FIRST_VISIT_USER_ID,
    CONTINUATION_USER_ID 
), 
-- 継続率
RETENSION AS (
SELECT
  FIRST_VISIT_DATE,   --初回訪問日  
  CONTINUATION_DAYS,  --継続日数
  COUNT(DISTINCT FIRST_VISIT_USER_ID) FIRST_VISIT_USERS,  --初回訪問日のユーザー数
  COUNT(DISTINCT CONTINUATION_USER_ID) CONTINUATION_USERS,  --継続日のユーザー数
  100 * COUNT(DISTINCT CONTINUATION_USER_ID) / COUNT(DISTINCT FIRST_VISIT_USER_ID) RETENSION  --継続率
FROM
  CONTINUATION_HISTORY
GROUP BY
  FIRST_VISIT_DATE,
  CONTINUATION_DAYS
)

SELECT * FROM RETENSION

こんなかんじで出ます。

• 初回来訪日
• 経過日数
• 初回来訪日のUU数
• 経過日の再来訪UU数
• 継続率

Row	FIRST_VISIT_DATE	CONTINUATION_DAYS	FIRST_VISIT_USERS	CONTINUATION_USERS	RETENSION
1	2017-09-21	1	100	50	50.0
2	2017-09-21	3	100	30	30.0
3	2017-09-22	1	100	40	40.0