如何将Firebase与Google Analytics数据合并为统一视图？

Hey Roger, let's tackle your cross-platform data unification needs—this is a really common pain point when managing web + mobile products, so I’ve got some practical steps for you:

1. 实时跨平台总活跃用户统计 #

The most straightforward way to get a unified real-time view is to leverage Google Analytics 4 (GA4) since it natively integrates with Firebase:

• Link Firebase to GA4: In your Firebase project settings, connect to a GA4 property. Make sure your website’s GA4 setup uses the same property (if you’re still on Universal Analytics, migrate to GA4 first—UA is sunsetting). Once linked, GA4 will automatically aggregate real-time active users across your website, iOS, and Android apps in its Real-Time report (look for the "Active users" metric, which combines all platforms).
• BigQuery fallback for custom real-time views: If you need more control (like custom user segmentation or deduplication logic), export both GA4 and Firebase Analytics data to BigQuery. Then run a SQL query to calculate real-time active users (e.g., users active in the last 5 minutes) across both datasets:

  SELECT COUNT(DISTINCT user_id) AS total_realtime_active_users
  FROM (
    SELECT user_id FROM `your-ga4-project.events_*` WHERE event_timestamp >= UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 5 MINUTE))
    UNION ALL
    SELECT user_id FROM `your-firebase-project.analytics_*` WHERE event_timestamp >= UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 5 MINUTE))
  )
  

  Pro tip: Ensure you’re using a consistent user_id across web and app (e.g., your internal user account ID) to avoid counting the same user twice.

2. 长期合并三方销售数据 #

For unified sales reporting, BigQuery + Looker Studio is the go-to stack:

• Centralize all sales data in BigQuery:
  • Export GA4 ecommerce data (website sales) to BigQuery.
  • Export Firebase In-App Purchase events to BigQuery.
  • Import any other sales channel data (e.g., external CRM, payment processor records) into BigQuery via CSV uploads or API syncs.
• Build unified sales models: Write SQL views to join sales data across platforms, aligning on key fields like user_id, order_id, and revenue. Use Looker Studio to connect to BigQuery and create dashboards that show combined sales trends, customer lifetime value, etc.

3. 用Google Sheet实时拉取数据 #

Yes, you absolutely can pull real-time data into Google Sheets—here are two methods:

• Built-in GA4 Connector: Use the =GA4DATA() function directly in Sheets to pull real-time GA4 data (including cross-platform active users). You’ll need to link your GA4 property to Sheets first via the "Data > Data connectors > Connect to GA4" menu.
• Google Apps Script for full control: Write a custom script to fetch data from both GA4 API and Firebase Analytics API, then populate your Sheet. Here’s a simplified snippet for pulling real-time active users from GA4:

  function fetchRealtimeGA4Data() {
    const propertyId = 'YOUR_GA4_PROPERTY_ID';
    const request = {
      metrics: [ { name: 'activeUsers' } ],
      dimensions: [ { name: 'platform' } ]
    };
    const response = AnalyticsData.Properties.runRealtimeReport(
      `properties/${propertyId}`, request);
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Realtime Data');
    sheet.clearContents();
    sheet.appendRow(['Platform', 'Active Users']);
    response.rows.forEach(row => {
      sheet.appendRow([row.dimensionValues[0].value, row.metricValues[0].value]);
    });
  }
  

  Set up a time-driven trigger (e.g., every minute) to run this script automatically. For Firebase data, use the Firebase REST API or BigQuery API to pull the corresponding metrics and merge them in the Sheet.

Important note: Make sure your Google Cloud project has the necessary APIs enabled (Analytics Data API, Firebase Management API) and that your script has proper authorization to access the data.

内容的提问来源于stack exchange，提问作者Roger Crevice