trocco第5回ユーザー会_troccoとAmazon Redshiftで挑んだコンテンツマーケティングの分析基盤構築

Slide 1

Slide 1 text

〜成果集計処理を劇的に改善!〜 troccoとAmazon Redshiftで挑んだコンテンツマーケティングの分析基盤構築株式会社キュービックテクノロジーエキスパートセンター Tech Lead 尾﨑勇太 2023.9.4 開示範囲:公開ドキュメント  1

Slide 2

Slide 2 text

株式会社キュービックとは? 2 株式会社キュービック /CUEBiC Inc. 社名事業設立資本金拠点 2006 年 10 月 24 日 31,000,000円人員約 300 名（単体）※インターンを含む約 484 名（連結) ※2022年3月現在デジタルメディア事業、集客支援事業ほか東京、福岡

Slide 3

Slide 3 text

3 ■これまでの実績創業時より一貫して取り組んできたデジタルメディア事業を軸に、たしかな成長を続けています。ヒト起点のマーケティング×デザインで、高品質なサービスやプロダクトを実現。それによりステークホルダーの皆さまから厚い信頼を獲得し、現在の実績につながっています。デジタルメディア業界のトップランナーとして成長し続ける

Slide 4

Slide 4 text

4 ■デジタルメディア事業ービジネスモデル

Slide 5

Slide 5 text

自己紹介 5 株式会社キュービック Tech Lead/データエンジニア尾﨑勇太（おざきゆうた）覚え方：尾崎豊(おざきゆたか)と一字違い 1990年和歌山県白浜町生まれ生息地：千葉県松戸市スキルセット 1. マネジメント/品質管理/データ分析 2. マイナスからゼロ、ゼロイチ 3. サーバーサイド(WEB/アプリ開発) @waichang111 経歴の詳細はてなブログ

Slide 6

Slide 6 text

セッション内容 6 1.ここで差がつく成果集計 2.Redshift始めました 3.メンバー登録に関して

Slide 7

Slide 7 text

7 troccoの強みってなんだろう？

Slide 8

Slide 8 text

8 1.広告コネクタが豊富 2.RDB/DWHに連携ができる 3.DWHに対して更新ができる

Slide 9

Slide 9 text

9 ■広告のtroccoの活用例各媒体のレポートの内、取得したい項目を丸めて生データとして Redshiftで転送整形、集計の際に必要となる設定値をカスタム変数で設定(trocco APIで動的に更新できるようにする) Tableau 広告費集計+独自分析軸 (メディア,LP別など) ROAS,CVRなどの分析広告媒体の運用調整広告運用者 ① ② ① ② ③ ④

Slide 10

Slide 10 text

10 troccoの弱みってなんだろう？

Slide 11

Slide 11 text

11 1.転送データが引き継げない 2.trocco APIの用途が限定的 3.戻り値が受け取れない

Slide 12

Slide 12 text

1.ここで差がつく成果集計 12

Slide 13

Slide 13 text

13 ■デジタルメディア事業ービジネスモデル売上の着地見込みを正確に早く算出して、日々の施策のPDCAを回せるかが鍵

Slide 14

Slide 14 text

既存のアーキテクチャ 14 広告/ASP アーキテクチャ CUEBiC Analytics RDS Tableau データ抽出/整形/蓄積データ分析データ出力独自の社内集計基盤システムCUEBiC Analyticsに広告と成果のレポートを取り込み集計結果を保存し、Tableau上で事業部のメディア担当者が分析できる状態に出力

Slide 15

Slide 15 text

既存のアーキテクチャのそれぞれの役割 15 広告/成果データの・データ設定・データインポート・データ集計・データエクスポートデータの保持・集計設定データ・広告/成果の生データ・広告/成果の集計データデータの加工・広告データ・データの加工データ分析・集計データの加工・ビジュアライズ CUEBiC Analytics RDS Tableau

Slide 16

