Content tuning at scale with the Search Console API
This article explains how to use the GSC API to find content tuning opportunities.
Last week, I wrote about content tuning, the concept of making ongoing changes to the content to rank higher and for more keywords. I showed a very manual process of content tuning but mentioned that I would show a more scalable way with the GSC API if people were interested. It turns out they are. So, here it is!
Google has reached a level at which it can understand and react to content tuning very quickly. In last week's article, I showed an example of my article about power laws:
"Look at the query “power law vs exponential” in the screenshot above. Google tries to rank my essay on position 18.6, but I don’t explain the difference between power laws and exponential functions in it. I provide a lot of context around that topic but don’t give a specific answer to this exact question. A little tweak should bump my article up to page 1."
I tuned the article and added a section about power laws vs. exponential functions on the 22nd. Google reacted with two quick tests of the content on position 16 for the query.
Not too impressive, but you can see how impressions spiked for several keywords shortly after the change.
One example doesn't prove anything. It just highlights the idea: Google is quick to test content for different queries when it's tuned. You should see a quick impact of your content changes.
A step-by-step tutorial for content tuning with the GSC API
You don't need much to pull data from the Google Search Console API: we'll work with Google Sheets and the Search Analytics for Sheets add-on. If you're interested in setting up an automated process, I recommend JR Oakes' article A simple tool for saving GSC data to BigQuery.
Extract data with the Search Analytics for Sheets add-on
Open a new Google Sheet.
Open the Search for Sheets add-on in Google Sheets (see screenshot below).
Select your site under "Verified Site" and a full month or week for "Date Range."
Select "query" and "page" in the "Group By" field.
Click "Request Data."
It should look like the configuration in my screenshot below.
Sort and filter your data
When you get the data, it will be sorted by clicks. But what you need is to sort by page. So, create a new filter for the first row and sort it by page (see screenshot below).
Filter the "Position" column by "condition = between 5 and 20" (you can also choose between 11 and 20) to focus on queries for which you have a chance to rank higher.
Filter the "Impressions" column by "condition = greater than 50" to sort out noisy queries. I recommend increasing the threshold until you have about 10 queries per page; otherwise, it gets too confusing.
You could stop right there, but if you are like me and like to highlight opportunities, keep going.
Style your data for better scoping
Select the "Impressions" row and click on "conditional formatting."
Select a color scale and use numbers for "Minpoint" and "Maxpoint." I selected a range of 100 and 1,000 because that's where most queries I rank for fall into, but you can play with this.
Do the same for the "Position" column, but with other numbers (screenshot below).
I also like to highlight duplicate queries to spot potential to optimize for double rankings or make the difference between two pieces of content clearer.
The process for highlighting duplicates is simple:
Select column A ("Query").
Open conditional formatting (under "Formatting").
Click on "add another rule."
Under "Format rules," select "Custom formula is..."
Enter “=countif($A:$A,A1)>1”.
Select a cool color (I took red).
Et voilà! You now have a list of queries with decent impressions (think: search demand) that your content could rank for but doesn't. You found content tuning potential!
Monitoring content tuning success
After you have tuned your content, repeat this process with a comparable data range to understand how successful your changes were. If you've pulled the initial data set for January, for example, pull it for February to compare. The same applies to weekly comparisons. Just make sure you stay consistent with the time range and watch out for seasonality.
Let me know if this worked for you and what you found. My Twitter DMs are open.