データをコネコネ！メール配信用データ生成の仕組み

- 4月の登壇資料
https://speakerdeck.com/kappezoro/gorigori-bigquery

- テックブログ

楽々スケール Digdag on GKE Autopilot の紹介とその運用Tips
https://techblog.zozo.com/entry/digdag-on-gke-autopilot

オンプレDWHをBigQueryに移行した話
https://techblog.zozo.com/entry/migration-on-premise-dwh-etl-to-bigquery-digdag

リアルタイムマーケティングシステムの紹介とそのリプレイス計画
https://techblog.zozo.com/entry/real-time-marketing-system

- 採用募集リンク

マーケティングオートメーション(SRE)
https://hrmos.co/pages/zozotech/jobs/0000196

マーケティングオートメーション(バックエンド)
https://hrmos.co/pages/zozotech/jobs/0000197

Transcript

1. データをコネコネ！
   メール配信用データ生成の仕組み
   株式会社ZOZO

   技術本部 MA部 MA施策・運用改善ブロック

   

   辻岡 温子

   Copyright © ZOZO, Inc.
   2022/08/03 Data Engineering Study #15「Reverse ETL 特集回」
   

   View Slide

2. © ZOZO, Inc.
   今日話すこと

   2
   ● 自己紹介・会社紹介
   ● メール配信までの流れ
   ○ コネコネ①：BigQuery → ファイル(EMail SaaS)
   ○ コネコネ②：PostgreSQL → BigQuery → PostgreSQL
   ○ コネコネ③：BigQuery → 長く永いクエリ → BigQuery
   ● 関連ブログ紹介・採用募集
   

   View Slide

3. © ZOZO, Inc.
   株式会社ZOZO

   技術本部 MA部 MA施策・運用改善ブロック
   辻岡 温子

   2020年5月に入社
   MA(マーケティングオートメーション)に関わる
   メルマガ・Push配信等に必要なデータ抽出及び
   配信システムの運用・開発を行っているバックエンドエンジニア
   

   3
   

   View Slide

4. © ZOZO, Inc.
   https://zozo.jp/

   4
   ● ファッションEC

   ● 1,500以上のショップ、8,400以上のブランドの取り扱い

   ● 常時90万点以上の商品アイテム数と毎日平均2,600点以上の新着 商
   品を掲載（2022年3月末時点）

   ● ブランド古着のファッションゾーン「ZOZOUSED」や

   コスメ専門モール「ZOZOCOSME」、靴の専門モール

   「ZOZOSHOES」、ラグジュアリー＆デザイナーズゾーン

   「ZOZOVILLA」を展開

   ● 即日配送サービス

   ● ギフトラッピングサービス

   ● ツケ払い など

   

   View Slide

5. © ZOZO, Inc.
   https://wear.jp/

   5
   ● ファッションコーディネートアプリ

   ● 1,600万ダウンロード突破、コーディネート投稿総数は1,200万件以上
   （2022年3月末時点）

   ● ピックアップタグから最新のトレンドをチェック

   ● コーディネート着用アイテムを公式サイトで購入可能

   ● WEAR公認の人気ユーザーをWEARISTAと認定。モデル・タレント・デザ
   イナー・インフルエンサーといった各界著名人も参加

   

   View Slide

6. © ZOZO, Inc.
   6
   https://zozo.jp/zozoglass/

   ● 自宅で簡単・高精度にご自身の顔の肌の色を計測できる
   フェイスカラー計測ツール
   ● ECにおけるコスメ購入時の課題であった「色選び」に関する
   不安や悩みを解消
   ● 肌の色を構成する成分、ヘモグロビン量とメラニン量を画像
   から推定
   ● コスメ専門モール「ZOZOCOSME」で取り扱うベースメイク
   の一部に対応
   ● 計測者数110万人を突破（2022年1月末時点）
   

   View Slide

7. © ZOZO, Inc.
   7
   ● 連携先SaaS都合でデータ抽出後ファイルを分割作成&並列処理したい
   ● DBのデータ量x長クエリで実行遅いのなんとかしたい
   ● クエリコピペ量産してて運用しづらいのなんとかしたい
   こんな人におすすめ
   

   View Slide

8. © ZOZO, Inc.
   8
   https://speakerdeck.com/kappezoro/gorigori-bigquery
   

   View Slide

9. © ZOZO, Inc.
   9
   https://speakerdeck.com/kappezoro/gorigori-bigquery
   今日はメール配信をピックアップします！（キリッ）
   

   View Slide

10. © ZOZO, Inc.
    10
    メール配信までの流れ
    

    View Slide

11. © ZOZO, Inc.
    11
    ● マス配信
    ○ 定期実行または配信施策に応じてスケジュール・配信
    ● パーソナライズ配信
    ○ 特定条件の対象者に対して時間やチャネルを最適化した上で配信
    MAのメール配信システムの種類について
    

    View Slide

