Bigqueryを基盤としたCDP環境の構築

Transcript

1. Bigqueryを基盤とした Customer Data Platformの構築と マーケティング施策の活用事例 2019/9/27 (Fri) #bq_sushi #11 マーケティングでも使える？
2. アジェンダ
 1. 自己紹介 2. Customer Data Platformの認識合わせ 3. プロジェクト説明 3.1. アーキテクチャ 3.2. データ処理 3.3. 活用 4. 振り返り
3. 自己紹介
 名前と所属
 ・岩井晋太郎( @hogeta_)
 ・株式会社オプト
 
 仕事
 ・最近はデータアーキテクト的なお仕事 
 ・基本はコード書いたり、SQL職人やったり、マーケっぽいことやったりBQお悩み解決屋さんしてる。 
 
 Bigqueryとのお付き合い 
 ・かれこれ3年目
 ・好きな関数はarray_agg 
 ・Scriptingのリリースを心待ちにしている 

4. Customer Data Platformとは

5. Copyright © 2019 OPT Inc. All Rights Reserved. 
 DMPとCDP　
 DMP Data Management Platform CDP Customer Data Platform Cookie、IDFAベース
 （デジタル上の識別子） ユーザIDベース 
 （存在する一意の顧客キー） セグメント中心設計 個人プロファイル中心設計
 デジタル上で取得可能な情報 顧客理解に関連する全ての情報 広告のターゲティング精度向上 リアル店舗も含めたオールチャネル 設計思想 ID統合 取得情報 活用先 3rdパーティ中心 1stパーティ中心
 収集データ
6. Copyright © 2019 OPT Inc. All Rights Reserved. 
 DMPとCDP　データの見え方の違いのイメージ
 IDFA aaaaa-bbbb-cccc どこかのだれか
 年齢　30-35歳 or 45-50歳　 
 居住地　関東　恐らく男性 
 既婚者、3-8歳の子供がいる 
 高校生 or 社会人
 利用App　ニュース/マネー/ゲーム 
 興味関心　マーケティング/筋トレ 
 趣味嗜好　スポーツ/読書/テック 
 デバイス　iPhone
 会員番号　1001 yamada taro
 生年月日　1979/1/1 
 住所　東京都xx市xxx丁目 
 会員ランク　　 シルバー 
 最近買ったもの プロテイン 
 最終購買日時　 2018/11/20 Eメール開封日 2018/12/10 
 店舗来店日　 2019/04/01 
 デバイス　　　 iPhone、iMac CDP DMP ※あくまでイメージ (個人情報にあたるデー タは取得していません)
7. Copyright © 2015 OPT Inc. All Rights Reserved. Copyright © 2018 OPT Inc. All Rights Reserved. 
 プロジェクト概要

8. Copyright © 2019 OPT Inc. All Rights Reserved. 
 ・Webの広告代理店 ・クライアントのデジタルシフト支援に力を入れるなど 広告以外のマーケティング領域にも注力 ・以下のツールを内省している(どちらもBQを採用している) webの広告効果計測ツール アプリSDK(adjust,appsflyer等) のログデータを貯めるDMP 登場人物
 OPT クライアント ・有店舗事業者様 ・基盤データはオンプレ環境に構築
9. Copyright © 2019 OPT Inc. All Rights Reserved. 
 シナリオ
 実現したいこと ・Web,App,店舗の全てのチャネルを横断しユーザー行動を分析でき、適切なコミュニケーションを取りたい ・高速でPDCAを回せるようにしたい アプローチ ・サイロ化された Web/App/店舗/施策 のデータを全てBigqueryに集約する ・チャネルを跨いだユーザーの分析ができるテーブルを作る
10. Copyright © 2019 OPT Inc. All Rights Reserved. 
 状況
 web ・ECサイト ・ADPLANを導入 店舗 ・いろんなところにある ・会員サービス有り app ・ポータルサイト(のようなもの) ・計測SDKはadjustを採用 ・SpinAppを導入 施策 ・Salesforce Marketing Cloudを採用 ・push通知/メール配信を実施
11. Copyright © 2019 OPT Inc. All Rights Reserved. 
 ここからシステム的な話をしますが、一部実装中の部分もあります。
 あらかじめご了承ください
 テーマは「可能な限りノンプログラミングで実装」

