GoogleAnalyticsで長期のコホート分析をなるべく自動でやってみた

Last updated at 2020-12-16Posted at 2020-12-16

XTechグループ Advent Calendar 2020の17日目です！

iXIT株式会社　松山がお送りします。
ちょうどあと一週間でクリスマスイブですね
私はサンタさんにWeb会議に耐えうるWi-Fi環境をお願いしようと思ってます

《ちょっとだけ自己紹介》
iXITでエンジニアをやっている新卒2年目です。
主に占いサイトの運用保守・開発をやっています。

サーバーサイド(PHP)がメインですが、フロント(HTML/CSS/JavaScript)やアクセス解析(GoogleAnalytics)など、サイト全体に携わらせていただいております。

はじめに

本記事は、一般的なWEBサイトアクセス解析ツールであるGoogleAnalytics（以下GA）を使って、
ある広告から来てくれたユーザーが翌月以降もアクセスしてくれているのか
を知れるコホート分析の作成方法を紹介します。

コホート分析とは

コホート分析とは、ユーザーの行動をグループ化し、指標ごとに数値化して分析するというものです。
例えば「7月1日にセッションを開始したユーザー」をグループとして、そのグループの何割が再訪したかなどを分析します。
参考）コホート分析とは? | Googleアナリティクスでユーザーの維持率をチェック

実は、GAにコホート分析機能はありますが、3か月までしか追えないです…

そのため、GAから数値を取得し、
Googleスプレッドシート（以下スプレッドシート）で計算しました。

ざっくり手順

「GoogleAnalytics API Add-on」を使って、スプレッドシートとGAを連携し、数値を自動取得
1.で作ったシートから必要な数値を「GoogleAppsScript(以下、GAS)」を使って一つのスプレッドシートにまとめる

事前準備

GAを閲覧できるGoogleアカウントのみにログインするウィンドウを作成
- 画像赤丸のように、ウィンドウのアカウントとタブ内でログインしているアカウントが異なっていると認証周りで上手くいかないことがあります
スプレッドシートに「GoogleAnalytics API Add-on」を追加し、Report Configurationシートを作成
- 基本的な使い方はこの辺りを参考にしてください
- 参考）【初級編】Google Analyticsのデータを完全自動でレポートにまとめる方法、ご存知ですか？

手順

1)GAでセグメントを設定

例)1月中に、ある広告経由で初めて来たユーザーのセグメント設定
- ここが間違っていると正しい数値が取れないので、数値を確認しながら設定するとGood!

2)「GoogleAnalytics API Add-on」用のセグメント定義文を作成

GAの開発者向けサイトでAPI定義文に変換
セグメントを選択し、「Show segment definitions instead of IDs.」にチェック。その値を使います
- ※そのままだとRun reportsの際、以下のようなエラーとなります…
  
  1 report failed due to errors　MUU: 次のエラーが発生し、analyticsreporting.reports.batchGet の呼び出しに失敗しました: Segment 4OFkjF_-ST61K17KjELjqw is not supported in the API.
  - GoogleApiの仕様で期間を設定したセグメントなど高度なセグメントはエラーになるようです
  - Google Analytics Spreadsheet Add-on|FAQs

3)スプレッドシートを作成

Report Configurationシートを以下の内容で入力して「Create Report」
- Metrics：Users
- Dimensions：Month of the year
- Segments：事前準備で設定したセグメント定義文
```
![2レポート設定.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/532045/59cadb42-5746-3dae-4530-79f53c22cdd9.png)
```
Report Configurationシートに戻り、「StartDate」「EndDate」を記載
- EOMONTH()関数を使って入力すると楽です
「アドオン > Google Analytics > Run reports」を選択

しばらく待つとレポートが取得できます
「アドオン > Google Analytics > Schedule reports」を選択し、自動更新を設定
作成されたレポートからコホート分析を行う
- [継続率]シートを作成し、そこで計算する
  - コピペでもいいけど、流入月が変わっていくので、関数でうまいことやりましょう
新しくスプレッドシートを作成し、同様の手順で初回アクセス月または流入元を変更したセグメントでレポートを設定
- １つのスプレッドシートで複数のセグメントの数値を取得可能ですが、管理の面で分けています

4)スプレッドシートIDをまとめたスプレッドシートを作成

分析するために、一つのシートで全ての数値を閲覧したくなったので、一つにまとめていきます。
スプレッドシートの利点として、別のスプレッドシートから値を参照することができます。

構文
IMPORTRANGE("スプレッドシートキー", "範囲の文字列")
『スプレッドシートキー』というところに値を参照したいスプレッドシートのURLを入力します。

※スプレッドシートキーとは、スプレッドシートのURLを指した名称で、以下の例でいう「××××」の部分のことです。
（例：https://docs.google.com/spreadsheets/d/××××/edit#gid=0）

参考）【超便利】スプレッドシートで別シートから参照したり集計したりする方法まとめ

今回はさらにGASで別シートから参照するために、SpreadsheetAppクラスのopenByIdメソッドを使います。


SpreadsheetApp.openById(id);

参考）GASで操作するスプレッドシートのワークブックを指定する３つの方法

新規でスプレッドシートを作成
[setting]シートを作成
E列に作成したいシートの名前、F列にスプレッドシートIDを空白行なく記載

5)GASを作成

GASについての基本的な説明は省略させていただきます。

■処理概要

[setting]シートのF列の値を使って、別のスプレッドシートから[継続率]シートの数値をコピー
[tmp]シートをコピーしてシート名を変更
1.でコピーした数値を貼り付け
[setting]シートの行数分、1~3の処理を繰り返す

// 実行する関数
function myFunction() {
    // スプレッドシートID一覧を取得
    var sheetIDArray = set_sheetId();

    // スプレッドシートIDの数だけ処理を繰り返す
    for (let i = 0; i < sheetIDArray.length; i++) {

        // スプレッドシートIDから指定のスプレッドシートを読み込む
        var spreadsheet = SpreadsheetApp.openById(sheetIDArray[i]);
        var sheet = spreadsheet.getActiveSheet();
        // [継続率]シートの値を取得
        var values = sheet.getRange('継続率!A1:E16').getValues();

        //　[継続率]シートの記述から、作成するシート名を定義
        var newSheetName = values[1][2] + "_" + Utilities.formatDate(values[2][2], "JST", "YYYY.MM");

        // 定義したシート名のシートを作成
        var newSheet = set_sheet(newSheetName);

        // 作成したシートに取得した値を貼付け
        newSheet.getRange(1, 1, 16, 5).setValues(values);
    }
}

function set_sheetId() {
    // [setting]シートを読み込む
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = spreadsheet.getSheetByName('setting');

    // F列（スプレッドシートID記載）の値を全て取得
    const FValues = sheet.getRange('F:F').getValues();

    // 空白の要素を除いた長さを取得
    const LastRow = FValues.filter(String).length;

    // スプレッドシートIDが記載されているセルを全て取得
    var valArray = sheet.getRange('F2:F' + LastRow).getValues();

    return valArray;
}

function set_sheet(name) {
    //同じ名前のシートがあればシート名を返却
    var sheet = SpreadsheetApp.getActive().getSheetByName(name);
    if (sheet)
        return sheet;

    // [tmp]シートを読み込む
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const tmpSheet = ss.getSheetByName('tmp');

    // [tmp]シートのコピーを作成
    var newSheet = tmpSheet.copyTo(ss);

    // 作成したシートのシート名を"name"に設定
    newSheet.setName(name);

    // シート名を返却
    return newSheet;
}