Firebase Analytics (GAv4) の数値をスプレッドシートに入力する作業を自動化

はじめに

スマートフォンアプリに Firebase Analytics を導入して、日々のアクティブユーザー数や売上（目安値）などの数値を取得してサービス運営に役立てている方は多いと思います。Firebaseのコンソール上でもそこそこチェックできますが、スプレッドシートへ数値を写せばより高度な数値分析ができます。ただし、Firebase Analytics (Google Analytics v4) に対応したスプレッドシート用の公式のアドオンが無い（もうすぐサービス終了する GAv3 用のアドオンしか無い...）ので、目視コピーで運用されている方も多いかもしれません。

私も当初目視コピーで運用してましたが、途中から流石に「これは面倒くさい」と思い、Google Analytics v4 に対応したサードパーティ製アドオンの SyncWith というものを使って、スプレッドシートへの数値自動コピーに対応してみました。

Google Analytics v3が2023年7月1日にサービス終了する関係で、スマホアプリに限らず今後はWebサイトの数値集計をスプレで行っていた方も（公式アドオンが無くなって）困ったことになる可能性が高いと思われますが、GAv4数値のスプレ自動集計関連の情報記事があまり見当たらなかったので今回記事を書いてみました。

1. SyncWith取得

集計するスプレッドシートの「拡張機能」→「アドオン」→「アドオン取得」で、

SyncWith を取得（firebaseで検索すれば出てきます）

2. SyncWith設定

「拡張機能」→「SyncWith」→「Manage connections」

「Browse」で「Google Analytics v4」（analyticsで検索すれば出てきます）を選択して、各種項目を入力します。

1	2

• DAU / MAU : MAU の何%ぐらいの DAU か
• Total ad revenue : 広告の収益目安金額（ドル単位）
• Purchase revenue : アプリ内課金の売上目安金額（ドル単位）
• 1-day active users : DAU
• 7-day active users : WAU
• 28-day active users : ざっくりMAU
• ARPU : 1ユーザーあたりの収益目安額
• New users : インストール数
• Bounce Rate : 直帰率
• Average session duration : セッション平均時間

他にも色々拾えます。

3. SyncWith 実行

実行すれば前項で設定したシートに結果が入ります。左上のチェックボックスをクリックすると最新値に更新できます。

4. データ転写

SyncWithで取得したシートから自前のシート（私の場合は上図のdaily）に値をコピーして使います。

この辺の作業も手動でやると面倒くさかったので GAS で自動化しました。

SyncWith で取得したシートのDate列（YYYYMMdd）から、自前シートのDate列（Time形式）の挿入ポイントを探して各種値を対応するレコードに書き込んでいく感じのスクリプトを組んでみました。（実は初めてGAS使ってみたのですが簡単で良いですね）

function update() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet()
  let dailySheet = sheet.getSheetByName('daily')
  let dailyDatesRaw = dailySheet.getRange(3, 1, dailySheet.getLastRow(), 1).getValues()
  let dailyDates = []

  // daily シートの日付列を YYYYMMdd の数値形式に変換（挿入開始位置の探索用）
  dailyDatesRaw.forEach(function (dailyDate) {
    let value = Number(Utilities.formatDate(new Date(dailyDate), "JST", "YYYYMMdd"))
    if (20000000 < value) {
      dailyDates.push(value)
    }
  })

  // 各データを反映
  update_Android(sheet, dailySheet, dailyDates)
  update_iOS(sheet, dailySheet, dailyDates)
}

