Googleアナリティクスの初期設定のままで、Excelを使って曜日時間別分析をする方法(力技)

先日、Web担当者フォーラムさんの「Excelのデータ表を劇的に見やすくするカラースケールやデータバーが便利すぎて驚いた」という記事で、データ分析にExcelの「条件付き書式」が使える!!!って取り上げられてました。

データ分析っていえば、やっぱり、ウェブ解析だわよね、っと思う今日この頃の見習。実は、ウェブ解析っぽいことしている見習は、ちょうど、この条件付き書式をこねくり回してたところだったので、今後困らないように、備忘録です。

結構、アクセス数が、曜日とか、時刻に左右されそうなサイトに関わっているので、曜日と時刻をクロスさせた表を作って分析してみます。

ggex22

じゃじゃ~ん。こんな風なのです。(中の数値については、深く追求しないでくらさい…)

ホントは、Googleアナリティクスの機能を駆使すれば、Excelでこねくり回さなくても行けそうな気もしますが、Excelのほうだけで決着つけました、力技だけど(Excel2007使用)。

★流れ

  1. 時間別に訪問者数をエクスポート。《Googleアナリティクス》
  2. オートフィルと関数を使って、曜日と時間のデータを付ける。《Excel》
  3. ピボットテーブルで、曜日と時間をクロスさせる。《Excel》
  4. 条件付き書式の「カラースケール」で色分けする。《Excel》

1.時間別に訪問者数をエクスポート。《Googleアナリティクス》

まずはGoogleアナリティクスにログインして、データをエクスポートします。

「訪問数」になっている。

「訪問数」になっている。

エクスポートしたい期間を選択後、左上の部分が、「訪問数」になっていることを確認します。

「時間別」をクリックする。

「時間別」をクリックする。

それから、右上のところで、「時間別」をクリックします。これで、毎日の1時間ごとのデータを取得できます。あとは、「エクスポート」から「Excel(XLSX)」を選択してエクスポートするだけ。

ggex02

2.オートフィルと関数を使って、曜日と時間のデータを付ける。《Excel》

残りの作業は、Excelのみです。でも、いきなり、さっきのデータをExcelで開くと…。

がーん。000000って…、一体…。

000000って…、一体…。

ががーん。A列に入っているのは、「000000」「000001」とか、単なる連番…。とほほ。

今回は、10月1日の0:00からのデータを取得したはずなので、「000000」は、10月1日0:00、「000001」は、10月1日1:00のことです。まずは、この連番を、きちんとした日付と時刻に戻してあげる必要があります。そして日付は曜日に変換してあげないと…。

B列の前に作業するために3列ほど列挿入をしておきます。

ggex0000

B1に「1:00」、B2に「2013/10/1」、B3に「$B$1+B2」と入力します。

ggex0001

そして、B3を選択した状態で、右下の「黒ぽち」をダブルクリックして、オートフィルします。これで、B列に、10月1日の0:00から始まる1時間刻みの連続データができました。

あとは、日付を曜日と時間に分けて、それぞれのデータにします。

日付も時刻も、そのまま「表示形式」を整えて、曜日や時刻が表示されるようにすればいいかなと思ったのですが、そうは問屋が卸してくれません。表示形式で整えただけだと、ピボットテーブルでうまいことまとめようと思っても、もとのデータで分類してしまいます。たとえば、日付を表示形式で「月」としても、本当のデータは、それぞれの日付なので、すべての月曜日のデータをひとつのものとして、まとめ上げてくれません。データそのものを曜日にしてあげる必要があります。また。時刻についても、「0:00」という文字データにしてあげないと…。

というわけで使ったのがTEXT関数。指定した表示形式の文字列にしてくれる関数です。

C2に「=text(B2,”aaa”)」と入力して、オートフィル。

D2に「=text(B2,”hh:mm”)」と入力して、オートフィルします。

ggex0002

これで、必要なデータはそろいました。

3.ピボットテーブルで、曜日と時間をクロスさせる。《Excel》

曜日列、時刻列、訪問者数列を選択して、ピボットテーブルにします。

C1をクリックしてから、【Shift】と【Ctrl】を押下した状態で、【↓】と【→】を順に押します。これで、一気にE列の最終行まで範囲選択できます。

ggex0003

範囲選択したら、「挿入」タブにある「ピボットテーブル」→「ピボットテーブル」をクリックします。

「ピボットテーブルの作成」ダイアログボックスが出るので、OKをクリック。

ggex17

図のように、列ラベルに「時刻」、行ラベルに「曜日」、「値」に「訪問数」を設定します。これでもOKかもですが、現状だと、訪問数の合計が表示されています。エクスポートした期間の区切りの関係で、各曜日の日数が違うことがあります。とくに月単位で区切っていたりすると。ということで、「1日あたりの平均訪問数」で比較するのが、いいかしらん。

ggex18
「合計/訪問数」のところをクリックします。
ggex19

「値フィールドの設定」を選択し、「平均」にして、OKします。

ggex23

列幅を調整してあげると…。おおっ、いいかんじ。

4.条件付き書式の「カラースケール」で色分けする。《Excel》

ggex21

あとは、色分け。B5:Y11を範囲選択して、「ホーム」タブにある「条件付き書式」→「カラースケール」と選んでいきます。どの色のでもOKですが、右上のは、訪問数が多いとき、赤っぽくなって、少ないとき青っぽくなるので、訪問数のイメージと一致する気がします。

ggex22

ということで、できたのが、これ。すんごい、力技だけど、なんとか、でけた。ほっ。