Complete Guide to Building a Firebase × BigQuery × Looker Studio Revenue Analytics Dashboard with Antigravity

-- Calculate daily IAP revenue from Firebase BigQuery export
-- The * wildcard targets all date-suffix tables at once
SELECT
  -- Convert event timestamp from UTC to JST
  DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS event_date,
 
  -- Currency code (JPY, USD, etc.)
  (SELECT value.string_value
   FROM UNNEST(event_params)
   WHERE key = 'currency') AS currency,
 
  -- Revenue total (convert from micros to standard unit)
  ROUND(SUM(
    (SELECT value.int_value
     FROM UNNEST(event_params)
     WHERE key = 'value') / 1000000.0
  ), 2) AS total_revenue,
 
  -- Transaction count
  COUNT(*) AS purchase_count
 
FROM
  -- Replace with your actual project ID and analytics dataset
  `YOUR_PROJECT_ID.analytics_XXXXXXXXX.events_*`
 
WHERE
  event_name = 'in_app_purchase'
  -- Limit to last 90 days to control query costs
  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'; // Your BigQuery dataset name
const TABLE_ID = 'admob_revenue';       // Destination table
 
/**
 * Fetches yesterday's AdMob revenue data and writes it to BigQuery.
 * Triggered by Cloud Scheduler via HTTP POST.
 */
exports.syncAdMobRevenue = async (req, res) => {
  try {
    // Authenticate with service account for AdMob API access
    const auth = await google.auth.getClient({
      scopes: ['https://www.googleapis.com/auth/admob.readonly'],
    });
 
    const admob = google.admob({ version: 'v1', auth });
 
    // Calculate yesterday's date
    const yesterday = new Date();
    yesterday.setDate(yesterday.getDate() - 1);
    const dateStr = yesterday.toISOString().split('T')[0]; // YYYY-MM-DD
    const [year, month, day] = dateStr.split('-').map(Number);
 
    // Request network report from AdMob
    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'],
        },
      },
    });
 
    // Transform API response to BigQuery row format
    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 || '',
        // AdMob returns earnings in micros (divide by 1M for 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(`✅ Wrote ${rows.length} AdMob rows to BigQuery`);
    }
 
    res.status(200).json({ success: true, rows: rows.length });
  } catch (error) {
    console.error('AdMob sync error:', error);
    res.status(500).json({ error: error.message });
  }
};

-- Unified revenue view combining all monetization sources
-- Looker Studio connects to this single view as its data source
 
CREATE OR REPLACE VIEW `YOUR_PROJECT_ID.revenue_analytics.unified_revenue` AS
 
-- Source 1: In-App Purchases from 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
 
-- Source 2: AdMob Advertising Revenue (synced via Cloud Function)
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
 
-- Source 3: Subscription Revenue (RevenueCat webhook to 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);
 
/**
 * Fetches last week's aggregated revenue from 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;
}
 
/**
 * Sends the weekly revenue summary as an HTML email via SendGrid.
 */
async function sendRevenueEmail(revenueData) {
  sgMail.setApiKey(process.env.SENDGRID_API_KEY);
  const total = revenueData.reduce((s, r) => s + r.total_revenue, 0).toFixed(2);
 
  await sgMail.send({
    to:      process.env.REPORT_EMAIL,
    from:    process.env.FROM_EMAIL,
    subject: `📊 Weekly Revenue Report — ${new Date().toDateString()}`,
    html: `
      <h2>📊 Last Week Revenue Summary</h2>
      <p>Total: <strong>$${total}</strong></p>
      ${revenueData.map(r =>
        `<p>${r.revenue_type}: $${r.total_revenue} (${r.unique_buyers ?? '—'} buyers)</p>`
      ).join('')}
      <p><a href="${process.env.LOOKER_DASHBOARD_URL}">→ View Full Dashboard</a></p>
    `,
  });
  console.log('✅ Weekly report email sent');
}
 
/**
 * Posts a concise summary to the #revenue Slack channel.
 */
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: `📊 *Weekly Revenue Report*\nTotal: *$${total}*\n\n${lines}\n\n<${process.env.LOOKER_DASHBOARD_URL}|View Dashboard>`,
  });
  console.log('✅ Slack summary posted');
}
 
module.exports = { fetchWeeklyRevenue, sendRevenueEmail, postSlackSummary };