スプレッドシートでGoogleアナリティクスのデータを自動で毎日取得する

スプレッドシートでGoogleアナリティクスのデータを自動で毎日取得する

スプレッドシートを使用して、Googleアナリティクスの数字を自動で取得します。Googleアナリティクスから自分が必要な数字だけを抽出してスプレッドシートに取得することで、効率よく数字を把握することができます。数字をスプレッドシートに集約して見ると大変見やすいのでオススメです。
「カワリニ」編集部

フリーランスでエンジニアをしている、ベルトトルトです。
前回、下記の記事でスプレッドシートにWordPressの記事を一覧で出力する内容を紹介しました。

WordPressとスプレッドシートを連携して記事を一覧で管理する

今回は書いた記事に対して、PVなどの数字をスプレッドシートで合わせて管理し、なおかつ、数字を毎日自動で取得するような仕組みを作る方法を紹介します。

なお、本記事では前回の記事で作成したスプレッドシートをそのまま使用します。

スプレッドシートの記事一覧

また、Googleアナリティクスを導入済みであることを前提としています。

スプレッドシートにGoogleアナリティクスのアドオンをインストール

まず、アドオンをインストールします。
スプレッドシートの「アドオン」メニューから、「アドオンを取得」を選択します。

「Google Analytics」で検索し、検索結果から「Google Analytics」を選択します。

G Suite Marketplace

「インストール」ボタンをクリックします。

G Suite MarketplaceのGoogleアナリティクス アドオン

インストールには権限が必要になりますので「続行」リンクをクリックします。

Googleアナリティクスアドオンのインストール準備

Googleアカウントを選択します。

Googleアカウントの選択

アクセスを許可する内容が表示されるので、「許可」ボタンをクリックします。

Googleアナリティクスアドオンのアクセス許可

以下の画面が表示されれば、インストール完了です。

Googleアナリティクスのインストール完了

インストールが完了すると、スプレッドシートの「アドオン」メニューに「Google Analytics」が表示されます。

Googleアナリティクスのデータを取得する設定

続いて、Googleアナリティクスからデータを取得する設定を行います。
スプレッドシートの「アドオン」メニューから、「Google Analytics」→「Create new report」を選択します。

スプレッドシートの右側に以下のような画面が表示されます。

Googleアナリティクスのアドオン create new report

以下のように入力します。

1) Name your report

レポートの名前を入力します。
自分が分かりやすい名前を入力すればOKです。

2) Select a view

自身に権限が付与されているGoogleアナリティクスのアカウント情報を選択することが可能です。
対象のアカウント、プロパティ、ビューをそれぞれ選択します。

3) Choose configuration options

メトリクス、ディメンション、セグメントを選択します。
今回は以下のように選択しました。

Googleアナリティクスアドオンのメトリクスとディメンションを選択

  • メトリクス:ページビュー数、ユーザー
  • ディメンション:ページ
  • セグメント:指定なし

すべて入力できたら、「Create Report」ボタンをクリックします。
すると、「Report Configuration」という名前のシートが作成されます。

Report Configuration

このシートがGoogleアナリティクスのアドオンで使用する設定用のシートになります。
これで取得する設定が完了です。

Googleアナリティクスのデータを取得する

設定が完了したので、実際にデータを取得してみます。
取得するにはスプレッドシートのアドオンメニューから、「Google Analytics」→「Run reports」を選択します。

選択すると、取得する処理が開始され、以下の画面が表示されれば完了です。

Googleアナリティクスのデータを取得する

ダイアログを閉じると、「ページごと」というシートが作成されているのが確認できます。
このシート名は、先ほど設定を行った際にレポートの名前で設定したものです。

Googleアナリティクスアドオンのレポート結果

これで、ページ(ディメンション)ごとに、ページビュー数とユーザー(メトリクス)が取得できました。

Googleアナリティクスからデータを取得する条件を設定する

ひとまずデータを取得することができましたが、さらにデータを取得する条件を設定することが可能です。

