The Production GA4 BigQuery Schema: How to Build High-Performance Session & Ecommerce Marts in Pure BigQuery SQL

Set 1: The Dynamic Parameterized Query Layer (On-the-Fly Slice & Dice)

Built for data scientists, analytics engineers, and exploratory queries where materializing tables isn't necessary.

• Persistent Scalar UDFs: Standardize parsing for string, integer, and numeric event parameters, URL query decoding, and Eastern Time conversion.
• Path Normalization UDF: fn_normalize_url_path cleans landing page URLs by removing query parameters, hash fragments, and trailing slashes.
• Centralized Marketing Channel UDF: fn_map_marketing_channel encapsulates all channel rules into a single reusable definition.
• Parameterized Table-Valued Function (TVF): ga4_utils.tvf_get_ga4_events_range(start_date, end_date) allows users to query arbitrary date ranges with clean, flattened fields, lowercased UTMs, centralized marketing channels, and Eastern Time alignment without writing a single UNNEST.

Set 2: The Materialized Production Marts (Hourly Incremental Warehouse)

Built for fast executive dashboards, BI reporting, and automated marketing feeds. Four partitioned and clustered physical tables updated every hour:

1. fct_ga4_sessions: Unified session grain with daily date partitioning (PARTITION BY session_date_est), clustering by marketing_channel, user_pseudo_id, session_id, 0/1 integer goal flags, lowercased UTMs, Eastern Time timestamps (session_start_est, session_end_est), normalized navigation pathing, and consent status.
2. fct_ga4_transactions: Deduplicated purchase orders with revenues, taxes, shipping, coupons, channel attribution, and session attribution.
3. fct_ga4_transaction_products: Unnested item-level line items with SKUs, categories (item_category, item_category_2, item_category_3), quantities, and item revenues.
4. fct_ga4_cart_events: Granular shopping event stream (view_item, add_to_cart, remove_from_cart, view_cart, begin_checkout).

Key Architectural Capabilities

1. Defining Channels Based on Landing Page Alone

In many organizations, high-impact traffic arrives without UTM parameters because links are shared organically or distributed through co-marketing agreements. By passing landing_page_path into the UDF, a session landing on /partners/acme or /creators/summer can be immediately mapped to Strategic Partnerships or Influencer / Creator-even if the UTMs are completely empty or (direct).

2. Elevating Strategic Partner Referrers Above Generic "Referral"

Standard GA4 dumps every external website link into Referral. But if your brand has a major co-marketing partnership with an enterprise vendor or portal (e.g. partnerportal.com), dumping that traffic into generic referrals hides its ROI. Our UDF inspects the referrer domain and elevates strategic alliances into their own channel before generic referral fallback rules apply.

3. Trailing Slash Normalization via fn_normalize_url_path

Urls frequently arrive as /partners/enterprise/ or /partners/enterprise?utm_source=ad. If an analyst writes WHERE landing_page = '/partners/enterprise', visits with trailing slashes or query strings are dropped.

Our normalization function cleans this in a single call:

CREATE OR REPLACE FUNCTION `ga4_utils.fn_normalize_url_path`(url STRING)
RETURNS STRING
AS ((
  SELECT
    CASE
      WHEN cleaned_path IS NULL OR cleaned_path IN ('', '/') THEN '/'
      ELSE REGEXP_REPLACE(cleaned_path, r'/+$', '')
    END
  FROM (
    SELECT LOWER(TRIM(
      COALESCE(
        REGEXP_EXTRACT(url, r'^[a-zA-Z]+://[^/?#]+([^?#]*)'),
        REGEXP_EXTRACT(url, r'^([^?#]*)')
      )
    )) AS cleaned_path
  )
));

Approach A: Pre-Materialized Column in fct_ga4_sessions (Our Implementation)

In our architecture, sp_hourly_ga4_sync executes the UDF at ingest time and permanently writes marketing_channel to fct_ga4_sessions.

• The Advantages:
  • Zero Query-Time CPU Overhead: Dashboards load instantly because BigQuery reads a simple pre-computed string.
  • Partition & Cluster Pruning: Because the table is clustered on CLUSTER BY marketing_channel, ..., a query filtering WHERE marketing_channel = 'Paid Search' scans only the specific storage blocks containing Paid Search rows, saving 80%+ on query scan costs.
