Paid Acquisition Reporting in BigQuery

David Krevitt

Lover of laziness, connoisseur of lean-back capitalism. Potentially the #1 user of Google Sheets in the world.

Meet Ahmad Kanani…

He’s the founder of the Siavak agency, and an expert in conversion-rate optimization (CRO).

Of course data analysis + reporting automation is a huge part of his team’s efforts – everything comes down to data.  

Ahmad’s most common data analysis is to blend ad spend + conversion data across channels (Facebook Ads, Google Ads, etc), to build a single comparative picture of conversion rates + costs across channels.

As Ahmad put it to me, the online businesses he works with are trying to answer questions like:

“Where should I put more resources? Where should I focus? And then those questions, they need to be answered with data. And it is hard to do it manually. I mean, going to each platform, trying to manually compare the numbers together is really tedious.” 

That’s what got Ahmad started in reporting automation in the first place – to answer these core questions while saving his team + his clients’ precious time.

Catch my interview with Ahmad in the video below, or read on to see why + how his team made the jump from Google Sheets to BigQuery.

The Problem

Ahmad had been building automated reporting in Google Sheets for years.  

But as his (mostly e-commerce) clients’ data size grew, his team eventually needed to move beyond Google Sheets for data analysis.  As he puts it:

“So we had a client with 5,000 transactions per day, which we use to get into Google Sheets…They couldn’t do, you know, month-over-month analysis, quarter-over-quarter analysis. And the volume of data was actually the trigger for me to look into other options.”

This becomes particularly acute he started to blend in ad platform data, as pulling in date + campaign-level metrics from Google and Facebook Ads quickly bogged down his Sheets.

The Solution

Ahmad decided to make the jump to BigQuery from Sheets, to unlock unlimited data storage + processing capacity.  

To help him make this move, he picked up our Build your Agency Data Pipeline course, which walks through the step-by-step process of building a data pipeline in BigQuery.  

He eventually built cross-channel paid acquisition dashboards in Google Data Studio, which read the data in his BigQuery data warehouse.

One important thing he was also able to do in BigQuery, was grouping campaigns by strategy with custom regex tagging.  

This type of tagging would be very slow to run in Google Sheets, but is a breeze in BigQuery:

As Ahmad put it to me, his e-commerce clients always want to answer the question “what’s the cost per acquisition of this strategy compared to the rest of them?”

And by running his data pipelines on BigQuery, Ahmad now has much faster + broader access to these answers.

The Results

Mmoving to BigQuery opened up not only access to more data analysis power – it also allowed his team to focus on *new types of analyses* and push their work to the next level.  

As he told me recently:  

“And it opened up much more capability for us to do something exciting.  It is not just you raise the volume limits, though I’m happy that we did that.  It was a turning point, and when I look back at it, it was a good decision.”

He’s gone on to build data pipelines in BigQuery for a number of online businesses, who’ve been able to save tons of time with tedious manual data analysis.  

Thanks again to Ahmad Kanani for joining me to chat about his journey building data pipelines in BigQuery – you can find more info about his conversion-rate optimization work over at Siavak.com

See you next time!

Wanna get more done?

✋ I can help with that…you can find everything we've ever built in this single Template Vault.

CRACK THE VAULT