[Tips]基準日毎の連続継続日数を出力してみる。

前提

• 作ったのはいいけど、整理しておかないと忘れるので、備忘録的にメモ。
• SQL黒魔術本（ビッグデータ分析・活用のためのSQLレシピ）になさそうなレシピだったので、残しておいても良さそうなので記載します。
• Googleが提供しているオープンソースbigquery-public-data.stackoverflow.commentsを利用します。
  • 説明ページ
  • "user_id"と"action実施日"のデータがあれば、他のデータソースでも試せます。

この分析の活用方法

• ユーザの定着を見るには、基準日からの経過日数毎のリテンション率を見る、コホート分析を活用します。
• それに対して、さらに、連続アクションかどうか？という基準を加えることで、より詳しくユーザの定着度合いを見ます。

クエリ

• 2018-10-01から使ってるのは、参考データソースのユーザー数が2018-10-01から多いためです。
• コホート作成の対象期間は適当に調整してください。

# standardSQL
WITH 
-- 元データからアクションユーザid、アクション実行日を作成
base_table AS (
  SELECT DISTINCT
    user_id
    , DATE(creation_date) AS action_date
    , 1 AS action_flag
  FROM
    `bigquery-public-data.stackoverflow.comments`
  WHERE
    DATE(creation_date) >= '2018-10-01'
)
-- コホート作成の対象期間を作成
, calendar_table AS (
  SELECT
    base_date
  FROM
    UNNEST(GENERATE_DATE_ARRAY('2018-10-01', '2018-10-09', INTERVAL 1 DAY)) AS base_date
)
-- アクションユーザid毎に、アクション実施日で並び替えて、次回のアクション実施日を取得
, process_table1 AS (
  SELECT DISTINCT
    base_date
    , action_date
    , user_id
    , action_flag
    , lead(user_id,1) OVER (PARTITION BY base_date, user_id ORDER BY action_date) AS lead_id
    , lead(action_date,1) OVER (PARTITION BY base_date, user_id ORDER BY action_date) AS lead_action_date
  FROM
    base_table
  CROSS JOIN
    calendar_table
  WHERE
    action_date >= base_date
)
-- アクションユーザid毎に、アクション実施日に対して次回のアクション実施日が連続となっているかどうかをチェック
, process_table2 AS (
  SELECT
    *
    , CASE
        WHEN lead_action_date is NULL THEN 1
        WHEN user_id = lead_id AND DATE_ADD(action_date,  INTERVAL 1 DAY)  <> lead_action_date THEN 1
        ELSE 0
      END AS not_continue_date_flg
  FROM
    process_table1
)
-- 対象期間中、　id毎にアクションが連続日になっていない日数をカウント
, process_table3 AS (
  SELECT
    *
    ,  SUM(not_continue_date_flg) OVER (PARTITION BY base_date, user_id) AS cum_not_continue_date_flg
  FROM
    process_table2
)
-- 対象期間中、id毎に連続日数を計算
, process_table4 AS (
  SELECT
    *
    , SUM(action_flag) OVER (PARTITION BY base_date, user_id, cum_not_continue_date_flg) AS continue_num
  FROM
    process_table3
)
-- 対象日について計測期間中の初回アクションかどうか？をフラグ立て
, include_base_date_flag_table AS (
  SELECT
    base_date
    , user_id
    , CASE
        WHEN base_date = min_action_date then 1
        ELSE 0
      END AS include_base_date_flag
  FROM (
    SELECT
      base_date
      , user_id
      , min(action_date) as min_action_date
     FROM
       process_table4
     GROUP BY
       1, 2
   )
)
-- 対象日からみた、連続日毎のUU
SELECT DISTINCT
  base_date
  , continue_num
  , COUNT(DISTINCT user_id) OVER (PARTITION BY base_date, continue_num) AS user_count
  , COUNT(DISTINCT user_id) OVER (PARTITION BY base_date) AS base_user_count
  , COUNT(DISTINCT user_id) OVER (PARTITION BY base_date, continue_num) / COUNT(DISTINCT user_id) OVER (PARTITION BY base_date) AS continue_rate
FROM
  process_table4 main
LEFT OUTER JOIN
  include_base_date_flag_table target
  USING(user_id, base_date)
WHERE
  include_base_date_flag = 1
 ; 

出力結果

• BigQuery consoleの新UIだと、”データポータルで調べる”で出力できるので便利です。

• グラフの種類を”ヒートマップ付きピボットテーブル”
• 所感：土日は、投稿は減りますね。

• グラフの種類を”折れ線”にして、"計測期間中の平均連続実行率"
  • 所感：意外と、連続で投稿している人がいるstackoverflow。

参考

• SQLで最長連続課金日数を求める