2022.10.24 コラム

• BigQuery
• SQL
• 回帰分析
• 時系列データ
• 機械学習

【機械学習】BigQuery MLで広告施策の貢献度を分析してみた

● はじめに

　BigQuery MLを活用し、店舗売上金額に対する広告施策ごとの貢献度を分析してみました。
　例えば店舗売上データと広告施策データのレポートが別々に分かれており統合した分析が難しいといったような場合でも、BigQuery MLを活用すれば分析が可能になります。

● BigQuery MLとは

　公式ドキュメント

• BigQuery上で機械学習（モデルの作成、モデルの評価、予測）を行うことができる
• SQLの知識があればできる（Python不要）
• 線形回帰、ロジスティック回帰、クラスタリング等さまざまな種類の学習ができる

● 分析の流れ

　今回は、おおまかに以下のような流れで分析を行います。

1. BigQuery上に入力データを用意
2. 入力データをもとに学習（トレーニング）し、回帰モデル作成
3. 広告施策ごとの売上に対する貢献度を算出

● BigQuery上に入力データを用意

　まずは、分析したいデータをBigQuery上に用意します。

●今回利用データ

　今回は、各広告施策の投下金額と、店舗売上金額の週次データをcsvで用意し、BigQuery上にアップロードしました。

• dt：日付項目（週次）
• campagn_a：マーケティング施策Aの投下金額（円）
• campagn_b：マーケティング施策Bの投下金額（円）
• campagn_c：マーケティング施策Cの投下金額（円）
• sales：店舗売上金額（円）

● 学習（トレーニング）実行

　用意したデータを使って、CREATE OR REPLACE MODELで学習モデルのトレーニングを実行します。
OPTIONSでmodel_typeを‘linear_reg’に指定して、input_label_colsには予測したい項目であるsales（店舗売上金額）を指定します。

【トレーニング】実行クエリ

　今回、BigQueryコンソール上で以下のSQLクエリを実行しました。

CREATE OR REPLACE MODEL
  `Project名.Dataset名.任意のモデル名` 
  OPTIONS( 
    model_type='linear_reg',
    input_label_cols=['sales'],
    DATA_SPLIT_METHOD = 'NO_SPLIT',
    OPTIMIZE_STRATEGY = 'NORMAL_EQUATION' 
　　) AS
SELECT
  sales,
  campaign_a,
  campaign_b,
  campaign_c
FROM
  `Project名.Dataset名.入力データ格納テーブル名`

【トレーニング】実行結果

　出力結果としてモデル（テーブルのようなもの）ができあがります。
モデルの評価指標（どのくらいうまくトレーニングできたか）なども確認することができます。

● 広告施策ごとの売上に対する貢献度を算出

　トレーニング結果を使って、各広告施策ごとの売上金額に対する貢献度を算出します。
　ML. WEIGHTS関数を使用すると、説明変数ごとの回帰係数（各施策ごとの重み）を算出できます。

【重みの算出】実行クエリ

　今回、BigQueryコンソール上で以下のSQLクエリを実行しました。

SELECT
processed_input,
weight
FROM
ML.WEIGHTS(
MODEL `Project名.Dataset名.モデル名` --トレーニング結果
)
WHERE processed_input IN ('campaign_a','campaign_b','campaign_c')

【重みの算出】実行結果

【貢献度の算出】実行クエリ

　各施策の重みの値を元データの各施策の投下金額に掛け合わせた値を合計し、施策ごとの貢献度を算出します。

　今回、BigQueryコンソール上で以下のSQLクエリを実行しました。

#standardSQL
with t1 as(
select
campaign_a,
campaign_b,
campaign_c,
"1" as key
from
`Project名.Dataset名.入力データ格納テーブル名`
),
A as(
SELECT
processed_input,
weight,
"1" as key
from
`Project名.Dataset名.重要度算出結果格納テーブル名`　--トレーニング結果
),
B1 as(
--重要度を横に
SELECT
weight as campaign_a,
key
FROM A
where processed_input = "campaign_a"
),B2 as(
SELECT
weight as campaign_b,
key
FROM A
where processed_input = "campaign_b"
),B3 as(
SELECT
weight as campaign_c,
key
FROM A
where processed_input = "campaign_c"
),B as(
SELECT
campaign_a,
campaign_b,
campaign_c,
B1.key
from B1 left join B2 on B1.key = B2.key 
left join B3 on B1.key = B3.key
),C as(
SELECT
t1.campaign_a * t2.campaign_a as effect_campaign_a,
t1.campaign_b * t2.campaign_b as effect_campaign_b,
t1.campaign_c * t2.campaign_c as effect_campaign_c
FROM t1
inner join B as t2 on t1.key = t2.key
)
SELECT
sum(effect_campaign_a) as effect_campaign_a,
sum(effect_campaign_b) as effect_campaign_b,
sum(effect_campaign_c) as effect_campaign_c
from C

【貢献度の算出】実行結果

　今回は、キャンペーンBの貢献度が約104万円と最も大きく、次いでキャンペーンAの約11万5,000円、キャンペーンCの貢献度が約5万4,000円と最も小さい結果となりました。

● まとめ

　今回、売上金額に対する貢献度はキャンペーンB > A > Cの順に大きい結果となりました。
BigQuery MLを活用することで、SQLのみで機械学習を使った広告施策の貢献度を分析することが可能です。

　その他にも、下記コラム記事でBigQueryを使った分析事例をご紹介しているので是非ご覧ください。

▼BigQueryを活用したデータ分析についてのコラムはこちら
⇒GTM × GA4 × BigQuery|サーバーサイドCookie単位でUB集計してみた
⇒GTM×GA4×BigQuery|バスケット分析による鉄板コンテンツ
⇒GTM × GA4 × BigQuery|データ統合の基本！マッピングテーブル構築
⇒GTM × GA4 × BigQuery|サーバーサイドCookieを活用したn1解析

▼BigQueryについてのコラムはこちら
⇒【API・データ検証奮闘記】＃８．BigQuery Transfer Serviceやってみた
⇒【API・データ検証奮闘記】＃９．BigQueryからTreasureDataへデータ移行やってみた
⇒【テックコラム】テーブルスナップショットでBigQuery の喪失データを復元してみる

　弊社では、定点的なリサーチやデータ分析、BIによる可視化などをおこなっています。性別や年代等の属性分析や検索トレンドのダッシュボード提供等様々なパッケージをご用意しておりますので、お気軽にお問い合わせください。

》CDP総合支援サービス ～構想・構築・活用～ のサービス資料はこちら

本件に関するお問い合わせは下記にて承ります。
株式会社DataCurrent
info@datacurrent.co.jp