BigQuery A/B Analyzer is a scalable, open-source and configuration-driven framework for running A/B analysis on BigQuery data.

The definition of the test is decoupled from the execution, and you can run analysis without writing a single line of SQL. The framework is highly flexible, and let you analyse (almost) any A vs. B variant.

The framework supports the following tools:

• Google Analytics (GA4)
  • Firebase
    • Supports Firebase experiment structure in BigQuery.
• GA4Dataform by Superform Labs
  • Flattened GA4 tables that are faster and more affordable to query.
• Amplitude
• Mixpanel
• PostHog

The solution is built around Google Sheet, BigQuery, Vertex AI and Data Studio.

What It Is (And What It Is Not)

This is not a traffic splitter. It does not replace tools like Firebase Remote Config, Optimizely, Kameleoon, Conductrics, GrowthBook or other solutions for A/B testing.

• What it is: A statistical engine that sits on top of your existing BigQuery data (GA4, GA4Dataform, Firebase, Amplitude, Mixpanel, PostHog).
• What it does: It answers the question, “Is the difference between these two groups mathematically significant?” without you having to write a single line of SQL.

The Architecture: Decoupling Definition from Execution

The solution uses 3 components:

1. Google Sheets: This is your centralized registry. You define the “What” (Variants, Metrics, Filters) here.
2. BigQuery: A script pushes your config to BigQuery, which triggers standardized User Defined Functions (UDFs) to calculate significance.
3. Data Studio: A dashboard that visualizes winners & loosers.

Key Capabilities

1. Filters: The “Precision Lens”

Averages lie. Often, a feature might look neutral overall but is actually broken for a specific group.

The framework allows you to apply filters directly in the config sheet.

• Segmented Analysis: Zoom in on specific audiences by including or excluding users based on device, geography, or traffic source (e.g., “Only count Mobile users”).
• Nested Ecommerce Deep-Dives: You can filter by eg. items.item_id, items.item_category, or items.item_brand to see if a variant specifically boosted the conversion rate of a particular product, category or other items scoped parameters/properties.
• Flexible Logic: Supports both Event and User scopes, utilizing Regex for sophisticated matching (e.g., “Exclude customer type A” or “Include only specific sub-domains”).
• No Re-Coding Necessary: You can pivot from a site-wide analysis to a product-specific deep dive instantly, simply by updating a row in the configuration.

2. User Overlap: Quality Control

Since this framework can analyze “natural” experiments, users might occasionally see both Version A and B.

The BigQuery A/B Analyzer includes strict logic to handle this “contamination.” You can choose to:

• Exclude: Remove them entirely (scientific purity).
• Credit Both: Useful for time-period comparisons.
• First/Last Exposure: Attribute them to the first or last version they saw.

3. Metric Flexibility: Measuring Intensity

Most tools force you into a binary world: Did they convert? Yes/No.

But for products in media, gaming, or e-commerce, the intensity of behavior could also matter.

The framework supports three distinct counting modes:

1. User Conversion Rate: Capped at 100%. (Did they sign up?)
2. Session Conversion Rate: Capped per session. (Did they search?)
3. Total Event Rate: Uncapped. (How many videos did they watch?)
   • Why this matters: If Variant B users watch 5x more videos than Variant A users, a binary “Did they play?” metric would miss the win. This tool captures it.

4. AI Summary: Your Personal Analyst

AI Summary is powered by Google Vertex AI (Gemini). Gemini is interpreting the test, and summarises the result with recommendations. You control the prompt from the Google Sheet, and can easily adjust how recommendations and tone of voice should be.

AI Summary is optional functionality.

5. User Flexibility: You define what a user is

The framework supports 4 different methods for identifying/analysing users:

1. DEVICE_ID: Default method for the selected tool to count and identify users. Ex. GA Client ID.
2. USER_ID: Your own identifier for individual users. Ex. GA User-ID feature.
3. USER_ID_OR_DEVICE_ID: Uses User ID if it exists, if not it fallbacks to Device ID.
4. EXP_DEVICE_ID: Bring your own ID, ex. Cookie ID from the A/B test tool.

The Statistical Engine

Standardizing the math eliminates “analyst bias”. The system automatically applies the correct statistical test based on your metric type:

Metric Type	Method Used	Why this method
Proportions (Yes/No)	Two-Proportion Z-Test	The industry standard for binary data (User/Session scope).
Event Intensity (Counts)	Z-Test for Poisson Rates (using Log-Linear approximation)	Because count data often exceeds 100% (e.g., “views per user”), standard proportion tests fail. Instead, we analyze the Incidence Rate Ratio to measure the relative lift between variants.
Continuous Values ($$$)	Welch’s t-test	Robust against unequal variance (e.g., “whales” skewing revenue data). Unlike Student’s t-test, it does not assume groups are identical.

