本日7月10日は、そう、納豆の日。そんなネバネバした日に私は挙式をしたのですが、納豆とは何ら関係ありません。
ただ納豆のように、「腐る」のではなく「発酵」という有益な化学変化をいつまでも続けて、賞味期限切れを起こさないよう精進していきたいと思います。ちなみに発酵期間は今日で6年経ちました。
さて、本日はそんなネバネバした話ではなく、サクッと傾向分析やデータ集計したいときに活用できるエクセル術をご紹介したいと思います。
データ分析をするにあたって
リスティング広告は様々な項目について分析が可能で、地域、時間帯、性別、年齢、デバイスなどなど、見始めたらいくらでも時間を使えます。
言い方を変えれば、闇雲にデータを見始めれば膨大な時間が必要となってしまいます。
弊社のようにリスティング広告の代理店であれば、一人で数十アカウントを運用している中、一つのアカウントについて何時間も分析に時間をかけてしまってはいくら時間があっても足りません。
今回は分析目的が明確になっていて、その分析をするために使える機能の紹介なので割愛しますが、そもそもこの分析目的が曖昧になっていると、分析手法どうのこうの言う前に、無駄な分析結果になったり、時間の浪費につながってしまいます。
そんな失敗あるあるについての紹介は下記を参照ください。
意味のないデータ分析はもう止めませんか？【リスティング広告】
SUBTOTAL関数
例えばクエリの傾向を見ようと思たとき、管理画面上であればフィルタ機能を使うと便利ですよね。
一方で、CSVレポート上でも同じようなことをしたいと思うことはないでしょうか。
例えば、他社運用などで直接見れないアカウントについてレポートベースで分析するとか、何かしらの傾向が見られるものとそうでないものを比較したいなど。
そんな時はエクセルのフィルタ機能にSUBTOTAL関数を使えば、管理画面と同じような感覚で傾向を見ることができます。
下記の例では、フィルタで「マンション」を含むクエリに絞り込むと、SUBTOTAL関数では絞り込んだものだけの合計値を返してくれます。
SUM関数で集計してしまうとフィルタに関係なく集計範囲全体の合計値となるので、フィルタ機能を使う場合はSUBTOTAL関数を組合わせると使いやすいと思います。
同じ集計範囲でSUBTOTAL集計÷SUM集計とすれば、フィルタ機能で絞り込んだものが全体に占める割合もパッとみることができます。
ちなみにSUBTOTAL関数は下記のような構成となっており、集計方法は合計以外にも平均値、最大値、標準偏差などを求めることもできます。
＝SUBTOTAL(集計方法,参照範囲）
集計方法：１→AVERAGE、２→COUNT、３→COUNTA、4→MAX、5→MIN、6→PRODUCT
７→STDEV.S、８→STDEV.P、9→SUM、10→VER.S、11→VER.P
ただ、ソート機能との併用ですので、ソートを解除、もしくは別条件に変更してしまうとSUBTOTALでの集計値も変わってしまうので、表にまとめたりするには向いてません。
分析の当たりを付けるようなときにサクッと傾向を確認するには使いやすいと思います。
分析した傾向を表にまとめたりする場合はSUMIF、SUMIFS関数を使うことで、上記と同じ分析が可能です。どちらを使っても同じことができますが、個人的にはサクッと傾向を見るのであれば、フィルタ機能とSUBTOTAL関数の方が、あまり関数に慣れてない人でも使いやすいと思います。
SUMIF、SUMIFS関数については下記を参照ください。
【エクセル活用術】データ集計の小技【リスティング広告】
ピボットテーブル
そもそも関数とかよくわからなくて無理！という方にはピボットテーブル機能がおすすめです。
すでに使っている人も多いと思いますし、弊社の過去記事でもピボットテーブルを使った紹介がいくつかありますが、使い方に言及した記事はなかったので、ピボットテーブル使ったことないよ！という方にここで簡単に紹介したいと思います。
そもそもピボットテーブルとは、
データの計算、集計、分析を行う強力なツールです。データを比較してパターン化し、傾向をつかむことが可能になります。※Excelヘルプより
とありますが、大量のデータを自分が見たい項目で傾向が見れるよう整理整頓してくれるもので、作り方も簡単で、サクッと傾向を見たり、簡易的に分析結果を示すにも使いやすいと思います。
作成方法
まず、分析したい項目が含まれるCSVレポートをダウンロードしてきます。
先ほどの例であればクエリレポートになります。そのCSVファイルを開いたら、「挿入」タブ内の一番左にある「ピボットテーブル」ボタンを押します。
すると作成における設定ウインドウが開きますが、はじめはあまり気にせず、OKボタンを押下。
そしてエクセル画面右側に下記のようなフィールド選択画面が表示されるので、見たい項目をチェックしていくと、「行」「列」「値」に割り振られます。
この時、左側のセル領域にはそれに基づきピボットテーブルが自動で生成されていきます。
ここまでできたら、あとは検索語句を任意のワードで絞り込めば、前項で紹介したSUBTOTAL関数+フィルタ機能、およびSUMIF関数と同等の分析が可能となりました。簡単ですね。
項目の並び替えや入れ替えもフィールドリスト上でドラッグ・アンド・ドロップできますし、値の集計方法も「合計」以外にも「平均」や「最大値」「標準偏差」なども見ることができます。
使い込んでいけば、報告にも使えるかたちでまとめるときにも使えるので、少しでも作業に使う時間を減らして、考察や改善施策に頭を巡らす時間を創出できるといいですね。
まとめ
最初にも述べたように様々な分析ができ、また配信設定にも反映できることがリスティングのいいところでもあるので、やはり分析は大事な作業となってまいります。
しかし、分析に時間がかかっていたり、見たいものが見れていなかったらせっかくの努力ももったいないですね。
世界一効率的な代理店を目指す一員として、なるべく工数をかけずに的確な分析ができるよう心掛けていきたいと思います。
