ビジネス映像メディア「PIVOT」は皆さんのおかげで、チャンネル開始から1年3ヶ月で、YouTubeチャンネル登録者数100万人を達成しました。

今回は、入社後に着手したデータ分析基盤構築の話をします。
入社日にオフィスより先に、Googleさんオフィスへ行ってMTGしたのも良い思い出です。
入社後に同時並行で実施したインフラのクラウド移行は、下記の記事を覗いて頂けると嬉しいです。

• Google Cloud選定の経緯と理由

自己紹介

PIVOTでソフトウェアエンジニアをしている黒澤と申します。
2023年4月に1人目エンジニアとして入社してから早6ヶ月が経ちました。
時間の経過が早すぎてタイムスリップしたのかもしれないと思う今日この頃です。
クラウド移行、データ分析基盤構築、機能実装と濃密な時間を過ごせてPIVOTライフを謳歌中です。

ちなみに私のお気に入りコンテンツはこちらです。

読んでわかること

1. PIVOTのデータ分析基盤

2. BigQueryデータ連携の容易さ

3. BigQuery連携方法

アーキテクチャ概念図

インフラ基盤にGoogle Cloudを選定したことで、非常に簡単にデータ連携することができ、かつ運用コストも最小限に抑えることが可能になっております。

特にメディアである弊社はYouTubeを活用しているため、後ほど紹介するBigQuery Data Transfer Serviceを使用して容易にデータ収集ができました。

その他、WEB/APPのアクセスログも、多くの企業で使用しているGoogle Tag Manager (Google Analytics4)をBigQueryに連携しております。

アプリのインストール数などは、それぞれのストアから取得しているのですが、取得方法は異なっておりまして、

Google Playは、BigQuery Data Transfer Serviceで数クリックで自動取得の設定が可能です。

App Storeとは自動連携できないため、Cloud Functionsで関数を実装し、API連携のうえ、BigQuery連携をしました。

弊社で一つ特徴的なサービスとして、自社プロダクトの動画配信にULIZAを使用していることが挙げられます。
ULIZAはAnalytics用のAPIが用意されているため、Cloud FunctionsでAPI毎に関数を実装してBigQuery連携をしています。

BigQuery連携

Google Analytics4 (GA4)

元々、複数のアカウントとプロパティが存在しており、ごちゃごちゃになっていたので現状（当時）理解から始まり、弊社アナリティクスアカウントに紐づいた形に綺麗にしました。

データストリームの設定に当たり、一部Firebaseのプロジェクトも整理して、設定を行いました。

BigQueryとの連携方法は、記事がたくさんあり、手順通りに実施すれば連携されます。連携まで24時間程かかると記載があるのですが、もう少し時間がかかった記憶があります。（私はせっかちなので、設定ミスなのでは？と何度もコンソールを確認してましたw）

最低限取得したいイベントのうち、カスタム設定が必要なものは、Google Tag Manager (GTM)で設定を行い、WEB / iOS / Android それぞれで実装をしております。

WEB: Next.js
iOS: Swift
Android: Kotlin

Cloud SQL

BigQuery から Cloud SQL データをクエリするための接続手順は以下になります。

EXTERNAL_QUERY関数を使用して、SQLでクエリを実行することができます。( Cloud SQL連携クエリ )

BigQuery Data Transfer Service

権限まわりさえ適切に設定できれば非常に簡単で、公式ドキュメント通りに設定すればOKです。

2023年9月現在、YouTube チャンネルやMCN/複数チャンネル運用者用のYouTube コンテンツ所有者、Google Playをはじめ、Google 広告など、サポートされるデータソースがいくつかあります。

• YouTube

制限事項を確認して要件を満たしていることを確認。
YouTubeチャンネル転送にはこちらの権限が必要。

YouTube: YouTube チャンネルの所有権の設定で少々詰まりました。

公式ドキュメント記載の通り、１日半程は下記のエラーが表示されますが、首を長くして待ちます。

データ転送には2日間は失敗すると公式にも記載がありますが、1日半ほどで転送には成功。しかし、「転送実行が正常に完了しました」とはなったものの、データが確認できない状態でした。

弊社のYouTubeを運用担当と連携して、チャンネル所有権を持っているアカウントで、データ転送するアカウント認証をしたのですが、私のアカウントで転送をしているようでした。（私は当然YouTuberではないため、チャンネルを保有しておらずデータが0件だった。）

