“Recipes” for BigQuery

Recipes are a new service from CIFL, where we run data analysis workflows in BigQuery on your behalf. To see if Recipes are a fit for your team, inquire here to connect with David, the Commissioner of CIFL.

The promise of every automation tool (Supermetrics, Zapier and their ilk) is essentially the same:

Your newfound power will eventually save you a ton of time. But what about the time you spend deciding what to do with it?

When you have unlimited access to data, deciding how to use it can easily eat up your schedule.

How do you actually cement this Great Promise of Automation into reality, and bank the extra time?

At CIFL, we have a simple answer: Recipes.

With Recipes, we break your data analysis process down into repeatable steps, and execute them on your behalf.

While you sleep, we do the day’s data wrangling. You receive results in Google Sheets or Data Studio and take action.

We’re the CIA, and you’re the President.

 

WTF is a Recipe?

We all have recurring data analyses that we run on our sites, campaigns and shops.

Stuff like (click to jump):

• Website content audits
• Ad funnel breakdowns
• Shopify buyer segmentation

These are the plays in your playbook, that you bust out anytime you need a win. They allow you to do a consistent pace of high quality work.

Every Recipe is baked with the same 3 elements:

1.Raw data feeds: we pull data from Google Analytics, FB Ads, etc, and push it to your BigQuery data warehouse (our analytics database of choice).

2.Data models: we join data together + calculate metrics (we like DBT for this step).

3.Data visualizations: you visualize results in Google Data Studio or Sheets dashboards.

Your team ultimately interacts with data in Data Studio or Sheets, but you’re also welcome to crack open the raw data in Google BigQuery.

In our opinion, having access to your own raw data (in BigQuery) is an absolute must. Many data analysis platforms provide you with end results, but not access to your raw data to run your own ad hoc analysis.

With Recipes, all of your data lives in your own BigQuery database, for you to use in any way you see fit.

Let’s dive deeper into how those 3 Recipes mentioned above make their way each night from raw data to finished reporting.

 

Examples of Recipes

To help contextualize what this might look like for your business, here’s a few recently complete Recipes:

 

1. Website Quality Audit

This is an old classic, that hundreds of SEO agencies use as part of The Blueprint framework.

It was originally built in Google Sheets (read more about the original Website Quality Audit here), and has now been ported to the BigQuery Recipes framework to accommodate working with sites of any size.

The WQA starts with a site crawl, and pulls together key SEO metrics for each page.

It allows you to make decisions about the “quality” of each page on a site from an SEO perspective, and assign next steps for each page to improve that quality.

Here’s how the Recipe comes together:

Raw data feeds

The WQA pulls together landing page + keyword-level data from:

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

For the paid APIs in the WQA (Deepcrawl, Majestic and SEMrush), CIFL provides API access as part of the Recipes service, so there’s no need to sign up for your own costly API keys.

All data sources with constant data updates (Google Analytics + Google Search Console) are refreshed each night.

Slower-moving datasets (Deepcrawl, Majestic and SEMrush) are refreshed monthly.

Data models

After pushing all raw data to BigQuery, we run a set of SQL models (written in DBT) to output your page-level actions.

First, the WQA joins together URL-level data from all sources listed above, to form a cohesive picture of your site.

Then, the Recipe assigns page-level actions based on a set of SEO best practice rules. The current ‘next steps’ list is:

• Leave page as is
• Update on-page content
• Target w/ links
• 301 to another page
• Canonicalize
• Block the crawl
• NoIndex

Using Deepcrawl data, the WQA also assigns a ‘page type’ (product, category, blog post / resource, lead generation, location) to each URL, so you can filter these actions based on a section of your site.

Visualization

The Website Quality Audit is essentially a task list generator for your SEO campaigns.

Because of that, the primary visualization is a Google Sheets template, that your team can use to feed your campaign planning process.

To track overall site health, we also provide a Google Data Studio report – but to dive into the nitty gritty of planning SEO work, you’ll want to primarily use the Sheets report.

 

2. Ad Funnel Breakdown

If you’re running ads across multiple platforms (Google Ads, Bing Ads, FB Ads, etc), this Recipe allows you a consolidated end-to-end view of your team’s results – without having to toggle between 5 different dashboards.

The goal is to arrive at a consolidated picture of ROAS for your site, by bringing together data from the ad platforms, analytics, and CRMs if necessary.

Here’s how it comes together:

Raw data feeds

The Ad Funnel Breakdown pulls together campaign / adset / ad + URL-level data from ad platforms and analytics tools:

• Google Ads
• Bing Ads
• FB Ads
• Twitter Ads
• LinkedIn Ads
• Google Analytics
• Adobe Analytics

Additionally, if you’re a B2B business storing sales + revenue data in a CRM (Hubspot, Salesforce, etc) rather than Google Analytics, we can also pipe that data to BigQuery for use in the Recipe.

Data models

The art of this Recipe is joining together data from disparate sources, into a consolidated view.

Ad platform data (spend, impressions, clicks, conversions) are joined to analytics data (sessions, goal completions, transactions, revenue), and CRM data (leads, opportunities, deals, revenue) if necessary.

This requires sharing settings for each of your sites with CIFL:

• How source / mediums in Google Analytics map to each ad platform
• Which conversion goals are active in analytics

A side benefit of setting up these mappings for each of your sites – it requires your team to fully document your analytics goal + UTM tracking plan, which often uncovers redundancies or consolidation opportunities.

Visualization

Reporting for the Ad Funnel Breakdown is done entirely in Google Data Studio.

The core is a full-funnel table showing ROAS by platform:

If you’re optimizing ad spend across platforms (Adwords, Bing Ads, FB Ads, etc), this is a critical view to decide where to allocate budget.

