4
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

BigQueryで継続率を計算する

Last updated at Posted at 2017-10-31

ある日に来た人のデータがあれば、継続率を計算できます。
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
4
4
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
4
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?