12. © ZOZO, Inc.
    12
    メール配信までの流れ
    

    View Slide

13. © ZOZO, Inc.
    13
    メール配信までの流れ
    ①
    ③
    ②
    

    View Slide

14. © ZOZO, Inc.
    14
    コネコネ①：BigQuery → ファイル(EMail SaaS)
    

    View Slide

15. © ZOZO, Inc.
    15
    コネコネ①：BigQuery → ファイル(EMail SaaS)
    

    View Slide

16. © ZOZO, Inc.
    16
    SaaS用にデータ抽出→csv生成→SaaSへ連携
    ※マスとパーソナライズは別アプリのため分かれていますが、ここの基本工程は一緒です。
    

    View Slide

17. © ZOZO, Inc.
    m17
    パーソナライズメール配信
    ● 最適化配信のため、一度の配信に対するデータ量は少なめ
    ○ →SaaS連携については特に課題なし。対して、マスは制約との戦いが.... →次のページへ
    ● PostgreSQLで悲鳴!?
    ○ → コネコネ②で詳しく話します。
    1施策ごとに1csvファイル生成
    

    View Slide

18. © ZOZO, Inc.
    18
    マスメール配信について
    ● 配信ボリュームが多いため
    ○ 1施策1csvだとSaaSのファイルサイズ規定上限超過
    ○ ファイル分割配信x直列だとSLA超過
    ● 並列実行するとして
    ○ 配信時のみスケールアウトしたい
    ○ 配信時のみリソース増やしたい
    

    View Slide

19. © ZOZO, Inc.
    19
    マスメール配信について
    ● 配信ボリュームが多いため
    ○ 1施策1csvだとSaaSのファイルサイズ規定上限超過
    ○ ファイル分割配信x直列だとSLA超過
    ● 並列実行するとして
    ○ 配信時のみスケールアウトしたい
    ○ 配信時のみリソース増やしたい
    そこでDigdag On GKE
    

    View Slide

20. © ZOZO, Inc.
    20
    Digdag on GKE Autopilot
    楽々スケール Digdag on GKE Autopilot の紹介とその運用Tips

    ↓↓詳しくはこちらのテックブログに載ってます！↓↓
    ● podが実行taskごとに立ち上がって実行
    →並列で動かしても他の実行taskが影響を受けない！
    ● digファイルでtaskごとのリソース設定可能
    →重いtaskだけリソース上げればいい
    →コスト最適化！
    リソース設定これだけ！
    弊社社員からDigdag本家へContributeも...
    https://techblog.zozo.com/entry/digdag-on-gke-autopilot
    

    View Slide

21. © ZOZO, Inc.
    21
    マスメール配信
    SaaS用の分割ファイル生成&配信リクエストを並列実行！SaaSの制約もSLAもクリア！
    

    View Slide

22. © ZOZO, Inc.
    22
    まとめ
    SaaSの制約に困った...
    ● 並列処理したい
    ● リソース最適化したい
    →Digdag on GKEが解決！お試しあれ。
    

    View Slide

23. © ZOZO, Inc.
    23
    コネコネ②：PostgreSQL → BigQuery → PostgreSQL
    

    View Slide

24. © ZOZO, Inc.
    24
    コネコネ②：PostgreSQL → BigQuery → PostgreSQL
    

    View Slide

25. © ZOZO, Inc.
    25
    1. BigQuery → PostgreSQLへ必要データを連携
    2. PostgreSQL と SQL Serverデータからデータ抽出し配信用データ生成
    PostgreSQL:
    - 前処理で各キャンペーンの配信対象者リストを格納
    - パーソナライズ配信実績を格納
    - アプリケーションで利用する各種実績集計値を格納
    SQLServer:
    - 商品情報、対象者の詳細情報等
    

    View Slide

26. © ZOZO, Inc.
    26
    1. BigQuery → PostgreSQLへ必要データを連携
    2. PostgreSQL と SQL Serverデータからデータ抽出し配信用データ生成
    PostgreSQL:
    - 前処理で各キャンペーンの配信対象者リストを格納
    - パーソナライズ配信実績を格納
    - アプリケーションで利用する各種実績集計値を格納
    SQLServer:
    - 商品情報、対象者の詳細情報等
    ここで問題発生
    

    View Slide

27. © ZOZO, Inc.
    27
    実績を使ったPostgreSQLのクエリ
    PostgreSQLの実績・集計値のデータ量 x クエリの複雑化
    → チューニングしてもクエリが終わらない><
    

    View Slide

28. © ZOZO, Inc.
    28
    実績を使ったPostgreSQLのクエリ
    PostgreSQLの実績・集計値のデータ量 x クエリの複雑化
    → チューニングしてもクエリが終わらない><
    そこでBigQuery
    

    View Slide

29. © ZOZO, Inc.
    29
    BigQueryにクエリを任せる
    1. PostgreSQLのデータをBigQueryに渡す
    2. BigQueryでクエリ実行した結果を
    PostgreSQLに渡す
    1. 2.
    適材適所！
    

    View Slide

