Automating SEO Reporting: A Case Study

Usually, in an agency or in-house scenario, an SEO will be brought in to do more than just day to day optimizations. From consulting with developers and stakeholders, doing keyword/market research, and building out new content, the last thing you have time for is the mundane gut-check that is reporting. While the actual optimization process can involve critical thinking, navigating JavaScript, crafting copy, you know, things that you might have to rub your chin like “The Thinker,” most reports I have ever dealt with primarily involve clicking around through Excel and Powerpoint. So, you are probably thinking, why wouldn’t I just use the built-in reporting tools that come with pretty much every SEO software? Well, for starters:

• Most SEO software’s reporting tools lack the customization needed for client-specific metrics. With out-of-the-box SEO reporting tools, you are more often than not left with either too broad of a view or far too granular for what your client or internal stakeholders want.
• The actual analysis/insights that make most reports meaningful are lost in translation. The time you spend with your data in gathering and formatting could be used for actual insights gathering.
• Aggregation of other data sources. While many platforms integrate other data sources through API connections internally, you would be hard-pressed to find any tool that does it so in a clean way.

The Problem

Our client wanted to track SEO metrics that were associated with the brand’s perception as thought leaders in a niche vertical. Utilizing internal website data, social media engagement and search engagement, the client wanted to see if their content efforts established them as the go-to source for information for their industry. To understand how the client was perceived from the lens of search, the following KPIs would be tracked as indicators.

• Number of keywords in top 10 positions
• Number of quality backlinks acquired (the threshold determined by domain authority)
• Click-through Rate (CTR) on the SERP

This exercise, from going to the different sources and pulling data and compiling through data to get consistent results and place them into their report every quarter would take around 2 hours per month for a total of 6 hours a quarter. While that does not seem like a lot of time, that’s 6 hours that could be used to better my clients. So, let’s come up with a solution that ultimately makes the reporting process come out to around 3 minutes per month.

The Report Automation Framework

Let’s start with the libraries that we used for this process.

With all of our tools in places, let’s look through each of the client’s ask and see what we can do about automating them.

Tracking top keyword positions

We will be using SEMrush’s flexible REST API (which I use for a majority of my automation projects) as our data source for keyword ranking. In this process, pull out the previous month’s date feed it into the function for the API, call for the keyword ranking data for our client’s site, compile the ranking data into a DataFrame and filter out keywords to count the number of top keywords we have.

The result, when you export out the file will ultimately look something like this.

Compiling SERP CTR

For understanding our engagement on search, we will be utilizing Google Search Console and a third-party wrapper that allows for easy API access.

To utilize the Search Console API, you will need to enable access and obtain the necessary credentials. Google provides a very comprehensive guide to doing so here. Once you have accomplished that, you can load your credentials into the wrapper and write up a function similar to what I have written (or just copy and paste mine, I won’t be offended).

Collecting Quality Backlink Data

We will once again be compiling data via SEMrush’s API. This method builds out the URL needed for the API call within a function and then utilizes previous functions to retrieve the data. Once the API call has been made, the timestamps on “First Seen” are converted into a legible time. From there, the DataFrame filters based on the month of the report and runs a secondary filter to only leave backlinks which are greater than a specific page score. For this example, the page score threshold has been artificially determined, but in your usage of these scripts, you can replace it with what you see fit.

Putting it All Together

With all of the necessary data for this exercise sitting within their unique DataFrames, we can compile all the data together into one easy to read, a navigatable spreadsheet that can be handed off to the team. ExcelWriter drafts up a new spreadsheet labeled based on the specific month the report is run and stitches together all of our examples into one.

Utilizing this method of reporting, we were able to not only pull the necessary data in a streamlined fashion, but we can also routinely pull this data without interacting with separate platforms or compiling methods. In this instance, the report was set on a timer to run at a specific time and emailed out (using a separate script) to all necessary channels.

Automating the SEO reporting process means returning hours to the SEOs on your team (even if you are a team of one) that can be leveraged for other more critical tasks. While this example just highlights one instance of report automation from the perspective of search, essentially all metrics associated with search (organic traffic compilations, log file analysis, paid/organic synergy, etc) can be streamlined using a Pythonic workflow. So take the challenge for yourself and make your life just a little bit easier

Want to learn a little more about automating SEO using Python? Check out my article on all things SEO automation here! Or, reach out to me directly through LinkedIn or GitHub!