Data pipeline + Looker Studio builder

Build Your very Own Google Search Console Data Pipeline using Google Search Console's Bulk Export tool to BigQuery.

Why you want this

It's the first tool I've seen built on top of Google's Bulk Export service that allows you to customize how you classify your data by brand vs non brand, purchase funnel stages, question terms, low hanging fruit and by url segments.

Did we mention the Google Looker Studio report is super fast too?

Pipeline Setup Steps | Looker Studio Report Setup | Faqs | Cost Calculator | Using the Looker Studio Report

Prerequisites

• You have A Google Cloud Project with Billing turned on.
• You have A Google Search Console Bulk export running already.
• You have access to the BigQuery environment where the Export is currently piping data.
• You have access to Google Search Console for the user tied to the GSC Porperty

Data Pipeline Setup Steps

Before we dig in, please remember:

1. Open this page in a new incognito tab in Chrome.

   This is necessary to make sure you're logged into the correct Google account for the steps below.

2. Open Google Search Console with email user that has access to the Google Search Console Property that has a Bulk Export set up already.

3. Make a copy of the Google Sheet Config Tool

   Copy Google Sheet Config Tool

   This tool allows you to set up a number of configuration variables for each pipeline including:

   • Brand terms
   • Top of funnel terms
   • Middle of funnel terms
   • Bottom of funnel terms
   • Low hanging fruit position range
   • Url segment classification
   • GBP Url classification

4. Paste your new Sheet's URL here

   This is necessary to build the BigQuery table that stores all the configuration values for you.

5. Setup the Your data Pipeline Config in the Google Sheet

   Project, Dataset, and site_url cells MUST be updated in the Config Sheet.

   Use the Help tab to guide you in this process

   Set up the values in the Config Sheet tab

6. Get Bulk Export Settings

   Go to the Bulk Export Settings page inside Google Search Console

   Select the property you used to set up the export from dropdown in top left of window.

   This will show you the Cloud Project ID, Dataset ID

7. Add your Google Cloud Project ID

8. Add your Dataset Name

9. Copy setup Code from textarea below

   Code will appear after you've added both your poject and dataset IDs above.

   Copy Code

10. Open BigQuery

    Go to Google BigQuery to set up the pipeline functions

11. Paste your Code into the BigQuery Editing text Area

12. Hit Run

    This will add what are called BigQuery procedures to your Google Cloud Project.

    This will also build out the views and tables necessary for the pipeline to run.

    These blocks of code are what run your data pipeline every day.

13. Create the daily run scheduled query

    Go to the Scheduled Queries tool.

14. Click Create scheduled query in editor button

15. Paste Code into editor

    Copy code from below & paste it into Schedules Query Editor IDE Copy Code

16. Click Save query

17. Click Schedule

    A new pane will slide in from left.

    Change Schedule frequency from every 1 hour to every 8 hours.

18. Run transform_search_data Routine

    This will run initial backfill

    Open the searchconole dataset

    Open routines

    Click on transform_search_data routine

    Click on Invoke Stored Procedure button.

    Click RUN when you see the new SQL Window pop up with the routine inside it:

    CALL `your-project.your-dataset.transform_search_data`();.

19. Set up your Google Looker Studio template

    What do you want to name the Looker Studio Report?

    Please note that we'll modify the name to make it work with Looker Studio naming rules

    The link to your report will appear after you've named it.

    Build my Looker Studio Template

    1. When Looker Studio report opens, Click Edit and Share.

       A new popup will appear.

    2. Click Acknowledge and Save.

    Congrats you should now have a working Looker Studio Report!

Important notes + FAQs

How do I change my Config settings after I've already built the setup?

No worries! We built a procedure inside your BigQuery dataset that we'll use to reclassify the data with your new format. Here's how to use it:

1. Update your configuration terms in your Google Sheet
2. Open Google BigQuery
3. Click open your project
4. Click open your dataset
5. Click Routines
6. Click Reclassify
7. Click on Invoke Stored Procedure button.

Data Pipeline Cost Calculator

Current Table Size (GB):

Get your Daily growth size by dividing searchdata_url_impression table size / # of partitions

Daily growth (MB): Number of Months to Calculate: Free Storage Tier (GB/month): Free Query Tier (TiB/month): Calculate

Currently Google charges around $0.02 / GB to store data in the US.

Data that's been stored for > 90 days costs $0.01 / GB to store in the US.

Currently Google charges $6.25 / TB (or 1000 GBs) to query data in the US.

Each day Google adds more and more data to the pipeline. We've seen medium sized sites that get 5MB of data added per day and larger sites with much more data

How can I save money while using the tool?

• Reduce the number of countries you look at in Google Looker Studio. We set them to be usa, can by default.
• Reduce the number of search types. We set it to Web by default.
• Be judicious in how many days worth of data you look at at a time.

How do I use the Looker Studio Report Template?

There are 9 Pages in the tool:

• Search Explorer

  A perfect page for high level analysis of your website. Detailed Instructions

• Anonymous Queries

  A page that allows you to see how much of your data is anonymous by day.

• Question Queries

  A perfect page to help you add FAQ content to your website. Detailed Instructions

• Topic Explorer

  A powerhouse page for exploring Topics across pages. Detailed Instructions

• Page Explorer

  A perfect page for optimizing individual pieces of content. Detailed Instructions

• Change Explorer

  A great tool for exploring big changes in performance. See what directories, pages, and queries are contributing to big changes. Detailed Instructions

• Keyword Cannibalization

  Get deep insights into cannibalization on your site. Detailed Instructions

• Site Structure Insights

  Explore performance by directory. Detailed Instructions

• Segment Explorer

  Track performance by URL segments that you configure according to what matters to you. Detailed Instructions

Channels

Analytics

Content

Datascience

General SEO

Local SEO

Reading List Resources

Technical SEO