Slide 16 text

既存のアーキテクチャ 16 広告/ASP アーキテクチャ CUEBiC Analytics RDS Tableau データ抽出/整形/蓄積データ分析データ出力事業成長に伴い、広告/成果の説明変数が増え、集計方法も複雑化 CUEBiC Analyticsの老朽化し、事業の要求に耐えられなくなってきた・・・・・集計がずれている・出力が遅い・参照されない個別実装が増えスプシの魔改造化・・・

Slide 17

Slide 17 text

既存のアーキテクチャ 17 広告/ASP アーキテクチャ CUEBiC Analytics RDS Tableau データ抽出/整形/蓄積データ分析データ出力 DX戦略としてCUEBiC Analyticsを解体し、ローコード化により業務効率化を測るとともに広告/成果以外のデータの分析軸も取りうる基盤へのリプレイスを実施

Slide 18

Slide 18 text

18 troccoの強みを活かしつつ弱みは代替しよう

Slide 19

Slide 19 text

リニューアル後のデータ分析環境でのそれぞれの役割 19 CUEBiC Analytics 広告データ/成果データ・データ抽出・データ転送・データ整形データの蓄積データの加工データの集計データ分析・集計データの加工・ビジュアライズ集計設定 Tableauのデータを連携・集計設定データ保存・その他マスタ保存 Oasis Tableau

Slide 20

Slide 20 text

実現したアーキテクチャ 20 広告/ASP アーキテクチャデータ抽出集計設定集計ロジックデータ蓄積データ分析 Oasis Tableau Tableau troccoをフロントとして配置してDWHとしてAmazon Redshiftを選定。 troccoの転送設定とデータマート機能により大幅に処理の簡素化に成功

Slide 21

Slide 21 text

実現したアーキテクチャ 21 広告/ASP アーキテクチャデータ抽出集計設定集計ロジックデータ蓄積データ分析 Oasis Tableau Tableau troccoの強みを活かしてデータマート機能でAmazon Redshiftのストアドプロシージャーを呼び出し、集計処理をSQLで完結させた

Slide 22

Slide 22 text

実現したアーキテクチャ 22 troccoから一行記載するだけ。 SQLをtrocco上に直書きを回避しつつ責務をRedshiftに寄せることが可能に！！ BigQueryのSQL ストアドプロシージャを想像してください。データマート-Amazon Redshift

Slide 23

Slide 23 text

導入後の試算効果 23 運用ミスによる集計誤差を自動化により40%低減単価情報の精度向上により20%〜30%向上コミュニケーション負荷20%〜30%軽減８人月→４人月 Rubyエンジニア工数の64%をノーコード/SQLで代替 DXエンジニアの工数の37.5%を自動化により削減エンジニアリング工数集計誤差

Slide 24

Slide 24 text

最もフォーカスしたい点  データ設定データ整形/集計アウトプットデータ収集 Rubyエンジニア工数の64%をノーコード/SQLで代替前後運用保守/技術負債返済運用保守/機能追加

Slide 25

Slide 25 text

troccoとSQLかければなんとかなるのでは？ 25

Slide 26

Slide 26 text

26 と・・・思われたが・・・・

Slide 27

Slide 27 text

直近の切実な悩み 27 広告/ASP アーキテクチャデータ抽出集計設定集計ロジックデータ蓄積データ分析 Oasis Tableau Tableau troccoの弱みとして転送データが引き継げないことにより一時的なデータ設定の間口として Oasisを用意し、Redshiftのストアドプロシージャによる集計処理との分離が必要となった

Slide 28

Slide 28 text

直近の切実な悩み 28 アーキテクチャデータ抽出集計設定集計ロジックデータ蓄積 Oasis Oasisからtrocco APIでの転送設定を起動とACTIVE RECORDからRedshiftのストアドプロシージャの呼び出しが発生しており、Rails側に責務がじわじわ寄っている trocco API ストアドプロシージャ

