Antigravity で作る Firebase × BigQuery × Looker Studio 収益分析ダッシュボード構築ガイド

-- Firebase BigQuery エクスポートから日別IAP収益を集計
-- events_* はワイルドカードで全日付テーブルを対象にする
SELECT
  -- イベント日付（タイムゾーン変換：UTC→JST）
  DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS event_date,
 
  -- 通貨コード（JPY / USD など）
  (SELECT value.string_value
   FROM UNNEST(event_params)
   WHERE key = 'currency') AS currency,
 
  -- 収益合計（マイクロ単位を通常単位に変換）
  ROUND(SUM(
    (SELECT value.int_value
     FROM UNNEST(event_params)
     WHERE key = 'value') / 1000000.0
  ), 2) AS total_revenue_usd,
 
  -- 購入件数
  COUNT(*) AS purchase_count
 
FROM
  -- プロジェクトIDとデータセット名は実際の値に置き換える
  `YOUR_PROJECT_ID.analytics_XXXXXXXXX.events_*`
 
WHERE
  event_name = 'in_app_purchase'
  -- 直近90日を対象（コスト最適化）
  AND _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
 
GROUP BY
  event_date, currency
 
ORDER BY
  event_date DESC

// functions/src/syncAdMobRevenue.js
const { google } = require('googleapis');
const { BigQuery } = require('@google-cloud/bigquery');
 
const bigquery = new BigQuery();
const DATASET_ID = 'revenue_analytics'; // BigQueryデータセット名
const TABLE_ID = 'admob_revenue';       // 書き込み先テーブル名
 
/**
 * AdMob Reporting API から昨日の収益データを取得して BigQuery に保存する
 * Cloud Scheduler から HTTP POST でトリガーされる
 */
exports.syncAdMobRevenue = async (req, res) => {
  try {
    const auth = await google.auth.getClient({
      scopes: ['https://www.googleapis.com/auth/admob.readonly'],
    });
 
    const admob = google.admob({ version: 'v1', auth });
 
    // 昨日の日付範囲を設定（JST基準）
    const yesterday = new Date();
    yesterday.setDate(yesterday.getDate() - 1);
    const dateStr = yesterday.toISOString().split('T')[0];
    const [year, month, day] = dateStr.split('-').map(Number);
 
    const publisherId = process.env.ADMOB_PUBLISHER_ID;
    const report = await admob.accounts.networkReport.generate({
      parent: `accounts/${publisherId}`,
      requestBody: {
        reportSpec: {
          dateRange: {
            startDate: { year, month, day },
            endDate:   { year, month, day },
          },
          dimensions: ['APP', 'AD_UNIT', 'COUNTRY'],
          metrics: ['ESTIMATED_EARNINGS', 'IMPRESSIONS', 'CLICKS'],
        },
      },
    });
 
    // BigQuery に書き込む行データを整形
    const rows = (report.data || [])
      .filter(row => row.row)
      .map(row => ({
        date: dateStr,
        app_id: row.row.dimensionValues?.APP?.value || '',
        ad_unit: row.row.dimensionValues?.AD_UNIT?.value || '',
        country: row.row.dimensionValues?.COUNTRY?.value || '',
        // 収益はマイクロ単位（1,000,000で割ると USD）
        estimated_earnings_usd:
          (row.row.metricValues?.ESTIMATED_EARNINGS?.microsValue || 0) / 1000000,
        impressions: parseInt(row.row.metricValues?.IMPRESSIONS?.integerValue || 0),
        clicks: parseInt(row.row.metricValues?.CLICKS?.integerValue || 0),
        synced_at: new Date().toISOString(),
      }));
 
    if (rows.length > 0) {
      await bigquery.dataset(DATASET_ID).table(TABLE_ID).insert(rows);
      console.log(`✅ ${rows.length}行のAdMobデータを BigQuery に書き込みました`);
    }
 
    res.status(200).json({ success: true, rows: rows.length });
  } catch (error) {
    console.error('AdMob同期エラー:', error);
    res.status(500).json({ error: error.message });
  }
};

-- 全収益ソースを統合する BigQuery ビュー
CREATE OR REPLACE VIEW `YOUR_PROJECT_ID.revenue_analytics.unified_revenue` AS
 