レポートの設定が記述された「Report Configuration」シートに戻ります。
「Report Configuration」シートでは、以下の内容を設定することができます。

  • Start Date
    データの取得期間の開始日を指定
    2020/06/01といった固定日の指定や、today、yesterday、NdaysAgo(nは動的、30日前にしたい場合は30daysAgo)という指定ができます。

  • End Date
    Start Dateと同じ。

  • Metrics / Dimensions
    先ほど、設定を行いましたが、シート上で直接入力することが可能です。
    直接入力する場合は、「ga:xxxxxx」という形式で入力し、複数指定する場合はカンマ区切りで指定します。
    例えば、ユーザーの場合、ga:users となります。
    主要な項目の名前は以下「メトリクスとディメンションの項目名」で書いていますのでご参照ください。

  • Order
    並び順を指定する項目の名前を入力し、複数指定する場合はカンマ区切りで指定します。
    例えば、ユーザーを並び順に指定する場合、ga:users を入力します。
    並び順はデフォルトが昇順で、降順に並べたい場合は頭に半角ハイフンを付けます。
    ユーザーの降順に並べる場合、-ga:users となります。

  • Filters
    取得する条件を指定することができます。以下の演算子が指定可能です。
    ①完全一致:==
    ②一致しない:!=
    ③文字列の一部に一致:=@
    ④文字列の一部に一致しない:!@
    ⑤ 正規表現の一致を含む:=~
    ⑥次の正規表現に一致する場合を除く:!~

    例えば、
    ga:pagepath==/login.html
    と指定すると、ページが「/login.html」と完全一致するページが対象となります。

  • Segments
    セグメントもメトリクス、ディメンションと同様にシート上で直接入力することが可能です。
    (例)
    新規ユーザー:gaid::-2
    リピートユーザー:gaid::-3

  • Limit
    取得する件数を指定します。
    1000 と入力すると、最大1000件で取得します。

上記のように細かい条件が指定可能です。
今回は、以下のように条件を変更、追加してみます。

  • Start Date:2020/06/01
  • Metrics:ga:users,ga:pageviews,ga:bounceRate,ga:exitRate,ga:avgTimeOnPage(直帰率、離脱率、平均ページ滞在時間を追加)
  • Order:-ga:users(ユーザーの降順)
  • Filters:ga:medium==organic(メディアがorganicと完全一致するもの)

再度、「Run Reports」から実行すると、以下のように条件が反映された形で取得できました。

Googleアナリティクスアドオンの取得結果

メトリクスとディメンションの項目名

以下に代表的なものを記載します。

▼ユーザー

  • ユーザータイプ:ga:userType
  • セッション数:ga:sessionCount
  • ユーザー:ga:users
  • 新規ユーザー:ga:newUsers
  • セッション/ユーザー:ga:sessionsPerUser

▼セッション

  • セッション:ga:sessions
  • 直帰率:ga:bounceRate
  • セッション時間:ga:sessionDuration

▼トラフィック

  • 完全なリファラー:ga:fullReferrer
  • キャンペーン:ga:campaign
  • 参照元:ga:source
  • メディア:ga:medium
  • 参照元/メディア:ga:sourceMedium
  • キーワード:ga:keyword
  • ソーシャル ネットワーク:ga:socialNetwork

▼ページトラッキング

  • ページ:ga:pagePath
  • ページ タイトル:ga:pageTitle
  • ランディング ページ:ga:landingPagePath
  • ページビュー数:ga:pageviews
  • ページ/セッション:ga:pageviewsPerSession
  • ページ別訪問数:ga:uniquePageviews
  • 平均ページ滞在時間:ga:avgTimeOnPage
  • 離脱率:ga:exitRate

▼イベントトラッキング

  • イベント カテゴリ:ga:eventCategory
  • イベント アクション:ga:eventAction
  • イベント ラベル:ga:eventLabel
  • 合計イベント数:ga:totalEvents

▼カスタム ディメンション / 指標

  • カスタム ディメンション:ga:dimensionXX(例 インデックスが「1」の場合、ga:dimension1)
  • カスタム指標:ga:metricXX

▼時刻

  • 日付:ga:date

▼ユーザーの分布

  • 年齢:ga:userAgeBracket
  • 性別:ga:userGender

▼チャネルグループ

  • デフォルト チャネル グループ:ga:channelGrouping

