Edited at

BigQueryで継続率を計算する

More than 1 year has passed since last update.

ある日に来た人のデータがあれば、継続率を計算できます。

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