📘 You're Losing Customers

📘 You're Losing Customers


How We’re Automating SEO with BigQuery

  • July 30, 2020

About the author

Ryan Stewart

I have an unhealthy obsession with being considered the world's BEST internet marketer. I'm highly active on social media and love a good debate.

The SEO industry is facing a data-scale problem.

There’s so much SEO data that needs to be reviewed in order to get top results for clients.

Keywords, links, competitors, SERP data, technical items, content items…the list goes on.

This data lives in half a dozen platforms – it takes hours just to pull and format the data.

As agencies, we’re forced to account for those hours by raising retainer prices, making quality SEO services unaffordable to most businesses.

Last year, I joined WEBRIS as Chief Automation Officer to change all of this.

My goal was simple:

Automate the busywork, not the analysis.

At the end of the day, SEO is still a service that requires trained human brains. We wanted to free those brains to focus on actions, not Spreadsheet busywork.

I came up with a solution using Google BigQuery.

In this article, I’m going to show you how BigQuery has helped us scale through automation.


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 we’re working on an eCommerce shop with 20,000 SKUs.

Pulling the data down from each tool, formatting, analyzing…20+ hours of busywork.

BigQuery accepts all that data (via API) to process and format at lightning speed. No more slow browsers or crashed spreadsheets, a few clicks and you’re ready to analyze.

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 analysis reports.

bigquery outputs

We’re able to get completed audits, ready for review, within a matter of minutes.


Examples of BigQuery in action

We’ve been able to push all of our deliverables into BigQuery. We call each of these deliverables a “Recipe” – Recipes automate the tedious, repeatable reports that our team runs every day, and allow us powerful customization on a per-project basis.


Recipe example: 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


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.

Leave a Comment

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

Comments ( 134 )

  • private mobile proxy regions Says
    6 months ago

    [[b]Private UKRAINIAN proxies in one hand:

    – type (http/socks 5);
    – – operators (Vodafone UA) – speed 25-35 mb.\s depending on the load of the
    mobile network;
    – – large pool of IP addresses;
    – work without VPN;
    – IP rotation by link and by time interval;
    – no speed limits;
    – traffic (UNLIMITED);
    24/7 SUPPORT: We will answer all your questions.

    About the purchase, write to telegram or to skype[/b]

  • realistic vs non dildos Says
    6 months ago

    I would like to invite all readers of webris.οrg tο join Peaaches
    and Screams affiliate program. Ꭰo you run a website ᧐r a blog?
    Wouⅼd you lіke to make money from your website?
    Ԝe can help ʏou. Our lingerie and sex toy affiliate program ᴡill enable yⲟu to display ⲟur banners օn ʏour website ɑnd when someone clicks on tһese banners or affiliate
    ⅼinks and purchases sex toys or linyerie products fгom our store, you will eazrn a handsome commission. Νow
    you can start makinjg money frkm eveгy single visitor whilst you sleep.
    We are currentfly expanding ⲟur affiliate base and havе over 30,000 affiliates whicһ include but are not limited to
    sex toy review blogs, lingerie blogs, е-commerce stores, travel blogs, lifestyle blogs,
    fashion blogs ɑnd more.

    Here’s Hoow Our Lingerie And Sex Toy Affiliate Program Ꮤorks

    Step 1: Siggn ᥙp at https://www.affiliatly.com/af-1016414/af-1016414/affiliate.panel
    Step 2: Choose your banneers ɑnd add them to your website or
    share affiliate ⅼinks oon youг socil media ρages ߋr e-mails
    Stepp 3: When someone clicks ߋn yoᥙr affiliate banner ߋr lіnks and buys and mɑkes a purchase frօm
    Peachrs аnd Screams,yⲟu earn money!
    Step 4: We pay youur affiliate earning tо уou!
    Step 5: Repeat!

    My blog … realistic vs non dildos

  • buy tiktok followers from tikdog Says
    3 months ago

    I’m not sure where you are getting your info, but great topic.
    I needs to spend some time learning more or understanding more.
    Thanks for great information I was looking for this information for my mission.

  • safe site to buy instagram followers Says
    3 months ago

    Hi my friend! I wish to say that this post is awesome, great written and come with approximately all vital infos.
    I would like to see extra posts like this .

  • Nude Nipples pics Says
    3 months ago

    Using a sex doll will be not because taboo because
    it was once. There is a multi-billion dollar industry close to the distribution and creation of sophisticated and not-so-sophisticated
    sex dolls. The phrase, Actual Doll provides become widely recognized and component of the mainstream vocabulary as new materials and technologies make the difficult, achievable.
    While males can completely appreciate standard setback up intercourse dolls still, nowadays
    we see very much a lot more detailed and better models
    all the time technologically. Metal Guy, consume
    your coronary heart out.

  • best apps to buy followers on instagram Says
    3 months ago

    Thanks for finally talking about > How We're Automating SEO with BigQuery – WEBRIS < Loved it!

  • sbobet Says
    3 months ago

    Do you mind if I quote a couple of your posts as long as I provide credit and sources back to your site?
    My blog is in the very same area of interest as yours and my users would really benefit from a lot of the information you
    present here. Please let me know if this ok with you.

    Thanks a lot!

  • cipit88 Says
    2 months ago

    Hi there, I check your new stuff regularly. Your story-telling style is
    witty, keep doing what you’re doing!

  • cipit 88 Says
    2 months ago

    We absolutely love your blog and find a lot of your post’s to be
    what precisely I’m looking for. can you offer guest
    writers to write content for you? I wouldn’t mind publishing
    a post or elaborating on a few of the subjects you write with regards to here.
    Again, awesome web log!

  • cipit88 Says
    2 months ago

    I savor, lead to I found just what I used to be taking a look for.
    You have ended my four day long hunt! God Bless you man.
    Have a nice day. Bye

  • vivo slot Says
    2 months ago

    No matter if some one searches for his necessary thing,
    so he/she needs to be available that in detail, therefore that thing is maintained over

  • microgaming Says
    2 months ago

    I am extremely impressed with your writing skills and also with
    the layout on your weblog. Is this a paid theme or did you customize it yourself?
    Either way keep up the nice quality writing, it is rare to
    see a great blog like this one these days.

  • cipit88 Says
    1 month ago

    When someone writes an article he/she retains the idea of a user in his/her brain that how a user can understand it.
    Therefore that’s why this post is great. Thanks!

  • cipit88 Says
    1 month ago

    Hi, this weekend is good in support of me, as this moment
    i am reading this impressive informative article here at my residence.

  • cipit 88 Says
    1 month ago

    Hi would you mind letting me know which webhost you’re
    working with? I’ve loaded your blog in 3 completely different
    web browsers and I must say this blog loads a lot faster then most.
    Can you suggest a good hosting provider at a honest price?
    Cheers, I appreciate it!

  • cipit88 Says
    1 month ago

    Hi, its good article regarding media print, we all understand media is a enormous source of data.

  • cipit88 Says
    1 month ago

    Its like you read my mind! You appear to know so much about this, like you wrote the book in it or something.
    I think that you could do with a few pics to drive the message home a little bit,
    but other than that, this is great blog. A fantastic read.

    I will definitely be back.


    Find out how much organic traffic your website should be getting through our Traffic Projection Analysis.




    Using data from your website, our Traffic Projection analysis can accurately forecast how much traffic (and revenue) your website could be getting from Google.