>>オーリーズが分かる資料セット（サービス資料・事例集）をダウンロードする

Google Apps Script(GAS)からBigQueryの連携が簡単に

最近、GASのライブラリに「Google Analytics Data API」というサービスが追加されました。

現在はベータ版の状態ではありますが、GA4のデータをGASを利用して取得することができるサービス連携となっており、以前まで構築が難しかったGA4レポートの自動更新を行えるものとなっています。

これにより、GA4移行への障害が一つ改善されるとは思いますが、GA4ではデータの最長保持期間が14ヶ月となっているため、取得したレポートを蓄積する先が必要となる場合が多いのではないかと思います。

本記事ではGASで取得したGA4のデータをGCPのサービスの一つであるBigQueryに蓄積する構築の手順をご紹介します。

システム構成・実行概要

１．取得データの指定
　　今回新しく追加された「Google Analytics Data API」を利用して日別・ランディングページ別のユーザー数データを取得します。

２．結果を受信
　　返却されたデータを整形します。

３．結果を貼り付け
　　SpreadSheetへ書き込みます。

４．外部テーブル読み込み
　　外部テーブルとして設定したSpreadSheetからデータを取得します。

５．蓄積
　　テーブルから取得したデータを取得します。

作業手順

前提・準備

👉 SpreadSheet、GA4、BigQueryの3つを同じユーザーで参照できる状態とします。また、BigQueryとGA4についてはアカウントを取得し、一通りの設定は終わっているものとします。

1.取得データの指定 ～ 3.結果の貼り付け

スプレッドシートの準備

👉 シート名をGA4Dataとしたシートを用意します

👉 開かれたページでサービスの追加ボタンを押します。

👉 Analytics Data APIを追加します。

スクリプトの作成

Googleのリファレンスを参考に作成します。

👉 コード.jsを以下のコードに書き換えて、propertyIdをGA4のプロパティIDに変更します。

function runReport() {
  /**
   * TODO(developer): Uncomment this variable and replace with your
   *   Google Analytics 4 property ID before running the sample.
   */
  const propertyId = '**********';

  try {
    const metric = AnalyticsData.newMetric();
    metric.name = 'activeUsers';

    const dimension = AnalyticsData.newDimension();
    dimension.name = 'date';

    const dimension2 = AnalyticsData.newDimension();
    dimension2.name = 'landingPage';

    const dateRange = AnalyticsData.newDateRange();
    dateRange.startDate = '7daysAgo';
    dateRange.endDate = 'today';

    const request = AnalyticsData.newRunReportRequest();
    request.dimensions = [dimension,dimension2];
    request.metrics = [metric];
    request.dateRanges = dateRange;

    const report = AnalyticsData.Properties.runReport(request,
        'properties/' + propertyId);
    if (!report.rows) {
      Logger.log('No rows returned.');
      return;
    }

    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("GA4Data");
    sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn()).clear();
    // Append the headers.
    const dimensionHeaders = report.dimensionHeaders.map(
        (dimensionHeader) => {
          return dimensionHeader.name;
        });

    const metricHeaders = report.metricHeaders.map(
        (metricHeader) => {
          return metricHeader.name;
        });
    const headers = [...dimensionHeaders, ...metricHeaders];

    sheet.appendRow(headers);

    // Append the results.
    const rows = report.rows.map((row) => {
      const dimensionValues = row.dimensionValues.map(
          (dimensionValue) => {
            return dimensionValue.value;
          });
      const metricValues = row.metricValues.map(
          (metricValues) => {
            return metricValues.value;
          });
      return [...dimensionValues, ...metricValues];
    });

    sheet.getRange(2, 1, report.rows.length, headers.length)
        .setValues(rows);

    Logger.log('Report spreadsheet created');
  } catch (e) {
    // TODO (Developer) - Handle exception
    Logger.log('Failed with error: %s', e.error);
  }
}

👉 GA4Dataのシートに過去7日間のデータが保管されます。

4.外部テーブル読み込み ～ 5.蓄積

まずは先ほどのスプレッドシートをBigQuery側から外部テーブルとして読み込めるように設定します。

👉 プロジェクトにデータ保管用のデータセットを作成します。

👉 データセット名はGA4_testとしています

👉 テーブルの作成元をドライブとして設定を進めると外部テーブルとして設定でき、SpreadSheetの内容をテーブルとして取り扱うことができます。

👉 外部テーブルになりました。

このままではスプレッドシートの中身が変わるたびにデータが変わってしまうのでデータを貯蓄するテーブルを作成します。
Accumulationの文字を取って接頭文字をAにしてA_GA4DATAとしましょう。
少し楽をするために外部テーブルとして作成したT_GA4DATAにスクリプトを実行することで新しいテーブルを作成してしまいましょう。

👉 新しいタブでクエリを開いてください

👉 SELECTとFROMの間に*(アスタリスク)を入れて、LIMIT句を削除します。

👉 展開⇒クエリの設定を押します。

👉 保存後に先ほどのSQLを実行することで新しくA_GA4DATAのテーブルが作成されました。

これから蓄積するためのスクリプトを設定するのですが、蓄積作業は蓄積用のテーブルであるA_GA4DATAを削除しながらSpreadSheetの内容を追加する方式とします。

このことにより最新の内容であるSpreadSheetの内容が反映されるのに加えて、スクリプトとしての冪等性を担保しやすくなります。（冪等性の重要性についてはまた記事にします。）

削除スクリプト(********はプロジェクト名に書き換えてください)

DELETE
FROM
  `********.GA4_test.A_GA4DATA` AS A
WHERE
  EXISTS (
  SELECT
    *
  FROM
    `********.GA4_test.T_GA4DATA` AS T
  WHERE
    T.date = A.date)

データ挿入スクリプト

INSERT INTO
  `********.GA4_test.A_GA4DATA`
SELECT
  *
FROM
  `********.GA4_test.T_GA4DATA`

上記の2スクリプトを実行すれば問題ありません。

スケジューリング

GASの実行

👉 トリガーを選択して

👉 トリガーを追加を押す。

👉 午前2時～3時の実行としました。

SQLの定期実行

👉 削除スクリプトに対してスケジュールボタンを押す。

👉 SpreadSheetの更新が3時までなのでこちらのスクリプトは4時に流すことにしました。

👉 データ挿入のスクリプトも同様にして4時半に設定しました。

エラー検知などまだまだやるべきことはありますが、一旦これでSpreadSheetもSQLも自動実行されてデータが蓄積されることになります。

おわりに

今回、追加されたGoogle Analytics Data APIによって、GA4移行への障害の一つであったレポートの自動更新の構築がしやすくなりました。GA4ではデータの最長保持期間が14ヶ月ですが、ここで紹介した方法でBigQueryにデータを蓄積することでその問題にも対応ができるので、ぜひ参考にしていただければと思います！

オーリーズではユニバーサルアナリティクスからGA4への移管のご依頼も受け付けております。ご検討の際は、ぜひお問い合わせ欄よりご連絡ください。