BigQueryで日次、月次のマスターデータを作る

rowデータで日や月に歯抜けがある場合、 group by month などやっても連番にならない可能性があります。
そこで日次、月次のマスターデータを作りそこに紐づける方法をまとめます。

日次のマスターデータを作る

SELECT day
FROM UNNEST(
    GENERATE_DATE_ARRAY(DATE('2022-01-01'), DATE('2022-01-31'), INTERVAL 1 DAY)
) as day

月次のマスターデータを作る

INTERVAL を変えるだけで簡単に作れます。

SELECT month
FROM UNNEST(
    GENERATE_DATE_ARRAY(DATE('2022-01-01'), DATE('2022-01-31'), INTERVAL 1 MONTH)
) as month

スプレッドシートで祝日判定をする

ダッシュボードを作る際に祝日を考慮したかったので検証しました。

完成形のスプレッドシートはこちらです。
答えだけほしい方はこちらから関数をコピペしてください。

docs.google.com

祝日データを内閣府CSVからインポートする

祝日のデータは内閣府が公開しているCSVを使います。 以下のサイトに載っています。 www8.cao.go.jp

まずは IMPORTDATACSVを読み込む

=IMPORTDATA("https://www8.cao.go.jp/chosei/shukujitsu/syukujitsu.csv")

文字化けしますが日付さえ取得できればいいのでこのままで進めます。

日付に対応した祝日が存在するかチェック

=if(COUNTIF('祝日シート'!$A:$A, {日付のセル})=0, "×", "○")

これで祝日の場合 が付きます。

曜日 or 祝を表示する

=if(COUNTIF('祝日シート'!$A:$A, {日付のセル})=0, TEXT(A2, "ddd"), "祝")

基本的には曜日を表示しますが、祝日の場合 と出力します。

条件付き書式で土日祝に色付けも追加してます。

最後に、完成形のスプレッドシートです。
参照してみてください。

docs.google.com

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

拡張機能の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で集計

データソース

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