【永久保存版】BigQuery 実購買データ分析でよく使うSQL CPM編

BigQueryを用いたデータ分析で頻繁に出てくるSQL文をここにまとめます。
各自のデータベースに合わせてお使いください。

##前提

今回用いるテーブルのスキーマとデータ例です。

テーブル名：items

フィールド名	データ型	説明
user_id	STRING	ユーザ ID
order_id	STRING	受注番号
total_price	INTEGER	購入合計金額
item_id	STRING	商品 ID
item_name	STRING	商品名
category_name	STRING	カテゴリ名
purchased_at	DATE	購入日

行	user_id	order_id	total_price	item_id	item_name	category_name	purchased_at
1	62178	272324	2780	702671	ピカチュウのぬいぐるみ	ぬいぐるみ	2021-11-01
2	62178	272324	3830	423852	ピカチュウの帽子	帽子	2021-11-01
3	43532	272325	3830	940234	コダックのぬいぐるみ	ぬいぐるみ	2021-11-01
4	62178	272324	2780	702671	ピカチュウのぬいぐるみ	ぬいぐるみ	2021-11-03
5	28564	272327	1200	435666	ミュウのキーホルダー	キーホルダー	2021-11-04
・	・・・	・・・	・・・	・・・	・・・	・・・	・・・

##CPM分析
手順として、
①サブクエリで「初回購入日」と「最終購入日」を算出する
②「初回購入日」と「最終購入日」の差を「在籍期間」とする

CPM分析

WITH main AS (
SELECT
    *,
    first_value(raws.purchased_at) OVER (PARTITION BY raws.user_id ORDER BY purchased_at ASC) as first_purchased_at,
    DATE_DIFF(CAST purchased_at AS DATE), CAST (purchased_at AS DATE), DAY) AS Recently,
    DATE_DIFF(CAST purchased_at AS DATE), CAST (first_value(raws.purchased_at) OVER (PARTITION BY raws.user_id ORDER BY purchased_at ASC) AS DATE), DAY) AS enrolled,
    SUM(raws.total_price) OVER(PARTITION BY raws.user_id ORDER BY raws.order_id rows between unbounded preceding and current row) AS ltv,
    ROW_NUMBER() OVER (PARTITION BY raws.user_id ORDER BY raws.purchased_at DESC) AS RowNo
  FROM
    テーブル名 AS raws
  WHERE
    CAST purchased_at AS DATE) > purchased_at
  ORDER BY
    purchased_at DESC
)
SELECT 
*,
CASE
    WHEN ltv >= 100000 AND Recently <= 240 AND enrolled >= 210 THEN '優良現役'
    WHEN ltv >= 100000 AND Recently > 240 AND enrolled >= 210 THEN '優良離脱'
    WHEN ltv >= 100000 AND Recently <= 240 AND enrolled >= 60 AND enrolled < 210 THEN '流行現役'
    WHEN ltv >= 100000 AND Recently > 240 AND enrolled >= 60 AND enrolled < 210 THEN '流行離脱'
    WHEN ltv <= 100000 AND Recently < 50 AND enrolled >= 60 AND enrolled < 240 THEN 'コツコツ現役'
    WHEN ltv <= 100000 AND Recently >= 50 AND enrolled >= 60 AND enrolled < 240 THEN 'コツコツ離脱'
    WHEN frequency = 1 AND Recently <= 240 THEN '初回現役'
    WHEN frequency = 1 AND Recently > 240 THEN '初回離脱'
    WHEN Recently <= 240 THEN 'よちよち現役'
    WHEN Recently > 240 THEN 'よちよち離脱'
END as cpm
FROM 
main

結果（本日が2021/11/11と仮定）

行	user_id	order_id	total_price	item_id	item_name	category_name	purchased_at	Recently
1	62178	272324	2780	702671	ピカチュウのぬいぐるみ	ぬいぐるみ	2021-11-01	10
2	62178	272324	3830	423852	ピカチュウの帽子	帽子	2021-11-01	10
3	43532	272325	3830	940234	コダックのぬいぐるみ	ぬいぐるみ	2021-11-01	10
4	62178	272326	2780	702671	ピカチュウのぬいぐるみ	ぬいぐるみ	2021-11-03	7
5	28564	272327	1200	435666	ミュウのキーホルダー	キーホルダー	2021-11-04	6
・	・・・	・・・	・・・	・・・	・・・	・・・	・・・	・・・