[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だと、”データポータルで調べる”で出力できるので便利です。
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だと、”データポータルで調べる”で出力できるので便利です。
#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。
参考
Author And Source
この問題について([Tips]基準日毎の連続継続日数を出力してみる。), 我々は、より多くの情報をここで見つけました https://qiita.com/tomo_will/items/0b519a4fd4f9c6b64859著者帰属:元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。
Content is automatically searched and collected through network algorithms . If there is a violation . Please contact us . We will adjust (correct author information ,or delete content ) as soon as possible .