みなさん、こんにちは！
データいじりが大好きなタカハシ(@ntakahashi0505)です。
今や多くの企業や個人がブログやWEBサイトを持って活動をされています。
Googleアナリティクスを開けばページビューや流入比率などはすぐに知ることができるのですが、もう少し突っ込んだ内容について知ろうとする場合、Googleアナリティクスからデータを出力してエクセルなどで集計や加工をするなどの手順が必要になります。
毎回作業が必要となると面倒ですから、あまり使わなくなってしまいます。
ですから逆に、Googleアナリティクスから出力したデータをそのままエクセルにペタっと貼るだけで知りたいことが表示される、という状態になれば次からは楽ちんに分析ができますよね。
この記事を含めていくつかの記事で、エクセルを使ったアクセス解析や分析方法について紹介していければと思います。
カテゴリ別のページビューを知る
どのカテゴリがよく読まれているのか、どのようにして知ることができますでしょうか？
Googleアナリティクスはカテゴリ情報を持っていないので、どのカテゴリが人気があるのかを正確に知るには一工夫が必要です。
ということで、今回はGoogleアナリティクスのデータからカテゴリ別のページビューを集計する方法をお伝えします。
なお、今回紹介する方法は、パーマリンクURLにカテゴリのスラッグが含まれている場合の方法です。そうでない場合の方法は別記事にて後日お伝えできればと思います。
Googleアナリティクスからページごとのデータをエクスポート
ではGoogleアナリティクスからページごとのデータを表示して、表示されたデータをエクスポートする手順について説明していきます。
ページごとのデータ表示と期間の設定
まず、Googleアナリティクスでページごとのページビューを表示させます。
左側のメニューから「行動→すべてのページ」を選択ですね。
この画面でページ単位でのページビューや直帰率などを知ることができます。
画面右上の日付をクリックして集計したい期間を設定してください。今回の例では7月全体を設定しました。
表示する行数の変更
画面にはページ数が上位のページに関するデータしか表示されていませんので、全てのページについて表示しておきます。
というのも、後でエクスポートするのですが、その際に表示されているデータしかエクスポートされませんので。
右下の「表示する行数」から適正な行数を選択して下さい。当ブログの場合はページの数が175でしたので、「250」を選択します。これで全てのページが表示されます。
ページタイトルを表示
今の状態ですとページURLで表示されています。運営サイトに詳しい方ならURLを見ただけでどのページかわかると思うのですが、他の方にはサッパリわからない…そんなときに、ページタイトルも表示しておくと良いです。
「セカンダリディメンション」から「ページタイトル」を選択します。
ページのとなりにページタイトルという列が差し込まれてページタイトルが表示されました。
これでエクスポートの前準備は完了です。
データをエクセル形式でエクスポート
左上の「エクスポート」メニューから「Excel(XLSX)」を選択します。
これで表示されている内容がエクセルファイルとして取得できます。
取得したエクセルはこちらです。ちゃんとページタイトルも入っていますね。
SUMIF関数でカテゴリ別ページビューを算出
Googleアナリティクスから出力したデータをSUMIF関数を使って集計していきます。
出力先シートの準備
まず、集計した結果を入れる出力先のシートを用意しておきます。
このようなシートを作成してください。
カテゴリ名、カテゴリURL、ページビューの３列です。
カテゴリURLはパーマリンクに含まれているカテゴリを表す文字列です。
ページビューの列にはSUMIF関数を使って条件に見合うデータのみを合計した結果を出力します。
検索条件の確認
Googleアナリティクスから出力したデータを再度確認します。
各記事のパーマリンクの作りとして
- スラッシュ「/」で始まり
- その次にカテゴリを表す文字列
- その後は記事ごとの任意の文字列
で構成されていますので、A列が「/」に続いて該当するカテゴリの文字列で始まっているものを検索条件として加算していくという集計をすればOKです。
SUMIF関数でカテゴリ別のページビュー合計を算出
SUMIF関数の使い方とその意味をおさらいしておきますね。
SUMIF(範囲,検索条件,合計範囲)
指定された範囲の中で、検索条件に該当しているデータについて、合計範囲のデータを合計する
セルC2には
=SUMIF(データセット1!A:A,”/”&カテゴリ!B2&”*”,データセット1!C:C)
と入力してください。
最初の要素は「範囲」ですので、検索対象の範囲である「データセット１」シートのA列全体を選択します。
次の要素は「検索条件」で「”/”&カテゴリ!B2&”*”」となっていますね。
「&」は文字列を連結する演算子です。
「*」はワイルドカードと言いまして「何か任意の文字列」を表します。
従って、この検索条件の部分は「スラッシュで始まり、次にカテゴリを表す文字列がきて、その後は何でもよい」という条件を表すことになります。
最後の３つ目の要素は「合計範囲」ですので、合計したいページビューが入っている列、つまり「データセット１」シートのC列全体を選択します。
これでカテゴリ別のページビュー合計が算出できました。
C2セルをC8セルまでコピーすれば完成です。
これでカテゴリ別のページビューの集計が完了です。半分近くが「Excel・VBA活用術」のページビューでしたね。定量的に出すと事象をクッキリ見ることができます。
強いカテゴリにより力を入れていくも良し、弱いカテゴリを強化していくも良しですね。
カテゴリ別ページビューのグラフを作成
これにてカテゴリ別ページビューの集計ができました。さらにひと手間しておけば、グラフも作成することができます。
グラフの範囲を指定
まずグラフの元データとなる範囲を選択しておきます。
カテゴリ名ごとのページビューですので、範囲はA列とC列です。列ごとごっそりでいいので選択します。
棒グラフの挿入
グラフのデータ範囲を選択した状態で、リボンから「挿入→2-D縦棒」です。
これでもうできちゃいます。エクセル、素晴らしいですね～！
グラフにすると数値で見るよりも、パッと見で全体像がわかるようになりますね。
まとめ
Googleアナリティクスからのデータを出力し、SUMIF関数を駆使してカテゴリ別のページビュー合計を表とグラフで確認する方法をご説明しました。
少し手順はありましたが、次回以降、別の期間の集計をするときはもう簡単ですのでご安心下さい。
Googleアナリティクスからの出力データを「データセット１」シートにペタっと貼り付るだけで、自動で表とグラフが算出されます。
これがデータ分析のときにとても大事なポイントですね。一度作ったら次以降も楽しちゃいましょう。
この記事の続編はこちら。
Googleアナリティクスのアドバンスという機能を使って、不要なデータを除外する方法についてお伝えしています。
どうぞお楽しみに！
