WTF is a “Recipe”?

google bigquery data recipes

David Krevitt

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

Contents hide
What goes into a Recipe?
Examples of Recipes?
How do Recipes work?
How can I setup my own Recipe?
How much do Recipes cost?
Ready to get started?

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

These analyses propel our marketing forward and give us ammo to make informed decisions.

As helpful as these analyses are, they’re also time consuming.

At CIFL, we have a simple solution: 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.

CIFL Recipe example

What goes into a Recipe?

Every Recipe is baked with the same 3 elements:

  1. Raw data feeds: we pull the necessary data from the platforms of your choice (GA, GSC, FB Ads, etc) and push it to your BigQuery data warehouse.
  2. Data models: we join data together + calculate metrics (we like DBT for this step).
  3. Data visualizations: you visualize results in your platform of choice (Google Data Studio, Looker, Sheets, etc).

Ultimately, your team interacts with data visualizations, 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.

 

Examples of Recipes?

To help contextualize what this might look like for your business, let’s break down one of our favorite Recipes: The Website Quality Audit.

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…

The Website Quality Audit Recipe

1. Raw data feeds

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

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.

 

2. 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:

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.

 

3. 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 and a Google Data Studio report to track overall site health.

website quality audit example

 

Want to see more examples of Recipes? Click here to check them out.

 

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.

 

How can I setup my own Recipe?

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 (read more about our Data Pipelines here).

 

How much do Recipes cost?

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:

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

This is not cheap, but it’s also much cheaper than your analyst’s time.

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?

Get started here – we’re looking forward to cooking for you!

Ready for BigQuery?

Get off the ground quickly, with quickstart Recipes for BigQuery.

GET COOKING