July 30, 2020
0 Comments
Years ago, Webris ran into a data-scale problem. We needed to pull a lot client data from different data channels, and time was bloating out of control.
We also saw a huge problem for our clients. They had straightforward questions like: “Is my return on ad spend better on Facebook or Google?” Simple reports didn’t give them the answers they wanted.
We needed a universal, comprehensive, customizable reporting solution.
So, we built one.
In this article, I’m going to show you how our agency uses the magic of Google BigQuery to solve all these problems, and show you custom reports we call “Recipes” that help us launch our campaigns faster.
Here’s how Google explains it:
“BigQuery is a cloud-based data warehouse for storing + processing large datasets.”
Here’s how we explain it:
Allow me to illustrate with an example.
Let’s say you have run eCommerce shop with 20,000 SKUs. Running any sort of data analysis on a website this size is slow, tedious or downright impossible.
Pulling the data down from each tool, formatting, analyzing… spreadsheet tools will get bogged down quickly.
For one company, pulling data from multiple sources can be a headache. For an agency with 50 clients, and over 200 accounts to manage, this becomes intractable.
Enter BigQuery.
BigQuery lets us just push all that eCommerce shop data (via API) to a cloud-based project where it can be processed and formatted at lightning speed. No more slow browsers or crashed spreadsheets, BigQuery can handle limitless data.
But wait…there’s more!
We can sync our BigQuery database with numerous visualization tools:
What’s that mean? We’re able to combine data from different platforms into powerful, custom reports.
There’s dozens of benefits to leveraging the power of BigQuery.
David offers his insights below:
1. Less work, better margins
A big portion of our team’s working hours was swallowed up by pulling data, formatting it with macros in Excel, analyzing, and then reporting on it. By leveraging BigQuery as a data pipeline, we reduced that time to a fraction of what it was. Setup and programming is done once, then non-technical team members can update data using simple inputs. The result? Far better reports, in less time.
2. Unique capabilities allowed us to close more/bigger deals
With BigQuery, we had access to every piece of marketing data we could ever need, all in one place. With this data, we can create custom outputs that essentially function as our own custom software (we call them “Recipes”).
Our clients loved these Recipes, and they’ve been a huge unique selling proposition for our agency.
We can easily customize this ‘software’ per-project, updating data, or reports with minimal additional effort.
3. Limitless data processing capacity
No more crashing spreadsheets or slow software to deal with. Leveraging enterprise processing power speeds up our work.
4. Complete data privacy
This is huge when when working with enterprise companies that insist on data privacy. We own our database, and we control which members of our organization have access via permissions levels. It creates a highly secure cloud-based data security system – 100% backed and encrypted by Google’s leading Cloud Security.
Our in-house automation was so successful, we launched a company to help other agencies leverage BigQuery through a process we call the “Agency Data Pipeline”. It’s based on the same framework we use for our own data. There are three main stages:
The Recipes are the main automation vehicle. Recipes automate the tedious, repeatable reports that our team runs every day, and allow us powerful customization on a per-project basis.
Here are a few Recipes we use on a regular basis.
This Website Quality Audit is more than just a standalone Recipe. We call this the “swiss army knife” of SEO audits because it can be used in so many ways. Part technical audit, part content audit, part keyword research tool, it does a little bit of everything.
This allows us to dive into our next campaign steps – keyword research and content creation – with a complete roadmap.
What goes into the Recipe?
What do we automate in BigQuery?
Imagine you have a 10,000 page eCommerce site to work through, how are you supposed to make a content plan for each page? No one could categorize those pages effectively, in a reasonable time frame, so we automate the organization so we can focus our efforts where it matters most.
How to we analyze the Recipe?
The output is a Google Sheets audit (full of all the SEO data in the world) and a Data Studio dashboard. We’ll push this audit forward into every other phase of our SEO Sprints.
The goal here is to get a picture of ROAS (return on ad spend) for ad campaigns by bringing together data from ad platforms, analytics, and CRMs. This includes adset, ad, and URL level data from:
What goes into the Recipe?
What do we automate in BigQuery?
From here we join the data from these individual sources into a consolidated view which includes:
Our clients with advertising campaigns go crazy for this one because they can now compare data across all channels side-by-side.
How to we analyze the Recipe?
A Google Data Studio full-funnel table showing ROAS by platform, plus a handful more awesome tables.
We work with a lot of eCommerce brands. Because eCommerce usually requires specific reports, we built a Recipe just for these projects. It tracks metrics like ROAS and AOV by the platform. This report includes ads, like the above report, but has other channels like social media and organic.
What goes into the Recipe?
This Recipe calls for only 2 ingredients:
What do we Automate in BigQuery?
How to we analyze the Recipe?
A Google Data Studio full-funnel table showing ROAS by platform.
Because BigQuery makes pulling this data easy, our team uses these reports and more on a daily basis with clients.
This efficiency frees us up to put more focus toward the execution of our unique service offerings – SEO Sprints.
Get started with an SEO Analysis call with Ryan.
We've helped hundreds of websites get more organic traffic from search engines. Give your email, get our blueprint.
We’ve helped hundreds of companies get more “ready to buy” visitors to their websites - we can do the same for you.
Like our site?
Let's design one for you, too.
