The Organic Pulse report lends you a birds-eye view of traffic + keyword trends across your sites, by aggregating Google Analytics + Search Console data each night.

The report tells you, over a selected date range, whether your sites are gaining or losing traffic, and what landing pages + keywords are driving that trend.

Crucially, it allows you to customize keyword mappings for each of your sites: the keyword categories (topics), search intents and branded phrases that are relevant for each site.

These keyword mappings are set in a companion Google Sheet template, which you can wire up to your BigQuery warehouse in a few clicks.

In this SOP, we'll cover 5 aspects of the Organic Pulse Recipe:

  1. Configuring the Data Studio report

  2. Setting your keyword mappings

  3. Walkthrough of the BigQuery table structure

It's important to note that this setup process happens once per BigQuery project - following setup, data will automatically flow into Data Studio for any new sites or keyword mappings you add.

The Data Studio Report

Browse the Data Studio template here.

The Organic Pulse report is broken down into 2 pages out of the box - we wanted to keep this report as brief and to-the-point as possible.

This single report is designed to be used for all the sites that your team works on, so that you can compare performance without opening a cavalcade of tabs.

If you'd like to copy a unique report for each of your sites (for example, to share with clients), you can filter for a single site's data from Data Studio's report settings (File -> Report Settings -> Filters).

Page 1: Anomaly Explorer

Shows gains in sessions across 4 levels of drilldown: by site, channel, landing page and keyword.

Crucially, selecting any row in a table filters the rest of the page for just that segment, so you can easily drill down into changes.

Page 2: Keyword Bucket Explorer

Pulls in the keyword mappings set by your team, and displays Search Console click trends by keyword bucket.

Keyword are bucketed across 3 segments:

  • Brand vs non-brand

  • Category (ie topic)

  • Search intent (commercial vs informational, etc)

Copying the Data Studio Report Template

Copying the Data Sources

To copy the Data Studio report for this Recipe, first you'll want to copy the data source below + connect it to the appropriate table in your BigQuery warehouse:

GA + GSC Organic Pulse

Reads the ga_gsc_channel_join table in your project's Organic Pulse dataset.

Please reference this doc if you're not exactly sure how to copy a Data Studio data source.

Note that you only need to copy this data source once, since it contains all of your sites' data. You can filter data for a single site at the report level.

Copying the Report Template

Once your data source os set up in Data Studio, you're ready to:

Copy the report template here.

If you're unfamiliar with how to copy reports in Data Studio, please check out this walkthrough video.

The report template by default contains data for *all* of your sites within a BigQuery project, so that you can toggle easily between sites.

If you would like to filter a report copy to display data from a *single* site (for example, to share with a client), you can set a filter on the report settings (File -> Report Settings).

Setting your Keyword Mappings

Browse the Keyword Mappings Sheet, or click here to make your copy.

Like the report, you'll only need to set this keyword mappings Sheet up once for your BigQuery project.

Once set up, you can enter mappings for any new sites within the same Sheet.

Setting your Mappings

