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

[XTechグループ Advent Calendar 2020](https://qiita.com/advent-calendar/2020/xtech-group)の17日目です！

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

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

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

# はじめに

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

### コホート分析とは

コホート分析とは、ユーザーの行動をグループ化し、指標ごとに数値化して分析するというものです。
例えば「7月1日にセッションを開始したユーザー」をグループとして、そのグループの何割が再訪したかなどを分析します。
参考）[コホート分析とは? | Googleアナリティクスでユーザーの維持率をチェック](https://wacul-ai.com/blog/access-analysis/google-analytics-method/cohort-analysis/#:~:text=%E3%82%B3%E3%83%9B%E3%83%BC%E3%83%88%E5%88%86%E6%9E%90%E3%81%A8%E3%81%AF%E3%80%81%E3%83%A6%E3%83%BC%E3%82%B6%E3%83%BC,%E8%AA%AC%E6%98%8E%E3%81%97%E3%81%A6%E3%81%84%E3%81%8D%E3%81%BE%E3%81%99%E3%80%82)

実は、GAにコホート分析機能はありますが、3か月までしか追えないです…
![2GAコホート分析.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/532045/8da92cf8-8a28-062a-d22a-258eb8eeefc1.jpeg)


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

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

# 事前準備

- GAを閲覧できるGoogleアカウントのみにログインするウィンドウを作成
    - 画像赤丸のように、ウィンドウのアカウントとタブ内でログインしているアカウントが異なっていると認証周りで上手くいかないことがあります
    
         ![無題.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/532045/78c5c8db-ab42-5844-ea4b-6b2c121fa81a.png)

- スプレッドシートに「GoogleAnalytics API Add-on」を追加し、Report Configurationシートを作成
    - 基本的な使い方はこの辺りを参考にしてください
    - 参考）[【初級編】Google Analyticsのデータを完全自動でレポートにまとめる方法、ご存知ですか？](https://www.mk-design.co.jp/blog/2018/05/marketing-google-analytic-spreadsheet-report-automation)


# 手順
### 1)GAでセグメントを設定
- 例)1月中に、ある広告経由で初めて来たユーザーのセグメント設定
![2セグメント設定.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/532045/e1ebcf15-2f32-e309-21ec-54d3d95e26c9.png)

    - ここが間違っていると正しい数値が取れないので、数値を確認しながら設定するとGood!

### 2)「GoogleAnalytics API Add-on」用のセグメント定義文を作成
- [GAの開発者向けサイト](https://ga-dev-tools.appspot.com/query-explorer/)でAPI定義文に変換
- セグメントを選択し、「Show segment definitions instead of IDs.」にチェック。その値を使います
    - ※そのままだとRun reportsの際、以下のようなエラーとなります…
    
        > <font color="DeepPink">1 report failed due to errors　MUU: 次のエラーが発生し、analyticsreporting.reports.batchGet の呼び出しに失敗しました: Segment 4OFkjF_-ST61K17KjELjqw is not supported in the API.</font>
        - GoogleApiの仕様で期間を設定したセグメントなど高度なセグメントはエラーになるようです
        - [Google Analytics Spreadsheet Add-on|FAQs](https://developers.google.com/analytics/solutions/google-analytics-spreadsheet-add-on#faqs)

### 3)スプレッドシートを作成
2. 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)


3. Report Configurationシートに戻り、「StartDate」「EndDate」を記載
    - EOMONTH()関数を使って入力すると楽です
          ![レポート設定.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/532045/1f2618ed-12f1-370c-82f9-982b181ecd08.jpeg)

4. 「アドオン > Google Analytics > Run reports」を選択
     ![runreports.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/532045/0d13282b-06c6-93e1-cc8e-18edb01ad339.jpeg)
   しばらく待つとレポートが取得できます
    ![2レポート例.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/532045/77e1b8d7-69a4-6b98-5407-9164ad724908.jpeg)

7. 「アドオン > Google Analytics > Schedule reports」を選択し、自動更新を設定
8. 作成されたレポートからコホート分析を行う
    - [継続率]シートを作成し、そこで計算する
        - コピペでもいいけど、流入月が変わっていくので、関数でうまいことやりましょう
        
      ![2まとめ例.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/532045/5e51de99-8e51-26b4-5e0f-6c2aa99ed78b.png)

1. 新しくスプレッドシートを作成し、同様の手順で初回アクセス月または流入元を変更したセグメントでレポートを設定
    - １つのスプレッドシートで複数のセグメントの数値を取得可能ですが、管理の面で分けています
      ![2シート一覧.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/532045/a0816f43-cd55-097f-53d9-31fdcd56a1d5.jpeg)


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

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

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

参考）[【超便利】スプレッドシートで別シートから参照したり集計したりする方法まとめ](https://tanuhack.com/refer-anothersheet/#i-3)

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

```javascript

SpreadsheetApp.openById(id);
```
参考）[GASで操作するスプレッドシートのワークブックを指定する３つの方法](https://tanuhack.com/gas-getworkbook/)

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

### 5)GASを作成

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

■処理概要

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

```javascript
// 実行する関数
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;
}
```

### 6)GASを実行

myfunctionを実行すると、シートが作成されます。
参考）[Googleスプレッドシートのスクリプト実行方法とボタンの実行方法](https://www.acrovision.jp/service/gas/?p=269)
![2一覧例.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/532045/a0f623b2-8bc1-c3aa-3b1e-028323fa5b8a.jpeg)

### 7)いい感じにまとめる

ここで力尽きたので、営業の方々に手伝っていただきました。（ありがとう！！）

# 成果物
こんな感じになりました。
3ヶ月以上の数値も追え、広告毎の数値が継続的に取れるようになりました！！
![2コホート分析例.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/532045/df0d616e-62c7-95a7-586f-92c81c61ef19.jpeg)



# 最後に
長くなってしまいましたが、ここまでお読みいただきありがとうございます。
不備などありましたら、ご教示いただけますと幸いです。

今回は自動化に重点を置いて、試行錯誤しました。
継続的かつ長期的なアクセス分析を行うことで、より効果のある広告を見極め、PDCAを回していくことができるかと思います。

今後は、よりサイトの活性化に向けて、広告流入元別だけでなく、ユーザー情報（性別や年齢）に基づいたアクセス分析も行っていきたいです。

GASについてはまだまだ勉強中なので、オススメの勉強方法とかありましたら教えてくださると嬉しいです。

個人的に、GoogleAnalytics API Add-onは使いこなすととても便利なので、GAを導入している方は一度使ってみるといいと思います。

[XTechグループ Advent Calendar 2020](https://qiita.com/advent-calendar/2020/xtech-group)はまだまだ続きます！
明日の執筆担当は@rukurxさんです。よろしくお願いいたします。