プロダクト横断分析に役立つ、事前集計しないサマリーテーブル設計

datatech-jp Casual Talks #7 の発表資料です。
https://datatech-jp.connpass.com/event/347574/

Transcript

1. 1 プロダクト横断分析に役立つ、 事前集計しないサマリーテーブル設計 ucchi- 2025-04-16 datatech-jp Casual Talks #7
2. 2 自己紹介 • アナリティクスエンジニア • X: @hanon52_ • 2021年 ピクシブ株式会社に新卒入社 ◦ 広告のデータ基盤設計、Looker や Salesforce の整備 ◦ 社内の様々なプロダクトのデータ基盤構築、助言 ◦ データの入口から出口まで何でもやります • 美味しいご飯を食べたり作ったりするのが好き 打出 紘基(ucchi-) 2
3. 3 プロダクト横断分析に役立つデータモデリング手法の紹介 発表の概要 集計月 店 Aにおける売上 Bにおける売上 2025年1月 一郎商店 100,000 1,000 2025年2月 二郎商店 1,000 12,000 集計月 店 Aにおける売上 2025年1月 一郎商店 100,000 2025年2月 二郎商店 1,000 集計月 店 Bにおける売上 2025年1月 一郎商店 10,000 2025年2月 二郎商店 12,000 プロダクトAとBを横断した売上テーブル プロダクトAにおける売上テーブル プロダクトBにおける売上テーブル
4. 4 他の DWH 製品や BI 製品を使われている場合は、適宜置き換えてお聞きください。 BigQuery と Looker を前提に話します
5. 5 プロダクト横断分析とは
6. 6 同じユーザーに対して、複数のプロダクトを展開する プロダクト横断分析とは
7. 7 プロダクト横断分析は、事業全体の最適化に貢献する プロダクト横断分析とは • ユーザー理解の促進：ユーザーをより深く理解することで、新たな施策を考えるための洞察を得る • ユーザー体験の向上：複数のプロダクト利用を阻害する要因を特定し、顧客満足度や継続率を向上させる • 事業全体の収益増加：クロスセルやアップセルのターゲットを特定し、収益向上につなげる
8. 8 プロダクト横断分析を行う上では、分析用のテーブルがあると便利 プロダクト横断分析とは プロダクト横断分析を行うには、それぞれのサービスのデータを収集し、加工し、結合する必要がある。 データ分析者が都度全てを行うのは大変なため、事前に前処理済みの分析用データがあると便利。 プロダクトA プロダクトB プロダクトC データ基盤 月 ユーザー ID Aの 指標 Bの 指標 Cの 指標 2025年1月 001 … … … 2025年2月 002 … … … プロダクト横断分析用テーブル 統合 アドホックな分析 収集 分析
9. 9 課題
10. 10 プロダクト横断分析では、ドリルアクロスを行うのが一般的 課題 プロダクト横断分析を行うためには、ドリルアクロスを行なう。 1. プロダクトごとに共通の集計軸でサマリーテーブルを作る 2. FULL JOIN を行い、テーブルを結合する 集計月 店 Aにおける売上 Bにおける売上 2025年1月 一郎商店 100,000 1,000 2025年2月 二郎商店 1,000 12,000 集計月 店 売上 2025年1月 一郎商店 100,000 2025年2月 二郎商店 1,000 集計月 店 売上 2025年1月 一郎商店 10,000 2025年2月 二郎商店 12,000 プロダクトAとBを横断した「月×店」の売上テーブル プロダクトAにおける売上テーブル プロダクトBにおける売上テーブル 集計軸が揃っているので 結合できる
11. 11 指標を事前集計すると、柔軟な分析が困難になってしまう 課題 サマリーテーブルの粒度に合わせて指標を集計してしまうと、後から元のログを復元することはできない。 そのため、いざ分析を行おうとすると、以下のような課題が発生する。 課題 具体例 粒度の変更が難しい 月ごとの「ユニークユーザー数」や「平均単価」といった指標を、後から四半期ごとや年ごと にすることはできない。サマリーテーブルの再集計が必要になる。 指標の追加が難しい 「売上」から、特定カテゴリーに絞った「売上」を計算することはできない。ログテーブルか ら指標「特定カテゴリーの売上」を集計し、サマリーテーブルに追加する必要がある。 深掘りが出来ない 指標「購買者数」から個別の購買者を掘り下げることはできない。ログテーブルを用いて分析 する必要がある。 複数プロダクトに またがった指標を 定義できない 指標「プロダクトA,B両方でその店から購入した人数」を、サマリーテーブルにある指標「Aで 店から購入した人数」と「Bで店から購入した人数」から計算することはできない。ログテーブ ルの段階で計算しておく必要がある。
12. 12 例えば、月ごとのユニークユーザー数のサマリーテーブルを用いて、 年ごとのユニークユーザー数を求めることはできない。 集計月 ユニークユーザー数 2024-01 100人 2024-02 200人 2024-03 300人 … … 2024-12 1,200人 粒度の変更が難しい例：ユニークユーザー数の計算 課題 集計年 ユニークユーザー数 2024 7,800人？ これは重複ありの 延べユーザー数 集計年 ユニークユーザー数 2024 2,000人 重複を排除すること で、正しい値になるが... 月ごとのユニークユーザー数テーブル 年ごとのユニークユーザー数テーブル？ 年ごとのユニークユーザー数テーブル …これをサマリーテーブルから直接 集計することはできない
13. 13 サマリーテーブルの「売上」から、特定カテゴリーに絞った「売上」を計算することはできない。 集計月 売上 2024-01 100万円 2024-02 150万円 … … 指標の追加が難しい例：特定カテゴリーの売上 課題 売上テーブル 集計月 売上 2024-01 10万円 2024-02 20万円 … … 書籍カテゴリーに絞った売上テーブル サマリーテーブルから直接 集計することはできない プロダクトAの売上テーブル 集計月 カテゴリー 売上 2024-01 書籍 100万円 2024-01 生活雑貨 120万円 … … … 集計月 売上 2024-01 100万円 2024-02 150万円 … … プロダクトBの売上テーブル ...集計粒度が異なるため ドリルアクロスできなくなる カテゴリーを 分析軸に加え れば集計可能 だが...
14. 14 例えば、指標「ユニークユーザー数」から、購買額が多いトップ3のユーザーを深ぼることはできない。 集計月 店 ユニークユーザー数 購買金額トップ3のユーザー 2024-01 一郎商店 100人 佐藤,田中,山田 2024-01 二郎商店 50人 鈴木,本田,伊藤 … … … … 深掘りができない例：トップ購買者の分析 課題 月ごとのユニークユーザー数テーブル 「ユニークユーザー数」から 直接集計することはできない …
15. 15 例えば、指標「プロダクトA,B両方でその店から購入した人数」を、 サマリーテーブルにある指標「Aにおける購入者数」と「Bにおける購入者数」から計算することはできない。 複数プロダクトにまたがった指標を定義できない例：同時購入した人数 課題 集計月 店 Aにおける 購入者数 Bにおける 購入者数 両方で購入 した人数 2025年1月 一郎商店 100人 50人 10人 2025年2月 二郎商店 50人 10人 1人 集計月 店 Aにおける 購入者数 2025年1月 一郎商店 100人 2025年2月 二郎商店 50人 集計月 店 Bにおける 購入者数 2025年1月 一郎商店 50人 2025年2月 二郎商店 10人 プロダクトAとBを横断した「月×店」の購入者数テーブル プロダクトAにおける店ごとの購入者数テーブル プロダクトBにおける店ごとの購入者数テーブル サマリーテーブルから 直接集計することはできない
16. 16 解決策
17. 17 集計粒度より細かいデータは全て ARRAY に格納する 解決策 データ分析において、どのような指標が必要になるかは分析を行なってみないとわからない。 そこで、サマリーテーブルの粒度より細かいデータは ARRAY 型で持つ。 これにより、分析を行うタイミングまで、指標の計算を先送りする。 購買日時 店 客 購買額 2025-03-01 10:00:00 一郎商店 田中 500円 2025-03-01 15:00:00 一郎商店 佐藤 1000円 2025-03-01 18:00:00 二郎商店 山田 5,000円 … … … … 集計月 店 購買履歴 ARRAY 2025-03 一郎商店 田中 500円 佐藤 1000円 2025-03 二郎商店 山田 5,000円 … … … … 月×店 で集約 購買履歴を ARRAY で格納
18. 18 複数のプロダクトのサマリーテーブルを結合する 解決策 サマリーテーブルの集計粒度を揃えることで、異なるプロダクトのファクトを結合できるようになる。 集計月 店 Aの購買履歴 2025-03 一郎商店 田中 500円 佐藤 1,000円 2025-03 二郎商店 山田 5,000円 … … … … 集計月 店 Bの購買履歴 2025-03 一郎商店 田中 50,000円 2025-03 三郎商店 鈴木 2,000円 山田 3,000円 … … … … 集計月 店 Aの購買履歴 Bの購買履歴 2025-03 一郎商店 田中 500円 田中 50,000円 佐藤 1000円 - 2025-03 二郎商店 山田 5,000円 - 2025-03 三郎商店 - 鈴木 2,000円 山田 3,000円 … … … … プロダクトAにおける購買テーブル プロダクトBにおける購買テーブル プロダクトAとBを横断した「月×店」のテーブル
19. 19 ARRAY から必要な項目を抜き出し、集約し、指標を計算する。 具体例）年の粒度で、店ごとの購買者数を計算する create function distinctCount(arr) as ( (select count(distinct v) from unnest(arr) as v) ); select extract(year from 集計月) as 集計年, 店, distinctCount( array_concat_agg( array( select 購買者 from unnest(Aの購買履歴) ) ) ) as 購買者数 from `table` group by 集計年, 店 order by 集計年, 購買者数 desc 分析の際は、ARRAY から必要な項目を抜き出す 解決策 集計月 店 Aの購買履歴 Bの購買履歴 2025-03 一郎商店 田中 500円 田中 50,000円 佐藤 1000円 - 2025-03 二郎商店 山田 5,000円 - 2025-03 三郎商店 - 鈴木 2,000円 山田 3,000円 … … … … 独自のUDFを定義 UDFを活用
20. 20 measure: "プロダクトAの購買者数" { type: number sql: distinctCount( array_concat_agg( array( select 購買者 from unnest(${TABLE}.Aの購買履歴) ) ) ) ;; } Looker を使って複雑な分析SQLを隠蔽する 解決策 Looker の measure を用いて、先ほどの複雑な SQL クエリを隠蔽する。 ユーザーは、複雑な分析用のSQLを直接書く必要がなくなり、試行錯誤や結果の解釈に集中できる。 measure: "プロダクトAの購買金額" { type: sum sql: ( select sum(購買金額) from unnest(${TABLE}.Aの購買履歴) ) ;; }
21. 21 ユーザーはSQLを書かなくてよくなり、管理的な負担も抑えられる 解決策 プロダクトA プロダクトB 加算性を保った プロダクト横断 サマリーテーブル SQLを隠蔽する プロダクト横断 Looker Explore 参考）「セマンティックレイヤー入門」（Ikki Miyazaki）を一部改変 ✅LookML を改修するだけで複 雑な指標を柔軟に追加できる ✅ユーザーはSQLを 書かなくてもよくなる
22. 22 技術的なポイント
23. 23 BigQuery の処理性能の高さと、半構造化データの取りまわしやすさ 技術的なポイント 技術的なポイントは以下。 • BigQuery の処理性能が高い。数千万行〜数億行の処理が2,3秒で返ってくる • 半構造化データを柔軟に取り回せる ◦ ARRAY() や ARRAY_CONCAT_AGG() など ◦ ARRAY に関する制約が少ない（2025年4月現在） ▪ 最大行サイズは 100MB ▪ ARRAY<STRUCT> の構造において、ネストされたレコードの最大深度は 15 レベル
24. 24 データウェアハウスの性能が向上したことで検討できるようになった 技術的なポイント
25. 25 UDF を噛ませることで、BigQuery のエラーを回避できる 技術的なポイント # UNNEST(ARRAY_CONCAT_AGG(arr))すると、”Aggregate function ARRAY_CONCAT_AGG not allowed in UNNEST” というエラーが出る ( select count(distinct v) from unnest( array_concat_agg(array(select 購買者 from unnest(購買履歴))) ) ) # UNNEST() を含む処理をUDFに切り出せば、エラーを起こさずにクエリが通る create function distinctCount(arr) as ( (select count(distinct v) from unnest(arr) as v) ); countDistinct(array_concat_agg(array(select 購買者 from unnest(購買履歴))) UNNEST() の中で ARRAY_CONCAT_AGG() を使うことはできない。 一方で、UNNEST() を含む処理を UDF に切り出すと、エラーを回避できる。
26. 26 結果
27. 27 従来の課題が解決 結果 サマリーテーブル上で指標を計算せず、分析時に ARRAY から計算することで、従来の課題を解決できる。 課題 具体例 粒度の変更が難しい 月ごとの「ユニークユーザー数」や「平均単価」といった指標を、後から四半期ごとや年ごと にすることはできない。サマリーテーブルの再集計が必要になる。 指標の追加が難しい 「売上」から、特定カテゴリーに絞った「売上」を計算することはできない。ログテーブルか ら指標「特定カテゴリーの売上」を集計し、サマリーテーブルに追加する必要がある。 深掘りが出来ない 指標「購買者数」から個別の購買者を掘り下げることはできない。ログテーブルを用いて分析 する必要がある。 複数プロダクトに またがった指標を 定義できない 指標「プロダクトA,B両方でその店から購入した人数」を、サマリーテーブルにある指標「Aで 店から購入した人数」と「Bで店から購入した人数」から計算することはできない。ログテーブ ルの段階で計算しておく必要がある。 再掲：従来の課題
28. 28 # 「店×月」ごとの集計 select 集計月, 店, distinctCount(array_concat_agg(select 購買者 from unnest(購買履歴))) as 購買者数 from table group by 集計月, 店 # 「店×四半期」ごとの集計 select date_trunc(集計月, quarter) as 集計四半期, # 集計粒度を変えても、指標は正しく集計される 店, distinctCount(array_concat_agg(select 購買者 from unnest(購買履歴))) as 購買者数 from table group by 集計四半期, 店 粒度の変更を行うクエリ 結果 分析時に指標を計算するため、「ユニークユーザー数」や「平均単価」といった非加算型の指標を正しく集計で きるようになった。 例）サマリーテーブルの粒度を「月」から「四半期」に変更する
29. 29 ARRAY から必要な情報を抜き出すことで、後から指標の定義を柔軟に調整することができる。 これにより、サマリーテーブルを作り直す必要がなくなった。 例）「購買者数」に加えて、「男性の購買者数」を追加する # 男性ユーザーに絞ってユニークユーザー数を計算するUDFを作成 create function countUniqueMaleUsers(user_array) as select count(distinct ユーザーID) from unnest(user_array)) as ユーザーID inner join (select ユーザーID from ユーザーテーブル where 性別 = “男性”) using(ユーザーID) ) ); select 集計月, 店, distinctCount(array_concat_agg(select 購買者 from unnest(購買履歴))) as 購買者数, # 探索的に、後から指標を追加できる countUniqueMaleUsers(array_concat_agg(array(select 購買者 from unnest(購買履歴)))) as 男性の購買者数, from table group by 集計月, 店 指標の追加を行うクエリ 結果
30. 30 ARRAY から必要な情報を抜き出すことで、後から個別のユーザーに深掘りをすることができるようになった。 例）購買額が多いトップ3のユーザーを確認する 深掘りを行うクエリ 結果 # 購買額が多いトップ3のユーザーを計算するクエリ create or replace function top3Users(arr ARRAY<STRUCT<購買者 STRING, 購買額 INT64>>) returns string as ( array_to_string( array( select 購買者 from unnest(arr) group by 購買者 order by sum(購買額) desc # 購買額が大きいトップ3のユーザーに絞り込み limit 3 ), ",") ); select 集計月, 店, top3Users(array_concat_agg(購買履歴)) as 購買額が大きいトップ3のユーザー # 個別のユーザーを確認することもできる from table group by 1
31. 31 select 集計月, 店, distinctCount( array_concat_agg( array( select 購買者 # プロダクトAとBどちらにも含まれる購買者に絞り込み from (select distinct 購買者 from unnest(プロダクトAの購買履歴)) inner join (select distinct 購買者 from unnest(プロダクトBの購買履歴)) using(購買者) ) ) ) as AとB両方の購買者数 from table group by 集計月, 店 複数プロダクトにまたがった指標を追加するクエリ 結果 それぞれのプロダクトの購買履歴を格納した ARRAY を用いることで、プロダクトをまたいだ指標を定義できるよ うになった。 例）プロダクトAとプロダクトBの両方で、同じ月に同じ店から購買している購買者数を計算する
32. 32 複雑なことをやりすぎると、クエリのパフォーマンスが悪化する。 • ARRAY に格納するデータが多すぎる • ユーザー数が多すぎる • テーブルの粒度を細かくしすぎる 今のところ、数千万〜数億行まではクエリが実行可能。 課題：クエリパフォーマンスが低い 結果
33. 33 まとめ
34. 34 まとめ まとめ • 「事前集計しないサマリーテーブル」では、集計粒度より細かいデータを全て ARRAY に格納する ◦ プロダクトごとのサマリーテーブルの集計粒度が揃うため、ドリルアクロスが出来る ◦ 指標を事前に集計しないため、後から指標を増やしたり改修したりするのが簡単 • データウェアハウスの処理性能が上がり、半構造化データのサポートが充実したことで、実装の選択肢と して検討できるようになった • 分析のSQLクエリが複雑になるが、Looker などのBIツールと併用することで複雑なSQLを隠蔽できる
35. 35 感想 まとめ • ARRAY を扱えるほどSQLに習熟している人には、今回の分析用テーブルは不要 ◦ 自力でドリルアクロスのクエリが書けるから • SQLに習熟していない人に、プロダクトを横断した高度な分析を行なってもらいたい場合には有効 • BigQuery の性能の高さに驚いた。数千万行〜数億行に及ぶ ARRAY の計算を数秒で処理できる