After copying the template Sheet, you'll want to set mappings in 4 tabs:

  • Sites (names should match the sites you've setup in the app)

  • Branded Keywords

  • Keyword Categories

  • Keyword Intents

For each of these tabs, only the columns highlighted in blue need to be updated - for example, on the Branded Keywords tab:

Rules are either contains, starts with or ends with.

Basically what the Sheet does, is translate these rules into a consolidated Regex formula (in column D), which prepares the mapping to be applied programatically in BigQuery.

If you'd like to include multiple keywords on the same line, you can separate them with a '|', for example "cifl|coding is for losers" as our branded keyword text would perform the same mapping as the two lines shown above.

All of these mappings are automatically aggregated to the BigQuery tab, which you'll configure as a table in BigQuery.

Pushing your Mappings Sheet to BigQuery

Watch the video above for a deep dive into these steps.

Like everything else in this setup, you'll only need to configure this BigQuery table once for your project.

To do this, navigate to your project + recipe dataset in the BigQuery console, and select 'Create Table':

Then, point that new table to your mappings Sheet, and name it keyword mappings - specifics for each form field are:

  • Create table from: Drive

  • Select Drive URI: Your Sheet copy URL

  • File format: Google Sheet

  • Sheet range: BigQuery!A:D

  • Table name: keyword_mappings

  • Schema: toggle edit as text, then paste this value - type:STRING,site:STRING,bucket:STRING,regex:STRING

  • Advanced options (Unknown values): check ignore unknown values

  • Header rows to skip: 1

Then select 'Create table' to complete the job.

This will link your Sheet with BigQuery, so that every time SQL models run on your BigQuery project (each night), whatever mappings are in your Sheet will be pulled into the report.

It's important to note that this linkage between your Sheet and report is not real-time (refreshes overnight), to save significantly on your BigQuery costs. If you need data to refresh on a shorter timescale, please contact support in live chat.

BigQuery Table Structure

The Organic Pulse recipe writes a number of data tables to your BigQuery warehouse, across 3 buckets:

  • Raw data feeds from Google Analytics and Google Search Console

  • Aggregated data tables, rolling up your traffic + keywords

  • QC tables, which check your landing page-level values against aggregate daily reports

In this recipe, you'll primarily be using one table in BigQuery: ga_gsc_channel_join, which contains all of the granular landing page + keyword data that you'll need at your fingertips.

But - we provide the underlying tables as well, in case you'd like a more-raw form of data for your own analysis.

Admin tables

keyword_mappings_hierarchy_proc

Translates the keyword mappings from your Sheet into a form that can be applied to your raw GA + GSC data.

Base feed processing tables

Google Analytics

Google Analytics

ga_proc

Processes the raw Google Analytics data after upload to BigQuery. Dedupes and strips URLs of any trailing query strings or slashes.

ga_rolling_stats_url_dod

Final Google Analytics table used in aggregation, filtered for only valid landing pages (removes 'not set' values).

Uses analytic functions in BigQuery to map out DoD traffic changes for each landing page, channel and date combination:

If a landing page doesn't appear for a given channel or date, it's marked as having 'lost' the traffic + conversions that it had on the previous day for which it was found.

Google Search Console

search_console_keyword_proc

Processes raw Search Console keyword-level data after upload to BigQuery. Dedupes, strips URLS, and removes special characters from keywords.

Applies keyword mappings as found in your Sheet: intent, category and branded flag (denoted in the keyword_intent, keyword_category, and keyword_brand fields in the table).

gsc_rolling_stats_keyword_dod

Reads the search_console_keyword_proc table, and derives DoD changes for each keyword + landing page combination for your sites.

Also calculates keyword distribution (top 5 / top 10 / top 20).

Aggregated data tables

ga_gsc_channel_join

Joins together data from Search Console (ga_rolling_stats_url_dod) + Google Analytics (gsc_rolling_stats_keyword_dod), into a single unified table to be used in Data Studio reporting.

QC data tables

qc_agg

Compares values from granular landing page (GA) + keyword-level (GSC) feeds to 'QC' feeds, which pull aggregate daily totals from each data source.

GA landing page-level feeds are expected to tie out to aggregate totals exactly, unless sampling is occurring (the threshold for GA sampling is 500k sessions per day).

The app runs automated tests that flag whether there is a gap in GA data for a given date, and queues those feeds for rerun.

Note that we don't need to review your underlying raw data to perform these tests - the app is notified of the metadata of a test failure, and is able to rerun data feeds from there.

GSC keyword-level data is not expected to tie out exactly to aggregate site-level data.

GSC data is heavily sampled, and summing up keyword-level clicks will often differ up to 50% from site-level totals for a given day. We offer these QC feeds to at least understand where these differences lay.

--

Wow you made it all the way down here!

If there's anything you're looking for in this SOP, but not able to find, drop us a line in live chat and we'll do our best to point you in the right direction.

Did this answer your question?