ペタっと貼るだけでGoogleアナリティクスのデータからカテゴリ別集計

★気に入ったらシェアをお願いします!


ハムスターの切手

photo credit: FPM# 29 via photopin (license)

みなさん、こんにちは!
データいじりが大好きなタカハシ(@ntakahashi0505)です。

今や多くの企業や個人がブログやWEBサイトを持って活動をされています。

Googleアナリティクスを開けばページビューや流入比率などはすぐに知ることができるのですが、もう少し突っ込んだ内容について知ろうとする場合、Googleアナリティクスからデータを出力してエクセルなどで集計や加工をするなどの手順が必要になります。

毎回作業が必要となると面倒ですから、あまり使わなくなってしまいます。

ですから逆に、Googleアナリティクスから出力したデータをそのままエクセルにペタっと貼るだけで知りたいことが表示される、という状態になれば次からは楽ちんに分析ができますよね。

この記事を含めていくつかの記事で、エクセルを使ったアクセス解析や分析方法について紹介していければと思います。

カテゴリ別のページビューを知る

どのカテゴリがよく読まれているのか、どのようにして知ることができますでしょうか?

Googleアナリティクスはカテゴリ情報を持っていないので、どのカテゴリが人気があるのかを正確に知るには一工夫が必要です。

ということで、今回はGoogleアナリティクスのデータからカテゴリ別のページビューを集計する方法をお伝えします。

なお、今回紹介する方法は、パーマリンクURLにカテゴリのスラッグが含まれている場合の方法です。そうでない場合の方法は別記事にて後日お伝えできればと思います。

Googleアナリティクスからページごとのデータをエクスポート

ではGoogleアナリティクスからページごとのデータを表示して、表示されたデータをエクスポートする手順について説明していきます。

ページごとのデータ表示と期間の設定

まず、Googleアナリティクスでページごとのページビューを表示させます。

左側のメニューから「行動→すべてのページ」を選択ですね。

Googleアナリティクスでページビューを見る

この画面でページ単位でのページビューや直帰率などを知ることができます。

画面右上の日付をクリックして集計したい期間を設定してください。今回の例では7月全体を設定しました。

表示する行数の変更

画面にはページ数が上位のページに関するデータしか表示されていませんので、全てのページについて表示しておきます。

というのも、後でエクスポートするのですが、その際に表示されているデータしかエクスポートされませんので。

右下の「表示する行数」から適正な行数を選択して下さい。当ブログの場合はページの数が175でしたので、「250」を選択します。これで全てのページが表示されます。

Googleアナリティクスで表示行数を変更

ページタイトルを表示

今の状態ですとページURLで表示されています。運営サイトに詳しい方ならURLを見ただけでどのページかわかると思うのですが、他の方にはサッパリわからない…そんなときに、ページタイトルも表示しておくと良いです。

「セカンダリディメンション」から「ページタイトル」を選択します。

Googleアナリティクスでページタイトルを表示

ページのとなりにページタイトルという列が差し込まれてページタイトルが表示されました。

Googleアナリティクスでページタイトルが表示された

これでエクスポートの前準備は完了です。

データをエクセル形式でエクスポート

左上の「エクスポート」メニューから「Excel(XLSX)」を選択します。

Googleアナリティクスでデータをエクスポート

これで表示されている内容がエクセルファイルとして取得できます。

取得したエクセルはこちらです。ちゃんとページタイトルも入っていますね。

Googleアナリティクスから出力されたデータ

SUMIF関数でカテゴリ別ページビューを算出

Googleアナリティクスから出力したデータをSUMIF関数を使って集計していきます。

出力先シートの準備

まず、集計した結果を入れる出力先のシートを用意しておきます。

このようなシートを作成してください。

カテゴリ別ページビュー集計出力フォーマット

カテゴリ名、カテゴリURL、ページビューの3列です。

カテゴリURLはパーマリンクに含まれているカテゴリを表す文字列です。

ページビューの列にはSUMIF関数を使って条件に見合うデータのみを合計した結果を出力します。

検索条件の確認

Googleアナリティクスから出力したデータを再度確認します。

Googleアナリティクスから出力されたデータ

各記事のパーマリンクの作りとして

  • スラッシュ「/」で始まり
  • その次にカテゴリを表す文字列
  • その後は記事ごとの任意の文字列

で構成されていますので、A列が「/」に続いて該当するカテゴリの文字列で始まっているものを検索条件として加算していくという集計をすればOKです。

