Tracking Readership Across a Large Blog Network with GA4, Google Sheets and BigQuery

Managing a network of blogs — whether it's ten sites or a hundred — creates an analytics problem that GA4 wasn't really designed to solve. Each property is a silo. The data is all there, but answering network-level questions requires either opening every property individually and building a mental picture, or building the infrastructure to combine the data into a single queryable layer.

The good news is that the infrastructure doesn't have to be complex. Depending on your network size and technical comfort, a Google Sheets and Apps Script approach can get you most of the way there without BigQuery at all. For larger networks or more advanced analysis, BigQuery is the natural next step — and the two approaches work well together.

What you're trying to answer

Before choosing tools, it's worth being specific about the questions. For a blog network, the most useful ones are typically:

• Which sites are getting the most traffic right now, and how does that compare to their historical baseline?
• Which sites had their best day ever recently — and by how much?
• Which sites are genuinely growing versus just getting a traffic spike from a single viral post?
• Are there sites in the network that are quietly declining while others get the attention?

These are different questions from "what are the total sessions this month" — they require historical context, cross-property comparison, and some notion of momentum rather than just volume.

Two approaches — choose based on your situation

For most blog networks up to around 30–40 properties, the Sheets and Apps Script approach is the faster path to a working system. For larger networks, or where you need the full flexibility of SQL and long historical data ranges, BigQuery is worth the additional setup. The two also combine well — Apps Script to pull and write data, BigQuery for the heavy querying and analysis.

The architecture

Option A: Google Sheets and Apps Script

// Apps Script — fetch yesterday's sessions for all properties
function fetchDailySessions() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const propsSheet = ss.getSheetByName('Properties');
  const dataSheet  = ss.getSheetByName('Daily Sessions');
  const properties = propsSheet.getDataRange().getValues().slice(1);

  // Yesterday's date in YYYY-MM-DD format
  const yesterday = new Date();
  yesterday.setDate(yesterday.getDate() - 1);
  const dateStr = Utilities.formatDate(
    yesterday, 'UTC', 'yyyy-MM-dd'
  );

  properties.forEach(([propertyId, siteName]) => {
    try {
      const response = AnalyticsData.Properties.runReport(
        {
          dateRanges: [{ startDate: dateStr, endDate: dateStr }],
          metrics:    [{ name: 'sessions' }]
        },
        `properties/${propertyId}`
      );

      const sessions = response.rows
        ? parseInt(response.rows[0].metricValues[0].value)
        : 0;

      dataSheet.appendRow([dateStr, propertyId, siteName, sessions]);

    } catch(e) {
      // Log errors but continue — one failed property
      // shouldn't stop the rest of the run
      console.error(`Error for ${siteName}: ${e.message}`);
    }
  });
}

// Check for new records and send email alerts
function checkForRecords() {
  const ss          = SpreadsheetApp.getActiveSpreadsheet();
  const dataSheet   = ss.getSheetByName('Daily Sessions');
  const recordSheet = ss.getSheetByName('Records');
  const data        = dataSheet.getDataRange().getValues().slice(1);

  // Build a map of property_id → all-time max (excluding today)
  const yesterday = Utilities.formatDate(
    new Date(Date.now() - 86400000), 'UTC', 'yyyy-MM-dd'
  );

  const historicMax = {};
  const todayRows   = [];

  data.forEach(([date, propId, siteName, sessions]) => {
    if (date === yesterday) {
      todayRows.push({ propId, siteName, sessions });
    } else {
      historicMax[propId] = Math.max(
        historicMax[propId] || 0, sessions
      );
    }
  });

  // Check each of yesterday's rows against the historic max
  const newRecords = todayRows.filter(({ propId, sessions }) =>
    sessions > (historicMax[propId] || 0)
  );

  if (newRecords.length === 0) return;

  // Build and send email
  const rows = newRecords.map(({ siteName, sessions, propId }) => {
    const prev = historicMax[propId] || 0;
    const pct  = prev > 0
      ? ((sessions - prev) / prev * 100).toFixed(1)
      : 'N/A';
    // Also log to Records sheet
    recordSheet.appendRow([yesterday, siteName, sessions, prev, pct + '%']);
    return `${siteName}: ${sessions} sessions (+${pct}% above record)`;
  });

  GmailApp.sendEmail(
    Session.getEffectiveUser().getEmail(),
    `🏆 ${newRecords.length} new daily record(s) — ${yesterday}`,
    rows.join('\n')
  );
}

Option B: BigQuery for larger networks

For networks above roughly 40 properties, or where you need complex historical analysis that would be slow in Sheets, BigQuery is the better foundation. The GA4 BigQuery export sends each property's event data to a separate dataset — you combine them using a UNION ALL query that reads sessions from each property's events_* table.

The core query pattern is the same as the Sheets approach conceptually — sessions per property per day — but expressed in SQL across the exported event tables:

-- Unified daily sessions across network properties
-- Add one UNION ALL block per property

SELECT
  PARSE_DATE('%Y%m%d', event_date)   AS date,
  'site-one.com'                    AS site_name,
  COUNT(DISTINCT CONCAT(user_pseudo_id, '.',
    (SELECT value.int_value
     FROM UNNEST(event_params)
     WHERE key = 'ga_session_id')))  AS sessions
FROM `project.analytics_111111111.events_*`
WHERE _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d',
  DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
AND event_name = 'session_start'
GROUP BY 1, 2

UNION ALL

SELECT
  PARSE_DATE('%Y%m%d', event_date),
  'site-two.com',
  COUNT(DISTINCT CONCAT(user_pseudo_id, '.',
    (SELECT value.int_value
     FROM UNNEST(event_params)
     WHERE key = 'ga_session_id')))
FROM `project.analytics_222222222.events_*`
WHERE _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d',
  DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
AND event_name = 'session_start'
GROUP BY 1, 2

-- repeat for each property in the network

Schedule this as a BigQuery scheduled query set to INSERT INTO an existing unified table daily. Record detection then runs as a second scheduled query using a window function to compare each day's sessions against the historical maximum for that property — see the full BigQuery approach post for the complete record detection SQL.

Looker Studio dashboard

Whether you're using Sheets or BigQuery as the data source, the Looker Studio setup is similar. Connect to whichever data source you built:

• For Sheets: use the Google Sheets connector and point to the Daily Sessions sheet
• For BigQuery: use the BigQuery connector and point to your unified sessions table

Dashboard structure for a network view

Network summary table — all sites ranked by sessions in the selected period. Columns: site name, sessions, percentage of network total. A date range filter defaulting to yesterday gives you the immediate daily leaderboard.

Trend chart — total network sessions by day as a line chart. Useful for spotting network-wide patterns — seasonal dips, traffic from a high-profile external link, the effect of a content push.

Records panel — a table sourced from the Records sheet or log, showing which sites broke their daily record recently. Keep this to the last 30 days and sort by date descending. This is the most editorially interesting view — it surfaces momentum rather than just volume.

Property filter — a dropdown of all site names. When an editor selects a specific site, all charts filter to show that site's trend in isolation, with a reference line showing the all-time record.

Travis Gunn

Founder of GA4 Health Check. Working with Google Analytics since 2013, with over 250 clients audited across almost every industry vertical. 100% Job Success on Upwork for over a decade.