Reporting

Analysed data can either be downloaded to the Google Sheet, or shown in a Data Studio dashboard.

The Data Studio dashboard is designed to answer “Who won?” at a glance.

Cost Control

The solution can estimate BigQuery cost and query size. Run the analysis for a shorter time period (ex. 1 day) to get a feeling of the cost before you run the full analysis.

Pre-Test Calculator

The Pre-Test Calculator is a planning tool for A/B testing. Before building or launching an experiment, this tool answers the question: “Do we have enough traffic to actually test this?“

By looking at historical BigQuery data, it calculates current baseline metrics and predicts how long the test needs to run to reach statistical significance. This information can also be used to inform AI Summary about the sample size that should be used in the evaluation of the experiment.

Setup

Image below illustrates the setup.

Open-source

This project is open-source. If you want a repeatable way to measure experiments in BigQuery, give it a try. Other tools can also export their data to BigQuery. If “your” tool is missing and you think this solution could be of value, get involved.

Some Afterthoughts

Why I made the BigQuery A/B Analyzer

The creation of this framework started with a simple question from “someone”:

“We just launched this new page. Does it sell more than the other page?”

Although the question was simple, the answer was not. The new page hadn’t been launched as a proper A/B test, and it was impossible to answer the question without diving into BigQuery. After spending considerable time figuring out the How, I finally found the What. I plugged the numbers into an A/B test calculator, and it showed that Variant B, the new page, was significantly better than the old one. Success!

However, after spending all that time on the manual “How,” I decided to make it easier for myself the next time a similar question popped up. From there, it escalated quickly.

Analysing A/B tests in GA4

GA4 shows an estimate of users using HyperLogLog++ (HLL++), not raw user count based on unique client IDs.

In the blog post The Perils of Using Google Analytics User Counts in A/B Testing the author Georgi Georgiev from Analytics Toolkit writes:

The number of users shown in any Google Analytics report should not be used for A/B testing statistical analyses if they are above 12,000 users per test group…

If you are using Google Analytics users for any kind of A/B tests you need to make sure that raw counts are used instead of the HyperLogLog probabilistic estimates..

In a batch of 20 tests, the 6 tests have completely reversed outcomes simply by using exact counts instead of the cardinality estimates offered by Analytics.

The answer to that is BigQuery.

Why this is the “scariest” solution I’ve released

I’m not a programmer by trade, though I’ve released several open-source solutions, such as Google Tag Manager Templates and the GA4 Documentation & Administration solution.

Because this solution is so flexible, the underlying code is naturally complex. If there is a flaw in the SQL or the calculation methods, someone could make an incorrect decision that costs them money. That’s why this is the “scariest” solution I’ve released.

Because of this, I have spent endless hours testing and verifying data. I am now as confident as I can be that the BigQuery A/B Analyzer delivers accurate, reliable results.

The Golden Punchcard Prize

I initially built the BigQuery A/B Analyzer for myself and my colleagues in Knowit AI & Analytics. But with the Superweek Analytics Summit approaching, I decided to enter the Golden Punchcard Prize. I added support for more tools beyond Google Analytics and included functionality that I don’t necessarily need myself, but which I know will be useful for others.

I didn’t make it to the podium this time, but I landed in the Top 5. I see the Golden Punchcard Prize as a fun, friendly competition among peers. That said, if it wasn’t for the “Superweek pressure,” I probably would never have polished this enough to make it publicly available.

Since Superweek I have improved the framework even more.

• AI Summary for helping in interpreting the result.
• Item / product scoped filtering for all tools.
• Pre-Test Calculator for estimating sample size.
• PostHog added.
• Experience Device ID: Bring your own ID, ex. Cookie ID from the A/B test tool.

Summary: What you gain

• Democratized access: Define tests in a Google Sheet, not in SQL. Share the results in Data Studio.
• Standardized math: Consistent statistical methods.
• Scalable execution: Analyze many experiments without rewriting queries.
• Flexible metrics: Conversion, session rates, event intensity, continuous values.
• Better data hygiene: Overlap handling + filters built in.
• Works with your stack: GA4, GA4Dataform, Firebase, Amplitude, Mixpanel, PostHog.
• AI Summary: A personal analyst that delivers actionable recommendations and translates complex data into plain language.
• Cost control: No BigQuery cost surprises.
• Pre-Test Calculator: Do we have enough traffic to actually test this?
• Doesn’t try to replace managed experimentation platforms. It complements them when the power of BigQuery is needed.