Slide 29

Slide 29 text

このままでは原点回帰では？？ 29

Slide 30

Slide 30 text

30 大丈夫です！更新前提で組んでいます

Slide 31

Slide 31 text

なぜこうなったか？ 31 転送設定に関してはカスタム変数がAPIのパラメータとして用意されており、外部サービスから任意のパラメータを連携することが可能転送ジョブの実行POST /jobs

Slide 32

Slide 32 text

なぜこうなったか？ 32 一方でシンクジョブはAPIのエンドポイントがなく、Redshiftのストアドプロシージャに trocco経由で引数を渡すことができない・・・転送ジョブの実行POST /jobs

Slide 33

Slide 33 text

なぜこうなったか？ 33 また、ワークフロー定義では転送設定のようにカスタム変数をパラメータとして設定ができず、収集されたレポートに対して再度取り込みが必要になった際に運用者が直感的に動的な変更ができないワークフロージョブの実行 POST /pipeline_jobs

Slide 34

Slide 34 text

Troccoへの期待 34 広告/ASP アーキテクチャデータ抽出集計設定集計ロジックデータ蓄積データ分析 Oasis Tableau Tableau troccoのデータマート機能で設定したシンクジョブのAPI化やワークフローAPIのカスタム変数の追加などにより、更なるローコード化が進む見込み

Slide 35

Slide 35 text

泥臭い作業はやっぱり必要！！ 35

Slide 36

Slide 36 text

いざってときはスクラッチで開発する気概を持ち続けたい 36

Slide 37

Slide 37 text

お次はガラッと変わりましてRedshiftについて解説します 37

Slide 38

Slide 38 text

2.Redshift始めました 38

Slide 39

Slide 39 text

Redshift ServerlessとRedshiftの違い Redshift ServerlessはAuto Scalingされるが Redshiftは自前でコンピューティングリソースとストレージリソースの調整が必要ということ

Slide 40

Slide 40 text

Redshiftの構成 Redshift クライアントから SQL クエリを受け付けコンパイルし、コンピュートノードに配信コンピュートノードは高速ローカル SSD ・キャッシュを利用しており、該当データがキャッシュに存在しない場合はマネージドストレージからブロック単位でデータ読み取りを実行複数のコンピュートノードでこの処理を並列実行するため、大量データを高速に処理することが可能 Amazon Redshift Serverless の概要今までのAmazon Redshift(RA3 インスタンス)より引用

Slide 41

Slide 41 text

Redshift Serverlessの構成 Redshift Serverless クラスターという概念がなくなり、「名前空間」「ワークグループ」というコンポーネントで管理されるノード部分の綿密なキャパシティプランニング（ノード数やインスタンスタイプの決定）をする必要がなくなり、分析のためのクエリ実行時に自動でプロビジョニング・スケールしてくれる Amazon Redshift Serverless の概要 Amazon Redshift Serverlessより引用

Slide 42

Slide 42 text

実際に運用前にぶつかった課題 1.Redshift Serverlessへの書き込み速度の劣化 ▶RubyのActiveRecordではupdate処理に大幅な劣化が見られた 2.RA3プラン移行による速度劣化の懸念 ▶同一RPUあたりの速度が劣化する可能性 3.Redshift Serverlessコスト肥大化の懸念 ▶日常的なレポート抽出/集計だけで月額15万円以上の費用が発生

Slide 43

Slide 43 text

コストメリットとパフォーマンス両方の担保が必要だ 43

Slide 44

Slide 44 text

解決策  1.Redshift Serverlessへの書き込み速度の劣化 ▶RedshiftからはFederated QueryでAuroraからデータを取得 2.RA3プラン移行による速度劣化の懸念 ▶RA3のクラスターも生成し、Redshift Serverlessと速度比較を実施 3.Redshift Serverlessではコスト肥大化の懸念 ▶1,2検証後にRedshiftのプロビジョニングのRA3プランに変更

