In the fast-moving world of Web3, waiting 24 hours for data is like reading yesterday’s newspaper. When you're running a campaign to drive "Wallet Connects," every minute counts.

What are Intraday Tables? (The "Live Stream" of Data)

GA4 usually sends data to BigQuery once a day in a polished "Daily Table." However, it also offers Intraday Tables (events_intraday_). Think of these as a "Live Stream." Data is piped into BigQuery in near real-time—often within minutes of a user clicking a button on your site.

The Catch: Why Real-Time Data is Usually "Broken"

While Intraday tables are fast, they have significant limitations that often make them useless for standard marketing reports:

The Attribution Gap: In real-time streams, GA4 often leaves the campaign and source fields NULL for new users. If you run a standard query, 90% of your traffic looks like "Direct/None."

Disconnected Events: A user might land on your site via a UTM link (the page_view event), but when they click connect_wallet two minutes later, that second event doesn't carry the campaign info.

Schema Volatility: Intraday data is "raw." It hasn't been cleaned or processed by Google’s attribution algorithms yet.

To get accurate real-time insights, you can't just "read" the table; you have to "reconstruct" the logic. The SQL script provided in this GitHub repo uses two advanced techniques to fix these limitations: https://github.com/madilk/big-query-real-time-data-web3-marketing-campaign-acquisition/blob/main/bigquery

1. Unnesting "Hidden" Data

Instead of relying on Google’s pre-processed attribution columns (which are often empty), the query goes straight to the source. It uses UNNEST(event_params) to extract the ga_session_id directly from the raw data packets. This ensures we can group every action a user takes into a single, coherent journey.

2. The "Session Stitching" Hack (FIRST_VALUE)

The real "magic" in the code is the use of the FIRST_VALUE window function

FIRST_VALUE(collected_traffic_source.manual_campaign_name IGNORE NULLS) 
OVER (PARTITION BY user_pseudo_id, session_id ORDER BY event_timestamp)

This tells BigQuery: "Look at this user's entire session. Find the very first event that actually has a campaign name (the landing), and 'stitch' that name to every other event in the session—including the wallet connection." By "filling down" this data, we overcome the Attribution Gap. We now know exactly which campaign drove a connect_wallet event, even if Google hasn't officially linked them yet.

Real-Time Decisions: Why Speed Matters By overcoming these technical hurdles, Web3 teams can make three critical decisions in real-time:

Kill "Zombie" Ads Instantly: If Ad Set A is getting clicks but zero "Wallet Connects" in your hourly breakdown, reallocate that budget to Ad Set B by lunchtime.

Influencer Management: Watch traffic spikes the second a tweet goes live. If users land but don't convert, jump into your Discord/Telegram immediately to troubleshoot.

Technical Smoke Testing: If connect_wallet events drop to zero after a code push, you’ve found a bug before it ruined your daily ROI.

The Bottom Line In Web3, the feedback loop needs to be as fast as the blockchain itself. By using unnested parameters and session-stitching logic, you move from reactive reporting to proactive growth.