30. © ZOZO, Inc.
    30
    まとめ
    データ量が多いxチューニングしても終わらないクエリ
    → BigQueryに任せてみよう。
    データの受け渡しはDigdag x embulkで楽々連携
    

    View Slide

31. © ZOZO, Inc.
    31
    コネコネ③：BigQuery → 長く永いクエリ → BigQuery
    

    View Slide

32. © ZOZO, Inc.
    32
    コネコネ③：BigQuery → 長く永いクエリ → BigQuery
    

    View Slide

33. © ZOZO, Inc.
    33
    半年前までの姿
    ↓↓詳しくはこちらのテックブログに載ってます！↓↓
    オンプレDWHをBigQueryに移行した話
    https://techblog.zozo.com/entry/migration-on-premise-dwh-etl-to-bigquery-digdag
    

    View Slide

34. © ZOZO, Inc.
    34
    積年の課題
    10年近くクエリにビジネスロジックを積み上げて運用していた。
    その結果、複雑かつ類似クエリが多く実績を残し生き残っている。
    メルマガ全体の変更等が走った場合に改修コストが高くなり
    ビジネスロジックの可読性が下がっていた。
    

    View Slide

35. © ZOZO, Inc.
    35
    積年の課題
    10年近くクエリにビジネスロジックを積み上げて運用していた。
    その結果、複雑かつ類似クエリが多く実績を残し生き残っている。
    メルマガ全体の変更等が走った場合に改修コストが高くなり
    ビジネスロジックの可読性が下がっていた。
    そこでクエリリファクタリング
    

    View Slide

36. © ZOZO, Inc.
    なんか似ているけど所々違うクエリが散見される

    36
    似ているクエリになるケース
    ● 掲載用のデータ加工
    ● 掲載用のよくやる絞込み・除外条件
    ● 掲載商品の順位付け
    ● 順位付け用のデータ加工・絞込み・順位付け
    

    View Slide

37. © ZOZO, Inc.
    クエリを部品化・ファイル分割

    37
    実績
    ● クエリ総行数15万行 → 3.5万行 まで減少
    

    View Slide

38. © ZOZO, Inc.
    部品化した抽出処理の流れ

    38
    SQL_A.dig SQL_B.dig
    

    View Slide

39. © ZOZO, Inc.
    39
    b
    a
    部品化
    SQL_B.sql
    SQL_A.sql
    A_original B_original
    

    View Slide

40. © ZOZO, Inc.
    40
    クエリの冗長化を防げる、中間データを確認しやすい、too complex….. の不安解消
    メリットは？
    クエリの冗長化を防ぐメリットってなんなの？
    長く運用すれば共通ロジックの変更が発生する
    後の修正コストを下げるメリットあり
    クエリをリファクタすれば後々の効率upにつながる
    ※PDCAが落ち着いてきた頃がオススメ
    クエリ作っても継続的に使われるかわからないじゃん
    継続利用されるまで(結果が出るまで)はリファクタは借金してもよい
    それほど使われなくなるクエリは多く出る
    最初からやっとくと追加改善はしやすくなる※個人の意見です
    

    View Slide

41. © ZOZO, Inc.
    41
    まとめ
    ボリュームが大きく、利用期間も長いクエリが増えてきた。
    運用効率化を考えないと回らなくなってきた。
    → 部品化して整理しましょ
    

    View Slide

42. © ZOZO, Inc.
    42
    関連ブログ紹介・採用募集
    

    View Slide

43. © ZOZO, Inc.
    43
    GKE x Digdagの仕組
    みもっと知りたい
    

    View Slide

44. © ZOZO, Inc.
    44
    https://techblog.zozo.com/entry/digdag-on-gke-autopilot
    楽々スケール Digdag on GKE Autopilot の紹介とその運用Tips
    

    View Slide

45. © ZOZO, Inc.
    45
    オンプレDWHからBigQueryに移
    行した話もっと知りたい
    

    View Slide

46. © ZOZO, Inc.
    46
    https://techblog.zozo.com/entry/migration-on-premise-dwh-etl-to-bigquery-digdag
    オンプレDWHをBigQueryに移行した話
    

    View Slide

47. © ZOZO, Inc.
    47
    パーソナライズ配信についてもっと
    知りたい
    

    View Slide

48. © ZOZO, Inc.
    48
    https://techblog.zozo.com/entry/real-time-marketing-system
    リアルタイムマーケティングシステムの紹介とそのリプレイス計画
    

    View Slide

49. © ZOZO, Inc.
    49
    https://hrmos.co/pages/zozotech/jobs/0000197
    https://hrmos.co/pages/zozotech/jobs/0000196
    マーケティングオートメーション(SRE)
    マーケティングオートメーション(バックエンド)
    ご応募
    お待ちしてます！
    

    View Slide

50. View Slide