With Data Studio’s built-in date filtering functionality, you can view this funnel across any WoW, MoM or YoY period.

For deeper analysis, the report includes (among other views) a campaign + URL explorer, where you can browse results across platform by campaign name or URL:

Sidenote: one corner case that we like to use the Ad Funnel Breakdown for, is QCing data between ad platforms (cough FB Ads cough) and analytics.

Often ad platform clicks don’t map well to analytics sessions, or conversions are tracking drastically differently between the two (due to attribution differences between Google Ads and Google Analytics, for example).

This recipe gives you a springboard to investigate those discrepancies without any time spent data wrangling.

 

3. Shopify Buyer Segmentation

This Recipe is simple but deadly – it allows you to find out who your best customers are, and what marketing channels brought them to you.

We built it back in 2018 along with the team from Growth Engines, and have executed it for a number of digital agencies and standalone businesses since then.

Raw data feeds

The Shopify Buyer Segmentation recipe pulls together two key data sources:

• Shopify orders + customers
• Google Analytics order-level attribution data

Looking at Shopify data alone will tell you a lot about your customers – but not where they came from.

Same for Google Analytics – you can attribute revenue to a channel for individual orders, but not for the actual customer themselves.

Blending the two together in BigQuery allows you to bridge that gap.

Data models

Joining these two datasets gives you a critical picture of how buyers behave over time depending on how you acquired them.

The Recipe calculates a few key metrics:

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

It then buckets each buyer into a couple different segments:

• Frequency segment (1, 2, 3+ purchases)
• Revenue segment (Top 10%, Middle 80%, Bottom 10%)

Having each buyer tagged with a GA acquisition channel + performance segments let’s us get a powerful comparative look at how our channels have performed over time.

Visualization

This Recipe is visualized strictly in Data Studio, although the buyer segment email lists (see below) can also be pulled into Sheets for use in retargeting campaigns.

First and foremost, the report offers a clear picture of how different acquisition channels (Paid, Organic, etc) perform in terms of revenue growth and retention:

You can also keep a pulse on the overall distribution of your buyer base. Is your shop top-heavy, with the top 10% of buyers driving most revenue, or relatively evenly distributed?

It also compares the effectiveness of each channel in acquiring your highest-spend customers. Which channels punch above their weight, and deliver a higher percentage of whales?

And last, but certainly not least, the Data Studio report features segmented lists of buyers (including names and email addresses), which can be easily exported to CSV for use in retargeting campaigns.

Build your own Recipe

The above 3 Recipes run the gamut of marketing data analysis, from SEO (WQA) to PPC (Ad Funnels) to e-commerce (Shopify Buyer Segmentation).

But – there’s always more to analyze. If you have a Recipe in mind for us to implement, we’re happy to help – just let us know here.

 

How do Recipes work?

We make a few promises about Recipes maintained by CIFL:

1. Always on. Data is always fresh, accurate and available.
2. Consistent. Today’s recipe was executed the same way as yesterday’s, so you’re comparing apples to apples.
3. Scalable. Repeatable from 1 site to 100 sites, with no drag on performance.
4. Shareable. Since we use Google reporting tools (Sheets + Data Studio), anyone on your team can pick up the output and use it.

These are the rules we live by, and from our experience they’ll serve you well too.

 

Setting up a New Site

Recipes can be set up for an unlimited number of your sites – a typical agency runs Recipes for anywhere from 20 to 70 client sites each night.

Recipes are configured site-by-site – to set up a new site, you first specify (via a form):

1. The Recipe to run.
2. The site domain or shop name to run the Recipe on.
3. The accounts (Google Analytics property, Search Console domain, etc) that map to that site.
4. Any supplemental settings (like GA conversion goal numbers, or source/medium to channel mappings) that may be required by the Recipe.

For the initial setup, data is backfilled and reports created within 2 business days.

Following that, data flows into BigQuery each night, and reports are automatically refreshed when you sit down for your morning coffee.

To easily access your list sites + reports, we share a master Tracking Plan Sheet with your team (the same template we use to execute our long-running Agency Data Pipeline service).

 

Recipe Pricing

For each site that you set up for a Recipe, you pay a flat fee per month of $25.

For Recipes that include access to paid APIs (like Deepcrawl or SEMrush), we’ll even cover your API costs for you, so you don’t have to purchase your own API key:

• Deepcrawl site crawl (includes Majestic backlinks data): $20 per site per month
• SEMrush keywords: $5 per site per month

This flat fee covers any size site – whether you’re running it for a blog of 100 pages or an e-commerce shop with 100k products.

So if you’re running the Ad Funnel Recipe for 20 client sites, your total monthly invoice would be $500 (flat $25 per site * 20 sites).

The only additional cost to cover on your end is your BigQuery database instance, since it’s not possible at the moment to share Google Cloud billing. To date our clients have mostly fit in BigQuery’s free tier, with the maximum bill being $20 per month.

This is not cheap, but it’s also much cheaper than your analyst’s time. Let’s say one member of your team normally spends a total of 1 day per week (or 4 days per month) working on reporting.

If that analyst is paid $75,000 per year ($6,250 per month, or roughly $312 per day), you’re paying them $1200 each month to wrangle data.

Running Recipes saves you roughly 60% in terms of cost, and gives valuable headspace back to your team.

Obviously that’s back-of-the-envelope math, but you get the picture – your team’s time is very valuable, and Recipes promises your team that time back.

 

Ready to get started?

The first step towards implementing Recipes for your team is to connect with David, the Commissioner of CIFL, and see which Recipes would be a fit for your work.

Get started here – we’re looking forward to meeting you.