![\"Flow](\"https:\/\/www.lukaswojcik.com\/blog\/wp-content\/uploads\/diagrams\/realtime-anomaly-en.png\")
Step 1: Pulling Live Counts with the Realtime Reporting API<\/h2>\n
The GA4 Data API’s A time-driven trigger runs this function every five minutes and appends the two counts, with a timestamp, to a log tab \u2014 the raw material both the live status and the baseline comparison are built from.<\/p>\n A 14-day rolling Z-score baseline is the right tool for a daily statistical audit, but it is overkill for a live dashboard meant to answer one question quickly: is the current volume roughly where it should be right now. A same-weekday, same-time-of-day comparison from exactly seven days earlier is simpler to compute and easier for a person glancing at a screen to reason about \u2014 a separate, once-daily trigger stores each day’s counts by hour, so the current pull always has last week’s equivalent hour to compare against.<\/p>\n A formula column turns the two counts into a percentage deviation and a status label:<\/p>\n Below roughly ten baseline events, a percentage swing stops meaning anything \u2014 a single missing event reads as a 50% drop overnight. A minimum baseline floor (for example, only evaluating the status once the baseline count passes 20) keeps the dashboard from crying wolf during the quietest hours.<\/p>\n The actual highlighting is done by a custom-formula conditional formatting rule on the status column: red when the deviation falls below The sheet works on its own for anyone with edit or view access, but pinning it to a shared screen during a launch window, or connecting it into Looker Studio as one tile among several, keeps it visible without anyone needing to have the sheet open. When it does go into Looker Studio, the data-freshness setting on the Sheets connector needs to be turned down from its default \u2014 otherwise a five-minute Apps Script refresh sits behind a much slower cache and the point of building this in the first place is lost.<\/p>\n This dashboard is a live symptom check, not a replacement for the statistical audit that runs against the completed day’s data \u2014 it will miss a slow, gradual decline that never crosses its threshold in any single five-minute window, which is exactly what a rolling baseline is for. Treat a red cell here as the prompt to go look, not as a confirmed incident on its own: a flash sale that legitimately triples add_to_cart volume, or a genuinely quiet hour, can both produce a swing outside the threshold without anything being broken.<\/p>\n From what the dashboard recorded itself, or from the regular reports. The Realtime Reporting API holds nothing beyond its window, so a figure from seven days ago has to either sit in the dashboard\u2019s own log tab or come from the GA4 Data API\u2019s runReport method, drawn from the processed daily data, for example by hour.<\/p>\n What matters is that both figures cover the same window. A realtime value spans roughly 30 minutes, an hourly count 60. With even traffic the realtime value therefore contains only about half the events of last week\u2019s hour, and the formula shows a deviation of \u221250% although nothing is broken. The hourly count has to be halved before the comparison, or the comparison uses the log entry written exactly seven days earlier at the same time of day, which covers the same window.<\/p>\n The second approach has a side effect: it needs a full week of log before the first status means anything.<\/p>\n Because every pull counts the last 30 minutes or so, and consecutive pulls overlap. If the event stops completely at a given moment, the next pull five minutes later still contains 25 minutes of healthy data. With even traffic, the arithmetic gives:<\/p>\n A complete outage therefore becomes visible after about ten minutes, a partial one later or not at all. If only half the events drop out, the deviation reaches \u221250% only after the full window and crosses the \u221230 threshold no earlier than the pull after 20 minutes; a loss below 30% never turns the cell red.<\/p>\n Yes, and it cannot tell the two apart. It counts events in GA4, not carts: a tag that stops firing after a deploy, or a changed consent banner that fewer visitors accept, produces the same red cell as a broken checkout. A look at the orders in the shop system settles which case applies.<\/p>\n<\/div>\nrunRealtimeReport<\/code> method accepts eventName<\/code> as a dimension and eventCount<\/code> as a metric, filtered to the property’s realtime data \u2014 no BigQuery export is involved. Apps Script’s built-in Analytics Data API advanced service can call it directly from a script bound to the dashboard sheet:<\/p>\nfunction pullRealtimeCounts() {\n const propertyId = 'properties\/XXXXXXXXX';\n const request = {\n dimensions: [{ name: 'eventName' }],\n metrics: [{ name: 'eventCount' }],\n dimensionFilter: {\n filter: {\n fieldName: 'eventName',\n inListFilter: { values: ['add_to_cart', 'begin_checkout'] }\n }\n }\n };\n const response = AnalyticsData.Properties.runRealtimeReport(request, propertyId);\n return response.rows || [];\n}<\/code><\/pre>\nStep 2: A Baseline Light Enough to Refresh Every Few Minutes<\/h2>\n
Step 3: Computing the Deviation and the Status<\/h2>\n
=IF(baseline_count=0, \"n\/a\",\n ROUND((current_count - baseline_count) \/ baseline_count * 100, 1))<\/code><\/pre>\nStep 4: Making Drops Visible with Conditional Formatting<\/h2>\n
-30<\/code>, amber between -30<\/code> and -15<\/code>, and no fill above that. It is the same visual pattern used for a statistical audit’s dashboard, applied here directly in Sheets. The ops view therefore updates the moment Apps Script appends a new row, with no extra data source and no connector in between.<\/p>\nStep 5: Putting It Where the Team Actually Looks<\/h2>\n
Staying Inside the Guardrails<\/h2>\n
Questions and answers<\/h2>\n
Where does last week\u2019s figure come from if the Realtime API only reaches back about 30 minutes?<\/h3>\n
Why does the cell not turn red immediately after a complete outage?<\/h3>\n
\n
Does the dashboard also fire when only the measurement breaks and the shop keeps working?<\/h3>\n