リスティングのお仕事は単純に管理画面を触るだけでは完結しませんよね。
提案、分析、レポーティング・・・
運用に付随する様々な業務に使う時間を短縮させられれば、と考えている運用者の方も多いのではないでしょうか？
もちろん様々なツールが存在しており、それらを利用する事で業務を効率化している方がほとんどだとは思います。
しかし、案件によってはそういったツールでは実現不可能なものだったり、提出フォーマットを先方指定のものに合わせなければいけなかったりと、型からはみ出してしまう事も少なからずあるはずです。
今回はそんな状況でも最大限のパフォーマンスを発揮出来るリスティング広告運用者に必要なエクセル関数を紹介します。
SUMIF関数
SUMIF関数は条件の合うものだけを加算する関数です。
=SUMIF(範囲,検索条件,合計範囲)
という形のものです。
例えば、上図のクエリレポートの中で「リスティング」を含むものだけの表示回数の合算値を知りたい時には、
=SUMIF($A$2:$A$14,”*リスティング*”,$B$2:$B$14)
となります。
検索条件部分の「”*リスティング*”」は「リスティング」という文字列を含むという意味です。
こうすることで特定の文字列を含んだものだけを合算する事ができます。
予めどこかのセルに合算したい対象の文字列を入力しておき、「”*”&A1&”*”」のようにセルの文字列を含むという形にしておくと楽になると思います。
また、複数条件に合致したものを合算したい場合はSUMIFS関数を使うのも良いです。
=SUMIFS(合計対象範囲,条件範囲１,条件１[,条件範囲２,条件２])
これで複数の条件に合ったものだけを合算できます。
LEFT関数、RIGHT関数、MID関数、FIND関数
これは文字列操作系の関数です。
基本的には「左から○文字目以降を抜き出す」「右から○文字目より後を抜き出す」などが出来ます。
また、FIND関数は対象となる文字の位置を返す関数です。
=LEFT(文字列,[文字数])
=RIGHT(文字列,[文字数])
=MID(文字列,開始位置,文字数)
=FIND(検索文字列,対象,[開始位置])
これらを組み合わせて文字列を抜き出してレポートに反映させる事で手間が省けるようになります。
上図では「_（アンダーバー）」の前後の文字を削って抜き出しています。
この関数は組み合わせることで結構自由度が高くなります。
RANK関数
次は順位を付ける関数です。
関数を使う必要がないのであれば（分析のためにちょっと並び替えてみるなど）フィルター機能でソートした方が早いですが、クエリのレポートをCV順に並べて提出が定期的にある場合にはCSV貼り付けで終わらせてしまいたいところなのです。
RANK関数を使えばそれも可能です。
=RANK(数値,参照,[順序])
今回は上図のクリック数を元に順位を出してみます。
=RANK(C2,$C:$C,0)
これでそれぞれの順位が出ました。
後はこれをVLOOKUPなどで並び替えるとクリック数順のレポートになります。
しかし、ちょっと待って下さい。
よく見ると同順位のものがいますね。
これではVLOOKUPがうまく機能してくれません。
そこで順位被りをなくしてしまいましょう。
=RANK(C2,$C:$C,0)+COUNTIF($C$2:C2,C2)-1
これで順位の被りがなくなりました。
こうしておけば今度こそ後はVLOOKUPで引っ張ってくるだけで済みます。
応用編
それでは煩雑なレポーティング業務をCSV貼り付けのみで終わらせてしまえるようにしてみましょう。
画像のようなデータがあったとします。
※）大抵の場合扱うデータはCSV形式ですが分かりやすく装飾してあります。
クライアント様からのご依頼は
- 毎週提出
- 商材ごとの成果をレポーティング
- デバイスは別けて集計
- クリック数降順
という条件です。
毎週データを手動で集計するのは効率的ではないのでデータを貼り付けて提出出来る形に出来れば楽ちんですね。
それでは早速作って行きましょう。
例題のアカウントは商材毎にキャンペーンを分けています。
重要なのは構築段階でキャンペーン名や広告グループ名、広告名に規則性を持たせることです。
規則性がなければ何をするにしても手間になってしまいます。
１）商材名の抜き出し
ということでまずは商材を抜き出しましょう。
LEFT関数、RIGHT関数、MID関数、FIND関数を駆使してそれぞれの商材名のみを抜き出します。
=MID(A2,FIND(“_”,A2)+1,LEN(A2)-(LEN(LEFT(A2,FIND(“_”, A2)))+LEN(A2)- FIND(“_”,A2, LEN(LEFT(A2,FIND(“_”,A2)))+1))-1)
抜き出すのは他のシートにした方がいいかもしれません。
これを元データの行数分だけ用意します。
キャンペーン名とデバイス程度であれば300行もあれば良いと思いますので別のシートに商材名をずらっと並べてしまいましょう。
元データのシートは「sheet1」としています。
２）重複している商材名を調べる
これだけでは商材名が重複します。
そのため重複したものを調べて重複していない商材名リストを作る必要があります。
これも手作業で出来ないことはないですが基本的にCSVをコピペで終わらせたいので関数で何とかしましょう。
ここではCOUNTIF関数とMAX関数を利用して被っていないものに連番を振っていきます。
=IF(COUNTIF($A$2:A2,A2)1,MAX($B$1:B1)+1,””)
COUNTIF関数で該当行までの同様の文字列の数をカウントします。（該当のセル含む）
もし１つの場合はMAX関数でそれまでの最大値を返し、１をプラスします。
１以外の場合は空欄にすれば重複した文字列はカウントしません。
３）体裁を整える
VLOOKUP関数で体裁を整えましょう。
=IFERROR(VLOOKUP($A25,$A$2:$B$21,2,FALSE),””)
エラーで見た目が美しくなくなるのは許せないのでIFERRORを使ってエラーの際には空欄にするようにしておきます。
４）クリック数を集計する
クリック数でソートするために予めクリック数だけ集計しておきます。
商材名とデバイスがそれぞれ合致する場合に集計するので、SUMIFS関数を利用します。
商材名のセルが空欄の場合はクリック数がすべて合算された数値になってしまうのでIF関数を使って空欄の場合は空欄を返すようにしておきます。
=IF(B25=””,””,SUMIFS(Sheet1!$D:$D,Sheet1!$A:$A, “*”&Sheet6!$B25&”*”,Sheet1!$B:$B,Sheet6!$C25))
５）順位を付ける
RANK関数を使ってクリック数の多い順に順位を付けます。
画像が煩雑になってきたのでPCのみにしました。
=IFERROR(RANK(D25,$D$25:$D$37,0)+COUNTIF($D$25:D25,D25)-1,””)
こちらもエラーを吐いたセルは空欄にしておきます。
エラーが出ても気にしない方はそのままでも特に問題は無いです。自己満足です。
次の手順でVLOOKUPで引っ張ってくる都合上商材名の前に順位を持ってきております。
６）商材名をクリック数降順に並べる
またシートを変えて商材名をクリック数降順で表示します。
このシートはお客様に提出するものになりますのでエラーをそのままにせず、奇麗に作りましょう。
A列に連番を振ってあるのは順位を参照するためです。
さっきまで作業をしていたシートは「sheet6」ですね。
奇麗にするためにA列にある連番は別のシートを参照するなどでも良いと思います。
７）項目を集計する
さて、ここまで来ればあとは項目を集計するだけです。
表示回数、クリック数、費用など必要な項目をSUMIFS関数で集計しましょう。
CPCやCTRはSUMIF関数などで集計してしまうととんでもない数値になりますのでお気をつけ下さい。
また掲載順位も加重平均を使って出すようにしましょう。
後は見た目など見やすいように体裁を整えて完成です。
提出する際にはこのまま送ると重すぎて先方のPCで開けないという事態も考えられますので関数を数値として貼り直しましょう。
また、CSV（元データ）を貼り付けたシートも削除しましょう。
貼り付け、テキスト化、シート削除、名前を付けて保存は一連の作業をマクロで済ませると更に楽になりますよ。
お疲れ様でした！
まとめ
こういった単純作業は出来る限り効率的にやりたいものです。
定期的に提出しなければならないレポートや提案書がツールのサポート範囲外でもエクセルを駆使して一度作ってしまえば後はCSVを貼り付けるだけで済んでしまいます。
ただ、頻度が低いものなどは都度作ったほうが早かったりします。
今回はレポーティングに焦点を当てすぎた感がありますが、本質はぱっと見では分からないデータを見やすくする点にあると思います。
もちろん関数以前にどんなデータを見るのか、という前提が定まっていなければいけませんが・・・
しかし知っているに越したことはないと思いますので、知識として覚えておくと効率化に繋がるかもしれませんよ。