最終的に、チャンネル所有権を持っているアカウントでGoogle Cloud コンソールにログインした上で、データ転送設定して解決に至りました。
（bqコマンドではなく、コンソールから実行）

その他、転送されるデータは、レポートの変換ページで確認することができます。今回は詳細には触れませんが、YouTube Studioのデータと多少差分があるため注意が必要です。

• Google Play

制限事項を確認して要件を満たしていることを確認。
Google Play転送にはこちらの権限が必要。

その他、転送されるデータは、レポートの変換ページで確認することができます。
インストール数など最新のデータは、3~4日前のものになります。(取得タイミングによる)

Cloud Functions

特別な要件がない限り、node.jsで実装する方針でいます。 (仮)

• App Store

こちら (Sales and Trends Reports) のデータを収集し、インストール数などを取得するために実装。

データ収集が成功すると `Product Type Identifier`に、1, 3, 7などの数値が入ってきます。参考記事内にもありますが、それぞれ以下の通りです。

実装に関しては、以下の記事を参考にさせていただきました。

• ULIZA

動画配信プラットフォームとして ULIZA を活用しており、視聴データをBigQuery連携しています。

AppStoreと同様に、API (App Store Connect API)を使用してデータを取得し、BigQueryにロードしています。

• YouTube

BigQuery Data Transfer Serviceで取得できていないデータを、YouTube Data APIで取得してBigQueryにロードしています。

おまけ

ダッシュボードを作るにあたって、MVPとして3つほど作成することになっていました。
そのうちコンテンツのダッシュボード作成にあたって、ULIZAのコンテンツ(ID) とYouTubeのコンテンツ (ID)が紐づいておらず、既存分については、名寄せを実施しました。

新規分は、CMSに入力項目を増やし、コンテンツ運用者へ説明を実施してYouTube Video IDを入力してもらう運用としました。（基本的にはYouTubeに動画をアップロードした後に、CMS設定を行っています）

既存分は、以下の手順でやってみました。
ULIZAのタイトルとYouTubeのタイトルが一致しないコンテンツが多いため、タイトルと配信日時を元に名寄せを実施。

• データ取得

  • YouTube Data API

    • video_id

    • title

    • published_at

  • サービスDB内のコンテンツマスタ

    • episode_id

    • title

    • published_at

直近データであれば、配信日時がほぼ一致しており、YouTube動画:サービス内動画が、1:1なので精度が良かったのですが、過去動画はどちらも満たせておらず、最終的には目視での名寄せかつ紐づけられないコンテンツが存在することになりました。

事前調査の大切さを痛感しました。

アウトプット

• ダッシュボード

弊社では、ダッシュボードにLooker Studioを利用しています。
元々はRe:dashを利用していましたが、BigQueryとシームレスに接続でき、低料金で運用できるため利用しています。

また、ELTの Transformにあたる処理はdbt Cloudを利用しています。
ダッシュボード及びdbt Cloudについては、弊社の優秀なメンバーが~別記事で書く予定となっているため割愛します。~  書いた記事を覗いて頂けると嬉しいです。

• Slack通知 (Daily)

元々は、cronで定期実行してアプリケーションからSlack通知をしていました。

現在は、Cloud Functionsで関数を実装し、BigQuery のmartからデータを取得してSlack APIを使用して通知の構成となっています。
既存実装は削除し、サーバレスへ移行しました。

上述の通り、Cloud SchedulerとPub/Subも利用して定期実行をしており、下記は通知サンプルになります。。
Google Playのデータ反映の都合で速報値が数日前のデータになっているため、更新時間の調整や見せ方(~文字多い~)の改善余地ありです。

今回は、データ分析基盤構築の話をしました。
過去データの取得には期限があるサービスが多いため、可能な限り早くデータ連携をすることが大事になリますね。

より大事なことは、集まったデータを分析することで、顧客価値向上を図れることであるため、プロダクトチーム一同そのことを頭に刻みながら脳みそフル回転で励んでいます。

今後とも、私たちは「日本をPIVOTする」を信念に、質の高いコンテンツをお届けします。ぜひとも、今後ともビジネスメディア「PIVOT」をお楽しみください！

PIVOTでは、Mobile Engineerの方を募集しています。

herp.careers Mobile Engineer

PIVOT株式会社

募集要項をみる

YouTubeチャンネルはこちら

Web版はこちら

iOS アプリはこちら

Android アプリはこちら