12. Copyright © 2019 OPT Inc. All Rights Reserved. 
 Data Lake アーキテクチャ
 App Engine Cloud Pub/Sub Cloud Dataflow BigQuery Compute Engine Cloud Storage BigQuery Compute Engine Cloud Storage Cloud Functions BigQuery BigQuery App 省略(AWS使ってます) DWH Data Mart Data Pipeline App Web CL Data 施策 BigQuery BigQuery BigQuery BigQuery BigQuery BigQuery BigQuery BigQuery CDP Mart BigQuery
13. Copyright © 2019 OPT Inc. All Rights Reserved. 
 DataLake ・構造化されたログが入っている ・DataPipelineがETLの役割を担っているので非構造化データではない ・データソースに理解がある人間であればアドホックな分析が可能 DWH ・アドホックな分析をするためのデータ ・DataLakeにマスタをjoinしたり、不要なカラムを落としたり、簡単な整形をしている ・データソースへの理解が乏しくてもある程度分析できる DataMart ・目的に応じて集計されたデータ ・SQLが書けないマーケターが BIツールやエクセルで分析ができる状態 DataLake/DWH/Datamartの役割

14. Copyright © 2019 OPT Inc. All Rights Reserved. 
 Appデータ処理
 (webはほぼ同じなので省略)

15. Copyright © 2019 OPT Inc. All Rights Reserved. 
 【App】Data Lake → DWH → Data Mart 
 Data Lake id mapping table ログインID adid idfa aa_id xx [aa,bb] [111] [222] DWH User Status adid インストール日 最終セッション 日 セッション 回数 平均滞在 時間 aa 2019/1/3 2019/8/1 51 3分 ログインID イベント日 イベント 商品 xx 2019/8/1 お気に入り追加 プロテイン adid イベント日 イベント ログインID 商品id idfa aa 2019/8/1 お気に入り追 加 xx 11 111 Data Mart ❶ schedule query ❷ schedule query ❸schedule query ❹ view ログインID インストール日 最終セッション 日 セッション 回数 平均滞在 時間 xx 2019/1/3 2019/8/1 51 5分
16. Copyright © 2019 OPT Inc. All Rights Reserved. 
 ❶ Data Lake からschedule queryで ID Mapping Tableを作成 
 
 ・login_idとそれに紐づくID(idfaやcookieなど)をマッピングしたテーブル
 → イベントログをlogin_id単位で見れるようにするために作成
 
 ・前日分だけの集計値とすでにある集計済みテーブルをfull joinしていくような処理をする。
 → adidは配列にアペンド, 広告IDはadidに対しての最新値を配列にアペンドするようにする
 ※ adidはadjustが発番しているIDで端末ユニークになる
 
 
 【App】Data Lake → DWH → Data Mart
 login_id adid aa [aaa,bbb] 処理前日までの状態 login_id adid aa [bbb,ccc] 処理当日のデータを集計 login_id bf_adid af_adid aa [aaa,bbb] [bbb,ccc] full join login_id adid aa [aaa,bbb,ccc] 2つの配列をくっつけてユニークにする。 以下サンプル (※ どちらかの配列が nullの場合はエラーになるのでケアが必要 ) SELECT login_id, (SELECT ARRAY_AGG(DISTINCT adid_array IGNORE NULLS) FROM UNNEST(ARRAY_CONCAT(bf_adid, af_adid)) adid_array) adid FROM table 処理のイメージ
17. Copyright © 2019 OPT Inc. All Rights Reserved. 
 ❷ Data Lake からschedule queryで User Status Tableを作成 
 ・App/Web単体での分析用に作成(loginしていないユーザーもいるため)
 ・デイリーで更新
 
 
 
 
 
 【App】Data Lake → DWH → Data Mart
 ❸ ID Mapping TableとData Lakeを掛け合わせてlogin_idをキーとしたログテーブル(DWH)を作成 
 ・DataLakeのadidに対応するlogin_idをID Mapping Tableから引き直す
 ・マスターデータをjoinし、対応するidを名称に直す
 
 
 
 
 
 ❹ DataMart(チャネル別) をviewテーブルで作成 
 ・DWHから用途に応じたDataMartを作成
 → 例) 商品閲覧履歴マート、デモグラマート、アクションマート
 ※ DataMartの定義が完全に決まりきっていないのでviewテーブルにしている
 
 
 
 

