【BigQuery x LookerStudio】複数のWeb広告成果を１つに統合して可視化するには？

あけましておめでとうございます。テクノロジー部の森です。
今年最初の記事はマーケティング会社らしくWeb広告のデータの取り扱いと可視化についての話題です。

弊社はWebマーケティングの会社として、代表的なGoogle広告、Yahoo広告をはじめ、様々なWeb広告のコンサルティングや出稿のご支援を行っています。
広告媒体が複数になると、それぞれの出稿実績はどうか？いったいどの広告が費用対効果がいいのか？媒体を横断して比較する必要があります。しかし、各広告媒体の管理画面を複数開いて見比べて評価するのはなかなか難しいものです。

そこで、弊社ではBigQueryとLookerStudioを中心とした技術を使って複数の広告媒体データを統一して扱えるように加工し、単一の画面で比較、分析しています。

今回はその仕掛けについてご紹介します。

データをためるBigQuery、データを表示するLookerStudio

ご存じの方も多いかと思いますが、まずはメインとなるBigQueryとLookerStudioについて簡単にご説明します。

BigQueryはGoogle Cloud Platformの１サービスです。大量のデータを格納することができ、それらの分析を得意とするデータウェアハウスです。Webサイトやシステムで利用されるリレーショナルデータベースとは異なり、列方向の処理が得意なので、集計や合計などの分析に向いています。

Looker Studioは元々Googleデータポータルと呼ばれていたデータ可視化サービスです。BigQueryに限らず、スプレッドシートやデータベースなどのデータソースと接続し、時系列の折れ線グラフやヒートマップ付きピボットテーブルなどを表示できます。

この２つはいずれもGoogleのサービスのため互いに親和性が高く、BigQueryにデータをためてLooker Studioで可視化する、という連携方法がよく使われます。

今回ご紹介する方法では、このBigQueryとLooker Studioを使って以下の手順で複数の広告成果を１つの画面で可視化します。

BigQueryに各広告のデータを取り込む
BigQuery上でデータ形式の統一やデータの結合を行う
できたデータをLooker Studioで可視化する

それでは詳細を説明していきます。

ETLサービスを使って各広告媒体データをBigQueryに取り込む

Google広告、Yahoo広告などの広告媒体のデータのBigQueryへの取り込みは、ETLサービスを使っています。ETLサービスはいくつも公開されており、例えば以下のようなものがあります。

Reconer
- kintone、stripe、shopify連携あり
trocco
- kintone、hubspot、Salesforce連携あり
Databeat
- A8.netなどアフィリエイトサービス、Gunosyなどニュースメディア広告との連携あり
Fivetran
- 海外サービス。hubspot連携などあり、接続可能なデータソース数が多い

それぞれ数十から数百のデータソースから取り込みが可能なので、ご自身が運用されている広告媒体やCRMサービス等に対応しているか確認の上、選定すると良いでしょう。

各サービス、媒体ごとに取り込み手順は異なるため、ここでは詳細は割愛します。これらのサービスに広告媒体のアカウント連携情報などを登録し、例えば１日１回などの頻度で広告成果データをBigQueryの特定のテーブルに転送するように指定します。

BigQueryでSQLを使ってデータ定義を加工、結合する

取り込んだ複数の広告データを最終的に１つに結合しますが、その前処理として以下のような加工を行っておきます。

データの定義を揃える
各媒体の区別がつくような「媒体名フィールド」を用意して媒体名を入れておく

具体的にはBigQuery上で、加工用のビューをSQLで定義します。例えば以下はGoogle広告に対する前処理の例です。結合した後にこのデータがGoogle広告のものと分かるように”adnetwork”にgoogleと指定したり、Google広告特有のパラメータを他の広告の表現と一致させるような加工を行います。

SELECT DISTINCT
    "google" AS adnetwork, /*googleと分かるように固定値フィールドを追加*/
    CASE
        WHEN REGEXP_CONTAINS(AdNetworkType1,"SEARCH") THEN "cpc"
        WHEN REGEXP_CONTAINS(AdNetworkType1,"MIXED") THEN ""
        WHEN REGEXP_CONTAINS(AdNetworkType1,"YOUTUBE") THEN "paid_video"
        ELSE "display" END AS medium, /*YOUTUBEなどGoogle独自のメディア名を一般化*/
    date,
    "google_adgroup" AS data_source,
    CampaignName AS campaign_name,
    AdGroupName AS ad_group_name,
    cost,
    impressions as impressions,
    clicks as clicks,
    conversions as ad_cv,
 FROM <データ取り込み用サービスでデータを格納したテーブル>

これを利用する各広告媒体の数だけ実施し、最後にそれらのビューをSQLのUNION句で結合してあげれば、複数の広告データを１つに統合したテーブルの完成です。

なお、弊社ではクライアント様ごとにこのような処理を行っています。クライアント様ごとの利用媒体の違いや参照元テーブルの違いを動的に切り替えるため、BigQueryの関連サービスであるDataformを使ってクライアント様ごとのテーブルを作っています。Dataformについては以下の記事でも紹介していますので、参考にしてください。

DataformでJavaScriptを使ってBigQueryのデータを変換する方法

Dataformのデータ加工の流れを可視化する機能で、上記の結合箇所を可視化したのが以下キャプチャになります。

Looker Studioでデータを可視化する

あとはLooker Studioで先ほど作成したBigQueryのテーブルをデータソースに指定してグラフや表を定義します。１つのテーブルにすべての広告媒体情報が格納されているため、１つのグラフや表で一度に広告成果を確認することができます。

例えば冒頭の媒体別効果の表以外にも、広告CVの時系列グラフなども表示できます。

GA4連携や予算管理も！マーケティングダッシュボード「GLASS」の活用

いかがでしたでしょうか？今回の記事の流れで複数広告媒体を単一グラフで確認できると媒体間の成果の違いなども確認しやすく、レポーティング工数も削減できるのではないでしょうか？

ただし、実現には各種サービスの知識やSQLを記述できるスキルが必要になります。「エンジニアではないため実現は難しい」「実現できてもメンテナンスや新規媒体追加などの対応が面倒」などの場合は、弊社で今回記事でご紹介したような画面をすぐに利用できるマーケティングダッシュボード「GLASS」を提供していますので、是非ご活用ください。

マーケティングダッシュボード「GLASS」では今回ご説明した広告成果データの統合以外にも「Google Analytics（GA4）データの統合」や「スプレッドシートで管理する予算データとの比較機能」なども提供しており、デジタルマーケティング全般で必要なデータを１つの画面で確認できるようになっています。

サービスの詳細、および、お申し込みは以下ページにて承っております。