Yahooプロモーションとキャンペーンマネージャーの結果をスプレッドシート合体して集計を効率化

今回は下記の2つのツールで指標を別々に集計して、
効率的に合体する方法をご紹介します。

【使用ツール】
1.Yahoo!スポンサードサーチ(Yahoo! プロモーション管理画面内の検索広告):週別、キャンペーン、広告グループ、表示回数、クリック数、費用
2.キャンペーンマネージャー(CM,CV等測定用):Tracking Code、CV

スプレッドシート内でのアウトプットの内容

データを集計する際はスプレッドシートを利用して、
報告や分析に必要なアウトプットの表が数式で自動作成させるように設定します。

【スプレッドシート内でアウトプットしたい表】
・全体結果(KPIである費用、CV、CPAを中心に)
・メインカテゴリ別結果(ほぼキャンペーンに近い分類)
・メインカテゴリ×サブカテゴリ別結果(ほぼ広告グループに近い結果)

Yahoo!スポンサードサーチ側では、最終リンク先URLの後ろにキーワード別のパラメーターを設定します。
これがキャンペーンマネージャー側で1対1対応された「Ad」と一致するので、キーワード別のCV数が分かるようになっています。

(例)キーワード:「キーワードA」→最終リンク先URL:「(リンク先)?ys_mainA_subA_0001」
※Tracking Codeがないと「none」など他と区別がつかない集計になってしまいます

スプレッドシートの6つのタブ構成

スプレッドシート内のタブは6つで、構成は下記のとおりです。

タブ1:Yahoo! スポンサードサーチ_KW週別データ

まずはYahoo! スポンサードサーチ(YSS)のrawデータを用意します。

媒体:Yahoo! スポンサードサーチ
レポート種類:キーワード×週別結果

期間は日曜〜土曜の7日間を日曜日の日付(A列「日付」欄)で統一して、各指標合計値を集計します。

rawデータはYahoo!スポンサードサーチのレポート内にあるテンプレートを作成して、
期間は「過去7日間」で「毎週日曜日」に自動出力の設定をすれば
指定の時間に自動作成されます。

こうすることで、必要な作業は1週間に一度最新データをダウンロードしてスプレッドシートに貼り付けるだけになり、ほとんど時間をかけずに行えます。

ここは毎週同じ作業を繰り返すことになるので、複数の案件を担当する場合は、マニュアル化して他のメンバーに作業依頼をするのが良いです。

タブ2:キャンペーンマネージャー_Code週別データ

キャンペーンマネージャーでも同様の作業を行ないます。

媒体:キャンペーンマネージャー
レポート種類:Tracking Code×週別結果

Tracking Code別のクリック数データも見れますが、(not set)が混ざってしまいます。

全体のクリック数は媒体側のデータで十分なので、キャンペーンマネージャーではCVが1件以上にフィルタを絞り抽出するようにすることがお勧めです。
日別に細分化する考えもありますが、データ量が増えればPC上の操作時にフリーズしやすくなるので、必要なければ週別にすれば作業時間も短縮されます。

※スプレッドシートの1ファイルあたりの上限セル数は20万個です。
5万セルを超えたくらいからフリーズしやすく、1つ1つの操作の反映に時間がかかります。

そこで、セル数を極力抑えるために次の工夫を行います。
・CV0件のデータは集計しない
・期間も日別ではなく週別にする

タブ3 : KW ― Code 連携データ

次に、2つのrawデータを合体させて、クリック、費用、CVがキーワード別に見れるようにします。


今回におけるこのタブの役割は、後の「タブ6:キーワードTOP10結果」でカテゴリ別に表示するためです。

つまり、Raw1とRaw2をCV降順に並べた際に、Raw1にCVデータがなくうまく紐づかないので、一旦Tracking Codeで表示回数、クリック、費用、CVを一列に並べる連携データを作成しています。

実際に使われている数式は、下記のようにsumifsを組み込んでTracking Code別に集計しています。
※今回はWeek×キーワードの結果を出力しないので期間を集約していますが、目的に応じて分割軸を変えて集計します。

タブ4:全体、カテゴリ別結果

ここからアウトプットのシートです。

まずは、全体、キャンペーン、広告グループ別の順に並べます。

sumifsでそれぞれ集計します。
CTR、CVR、CPC、CPAは計算指標なので、ここで同じ列の指標の数式を用いて算出します。

タブ5:週別×カテゴリ別結果

次に、各カテゴリの週別の結果です。

こちらもsumifsで、複数の指定項目を加えて集計します。
1つ目で週、2つ目でキャンペーンを限定する指定を加えています。

タブ6:キーワードTOP10結果

さらに掘り下げて、各カテゴリのキーワードCV TOP10の結果をまとめます。

こちらはsortn,filterの数式を使って、TOP10を抽出します。

このようにすることで、タブ1、タブ2のrawデータを週に一度更新するだけでタブ3-6も自動更新されます。報告する際にもこのまま共有して、結果の進捗を伝えることが可能です(もちろん相手によりますが)。

更新時間としては10分/週なので、手動でエクセルにまとめる場合と比べると、回数を重ねるごとに数十時間と違ってきます。このようにスプレッドシートで数式を組んでアウトプットを自動化することで大きな時間節約となり、肝心の分析や改善案考案に時間を使うことができるようになります。

まとめ

最初は慣れなくて時間がかかることもありますが、更新作業に慣れてしまえばRawデータを整理して数値間違いを確認することが大半の作業となり、とても楽な管理方法です。

スプレッドシートはタブ間の連携が自在にできて(シート間も同様)、
共同編集もできるので保存してメール送付する必要などないのでとても便利です。

是非、上記のような方法もできるようにして効率良いやり方を模索してみましょう。

最新情報をチェックしよう!