Slide 45

Slide 45 text

Federated Queryとは？いわゆる、RedshiftからPostgreSQLに対してデータベースリンクする機能(RDS,Aurora) 以下のような外部スキーマを定義することで、接続が可能になります CREATE EXTERNAL SCHEMA apg FROM POSTGRES DATABASE 'database-1' SCHEMA 'myschema' URI 'endpoint to aurora hostname' IAM_ROLE 'arn:aws:iam::123456789012:role/Redshift-Secre tsManager-RO' SECRET_ARN 'arn:aws:secretsmanager:us-west-2:12345678901 2:secret:federation/test/dataplane-apg-creds-Yb VKQw'; ※事前に適切なセキュリティグループ/ロールの設定が必要です 45 1.Federated Queryへの活用 

Slide 46

Slide 46 text

1.Federated Queryへの活用  背景 Oasisからの設定内容の更新はRedshift Serverlessに対して直接Updateを行う想定だった・速度劣化が見られたことからAWS社のSAと相談し、Auroraに責務を移すことに検証結果無事疎通が完了詰まった点 AuroraでIPを許可する形をとっていたが、Redshift Serverlessは公開IPがない →検証用にRedshift Serverlessを立てる必要があった 46

Slide 47

Slide 47 text

47 2.Redshiftプロビジョニング作成当初検証想定 1.Redshift Serverlessから手動でスナップショットを作成 2.スナップショットからRedshiftのクラスターを生成

Slide 48

Slide 48 text

1.Redshift Serverlessから手動でスナップショットを作成自動スナップショットからは生成不可のため、手動でスナップショットを生成してクラスターの復元への復元を実施 48 2.Redshiftプロビジョニング作成

Slide 49

Slide 49 text

2.スナップショットからRedshiftのクラスターを生成デフォルトで選択できるノードが16ノード!! 生成後にノードサイズ変更をしてもエラーとなり、ストレージサイズも下げられなかったスナップショットからの生成は断念 49 2.Redshiftプロビジョニング作成

Slide 50

Slide 50 text

代替検証 1.手動でRedshiftのクラスターを1ノードで生成 2.Redshift Serverlessからデータを移行 50 2.Redshiftプロビジョニング作成

Slide 51

Slide 51 text

データ移行ってめんどくさい 51

Slide 52

Slide 52 text

大部分は以下で移行完了 1.DBeaverからSQLファイルをエクスポート 2.クエリエディタからInsert文を直接実行 3.ストアドプロシージャーは定義情報から手動生成困った点・クエリエディタ上ではInsertできる上限は300000文字・S3からコピーコマンドをするとキャストで化ける・・・・AWS Glueは同期設定するのがしんどい・・・(何よりコストがやばい) 52 2.Redshiftプロビジョニング作成

Slide 53

Slide 53 text

どうにかできないだろうか・・・ 53

Slide 54

Slide 54 text

54 可能です。そうtroccoならね！！

Slide 55

Slide 55 text

troccoはバックグラウンドでembulkで 1.S3に配置 2.S3からRedshiftにコピーを自動でやってくれる面倒な実装や設定は一切不要。しかもカラムの型のキャストも完璧 55 2.Redshiftプロビジョニング作成

Slide 56

Slide 56 text

いよいよパフォーマンスチェック。速度劣化しないでくれ・・・ 56

Slide 57

Slide 57 text

事前設定 1.troccoにRedshiftの接続情報を設定 2.実績のある広告媒体/ASPの転送設定を作成 3.データマート機能で集計のプロシージャを設定 4.ワークフロー機能で2,3を結んだものを実行検証内容同一の広告媒体/ASPでレポート取得/集計時間を Redshift Serverless vs Redshiftで行う 57 3.パフォーマンス比較

Slide 58

Slide 58 text