SUMIF関数でカテゴリ別のページビュー合計を算出

SUMIF関数の使い方とその意味をおさらいしておきますね。

SUMIF(範囲,検索条件,合計範囲)
指定された範囲の中で、検索条件に該当しているデータについて、合計範囲のデータを合計する

セルC2には

=SUMIF(データセット1!A:A,”/”&カテゴリ!B2&”*”,データセット1!C:C)

と入力してください。

最初の要素は「範囲」ですので、検索対象の範囲である「データセット1」シートのA列全体を選択します。

次の要素は「検索条件」で「”/”&カテゴリ!B2&”*”」となっていますね。

「&」は文字列を連結する演算子です。

「*」はワイルドカードと言いまして「何か任意の文字列」を表します。

従って、この検索条件の部分は「スラッシュで始まり、次にカテゴリを表す文字列がきて、その後は何でもよい」という条件を表すことになります。

最後の3つ目の要素は「合計範囲」ですので、合計したいページビューが入っている列、つまり「データセット1」シートのC列全体を選択します。

これでカテゴリ別のページビュー合計が算出できました。

SUMIF関数でカテゴリ別ページビューを算出

C2セルをC8セルまでコピーすれば完成です。

SUMIF関数の数式をコピー

これでカテゴリ別のページビューの集計が完了です。半分近くが「Excel・VBA活用術」のページビューでしたね。定量的に出すと事象をクッキリ見ることができます。

強いカテゴリにより力を入れていくも良し、弱いカテゴリを強化していくも良しですね。

カテゴリ別ページビューのグラフを作成

これにてカテゴリ別ページビューの集計ができました。さらにひと手間しておけば、グラフも作成することができます。

グラフの範囲を指定

まずグラフの元データとなる範囲を選択しておきます。

カテゴリ名ごとのページビューですので、範囲はA列とC列です。列ごとごっそりでいいので選択します。

カテゴリ別ページビューのグラフ範囲選択

棒グラフの挿入

グラフのデータ範囲を選択した状態で、リボンから「挿入→2-D縦棒」です。

カテゴリ別ページビューの棒グラフ作成

これでもうできちゃいます。エクセル、素晴らしいですね~!

グラフにすると数値で見るよりも、パッと見で全体像がわかるようになりますね。

まとめ

Googleアナリティクスからのデータを出力し、SUMIF関数を駆使してカテゴリ別のページビュー合計を表とグラフで確認する方法をご説明しました。

少し手順はありましたが、次回以降、別の期間の集計をするときはもう簡単ですのでご安心下さい。

Googleアナリティクスからの出力データを「データセット1」シートにペタっと貼り付るだけで、自動で表とグラフが算出されます。

これがデータ分析のときにとても大事なポイントですね。一度作ったら次以降も楽しちゃいましょう。

この記事の続編はこちら。

Googleアナリティクスのデータを特定条件で絞り込むアドバンスの使い方
Googleアナリティクスからのデータに含まれているプレビューや見つかりませんページなどの余計なデータを、特定条件でデータを絞り込むアドバンスセグメントを使って除外する方法についてお伝えします。

Googleアナリティクスのアドバンスという機能を使って、不要なデータを除外する方法についてお伝えしています。

どうぞお楽しみに!

連載目次:Googleアナリティクスのデータをペタっと貼るだけの集計分析

  1. ペタっと貼るだけでGoogleアナリティクスのデータからカテゴリ別集計
  2. Googleアナリティクスのデータを特定条件で絞り込むアドバンスの使い方
  3. 平均って種類があるの…?!重みがある場合の加重平均とその算出方法
  4. エクセルデータ分析の強い味方!初心者向けSUMPRODUCT関数の使い方
  5. 条件付きSUMPRODUCT関数の使い方とよく出くわすエラーの回避方法
  6. Googleアナリティクスのデータを貼り付けるだけで瞬時にカテゴリ別分析
  7. Googleアナリティクスデータを貼るだけでカテゴリ別分析結果のグラフも出す
  8. ビジネスマン必携!あらゆる場面で活用できるパレートの法則とパレート図の作り方

The following two tabs change content below.
1976年こどもの日生まれ。東京板橋区在住。「ITで日本の『働く』の価値を上げる!」をテーマに、VBA&GASの開発、講師、コンサル、執筆本を中心に活動しています。→詳しいプロフィールはコチラ ★ご依頼・ご相談はお気軽にどうぞ!→お問い合わせはコチラ ★フォロー頂ければ嬉しいです。