上記以外は公式リファレンスをご参照ください。
※公式リファレンス(英語)
https://ga-dev-tools.appspot.com/dimensions-metrics-explorer/

Googleアナリティクスからデータを取得する処理をスケジューリングする

ここまでGoogleアナリティクスからデータを取得するには「Run Reports」を実行していましたが、取得する処理をスケジューリングすることが可能です。

Googleアナリティクスの数字は毎日確認したいので、1日1回取得するようにスケジューリングしてみます。
スケジューリングするには、スプレッドシートの「アドオン」メニューから、「Google Analytics」→「Schedule reports」を選択します。

ダイアログが表示されるので、以下のように「Enable reports to run automatically.」にチェックを入れます。
チェックを入れるとスケジュールが入力できるようになるので、今回は「every day」で「4 a.m. – 5 a.m.」を選択しました。

データの取得をスケジューリングする

これで毎日、午前4時から5時の間にデータを取得するようにスケジューリングできます。

スケジューリングは、毎時、毎週何曜日、毎月何日などが設定可能です。

記事の一覧とGoogleアナリティクスの数字を紐付ける

さて、ここまで出来たら、前回作成した記事の一覧とGoogleアナリティクスの数字を紐付けます。

まず、記事一覧のヘッダを記述します。
前回は、記事ID〜更新日までを記述していましたが、以下のようにユーザー(I列)以降を追加しました。

記事一覧のヘッダー

そして、それぞれの列に関数を埋め込んで、先ほど取得したGoogleアナリティクスの数字と紐付けます。
以下がユーザーの場合の例です。

=ARRAYFORMULA(IF($A2:$A<>"",IFERROR(VLOOKUP($C2:$C,'ページごと'!$A$16:$F,2,false),0),""))

VLOOKUP関数で、先ほど取得したGoogleアナリティクスの数字のシートを参照しています。
また、該当する結果がない場合はIFERROR関数で「0」とするのと、IF関数で記事IDが入っている行のみ対象としています。
そして、ARRAYFORMULA関数で、各行同じ数式を使用するようにしています。

ページビュー、直帰率、離脱率は、同様の数式とし、VLOOKUPで参照する列が変わるだけとなります。

平均ページ滞在時間(秒)は以下のようにROUND関数を追加して、四捨五入する処理を追加します。

=ARRAYFORMULA(IF($A2:$A<>"",IFERROR(ROUND(VLOOKUP($C2:$C,'ページごと'!$A$16:$F,6,false)),0),""))

    そして、Googleアナリティクスから取得できる数字は秒のため、N列で以下のように分秒の形式に変換します。

    =ARRAYFORMULA(IF(M2:M<>"",INT(M2:M/60)&"分"&MOD(M2:M,60)&"秒",""))

    また、直帰率、離脱率は、実数で取得しているため、スプレッドシートの書式を%表記にします。

    ここまで完了すると、最終的には以下のような形式となり、記事の内容とGoogleアナリティクスの数字を紐付けて一覧化できます。

    記事の内容とGoogleアナリティクスの数字を紐付け

    スプレッドシートでGoogleアナリティクスのデータを自動で毎日取得して、効率的に記事の数字を把握する

    スプレッドシートでGoogleアナリティクスのデータを自動で毎日取得し、記事の内容と紐付けて一覧化する方法を紹介しました。

    Googleアナリティクスのデータも毎日自動で取得でき、一覧で記事の内容と一緒にGoogleアナリティクスの数字を見ることで、どの記事が伸びているか、逆に伸びていない記事はどれか、ということが効率的に把握できるようになります。

    記事を書いた後、その内容がどう見られているかを把握することは非常に重要ですので、効率良くGoogleアナリティクスの数字を把握してみてはいかがでしょうか?

     

    ギルドプロジェクトではシステム、サービスの導入や、サービス間を連携するプログラムの作成などを通して業務効率化を行っており、様々な効率化事例から業務効率化を代行する「カワリニ」というサービスを展開しています。

    お問い合わせは無料で対応させていただいていますので、専門の方がいらっしゃず、どうしたら良いか分からないなどお困りの場合は、お気軽にお問い合わせください。

    カワリニに相談する