-- 1. アプリ内課金（IAP）収益（Firebase Analytics から）
SELECT
  DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS date,
  'iap' AS revenue_type,
  app_info.id AS app_id,
  geo.country AS country,
  ROUND(
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'value') / 1000000.0, 4
  ) AS revenue_usd,
  user_pseudo_id AS user_id
FROM `YOUR_PROJECT_ID.analytics_XXXXXXXXX.events_*`
WHERE event_name = 'in_app_purchase'
  AND _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY))
 
UNION ALL
 
-- 2. AdMob 広告収益
SELECT
  PARSE_DATE('%Y-%m-%d', date) AS date,
  'admob' AS revenue_type,
  app_id, country,
  estimated_earnings_usd AS revenue_usd,
  NULL AS user_id
FROM `YOUR_PROJECT_ID.revenue_analytics.admob_revenue`
WHERE PARSE_DATE('%Y-%m-%d', date) >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
 
UNION ALL
 
-- 3. サブスクリプション収益（RevenueCat Webhook → BigQuery）
SELECT
  DATE(purchased_at) AS date,
  'subscription' AS revenue_type,
  app_id,
  country_code AS country,
  price_in_purchased_currency / exchange_rate AS revenue_usd,
  subscriber_attributes.user_id AS user_id
FROM `YOUR_PROJECT_ID.revenue_analytics.revenuecat_events`
WHERE event_type = 'INITIAL_PURCHASE'
  AND DATE(purchased_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)

// services/weeklyReport.js
const { BigQuery } = require('@google-cloud/bigquery');
const sgMail = require('@sendgrid/mail');
const { WebClient } = require('@slack/web-api');
 
const bigquery = new BigQuery();
const slack = new WebClient(process.env.SLACK_BOT_TOKEN);
 
/**
 * 先週の収益サマリーを BigQuery から取得する
 */
async function fetchWeeklyRevenue() {
  const query = `
    SELECT
      revenue_type,
      ROUND(SUM(revenue_usd), 2) AS total_revenue,
      COUNT(DISTINCT user_id) AS unique_buyers,
      COUNT(*) AS transaction_count
    FROM \`YOUR_PROJECT_ID.revenue_analytics.unified_revenue\`
    WHERE date BETWEEN DATE_SUB(DATE_TRUNC(CURRENT_DATE(), WEEK(MONDAY)), INTERVAL 7 DAY)
                   AND DATE_SUB(DATE_TRUNC(CURRENT_DATE(), WEEK(MONDAY)), INTERVAL 1 DAY)
    GROUP BY revenue_type
    ORDER BY total_revenue DESC
  `;
  const [rows] = await bigquery.query({ query });
  return rows;
}
 
/**
 * 収益データをHTMLメール形式に整形して SendGrid で送信する
 */
async function sendRevenueEmail(revenueData) {
  sgMail.setApiKey(process.env.SENDGRID_API_KEY);
  const totalRevenue = revenueData.reduce((sum, r) => sum + r.total_revenue, 0).toFixed(2);
 
  await sgMail.send({
    to: process.env.REPORT_EMAIL,
    from: process.env.FROM_EMAIL,
    subject: `📊 週次収益レポート ${new Date().toLocaleDateString('ja-JP')}`,
    html: `
      <h2>📊 先週の収益レポート</h2>
      <p>総収益: <strong>$${totalRevenue}</strong></p>
      ${revenueData.map(r =>
        `<p>${r.revenue_type}: $${r.total_revenue}（${r.unique_buyers || '-'}件）</p>`
      ).join('')}
      <p><a href="${process.env.LOOKER_DASHBOARD_URL}">→ 詳細ダッシュボードを見る</a></p>
    `,
  });
  console.log('✅ 週次レポートメール送信完了');
}
 
/**
 * Slack の #revenue チャンネルにサマリーを投稿する
 */
async function postSlackSummary(revenueData) {
  const total = revenueData.reduce((s, r) => s + r.total_revenue, 0).toFixed(2);
  const lines = revenueData.map(r => `• ${r.revenue_type}: $${r.total_revenue}`).join('\n');
  await slack.chat.postMessage({
    channel: '#revenue',
    text: `📊 *週次収益レポート*\n総収益: *$${total}*\n\n${lines}`,
  });
  console.log('✅ Slack サマリー投稿完了');
}
 
module.exports = { fetchWeeklyRevenue, sendRevenueEmail, postSlackSummary };