🔎 Focus: Automation
🔴 Impact: High
🟢 Difficulty: Super Simple
Sponsored by Ahrefs
Want to automate the grunt work?
Meet Agent A, the AI teammate with full access to your Ahrefs data. From fixing keyword cannibalization to shipping technical reports straight into Notion or Google Docs, it does the work you’d rather not.
Dear Tech SEO 👋
How I Win The SEO Match
One my client’s brand manager asked me:
- “Jairo, can we know the share search of our brand “
I said “you men the Share of Voice“
“No, no“ he said; “I want to know, given all branded searches for us and our competitors, what is our share in terms of volume…“
So I built it using Ahrefs API and a simple Google Sheet with a script:
I take any brand and run a “Matching Terms“ check up
The matching terms provide an overview of ALL branded search volume
I merge all brands I want in a Google Sheet
I run a script with a 24-month window
Imagine you want to know the share of branded search of Netflix. With my sheet you can easily do that:
Imagine all the branded searches like:
netflix
netflix login
netflix price
netflix family
netflix movies
…
And now imagine all Netflix competitors’ searches: max, max login, max series, etc.
With this sheet you can see the share between competitors:
My Google Sheets dashboard provides the current snapshot of share of branded search
Do It Manually
I actually started this dashboard by copy/pasting the data straight from Ahrefs.
And you can actually use the sheet without the need of an API key from Ahrefs.
Here is the simple 3-step process to build the dashboard manually:
Copy my “Share of Branded Search“ sheet: https://docs.google.com/spreadsheets/d/1yybaqYHmQDBgvbV5AJYVO4CXoqujpv6Jj7ozoJ-KCFo/copy
A simple HTML tag change can remove functionality
Once you copied into your own account you will see the sample I made with Netflix and its competitors, like this:
The button is not identified as such
Add any competitors you want on the table bottom left:
Start on row 21
Column B: add the brand domain (netflix.com)
Column C: add the brand name (Netflix)
In Column A there is a checkbox. If you check it that brand line will make it to the dashboard
The data is retrieved from each brand tab which is named after the brand:
Download as many rows as Ahrefs allows you and add it in a tab with the exact same name the brand was listed in the sheet:
Once the data is ready just make sure the tab you created has the 2 following fields straight from Ahrefs as they are used to generate the timeline overview and filter irrelevant topics:
SV trend
Category
Last step is to click the button “Calculate SoS” and the dashboard will generate the overview is a few seconds:
API Connection with Ahrefs
The manual process, despite being quite easy to execute, it just takes time.
To collect all data automatically I added a function in the script to collect all straight from the Ahrefs API so you only select ANY brand you want and it will automatically get all the data (matching terms) from Ahrefs database:
You of course, need an API Key from your account for this to work.
If you see any API key somewhere on my scripts I (hopefully) revoked it so you don’t have to worry on consuming my API usage 😄
Manual vs Automated
I am adding this image here just ‘cause is gonna be a very exciting Sunday plan
(I grew up in Spain but I am from Ecuador, it feels good either way)
Caveats:
Manual or automated via API there are always limits on data, so treat this dashboard as a trend benchmark rather than accurate science.
Manual limitations: depending on your account you might be able to download only 30k, 50k rows. That is more than enough to get you a good dataset.
API limitations: The limitations on API usage is even harder and also, when using the API you do not even notice and you consume it all faster than you expect.
If you want me to build your own dashboard on Sheets or Data Studio just reply to this email with:
What you want to measure
Why you want to measure
How do you measure right now
I will pick up the most challenging requests and work on them personally with you.
Also totally unrelated, who do you support in this WC final? Reply with the flag 🇪🇸 🇦🇷
Let’s meet OFF line
Next conferences I’m attending and I’d love you catch up with you:
That was it for today. Enjoy.
Until next time 👋
—