Building an Enterprise Analytics Firewall: A DataOps Approach to Bot Mitigation in Google Cloud

Analytics data without strict traffic filtering actively damages business intelligence. When automated traffic infiltrates the data warehouse, it artificially inflates session volumes, drives down Conversion Rates, and distorts Cost Per Action (CPA) and Customer Acquisition Cost (CAC) metrics.

To address this, we engineered an Analytics Firewall natively within Google Cloud Platform (GCP). The objective was to identify and classify bot traffic directly within BigQuery, optimize querying costs, and establish an automated feedback loop with infrastructure security.

This article details our architectural evolution from querying raw event streams to deploying a fully orchestrated DataOps pipeline integrated with Server-Side GTM and Google Cloud Armor.

Phase 1: The Raw Data Approach and the FinOps Impact

The standard starting point for many data teams is analyzing the raw Google Analytics 4 (GA4) events_* tables. Our initial logic relied on evaluating individual user actions over a rolling 10-day window.

To find bots, we wrote queries that looked like this:

SQL

SELECT
  user_pseudo_id,
  COUNT(event_name) AS total_events,
  COUNTIF(event_name = 'click') AS clicks,
  TIMESTAMP_DIFF(MAX(event_timestamp), MIN(event_timestamp), SECOND) AS lifespan_seconds
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 10 DAY)) 
                      AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
GROUP BY user_pseudo_id
HAVING total_events > 50 AND clicks = 0

The Architectural Flaw: BigQuery uses the Dremel columnar storage engine. Billing is calculated strictly based on the volume of data scanned.

Formula for on-demand pricing: Query Cost = (Bytes Processed / 1 TiB) * $6.25

Querying raw GA4 data involves processing deeply nested arrays. By executing complex aggregations on raw tables for a 10-day lookback window every day, we forced the engine to rescan the same historical terabytes repeatedly.

Furthermore, grouping data by user_pseudo_id over 10 days merged distinct sessions. Legitimate, highly active users who visited the site multiple times a week accumulated enough page views to trigger the total_events > 50 AND clicks = 0 rule. We were burning computing budget to penalize active customers.

Phase 2: Session-Based Aggregation and the False Positive Trap

To resolve the compute inefficiency, we shifted the architecture to query pre-calculated session tables (such as owoxbi_ga4_sessions or dbt-materialized session models). Because the data is already grouped into discrete, time-bound sessions, BigQuery processed significantly fewer bytes.

We ran a test using strict engagement rules to identify “Idle Bots”:

SQL

SELECT session_id
FROM `project.dataset.sessions`
WHERE interactions = 0 
  AND duration_seconds < 2

During a 48-hour test, the system flagged over 513,000 bot sessions. However, analyzing the anomaly distribution revealed a critical logic error regarding mobile web behavior. Over 67% of the flagged sessions exhibited this exact fingerprint:

  • Source / Medium: tiktok.com / referral or instagram.com / referral
  • Operating System: iOS or Android
  • Interactions: 0 clicks, 0 scrolls

In reality, these were human users performing a standard mobile bounce. A user clicks an ad inside the TikTok app, the embedded in-app browser (WebView) initiates the page load firing a session_start event, and the user immediately swipes back.

Deploying this binary algorithm would have decimated legitimate paid traffic data. We eliminated the “Idle Bot” category entirely, shifting the responsibility for filtering accidental ad clicks back to the ad networks’ proprietary anti-fraud systems.

Phase 3: The Behavioral Scoring Matrix

To replace binary rules, we developed a weighted scoring matrix in SQL. The system assigns penalty points based on specific behavioral anomalies. A session is classified as a bot only if the cumulative score exceeds a threshold of 100 points.

SQL

WITH scored_sessions AS (
  SELECT
    session_id,
    traffic_source,
    (
      -- Missing initialization
      IF(session_start_event IS NULL, 20, 0) +
      
      -- Suspicious entry
      IF(traffic_source = 'direct' AND referrer IS NULL, 10, 0) +
      
      -- Headless scraper traversing multiple pages without JS DOM events
      IF(interactions = 0 AND pageviews >= 4, 60, 0) +
      
      -- Non-human velocity (e.g., 100 events in 10 seconds)
      IF(total_events >= 100 AND duration_seconds < 10, 80, 0)
    ) AS bot_score
  FROM `project.dataset.parsed_sessions`
)

This “Soft Scoring” approach ensures that sophisticated Headless bots are detected through abnormal multi-page traversal, while standard human bounces (1 pageview, 0 interactions) receive exactly 0 penalty points.

Phase 4: BigQuery SQL Optimization Techniques

To execute the scoring matrix at scale, we applied two advanced BigQuery optimizations to minimize Slot Time and RAM consumption.

1. Early Fingerprint Evaluation

Parsing behavioral arrays is computationally expensive. Reading string headers is cheap. We restructured our classification logic to prioritize explicit technical signatures using REGEXP_CONTAINS.

SQL

