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

Slide 1

Slide 1 text

Bigqueryを基盤とした Customer Data Platformの構築とマーケティング施策の活用事例 2019/9/27 (Fri) #bq_sushi #11 マーケティングでも使える？

Slide 2

Slide 2 text

アジェンダ  1. 自己紹介 2. Customer Data Platformの認識合わせ 3. プロジェクト説明 3.1. アーキテクチャ 3.2. データ処理 3.3. 活用 4. 振り返り

Slide 3

Slide 3 text

自己紹介  名前と所属  ・岩井晋太郎( @hogeta_)  ・株式会社オプト    仕事  ・最近はデータアーキテクト的なお仕事   ・基本はコード書いたり、SQL職人やったり、マーケっぽいことやったりBQお悩み解決屋さんしてる。     Bigqueryとのお付き合い   ・かれこれ3年目  ・好きな関数はarray_agg   ・Scriptingのリリースを心待ちにしている  

Slide 4

Slide 4 text

Customer Data Platformとは 

Slide 5

Slide 5 text

Copyright © 2019 OPT Inc. All Rights Reserved.   DMPとCDP　  DMP Data Management Platform CDP Customer Data Platform Cookie、IDFAベース  （デジタル上の識別子）ユーザIDベース   （存在する一意の顧客キー）セグメント中心設計個人プロファイル中心設計  デジタル上で取得可能な情報顧客理解に関連する全ての情報広告のターゲティング精度向上リアル店舗も含めたオールチャネル設計思想 ID統合取得情報活用先 3rdパーティ中心 1stパーティ中心  収集データ

Slide 6

Slide 6 text

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 ※あくまでイメージ (個人情報にあたるデータは取得していません)

Slide 7

Slide 7 text

Slide 8

Slide 8 text

Copyright © 2019 OPT Inc. All Rights Reserved.   ・Webの広告代理店・クライアントのデジタルシフト支援に力を入れるなど広告以外のマーケティング領域にも注力・以下のツールを内省している(どちらもBQを採用している) webの広告効果計測ツールアプリSDK(adjust,appsflyer等) のログデータを貯めるDMP 登場人物  OPT クライアント・有店舗事業者様・基盤データはオンプレ環境に構築

Slide 9

Slide 9 text

Copyright © 2019 OPT Inc. All Rights Reserved.   シナリオ  実現したいこと・Web,App,店舗の全てのチャネルを横断しユーザー行動を分析でき、適切なコミュニケーションを取りたい・高速でPDCAを回せるようにしたいアプローチ・サイロ化された Web/App/店舗/施策のデータを全てBigqueryに集約する・チャネルを跨いだユーザーの分析ができるテーブルを作る

Slide 10

Slide 10 text

Slide 11

Slide 11 text

Slide 12

Slide 12 text

Copyright © 2019 OPT Inc. All Rights Reserved.   Data Lake アーキテクチャ  App Engine Cloud Pub/Sub Cloud Dataﬂow 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

Slide 13

Slide 13 text

Copyright © 2019 OPT Inc. All Rights Reserved.   DataLake ・構造化されたログが入っている・DataPipelineがETLの役割を担っているので非構造化データではない・データソースに理解がある人間であればアドホックな分析が可能 DWH ・アドホックな分析をするためのデータ・DataLakeにマスタをjoinしたり、不要なカラムを落としたり、簡単な整形をしている・データソースへの理解が乏しくてもある程度分析できる DataMart ・目的に応じて集計されたデータ・SQLが書けないマーケターが BIツールやエクセルで分析ができる状態 DataLake/DWH/Datamartの役割 

Slide 14

Slide 14 text

Slide 15

Slide 15 text

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分

Slide 16

Slide 16 text

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 処理のイメージ

Slide 17

Slide 17 text

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テーブルにしている         

Slide 18

Slide 18 text

Slide 19

Slide 19 text

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 商品マスタ

Slide 20

Slide 20 text

Copyright © 2019 OPT Inc. All Rights Reserved.   【CL Data】Data Pipeline → Data Lake → DWH → DataMart  ・ユーザー識別子はログインID     ・POSデータとマスターデータを連携しているのでそれをjoinしたviewテーブルを作成   (これだけ書くと簡単ですが、様々な条件があり実際はかなり複雑)     ・スパゲッティSQLすぎて誰がレビューできるんだろう状態(書いた自分も分からなくなる)     ・分析用途でのDWHをいくつも作成している     ・DataMartもviewで作成 

Slide 21

Slide 21 text

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

Slide 28

Slide 28 text

Copyright © 2019 OPT Inc. All Rights Reserved.   SQLのレビューでGoogle Colaboratory を使った  やってよかったこと①  Client DataのDWHはかなり入り組んでおり説明・理解が辛い状態。 Google Colaboratoryを使って、途中の処理を可視化しながら書き記すことで SQLが分からないビジネスメンバーへの説明も容易になった。途中処理の描画処理の説明 SQL を並べて表示できるのでレビュワーには分かりやすく、SQLが分からないビジネスサイドもある程度処理の流れを把握できる

Slide 29

Slide 29 text

Copyright © 2019 OPT Inc. All Rights Reserved.   やってよかったこと②  ClientDataの受け取りで GCS to Cloud Function を採用した  クライアントにはGCSにファイルをあげてもらうだけなのでかなり楽。エンジニアリソースがかなり少なかったのでデータ授受の処理が数十行のコードで済むのはかなり助かった。しかもタダ ※事前にデータ定義を確認してテーブルを作る必要はある

Slide 30

Slide 30 text

Slide 31

Slide 31 text

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倍になってる！

Slide 32

Slide 32 text

Copyright © 2019 OPT Inc. All Rights Reserved.   ・viewを使うと依存関係をある程度考慮しなくていいので楽だが、課金やレスポンスの遅さは気になる。   (今後データが増えてくると顕著に影響が出てくるはず)。   Cloud Composerなどのワークフローエンジンを使って依存関係のある処理を逐次処理する仕組みに変えたい。ついでにcluster table化したい     ※viewであまりにも重い処理をすると Resources exceeded が出るかもしれないので注意。   (前は問題なく出てたけど、データ量が増えたらリソース枯渇とかあるかもしれない。)   今後やりたいこと②  全てのviewを廃止したい　        

Slide 33

Slide 33 text

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