function update_Android(sheet, dailySheet, dailyDates) {
  let androidSheet = sheet.getSheetByName('Android')
  let android = androidSheet.getRange(3, 1, androidSheet.getLastRow() - 1, 11).getValues()

  // Android のデータの daily の挿入開始位置を探索
  for (var i = 0; i < android.length; i++) {
    if (Number(android[i][0]) < 1) {
      android.splice(i)
      break;
    }
  }
  let targetDateA = Number(android[0][0])
  var targetPosA = -1
  for (var i = 0; i < dailyDates.length; i++) {
    if (dailyDates[i] == targetDateA) {
      Logger.log('Detect valid insert position of Android sheet: ' + i + ' (' + targetDateA + ')')
      targetPosA = i + 3
      break
    }
  }

  // Android のデータを　daily シートの対象位置へ挿入
  if (0 < targetPosA) {
    let adsProfit = []
    let iapProfit = []
    let dau = []
    let wau = []
    let mau = []
    let installs = []
    let bounce = []
    let sessionDuration = []
    android.forEach(function (record) {
      adsProfit.push([record[2]])
      iapProfit.push([record[3]])
      dau.push([record[4]])
      wau.push([record[5]])
      mau.push([record[6]])
      installs.push([record[8]])
      bounce.push([record[9]])
      sessionDuration.push([record[10]])
    })
    dailySheet.getRange(targetPosA, 3, adsProfit.length, 1).setValues(adsProfit)
    dailySheet.getRange(targetPosA, 7, iapProfit.length, 1).setValues(iapProfit)
    dailySheet.getRange(targetPosA, 15, dau.length, 1).setValues(dau)
    dailySheet.getRange(targetPosA, 18, wau.length, 1).setValues(wau)
    dailySheet.getRange(targetPosA, 21, mau.length, 1).setValues(mau)
    dailySheet.getRange(targetPosA, 24, installs.length, 1).setValues(installs)
    dailySheet.getRange(targetPosA, 30, bounce.length, 1).setValues(bounce)
    dailySheet.getRange(targetPosA, 32, sessionDuration.length, 1).setValues(sessionDuration)
  }
}

function update_iOS(sheet, dailySheet, dailyDates) {
  let iosSheet = sheet.getSheetByName('iOS')
  let ios = iosSheet.getRange(3, 1, iosSheet.getLastRow() - 1, 11).getValues()
  for (var i = 0; i < ios.length; i++) {
    if (Number(ios[i][0]) < 1) {
      ios.splice(i)
      break;
    }
  }

  // iOS のデータの daily の挿入開始位置を探索
  let targetDateI = Number(ios[0][0])
  var targetPosI = -1
  for (var i = 0; i < dailyDates.length; i++) {
    if (dailyDates[i] == targetDateI) {
      Logger.log('Detect valid insert position of iOS sheet: ' + i + ' (' + targetDateI + ')')
      targetPosI = i + 3
      break
    }
  }

  // iOS のデータを　daily シートの対象位置へ挿入
  if (0 < targetPosI) {
    let adsProfit = []
    let iapProfit = []
    let dau = []
    let wau = []
    let mau = []
    let installs = []
    let bounce = []
    let sessionDuration = []
    ios.forEach(function (record) {
      adsProfit.push([record[2]])
      iapProfit.push([record[3]])
      dau.push([record[4]])
      wau.push([record[5]])
      mau.push([record[6]])
      installs.push([record[8]])
      bounce.push([record[9]])
      sessionDuration.push([record[10]])
    })
    dailySheet.getRange(targetPosI, 4, adsProfit.length, 1).setValues(adsProfit)
    dailySheet.getRange(targetPosI, 8, iapProfit.length, 1).setValues(iapProfit)
    dailySheet.getRange(targetPosI, 16, dau.length, 1).setValues(dau)
    dailySheet.getRange(targetPosI, 19, wau.length, 1).setValues(wau)
    dailySheet.getRange(targetPosI, 22, mau.length, 1).setValues(mau)
    dailySheet.getRange(targetPosI, 25, installs.length, 1).setValues(installs)
    dailySheet.getRange(targetPosI, 31, bounce.length, 1).setValues(bounce)
    dailySheet.getRange(targetPosI, 33, sessionDuration.length, 1).setValues(sessionDuration)
  }
}

なお、私が運営しているサービスでは、iOSとAndroidでFirebaseのプロジェクトを別々にしているので、iOSとAndroidそれぞれで似たようなロジックが入っていてコードが若干汚いですね...（それで困りそうになったらもっと良い感じに書き直そう）

5. 注意点

SyncWith は、Hobbyプラン（無料）の場合、1ヶ月あたりの実行上限が35に制限されています。

私は週1ぐらいの頻度で実行できれば十分なので無料で特に問題ありませんが、毎日数値を拾いたい熱心な方や商用利用される場合、有料サブスクリプションに加入する必要があるようです。

お値段は、リフレッシュ実行頻度が1時間/1回なら $24.99/mo 、5分/1回なら $49.99/mo とのことです。手動コピーに掛かる人件費と比べればリーズナブルかと思われます。