Paid Acquisition Reporting in BigQuery

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 we put more resources? Where should we 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.

The Problem

Ahmad had been leveraging 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:

“We had a client with 5,000 transactions per day, which we use to get into Google Sheets…They couldn’t 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.  

He leveraged 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

Moving 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.  

Interested in your own Data Pipeline? Schedule a time to chat with me directly about automating your data workflows.

Ready for BigQuery?

Get off the ground quickly, with customizable data pipeline Recipes for BigQuery.