SELECT
  session_id,
  CASE
    -- 1. Signature check (Executes first, short-circuits if true)
    WHEN REGEXP_CONTAINS(LOWER(user_agent), r'(python-requests|ahrefsbot|curl|headlesschrome|bot|spider)') 
      THEN 'Signature Bot'
    
    -- 2. Behavioral check (Evaluated only if the first condition is false)
    WHEN bot_score >= 100 
      THEN 'Behavioral Bot'
      
    ELSE 'Human'
  END AS classification
FROM scored_sessions

If the user_agent matches known automated tools, the session is instantly classified.

2. Single-Pass Array Processing

Instead of using multiple scalar subqueries to extract different event metrics from the hits array, we implemented the SELECT AS STRUCT pattern.

SQL

SELECT 
  session_id,
  (SELECT AS STRUCT
     COUNTIF(event_name IN ('scroll', 'click')) AS interactions,
     COUNTIF(event_name = 'form_submit') AS form_submits,
     COUNTIF(event_name = 'page_view') AS pageviews
   FROM UNNEST(events) 
  ) AS metrics
FROM `project.dataset.sessions`

This forces the Dremel engine to unnest the array exactly once per session in active memory. The database extracts all necessary metrics in a single linear pass without writing physical temporary tables.

Phase 5: Storage Architecture and Dataform Orchestration

The pipeline outputs to a dedicated production table: bot_classification.

We manage this using Dataform (Google Cloud’s built-in SQL orchestrator). The pipeline is configured as an incremental model. Instead of scanning years of history, the MERGE statement only processes new sessions from the last 24 hours.

Dataform configuration (.sqlx):

JavaScript

config {
  type: "incremental",
  schema: "web_analytics",
  name: "bot_classification",
  uniqueKey: ["session_id"],
  bigquery: {
    partitionBy: "DATE(session_date)",
    clusterBy: ["classification", "traffic_source"]
  }
}

SELECT 
  session_id,
  DATE(session_timestamp) AS session_date,
  classification,
  traffic_source,
  ip_address
FROM ${ref("scored_sessions")}

${when(incremental(), `WHERE DATE(session_timestamp) >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)`)}

Partitioning ensures downstream queries only scan specific date ranges. Clustering organizes the data physically by category, enabling extremely fast and cheap LEFT JOIN operations when analysts filter out bots in marketing dashboards.

Phase 6: Edge Filtering and Proactive Defense

To evolve from passive reporting to active infrastructure defense, the architecture integrates components outside of the data warehouse.

1. Server-Side GTM (sGTM) Edge Filtering

Processing obvious bots in BigQuery incurs streaming inserts and storage costs. We configured Server-Side Google Tag Manager as the first line of defense. Using a custom Client template written in sandboxed JavaScript, we evaluate the headers:

JavaScript

const getRequestHeader = require('getRequestHeader');
const returnResponse = require('returnResponse');
const userAgent = getRequestHeader('User-Agent') || '';

if (userAgent.toLowerCase().indexOf('curl') !== -1) {
  // Drop the request at the edge, preventing a BigQuery insert
  returnResponse({
    statusCode: 403,
    body: 'Forbidden'
  });
}

2. Google Cloud Armor Automation Loop

A high concentration of “Malicious Bots” (automated credential stuffing or API scraping) requires infrastructure-level mitigation.

We implemented a proactive defense loop using Cloud Run and the Cloud compute API. A Scheduled Query extracts IP addresses that exceeded the malicious scoring threshold and triggers a Cloud Run job via Pub/Sub.

Python Cloud Run snippet (updating Cloud Armor):

Python

from google.cloud import compute_v1

def block_malicious_ips(project_id, policy_name, ip_list):
    client = compute_v1.SecurityPoliciesClient()
    
    # Construct the rule to block identified bot IPs
    rule = compute_v1.SecurityPolicyRule(
        action="deny(403)",
        priority=1000,
        match=compute_v1.SecurityPolicyRuleMatcher(
            versioned_expr="SRC_IPS_V1",
            config=compute_v1.SecurityPolicyRuleMatcherConfig(
                src_ip_ranges=ip_list  # Array of IPs from BigQuery
            )
        )
    )
    
    # Apply the rule to the global Cloud Armor policy
    request = compute_v1.AddRuleSecurityPolicyRequest(
        project=project_id,
        security_policy=policy_name,
        security_policy_rule_resource=rule
    )
    client.add_rule(request=request)

Business Application

The bot_classification table serves three distinct business functions:

  1. Clean Marketing Attribution: Analysts use the table as a blocklist. By performing a LEFT JOIN against the main sessions table (WHERE bot_classification.session_id IS NULL), ROI, LTV, and conversion funnels are calculated using exclusively validated human traffic.
  2. Executive Dashboards: Connected directly to Looker Studio, providing security and marketing teams with visibility into scraper volume, budget saved, and blocked attack vectors.
  3. Automated Security: The architecture bridges the gap between Analytics and DevOps, turning BigQuery behavioral data into active WAF rules in Cloud Armor.

Similar Posts