📘 This audit will change your life...

📘 Game changer 🤯

SHOW ME
WEBRIS Digital Agency

Our Internal Tech Stack

  • Category : Strategy
  • 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.

what is a data pipeline

 

WTF is Google BigQuery?

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.

setting up bigquery

But wait…there’s more!

We can sync our BigQuery database with numerous visualization tools:

  • Google Sheets
  • Google Data Studio
  • Tableau
  • Looker

What’s that mean? We’re able to combine data from different platforms into powerful, custom reports.

bigquery outputs

 

Why BigQuery for an Agency?

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”).

  • Technical SEO reports
  • Monthly status reports
  • Keyword performance tracking
  • Funnel visualizations

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.

Examples of BigQuery in action

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:

  1. Import all of our raw data to a Google BigQuery database (GA, GSC, AdWords, FB Ads, etc)
  2. Write, and apply, all of the logic using SQL that would normally take place in Google Sheets
  3. Output the information to where we need it, be that Google Sheets or Data Studio (aka Recipes)

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.

Recipe #1: The Website Quality Audit (WQA)

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?

  • A full Deepcrawl site scrape
  • Google Analytics traffic and conversions
  • Google Search Console impressions and CTRs
  • Majestic links
  • SEMrush keywords

What do we automate in BigQuery?

  • Map URL level data from all sources (keyword rank, sessions, backlinks, etc.,) to create an SEO data ‘map’
  • Auto-assign page-level actions, based on a set of SEO best practice rules
  • Auto-assign a ‘page type’ to each URL (i.e. blog post, product page, etc)

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.

bigquery recipe wqa

Recipe #2: The Ad Funnel Breakdown

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?

  • Google Ads
  • Bing Ads
  • Facebook Ads
  • Twitter Ads
  • LinkedIn Ads
  • Google Analytics
  • Adobe Analytics
  • Youtube ads
  • Any other source of advertising data.

What do we automate in BigQuery?

From here we join the data from these individual sources into a consolidated view which includes:

  • Ad platform data (spend, impressions, clicks and conversions)
  • Analytics data (sessions, goal completions, transactions and revenue)
  • CRM data (leads, opportunities, deals and revenue)

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.

ad funnel breakdown

 

Recipe #3: eCommerce (Shopify) Buyer Segmentation

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:

  • Shopify orders + customer data
  • Google Analytics order level attribution data

What do we Automate in BigQuery?

  • Buyer-level frequency, spend and AOV
  • Growth + retention rates for each acquisition channel
  • Frequency segmentation, revenue segmentation

How to we analyze the Recipe?

A Google Data Studio full-funnel table showing ROAS by platform.

shopify buyer segmentation

 

Want to Work With Us?

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.

Leave a Comment

Your email address will not be published. Required fields are marked *

Comments ( 0 )

About the author

David Krevitt

Find out more

Steal our organic traffic Blueprint.

We've helped hundreds of websites get more organic traffic from search engines. Give your email, get our blueprint.

Want to work with us?

We’ve helped hundreds of companies get more “ready to buy” visitors to their websites - we can do the same for you.