databeatにある広告データをBigQueryで取り扱う方法

この記事では、databeat に蓄積した広告データを BigQuery で扱う方法について紹介します。

特に今回は Google 広告のデータに題材に紹介しますが、他の広告データでも基本的に同じようにデータを扱うことができます。

広告の構造

Google 広告では、キャンペーン(Campaign) > 広告グループ(AdGroup) > 広告(Ad)という要素に分かれます。

キャンペーン: 商品やサービスのカテゴリのような大きい粒度を指す
広告グループ: 類似したターゲットを共有する広告をまとめたものを指す
広告: バナー広告や YouTube 広告などの広告単体を指す

これを念頭に置いた上で、必要なデータを使用する必要があります。

データについて

広告の媒体によってデータセットが分かれます。

引用: BigQuery について

各データセット内に存在するテーブルは媒体によって異なりますが、Google 広告であれば上記右図のようになります。

テーブルの構造

campaign テーブルに注目すると、このテーブルは「日付 × キャンペーン × デバイスごとに各メトリクスを集計したテーブル」となります。 adgroup テーブルでも同様に、「日付 × 広告グループ × デバイスごとに各メトリクスを集計したテーブル」となり、各メトリクス内容は同じものになります。

各テーブルのカラムは以下のようになっており、スキーマにカラムの説明が書いてあるのでそちらを参照すると良いでしょう。

よく使う指標

各テーブルのカラム量が大量に存在するため、まずは必要な指標を抽出して分析しやすくする必要があります。

ユースケースによって異なりますが、一般的によく使うカラムを以下にまとめます。

- AdvertisingChannelType,
- BaseCampaignId,
- CampaignId,
- CampaignName,
- CampaignStatus,
- Device,
- Cost,
- Impressions,
- Clicks,
- Interactions,
- Engagements,
- Conversions

実践:媒体横断した広告データをまとめて分析する

1 つのキャンペーンで複数の媒体で広告を実施することも多くあると思います。また、媒体横断して分析したいというニーズも当然出てきます。

databeat の場合、別の媒体でもテーブルの構造はほぼ同じであるため結合することができます。 ### よく使う指標でも記載したように、結合するタイミングで必要なカラムを抽出しておくと良いでしょう。(BigQuery の料金的にもカラムを減らすことで無駄なコストを抑えることができます)

Google 広告のデータと LINE 広告のデータを結合する以下のようなクエリを書くことで、容易に媒体横断で分析することが可能になります。

-- google広告キャンペーン粒度のデータ
  google AS (
  SELECT
    Date,
    'google' AS media,
    AdvertisingChannelType,
    BaseCampaignId,
    CampaignId,
    CampaignName,
    CampaignStatus,
    Device,
    Cost,
    Impressions,
    Clicks,
    Interactions,
    Engagements,
    Conversions
  FROM
    `<プロジェクト名>.google_ads_xxxx.campaign`
  WHERE
    Date >= "<適切な日付>" ),
  -- line広告キャンペーン粒度のデータ
  line AS (
  SELECT
    Date,
    'line' AS media,
    AdvertisingChannelType,
    BaseCampaignId,
    CampaignId,
    CampaignName,
    CampaignStatus,
    Device,
    Cost,
    Impressions,
    Clicks,
    Interactions,
    Engagements,
    Conversions
  FROM
    `<プロジェクト名>.line_ads_xxxx.campaign`
  WHERE
    Date >= "<適切な日付>" ),
  unioned AS (
  SELECT
    *
  FROM
    google
  UNION ALL
  SELECT
    *
  FROM
    line
  )
  SELECT
    *
  FROM
    unioned

参考

備考

Hakky では社内のデータ活用やサービスとしてデータ提供を行うためのソリューションを展開しております。

「BI ツールでの Dashboard 可視化に困っている」など具体的な相談はもちろんのこと、「どんなことをお願いできるのか知りたい」や「こんなことをやりたい」など、ご検討段階でも構いませんので、ぜひお気軽にフォームよりお問い合わせくださいませ。