18. Copyright © 2019 OPT Inc. All Rights Reserved. 
 Clientデータ処理
 (一部のみ)

19. Copyright © 2019 OPT Inc. All Rights Reserved. 
 【CL Data】Data Pipeline → Data Lake → DWH → DataMart
 ログインID 購買日 商品ID 購入金額 購入数 aa 2019/8/1 11 8000 2 ログインID リーセンシー LTV フリークエンシー 最新購入商品 初回購入日 aa 2019/8/1 8000 2 プロテイン 2017/2/1 Data Lake Data Mart ログインID 購買日 商品名 購入金額 購入数 aa 2019/8/1 プロテイン 8000 2 DWH 商品ID 商品名 11 プロテイン view view 商品マスタ
20. Copyright © 2019 OPT Inc. All Rights Reserved. 
 【CL Data】Data Pipeline → Data Lake → DWH → DataMart
 ・ユーザー識別子はログインID 
 
 ・POSデータとマスターデータを連携しているのでそれをjoinしたviewテーブルを作成 
 (これだけ書くと簡単ですが、様々な条件があり実際はかなり複雑) 
 
 ・スパゲッティSQLすぎて誰がレビューできるんだろう状態(書いた自分も分からなくなる) 
 
 ・分析用途でのDWHをいくつも作成している 
 
 ・DataMartもviewで作成

21. Copyright © 2019 OPT Inc. All Rights Reserved. 
 施策データ処理

22. Copyright © 2019 OPT Inc. All Rights Reserved. 
 【施策】Data Pipeline →Data Lake → DWH → DataMart
 ・ユーザー識別子はログインID 
 ・BigqueryをELT的に使い、DataLakeの表記ゆれを直してDWH化 
 ・単純なgroup byをしたviewテーブルを作るだけ(これは秒殺) 
 ・SalesForceのデータは数GBある zipファイルなのでGCE上で解凍した上で 
 　GCSにあげないといけないのがちょっと辛い 
 ログインID 送信日 開封日 メッセージ aa 8/1/2019 8/2/2019 クーポン配布！ ログインID 送信数 開封数 最新開封日 aa 2 1 2019/8/2 Data Lake Data Mart ログインID 送信日 開封日 メッセージ aa 2019/8/1 2019/8/2 8000 DWH view view
23. Copyright © 2019 OPT Inc. All Rights Reserved. 
 CDP Mart

24. Copyright © 2019 OPT Inc. All Rights Reserved. 
 CDP Mart
 ログインID 送信数 開封数 最新開封日 aa 2 1 2019/8/2 施策 Data Mart ログ イン ID リーセ ンシー LTV フリークエ ンシー 最新購入 商品 初回購入 日 aa 2019/ 8/1 8000 2 プロテイ ン 2017/2/1 CL Data Mart web/app Data Mart ログインID インストール 日 最終セッショ ン日 セッション 回数 平均滞在 時間 xx 2019/1/3 2019/8/1 51 5分 ログインID トータルLTV トータルセッ ション数 セッション 回数 最新接触面 xx 12000 82 51 App DataMartを統合してチャネルを横断したCDP Martを作成する。 
 CDP Mart
25. Copyright © 2019 OPT Inc. All Rights Reserved. 
 DataMartの活用

26. Copyright © 2019 OPT Inc. All Rights Reserved. 
 DataMartの活用例
 DataMartにTableauを[抽出]にてつなぐ。 DataMart自体はlogin_idベースでの情報なので、それをいい感じにサマってダッシュボード化 リストをSalesforceに送る機能をSpinAppで開発した。 例) ・全ての面でのRFMランクを出しシナリオ配信 DataMartからID Mapping Tableを使ってIDFA/AAIDを引き、広告配信に活用。 例) ・ロイヤルユーザーの Look a like 配信 ・全ての面で直近1ヶ月接触がないユーザーへのリタゲ 可視化 CRM Ads
