ExcelでGoogleアナリティクスでエクスポートしたデータでヒストグラムを作る
おほほほほ。Excelでヒストグラムが作れてうれしい見習です。
勢いのって、Googleアナリティクスのデータでできるかな？？？と試してみました(Excel2016使用)。
こんなの作ってみました。
Googleアナリティクスのデータでヒストグラムを作るとこんな風です。
Googleアナリティクスでデータをエクスポートする
まず、どんなデータでヒストグラムを作りたいのか･･･。
たとえば、こんなのがありそうです。
先月の日別セッション数は、どんな感じになっているのか。それに、新規ユーザーとリピーターで比較して見たい。
まずは、Googleアナリティクスにログインし、エクスポートするデータを整えます。
- 期間を設定
- 比較したい指標を設定
- 比較したいセグメントを設定
- 区切りたい期間を設定
今回は
- 期間を設定(2018/6/1-2018/6/30)
- 比較したい指標を設定(セッション)
- 比較したいセグメントを設定(新規ユーザー、リピーター)
- 区切りたい期間を設定(日)
を設定します。そしたら、
[エクスポート] → [Excel(XLSX)]と進み、エクスポートされたExcelを「名前をつけて保存」し、開きます。
Excelで作業する
複数のシートから構成されているので、「データセット2」シートを開きます。
最初に、合計行（A62:D63）を削除しておきます。
★ピボットテーブルを作る
データを選択した状態で、[挿入]タブ→[ピボットテーブル]へと進みます。
[データ範囲]を確認し[OK]します。
ピボットテーブルの設定は、以下のとおりにします。
- 列「セグメント」
- 行「日の指標」
- 値「セッション」（合計）
★ヒストグラムの作成
データ区間を検討する
※上記で作成した「ピボットテーブル」のシートで作業します。
Min関数とMax関数を使い、最小値と最大値を確認します（関数中に、B列C列の合計行が入らないように注意）。
[ホーム]→[フィル]→[連続データの作成]で
範囲「列」、種類「加算」、増分値「20」(ヒストグラムでの1メモリの幅)、停止値「220」(Max関数の209から)を入力し、OKします。
ヒストグラム作成
ピボットテーブルをクリックした状態で[データ]→[データ分析]→[ヒストグラム]→[OK]とし、ヒストグラムのダイアログボックスを出します。
[ヒストグラム]のダイアログボックスでは、
[入力元]として、[入力範囲]に「リピーター」のデータ[$B$5:$B$34]、
データ区間として[$B$5:$B$34]、
[新規ワークシート][グラフ作成]にチェックをいれて、[OK]します。
※リピーターのヒストグラムが出力されるので、B1のセルを「リピーター」と入力しておきます。
ピボットテーブルが出力されたシートに戻り
ピボットテーブルをクリックした状態で[データ]→[データ分析]→[ヒストグラム]→[OK]とし、ヒストグラムのダイアログボックスを出します。
[ヒストグラム]のダイアログボックスで、
[入力元]として、
[入力範囲]に「新規ユーザー」のデータ[$B$5:$B$34]、
データ区間として[$B$5:$B$34]、
出力先に、さきほど「リピーター」のヒストグラムが出力されたシートのC1セルを入力し、
[グラフ作成]にチェックをいれて、[OK]します。
※新規のヒストグラムが出力されるので、D1のセルを「新規」と入力しておく。
「リピーター」と「新規」の2つのヒストグラムが出来上がるので、「リピーター」のヒストグラムをクリックしアクティブにします。
[デザイン]タブの[データの選択]→[データソースの選択]で[編集]をクリックします。
[系列名]に見出し行の[B1]、
[系列値]にデータの範囲[B2:B13]を入力（「次の級」のセルを範囲から外す）して、[OK]をクリックします。
次に、[追加]をクリックし、「新規」のデータを足します。
[系列名]に見出し行の[D1]、
[系列値]にデータの範囲[D2:D13]を入力（「次の級」のセルを範囲から外す）して、[OK]をクリックします。
「リピーター」と「新規」がひとつのヒストグラムになりました。
ヒストグラムの見た目を整えます
ヒストグラムを選択し[書式]タブ[系列”リピーター”]→[選択範囲の書式設定]で[データ系列の書式設定]から[系列の重なり]を100%[要素の間隔]を0％にします。
また、[塗りつぶし]を[塗りつぶ(単色)]にし、任意の色を設定、[透明度]を60%程度にします。
続けて、ヒストグラムを選択し[書式]タブ[系列”リピーター”]→[選択範囲の書式設定]で[データ系列の書式設定]から[系列の重なり]を100%[要素の間隔]を0％にします。
また、[塗りつぶし]を[塗りつぶ(単色)]にし、任意の色を設定、[透明度]を60%程度にします。
データの書式設定
※系列の重なりと要素の間隔の設定で、2つの系列が重なってしまいます。
透明度を下げることにより、2つの系列に同じデータ系列にデータがあったときも透けて見えます。
というわけで、できました！
たぶん、これでいいはず･･･。とどきどきする見習です。みなさま、ごきげんよー。
森歩き日記
自称パソプロ化計画
