GASでスプレッドシートにGoogleアナリティクスのデータを定期的に出力する

GoogleAppScript

拡張機能のGoogleAnalyticsだとサンプリングに引っかかったり、期間の調整が必要になるのでそれをGoogleAppScriptで自動化するコードを書きました。

参考 こういった出力が期待できます。
出力するメトリクスはセッション数以外でもUU数や目標完了数など必要に応じて変更可能です。
値は公式ドキュメントを参照してください。

ga-dev-tools.web.app

GASでスプレッドシートにアナリティクスのデータを定期的に出力するスクリプト

GAで作成したセグメントのデータを指定したシートに出力するスクリプト

function main() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  const configs = [
      // sheetName：データを反映するシート名、 segment：GAセグメントIDを入力してください
      { sheetName: 'ga_all', segment: 'gaid::ABCDEFG },
    ]

  configs.forEach(({ sheetName, segment }) => {
    let sheet = spreadsheet.getSheetByName(sheetName)
    // 無ければシートを作る
    if (!sheet) {
      sheet = spreadsheet.insertSheet(sheetName)
    }

    // 動かなかったときのため直近n日分を更新する
    const nDay = 4;
    let date = new Date()
    date.setDate(date.getDate()-nDay);
    for(let n=0; n<nDay; n++) { 
      date.setDate(date.getDate()-1);

      const result = getGoogleAnalytics({ date, segment })
      result.forEach(row => {
        const rowDate = toDate(row[0])
        // スプシ上ではスラッシュ区切り
        const formatDate = Utilities.formatDate(rowDate, 'JST', 'yyyy/MM/dd')
        let cell = getCell({ sheet, text: formatDate })
        if (!cell) {
          // 無ければA列最後のセルを使う
          cell = sheet.getRange(sheet.getLastRow() + 1, 1)
        }
        // 列を結果の個数分広げる
        const range = cell.offset(0, 0, 1, row.length)
        let insertRow = row.concat()
        insertRow[0] = formatDate
        range.setValues([insertRow])
      })
    }
  })
}

function getGoogleAnalytics({ date, segment }) {
  // GA APIではハイフン区切り
  const formatDate = Utilities.formatDate(date, 'JST', 'yyyy-MM-dd')
  //Google Analytics APIリクエストして、グーグルアナリティクスのデータを取得する
  const gaData = Analytics.Data.Ga.get(
    'ga:123456', // GAビューIDを入力してください
    formatDate,
    formatDate,
    'ga:sessions', // メトリクス https://ga-dev-tools.web.app/dimensions-metrics-explorer/
    {
      segment,
      'dimensions': 'ga:date'
    }
  ).getRows()

  return gaData
}

function toDate (str) {
  var arr = (str.substr(0, 4) + '/' + str.substr(4, 2) + '/' + str.substr(6, 2)).split('/');
  return new Date(arr[0], arr[1] - 1, arr[2]);
};

// 指定されたテキストのセル（Range）を返す
function getCell({ sheet, text }) {
  var textFinder = sheet.createTextFinder(text);
  var cells = textFinder.findAll();
  
  for(var i=0; i<cells.length; i++){ 
    return cells[i]
  }
}

使い方解説

GAビューIDを設定

ここに利用するビューIDを設定してください。

  const gaData = Analytics.Data.Ga.get(
    'ga:123456', // GAビューIDを入力してください

GAでセグメントIDを設定

アナリティクス上でセグメントを作ってからセグメントIDを取得します。
UA Query Explorerで検索可能です。

segment 部分に取得したセグメントIDを入力してください。

  const configs = [
      // sheetName：データを反映するシート名、 segment：GAセグメントID
      { sheetName: 'ga_all', segment: 'gaid::ABCDEFG },
    ]

書き出すシートを用意する

sheetName に書いた名前のシートを用意します。

1行目に date sessions など適宜見出しをつけます。

定期実行を設定

BigQueryで日次、週次、月次の集計をする

BigQuery データ分析

BigQueryで集計

データソース

created_at にTIMESTAMP型で何かしらのデータの作成日が入っている想定です。

created_at(TIMESTAMP) user_id(INT)
2022-01-02 14:13:30.801506 UTC 1
2022-01-03 12:13:30.801506 UTC 2
2022-01-04 11:13:30.801506 UTC 1

日次で集計

UU数で出すように DISTINCT を付けていますが適宜外してください。

SELECT
  CAST(created_at AS DATE) day,
  COUNT(DISTINCT user_id)
FROM
  posts
GROUP BY
  day
ORDER BY
  day

週次で集計

週次の集計は DATE_TRUNC 関数で WEEK を指定し日付の丸め込みを行います。

cloud.google.com

日曜始まりで週次集計する場合

SELECT
  DATE_TRUNC(CAST(created_at AS DATE), WEEK) AS week,
  COUNT(DISTINCT company_id)
FROM
  posts
GROUP BY
  week
ORDER BY
  week

月曜始まりで週次集計する場合

SELECT
  DATE_TRUNC(CAST(created_at AS DATE), WEEK(MONDAY)) AS week,
  COUNT(DISTINCT company_id)
FROM
  posts
GROUP BY
  week
ORDER BY
  week

月次で集計する場合

SELECT
  DATE_TRUNC(CAST(created_at AS DATE), MONTH) AS month,
  COUNT(DISTINCT company_id)
FROM
  posts
GROUP BY
  month
ORDER BY
  month

Dockerコンテナ内からホスト側にアクセスする方法

Dockerで立てたコンテナ内から別コンテナに接続したかったのですが、ネットワークが別になるのでコンテナ名では繋げられず困っていました。

例えばコンテナAでDBを立ち上げていて、コンテナBからDBに接続したいなど。

考えを変えて、ホスト側にさえアクセスできれば勝手にルーティングされると思い、Dockerコンテナ内からホスト側にアクセスする方法を探したところ

host.docker.internal

でホスト側にアクセスできるよう。

なのでコンテナB側の接続情報を host.docker.internal:3306 とすることでアクセスができました。

こちらの記事を参考にしました。

uepon.hatenadiary.com

Sidekiqを6.4.0に上げるとsidekiq-historyでundefined method `new' for Sidekiq::Worker:Module

環境

  • ruby 3.0.3
  • rails 6.1.4.4
  • sidekiq 6.4.0
  • sidekiq-history 0.0.11

以下のエラー

NoMethodErrorvendor/bundle/ruby/3.0.0/gems/sidekiq-history-0.0.11/lib/sidekiq/history/middleware.rb:14
undefined method `new' for Sidekiq::Worker:Module

sidekiq-history のバージョンが古いのが原因でした。 こちらのコミットで sidekiq 6.2.2 以上に対応していたので sidekiq-history をアップデートします。

github.com

bundle update sidekiq-history

バージョンが上がったのを確認

$ git diff

-    sidekiq-history (0.0.11)
+    sidekiq-history (0.0.12)