|

Analytics Firewall: How we searched for bots in BigQuery, burned some CPU, and saved the marketing team from a heart attack

Analytics without strict traffic filtering is like trying to calculate airplane aerodynamics while pigeons fly in the wind tunnel. You seem to have data, and charts are drawn, but why does the Cost Per Action (CPA) go through the roof, and the conversion rate stays at the level of a statistical error?

We decided to build an Analytics Firewall — an asynchronous tool in Google Cloud Platform that would not just block junk traffic, but do it cheaply, elegantly, and without breaking real business metrics.

This article is a look inside our architectural process. From naive attempts to scan terabytes of raw data to creating an Enterprise solution that can tell the difference between a simple scraper and a bored teenager on TikTok.

Act I: Naivety, Raw Data, and a FinOps Disaster

Every data engineer begins with the same mistake: we take raw events_* tables from Google Analytics 4, use UNNEST(event_params), and start writing huge CASE WHEN rules.

Our first algorithm was pure mathematical paranoia. We counted everything: page views, time between clicks, and the presence of session_start events. The logic seemed rock-solid:

  • More than 50 pages and 0 clicks? It is a bot.
  • No UTM tags? It is a spam bot.
  • Came from nowhere and sits for 10 minutes? It is a monitoring bot.

Result: We created a monster. The query grouped data by user_pseudo_id for 10 days. BigQuery happily unpacked parameter arrays for every single user action. Since we pay for infrastructure based on processed data ($\text{Cost} = \text{Data Processed (TB)} \times \$6.25$), we were paying for the database to chew the same terabytes over and over again. Even worse, by grouping 10 days into one row, we turned loyal customers who visited the site three times a week into “abnormally active bots.”

We needed a different level of abstraction.

Act II: OWOX to the Rescue and the False Positives Trap

Our architectural change led us to the owoxbi_ga4_sessions tables. Moving to pre-calculated sessions instantly solved the problem of doing the same math twice. The data is already grouped, sessions have time limits, and basic metrics are calculated.

We rewrote the code, ran a 2-day test, and… found 513,419 bots.

Half a million dangerous sessions in 48 hours. The security team could open champagne and ask for a bonus for stopping a massive cyber attack. But looking closer at the data revealed a hard truth about marketing.

Out of these 513 thousand “threats,” more than 346 thousand were marked as Idle Bot (Inactive bot). Their pattern was:

  • Source: tiktok.com or instagram.com.
  • OS: iOS.
  • Interactions: 0.
  • Time on site: 1 second.

The math of the algorithm was perfect. No clicks, no scrolls, one event — so it is a bot. But business logic screamed the opposite. This was not a DDoS attack. This was a classic Bounce Rate from mobile in-app browsers. A person accidentally clicked an ad on TikTok, the page started loading, and they swiped back.

If this algorithm went into Production, we would delete half of the paid traffic for our marketing team. An algorithm created to clean data became its main destroyer. We understood the main rule of filtering: if a simple script behaves exactly like a bored human with a smartphone, we must count it as a human.

We completely deleted the Idle Bot category. We sent the job of cleaning accidental clicks back to where it belongs — the advertising platforms.

Act III: Evolution to Enterprise Level (The DataOps Way)

To stop punishing real users, we looked into the “trash bin” of our scoring system — the Mixed / Unknown category. We found out that we were generously giving penalty points just because UTM tags were missing or the session_start event was lost. Direct organic traffic (SEO) suffered because of our love for perfect attribution.

We rewrote the core of the Analytics Firewall and added three critical updates.

1. Signature Level (Fingerprints)

Analyzing behavior is expensive. Reading headers is cheap. We added data extraction for device.userAgent, device.operatingSystem, and trafficSource.referrer.

In our CASE block, we put technical rules at the very top. If a visitor knocks on the door with a name tag saying python-requests, AhrefsBot, or curl, we do not count their scrolls. We mark them as Technical / Signature Bot in the first millisecond and stop calculating.

2. Extreme Optimization (Single-Pass Array Processing)

Instead of scanning the nested hits array many times to find different events, we used the SELECT AS STRUCT pattern.

SQL

(SELECT AS STRUCT
   COUNTIF(h.eventInfo.eventName IN ('scroll', 'click')) AS interactions,
   COUNTIF(h.eventInfo.eventName = 'form_submit') AS form_submits
 FROM UNNEST(hits) AS h
) AS m

The array is opened exactly once per session. The database gets 5 needed metrics in one go using fast memory (RAM). No physical temporary tables (TEMP TABLE), no extra input-output operations. The script became a linear, fast pipeline.

3. Protecting Organic Traffic (Soft Scoring)

We changed the point system. Now, the system only gives a penalty for zero clicks if the user viewed more than 3 pages. People who view just 1 page and leave are safe. A JS-Headless Bot is now detected only if it actively surfs the site without making basic events.

How to Use the Results: A Guide for Business

The final step was saving the results into a partitioned (by date) and clustered (by bot type) table named bot_classification.

This is not just a table. It is the foundation for three separate business processes.

1. Dashboard Connection (Data Visualization)

The table is designed to connect directly to Looker Studio or Tableau.

  • Threat Summary (For Managers): Traffic distribution charts. How much budget goes to scrapers? What percentage of attacks did we block?
  • Data Quality Control: The table has user_agent and referrer columns. An analyst can always click on the Spam Bot category, look at the real websites (like traffic-bot.xyz), and check that the rules work correctly.

2. Cleaning Marketing Data

The secret to using bot_classification is simple — the table acts as a Blacklist. When building reports for ROI, attribution, or sales funnels, an analyst only needs to make a simple LEFT JOIN with the sessions table:

SQL

SELECT s.*
FROM `owoxbi_ga4_sessions` s
LEFT JOIN `web_analysts.bot_classification` b
  ON s.sessionId = b.session_id
WHERE b.session_id IS NULL -- We take only clean humans

Marketers will finally see the real website conversion rate, cleaned of 40,000 scrapers that were ruining the sales funnel numbers.

3. Infrastructure Audit and Security

A high concentration of Low-Interaction Bots or Malicious Bots (programs that fill out forms) is a direct signal for DevOps. If thousands of sessions come to download your catalog, the data from the Analytics Firewall dashboard can be used to set rules in Cloud Armor or Nginx. We know their locations, we know their User Agents, and we see their patterns.

Conclusion

We built a system that respects both business and math. We stopped fighting the windmills of marketing noise and focused BigQuery’s computing power on real infrastructure threats. Analytics Firewall is proof that the best code is not the one that catches the most rule-breakers, but the one that knows exactly who to forgive.

Similar Posts