27. Copyright © 2015 OPT Inc. All Rights Reserved. Copyright © 2018 OPT Inc. All Rights Reserved. 
 振り返り

28. Copyright © 2019 OPT Inc. All Rights Reserved. 
 SQLのレビューでGoogle Colaboratory を使った
 やってよかったこと①
 Client DataのDWHはかなり入り組んでおり説明・理解が辛い状態。 Google Colaboratoryを使って、途中の処理を可視化しながら書き記すことで SQLが分からないビジネスメンバーへの説明も容易になった。 途中処理の描画 処理の説明 SQL を並べて表示できるのでレビュワーには 分かりやすく、SQLが分からない ビジネスサイドもある程度処理の流れを把握できる
29. Copyright © 2019 OPT Inc. All Rights Reserved. 
 やってよかったこと②
 ClientDataの受け取りで GCS to Cloud Function を採用した
 クライアントにはGCSにファイルをあげてもらうだけなのでかなり楽。 エンジニアリソースがかなり少なかったのでデータ授受の処理が数十行のコードで済むのはかなり助かった。しかも タダ ※事前にデータ定義を確認してテーブルを作る必要はある
30. Copyright © 2019 OPT Inc. All Rights Reserved. 
 やってよかったこと③
 要所でのschedule queryの採用 
 UIでバッチ的に処理できるのは驚異的な便利さ。 依存関係の担保はできないけど実行時間の指定である程度担保はできる。 (増えてきたら管理が大変そう。。。 )
31. Copyright © 2019 OPT Inc. All Rights Reserved. 
 DataMartはシャーディングテーブル を採用し過去との比較ができるようにしたい。 
 
 viewを採用したのはDataMartの定義がまだ完全に固まっておらず変更容易性を優先したため。 
 viewはDWH(DWHがviewの場合はDatalake)を見にいくので課金はかかるしクエリが遅い。 
 
 シャーディングテーブルで日毎にユーザーのステータスを管理すれば過去との比較が容易になるので 
 分析の幅が広がる（はず） 
 ※これもSchedule Queryで実装するつもりだったが、検証中にテーブル名にワイルドカードが入るとエラーになると いうバグを踏んでしまった( https://issuetracker.google.com/issues/139907833 )
 
 
 
 今後やりたいこと①
 login_id LTV Frequency Recency xx 5000 15 2019/7/1 login_id LTV Frequency Recency xx 15000 32 2019/9/15 datamart_20190815 datamart_20190915 1ヶ月でLTVが3倍になっ てる！
32. Copyright © 2019 OPT Inc. All Rights Reserved. 
 ・viewを使うと依存関係をある程度考慮しなくていいので楽だが、課金やレスポンスの遅さは気になる。 
 (今後データが増えてくると顕著に影響が出てくるはず)。 
 Cloud Composerなどの ワークフローエンジンを使って依存関係のある処理を逐次処理する仕組みに変えたい。 つ いでにcluster table化したい 
 
 ※viewであまりにも重い処理をすると Resources exceeded が出るかもしれないので注意。 
 (前は問題なく出てたけど、データ量が増えたらリソース枯渇とかあるかもしれない。) 
 今後やりたいこと②
 全てのviewを廃止したい　 
 
 
 

33. Copyright © 2019 OPT Inc. All Rights Reserved. 
 で、Bigqueryはマーケティングに使えるの？
 ① データ貯めるのが楽
 サーバレスなのでエンジニアいらずで始められる。 
 FirebaseやGA360ならボタンぽちぽちでデータがたまる。 
 GCS & Cloud Functionも 強力だし、開発リソースがあればDataflowとか絡めるとそれはもう素敵。 
 
 ② 処理が信じられないほど速い 
 大容量のデータ処理も高速なので ELT的に使える。
 とりあえず一旦BQにさえデータを入れてしまえばあとはSQL力しだいで何とかなる。 
 個人的にはデータマーケティングには必須なツールだと思う Bigqueryは飽くまでもデータを貯め処理する仕組みであり それ自体がマーケティングに直接的に役立つわけではない。 データをどうマーケティングに活かすか試行錯誤できるのであれば、 Bigqueryは大きな助けになると思う
34. Copyright © 2019 OPT Inc. All Rights Reserved. 
 ご静聴ありがとうございました