広告媒体:検証結果レポート取得 Redshift:24秒 Redshift Serverless:2分25秒 Redshift 集計 Redshift:15秒 Redshift Serverless:43秒 58 3.パフォーマンス比較

Slide 59

Slide 59 text

成果:検証結果レポート取得 Redshift:39秒 Redshift Serverless:2分7秒 Redshift 集計 Redshift:3分49秒 Redshift Serverless:6分9秒 59 3.パフォーマンス比較

Slide 60

Slide 60 text

検証結果：サマリーサービス広告成果比較項目 Redshift Redshift Serverless Redshift Redshift Serverless 収集 24秒 2分25秒 39秒 3分49秒集計 15秒 43秒 2分7秒 6分9秒同一条件下ではRedshiftの方が優位な結果となった要因立ち上げ時のデータベース接続のオーバーヘッド 60 3.パフォーマンス比較

Slide 61

Slide 61 text

3.パフォーマンス比較今回は1タスクあたりの速度比較だった平均RPUが高くなった場合はRedshift Serverlessの方に軍配が上がる可能性はありただし、現状の運用想定では毎朝のレポート取得と集計以外は RPUが100を超えるような負荷は想定されない常時、速度維持を高RPUでも実現する必要はないためRA3へのプラン変更を行った 61 結論 

Slide 62

Slide 62 text

3.メンバー登録に関して 62

Slide 63

Slide 63 text

63 メンバーになっていただくことにより、優先的に案内いたします。キュービックメンバー登録について CUEBiC TEC Blog セミナー案内交流会の案内登録フォームはこちら

Slide 64

Slide 64 text

64 ご清聴、ありがとうございました

Slide 65

Slide 65 text

Q&A 65

Slide 66

Slide 66 text

Q1  66 troccoにSQLを直書きせずにストアドプロシージャの関数をCALLすることによる効果/ メリットとは？

Slide 67

Slide 67 text

Q1.Answer  67 1.責務をtroccoからRedshiftに分けることができ、 troccoを使用する運用メンバーとエンジニアリング領域を分離できる 2.troccoのAPIで補完できない機能を外部サービスからストアドプロシージャーを呼び出すことで補完できる

Slide 68

Slide 68 text

Q1.Example  データ設定データ整形/集計アウトプットデータ収集 DXエンジニア事業部担当者データエンジニア Oasis Tableau APPエンジニア事業部担当者開発運用 DXエンジニア整形の責務は分離

Slide 69

Slide 69 text

Q2  69 troccoやrailsを用いてRedshiftに蓄積されたデータは、社内のどのような人員が活用するのか?

Slide 70

Slide 70 text

Q2.Answer  70 誰が? 事業部のSEO/ADの運用担当者や経営陣 1.ASP/広告媒体レポートの収集データを目的変数で分類 2.Tableauでメディアやクライアント別の収益を分析 3.売上目標と予測値のずれを認識しつつ、PDCAサイクルを回して数値改善を行なう ※アウトプットのデータの粒度は担当者によって違いますが、データを構成している最小の値は同じというような感じですどのように？

Slide 71

Slide 71 text

Q3  71 今後troccoで新しく取り組んでいきたいこと

Slide 72

Slide 72 text

Q3.Answer  72 Redshiftの広告/成果データとの掛け合わせでシナジーを生み出す troccoのポテンシャルはマルチプラットフォーム間のデータ連携にあり!!

Slide 73

Slide 73 text

Q3.Example  73 アーキテクチャ Tableau データ収集データ分析 BigQueryに蓄積されたGA4データをtroccoのデータマート機能を使用して整形したデータを Amazon Auroraに退避し、TableauからカスタムSQLで参照するデータ整形 Auroraをデータソースとして参照し、カスタム SQLでデータを取得データマート機能でBigQueryのGA4蓄積データを整形して、BigQueryに整形後のデータをパーティションで作成 BigQueryデータを Amazon Auroraに転送 troccoワークフロー