• The Operational Trade-off (What Happens When Rules Change?):

  If marketing reclassifies an affiliate network or creates a new channel rule, updating the UDF does not automatically change historical rows in fct_ga4_sessions.

  The Remediation: You must run a targeted partition update to refresh historical sessions:

UPDATE `ga4_marts.fct_ga4_sessions`
SET marketing_channel = `ga4_utils.fn_map_marketing_channel`(
  utm_source,
  utm_medium,
  utm_campaign,
  page_referrer,
  landing_page_path
)
WHERE session_date_est BETWEEN '2026-09-01' AND '2026-10-27';

Approach B: Query-Time Dynamic UDF Execution

Alternatively, you can omit the physical column and invoke fn_map_marketing_channel(...) dynamically inside a SQL query or a dynamic BigQuery VIEW.

• The Advantages:

  Instant Rule Propagation: Any edit to the UDF is immediately reflected across all queries with zero backfilling or DML updates.

• The Disadvantages:
  • Significant Compute Costs (Slot Exhaustion): BigQuery must execute complex string matching and regex logic for every single session row on every single dashboard refresh.
  • Loss of Cluster Pruning: You cannot cluster on a dynamically computed function call.

The dbmarketer Philosophy: Set-Based Wildcard Extraction

Google's daily export model has two fundamental rules:

1. 10/25/2026 sessions do not arrive over multiple days. When Google finishes and delivers events_20261025, that daily export table is closed and settled.
2. BigQuery is a set-based engine. If you need to catch up across 3 missing days, you don't iterate through them one at a time. You query the wildcard table with _TABLE_SUFFIX >= v_next_process_date and process all 3 days in parallel in a single query!

The Three Operational Scenarios Handled Flawlessly:

Scenario 1: Normal Hourly Run (Natural Idle Exit)

• It's 2:00 PM on October 27th.
• fct_ga4_sessions has data up to October 26th (yesterday).
• The procedure checks for _TABLE_SUFFIX >= '20261027' (today).
• Because today is still in progress, Google has not finalized events_20261027.
• The metadata check returns NULL.
• The procedure logs SKIPPED_UP_TO_DATE and terminates in under 1 second. Zero bytes scanned, zero query slots consumed.

Scenario 2: Google Delivers Yesterday's Table

• It's 6:00 AM on October 27th. Google finishes processing and delivers events_20261026.
• The procedure checks: Last processed date was 2026-10-25. Next suffix is '20261026'.
• The metadata check returns MAX(_TABLE_SUFFIX) = '20261026'.
• The single set-based query extracts and materializes all sessions for 2026-10-26 in seconds.
• At 7:00 AM, 8:00 AM, and 9:00 AM, the procedure runs, sees _TABLE_SUFFIX >= '20261027' is not yet ready, and naturally exits.

Scenario 3: Catch-Up Mode (Google 48h Delay or 3-Day Job Outage)

• Suppose the job was paused over a 3-day long weekend, or Google experienced an upstream pipeline delay and delivers 3 days of backlog at once (20261024, 20261025, 20261026).
• Last processed date is 2026-10-23. Next suffix is '20261024'.
• The metadata check returns MAX(_TABLE_SUFFIX) = '20261026'.
• No loop is executed. The procedure runs a single set-based query:

WHERE _TABLE_SUFFIX >= '20261024'
  AND NOT REGEXP_CONTAINS(_TABLE_SUFFIX, r'intraday')

BigQuery processes all 3 days concurrently across hundreds of parallel workers, writes the three date partitions into fct_ga4_sessions, and updates the watermark in one atomic run.

Deploying the Routines & Setting Up the Hourly Scheduled Call

In BigQuery terminology, User-Defined Functions (both scalar and table-valued) and Stored Procedures are formally catalogued as Routines.

1. The Routine Source Scripts

All routines are provided in two ready-to-run formats in the sql/ directory:

• Consolidated All-In-One Script (sql/01_create_all_routines.sql): Deploys all 11 BigQuery routines in one single execution (all 9 scalar UDFs, the 1 TVF, and the sp_hourly_ga4_sync stored procedure).
• Modular Scripts:
  • sql/01_udfs.sql: Deploys the 9 parsing and channel-mapping scalar UDFs and the tvf_get_ga4_events_range table-valued function.
  • sql/03_procedure_hourly_sync.sql: Deploys the sp_hourly_ga4_sync stored procedure.
• Scheduling Guide (sql/05_schedule_hourly_sync.sql): Contains the SQL trigger, console instructions, bq CLI command, and Terraform definition.

2. Setting Up the Hourly Scheduled Call via BigQuery Console UI

Once the routines are created, set up the hourly scheduled execution directly in Google Cloud Console:

1. Open BigQuery Studio (https://console.cloud.google.com/bigquery).
2. Paste the routine execution statement into the Query Editor:
   sqlCopy

   CALL `your_project_id.ga4_marts.sp_hourly_ga4_sync`(NULL);

3. Click the Schedule dropdown in the query toolbar and choose Create new scheduled query.
4. Configure the scheduled query settings:
   • Name for scheduled query: ga4_marts_hourly_sync
   • Repeats: Hourly
   • Starts: Default (Immediately)
   • Dataset: Leave blank (the stored procedure handles all table destination writes)
   • Destination table: Leave blank
   • Location: Select your dataset region (e.g. US or EU)
5. Click Save. BigQuery will now trigger the routine at minute 0 of every hour.

3. Setting Up the Hourly Scheduled Call via Google Cloud CLI (bq mk)

Alternatively, deploy the scheduled query with a single terminal command:

bq mk \
  --transfer_config \
  --project_id="YOUR_PROJECT_ID" \
  --data_source=scheduled_query \
  --display_name="GA4 Marts Hourly Sync" \
  --schedule="every 1 hours" \
  --location="US" \
  --params='{"query":"CALL `YOUR_PROJECT_ID.ga4_marts.sp_hourly_ga4_sync`(NULL);"}'

4. Monitoring the Hourly Routine

Every hourly run records its progress, processed row counts, and status in ga4_marts.etl_watermark:

SELECT
  execution_timestamp,
  table_name,
  last_processed_date_est,
  rows_processed,
  execution_status,
  notes
FROM `ga4_marts.etl_watermark`
ORDER BY execution_timestamp DESC
LIMIT 20;

Daily Date Partitioning: PARTITION BY session_date_est

fct_ga4_sessions is partitioned by the Eastern Time business date (session_date_est).

CREATE TABLE IF NOT EXISTS `ga4_marts.fct_ga4_sessions` (
  ...
)
PARTITION BY session_date_est
CLUSTER BY marketing_channel, user_pseudo_id, session_id

When an analyst queries:

WHERE session_date_est >= DATE_SUB(CURRENT_DATE('America/New_York'), INTERVAL 30 DAY)

BigQuery automatically prunes all partitions outside that 30-day window. Years of historical web traffic are completely ignored, reducing query bytes scanned by 90% to 98%.

Multi-Column Clustering: marketing_channel, user_pseudo_id, session_id

Within each date partition, BigQuery collocates data blocks based on clustering keys:

1. marketing_channel: Placed first in the clustering list because almost every marketing report filters by channel (WHERE marketing_channel = 'Paid Social').
2. user_pseudo_id: Enables rapid user-level stitching and cohort retention lookups.
3. session_id: Speeds up session deduplication and cross-table joins with orders.

Why Binary INT64 (0 or 1) Flags are Superior

In fct_ga4_sessions, each goal is evaluated as a strict INT64 with values of only 0 or 1:

MAX(IF(event_name = 'view_item', 1, 0)) AS has_view_item,
MAX(IF(event_name = 'add_to_cart', 1, 0)) AS has_add_to_cart,
MAX(IF(event_name IN ('begin_checkout', 'checkout'), 1, 0)) AS has_checkout,
MAX(IF(event_name = 'purchase', 1, 0)) AS has_purchase,
MAX(IF(event_name IN ('newsletter_signup', 'email_signup', 'lead', 'sign_up'), 1, 0)) AS has_newsletter_signup

This simple architectural shift unlocks mathematical superpowers:

1. Volume via SUM(): The count of sessions reaching a goal is simply SUM(has_goal). Because addition is commutative and associative, BigQuery aggregates these fields using high-speed SIMD vector processing.
2. Conversion Rate via AVG(): The conversion rate of any goal across any slice of data is simply AVG(has_goal). For example, AVG(has_purchase) returns 0.0342 (a 3.42% conversion rate).
3. Trivial BI Setup: In Looker Studio or Power BI, drag has_purchase into your scorecard or table:
   • Aggregation = SUM → Instant Total Conversions.
   • Aggregation = AVERAGE → Instant Conversion Rate %.
4. Additive Multi-Goal Funnels in a Single Pass:

-- Clean, Fast, Additive Channel Funnel
SELECT
  marketing_channel,
  COUNT(1) AS total_sessions,
  
  -- Volumes
  SUM(has_view_item) AS views,
  SUM(has_add_to_cart) AS cart_adds,
  SUM(has_checkout) AS checkouts,
  SUM(has_purchase) AS purchases,
  SUM(has_newsletter_signup) AS leads,

  -- Micro-Conversion Rates
  ROUND(AVG(has_view_item) * 100, 2) AS view_rate_pct,
  ROUND(AVG(has_add_to_cart) * 100, 2) AS cart_rate_pct,
  ROUND(AVG(has_checkout) * 100, 2) AS checkout_rate_pct,
  ROUND(AVG(has_purchase) * 100, 2) AS purchase_cvr_pct,
  ROUND(AVG(has_newsletter_signup) * 100, 2) AS lead_cvr_pct
FROM `ga4_marts.fct_ga4_sessions`
WHERE session_date_est >= DATE_SUB(CURRENT_DATE('America/New_York'), INTERVAL 30 DAY)
GROUP BY marketing_channel
ORDER BY purchases DESC;

The Underlying Mechanics

1. event_timestamp: Stored as an integer representing microseconds elapsed since the Unix epoch (January 1, 1970). This timestamp is ALWAYS in UTC.
2. event_date: Stored as a string (e.g. '20261027'). This date is determined by the Reporting Timezone configured in your GA4 Property Admin settings (for example, America/New_York or America/Los_Angeles).

The Midnight Boundary Bug

Imagine a customer in New York purchases an item at 9:30 PM EST on October 27, 2026:

• Local Time (New York): 2026-10-27 21:30:00 EST
• UTC Equivalent: 2026-10-28 01:30:00 UTC
• GA4 event_date in table: '20261027' (Because GA4 admin is set to New York)
• GA4 event_timestamp in table: 1793151000000000 (UTC microseconds)

If an analyst writes:

-- WRONG: Defaults to UTC calendar date
SELECT 
  DATE(TIMESTAMP_MICROS(event_timestamp)) AS session_date,
  COUNT(1) 
FROM raw_events 
GROUP BY 1;

BigQuery casts the timestamp to UTC date: October 28th. Meanwhile, the GA4 UI reports the transaction on October 27th. The CEO compares the dashboard against the GA4 UI, sees mismatched daily revenues, and assumes the data warehouse is broken.

The Solution: Explicit Microsecond-to-Eastern Conversion

Our pipeline introduces dedicated functions that explicitly convert UTC timestamps to America/New_York (handling both Standard Time and Daylight Saving Time automatically):

CREATE OR REPLACE FUNCTION `ga4_utils.fn_utc_to_est_datetime`(ts_micros INT64)
RETURNS DATETIME
AS (
  DATETIME(TIMESTAMP_MICROS(ts_micros), 'America/New_York')
);

CREATE OR REPLACE FUNCTION `ga4_utils.fn_utc_to_est_date`(ts_micros INT64)
RETURNS DATE
AS (
  DATE(TIMESTAMP_MICROS(ts_micros), 'America/New_York')
);

In fct_ga4_sessions, we partition on session_date_est, ensuring that session durations (DATETIME_DIFF(session_end_est, session_start_est, SECOND)), daily rollups, and hour-of-day charts align 100% with real-world US commercial hours.

Why Naive Pipelines Drop 20% to 40% of Users

Most engineers write their session extraction as:

-- WRONG: Silently deletes unconsented traffic!
SELECT
  user_pseudo_id,
  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id
FROM raw_events
WHERE user_pseudo_id IS NOT NULL;

By doing this, every unconsented user is dropped from your reporting warehouse. Your site traffic drops, your customer acquisition cost (CAC) calculations are skewed, and your bounce rates become inaccurate.

The dbmarketer Fallback Strategy

Our architecture refuses to discard unconsented visitors. We handle them by:

1. Creating a deterministic synthetic session key using BigQuery's FARM_FINGERPRINT across the anonymous payload:
   sqlCopy

   COALESCE(
     IF(user_pseudo_id IS NOT NULL AND session_id IS NOT NULL,
        CONCAT(user_pseudo_id, '.', CAST(session_id AS STRING)),
        NULL),
     CONCAT('anon.', event_date, '.', CAST(FARM_FINGERPRINT(CONCAT(COALESCE(user_pseudo_id, 'unconsented'), CAST(event_timestamp AS STRING), COALESCE(geo.country, ''))) AS STRING))
   ) AS session_key

2. Storing explicit consent columns on each session:
   • consent_status: 'GRANTED', 'DENIED', or 'UNKNOWN'.
   • is_consented: 1 (Granted) or 0 (Denied/Cookieless).

This preserves true site traffic baselines while enabling powerful privacy analytics, such as comparing the conversion propensity of consented vs. unconsented visitors.

The Case Sensitivity Trap

In SQL, string comparisons are case-sensitive:

'Facebook' != 'facebook' != 'FACEBOOK'

When campaign managers run paid ads across different teams, variations in UTM capitalization create split rows in grouping tables. Even worse, GA4's channel grouping rules fail to match title-cased parameters, tossing paid media traffic into (Unassigned).

Our pipeline enforces rigorous normalization via ga4_utils.fn_clean_utm:

CREATE OR REPLACE FUNCTION `ga4_utils.fn_clean_utm`(raw_val STRING)
RETURNS STRING
AS (
  NULLIF(LOWER(TRIM(REGEXP_REPLACE(raw_val, r'\+', ' '))), '')
);

• Forces all characters to lowercase.
• Trims leading and trailing whitespace.
• Replaces URL-encoded + symbols with standard spaces.
• Converts empty strings '' into true SQL NULLs.

Full Spectrum Coverage: All 9 UTM Parameters

Our schema models the full 9-parameter matrix, including Google's modern paid media taxonomy:

What is in the Git Repository

The repository is structured into four core directories:

1. scripts/ (Automated Orchestration): Turnkey deployment scripts designed for CI/CD runners, Google Cloud Shell, and local developer workstations (macOS, Linux, and Windows PowerShell).
2. sql/ (Database Assets & Routines): Pure BigQuery SQL files containing dataset initialization, 11 compiled routines (9 scalar UDFs, 1 table-valued function, and 1 stored procedure), partitioned table DDLs, analytical business queries, and scheduling templates.
3. schemas/ (BigQuery JSON Schemas): Production-ready JSON schema definitions matching BigQuery's native table definition format for automated schema validation, migration tools, or deployment via bq mk --schema.
4. Root Documentation & Licensing: The comprehensive architectural whitepaper (ARTICLE.md), an interactive executive HTML document (ARTICLE.html), the repository guide (README.md), and an Apache 2.0 open-source license (LICENSE).

Script Inventory & Functional Summary

The table below summarizes each file in the repository, its target BigQuery object or operational scope, and what it executes:

Executing Option #1: Turnkey Deployment

To deploy the entire architecture into your Google Cloud project in under 5 minutes:

1. Clone the repository:
   bashCopy

   git clone https://github.com/rudy0903/dbmarketer.git
   cd dbmarketer/ga4-bigquery-schema

2. Execute the automated deployment script:
   Linux / macOS / Google Cloud Shell:

   bashCopy

   chmod +x scripts/deploy_all.sh
   ./scripts/deploy_all.sh "your-gcp-project-id" "US"

   Windows (PowerShell):

   powershellCopy

   .\scripts\deploy_all.ps1 -ProjectId "your-gcp-project-id" -Location "US"

3. Verify Execution Health:

   Once triggered, inspect the etl_watermark table to confirm the ingestion pipeline is operational:

   sqlCopy

   SELECT * 
   FROM `ga4_marts.etl_watermark` 
   ORDER BY execution_timestamp DESC 
   LIMIT 10;