Keywords for your Google Discover GSC export. How to easily get them within Google Sheets
Let’s directly start with a disclaimer … it’s not really keywords someone used in Discover or some magic hidden keyword Google is using, it’s the topics you are writing about (represented as keywords), which a NLP entity detection API was able to find.
The idea
While checking URLs in the Google Discover performance report you realize that Harry (Duke of Sussex) seems to top performing topic.
Articles about this celebrity seem to work and you could double down on that writing more about him. But what about all the other celebrities, which are not mentioned in the URL of the top 10 Discover articles? Hard to detect them… the idea of the following method is to make this simpler.
The problem with Search Consoles Discover data
It’s just URL’s. As a result I don’t really know without reading the article what it was about. I can’t check a pivot table with topics or keywords. To analyse a list of URLs and open all of them is a lot of work.
Some speculation about how Google Discover works
With the changes in Google Discover in the beginning of 2021 it looks like every site seems to have some topics connected to it in Google’s Discover algorithm. These topics are the “easy to rank in Discover topics” for each site. Not preferred topics are much harder to rank good with for the site. E.g.
Site 1: General interest publisher with strong economy department
- Good Discover rankings for economy topics. Google seems to prefer the site for economy topics
Site 2: General interest publisher with strong sport department
- Good Discover rankings for sport topics. Google seems to prefer the site for sports
- Hard to rank for economy topics, because of strong competitor, which is kind of preferred even if Site 2 wrote something outstanding about economy
This example is just news categories. You could cluster here with celebrities popular for old readers vs. young reader’s celebrities and similar stuff.
A simple tool to pre-select topics / keywords for a closer look
Something like this can help you identify the topics which worked best in terms of impressions. It’s similar to the search performance report in GSC:
This single entity report is even closer to a keyword report
What I can find out here with a glance is that “harry” isn’t the celebrity with highest average impressions … “dieter bohlen” is much better. More articles about him are more promising.
This is an example of just the Google Discover export but enriched with entities:
How to create these cool entity enriched Google Discover reports in Google Sheets?
- Copy this Google Spreadsheet
https://docs.google.com/spreadsheets/d/1hShepH1lagl8ena5YrLwW0JJHci_fVQFU247JqV3JPg/edit?usp=sharing
It has 4 Sheets.
Discover: This is the only data source you need to edit. Basically clean up the sheet and copy+paste the exported data from Google Search Console’s Discover Report into the columns A:D. The script (described below 👇) will set column E with scraped title + description and column F with pipe separated entities per URL. It’s a simple extension of the GSC export.
Pivot Discover: This is a pivot table based on the Discover Sheet data
NLP Results: This is a new created report. You have to do nothing here. It’s just generating properly separated URLs + entity rows.
Pivot NLP Results:
The script, how to set it up and how to run it
1) Copy files and code
Make a copy (File > Make a copy) of my spreadsheet https://docs.google.com/spreadsheets/d/1hShepH1lagl8ena5YrLwW0JJHci_fVQFU247JqV3JPg/ You need a copy you own to run scripts. You must be editor of the spreadsheet.
Open Tools > Script editor
Copy this script to the script editor
https://github.com/Zrce/GoogleDiscoverNLP/blob/main/index.js
2) Now add Cheerio / the scraper
This is used to scrape Title and Description. Select “Resources” > “Libraries…” in the Google Apps Script editor. Enter the project key (1ReeQ6WO8kKNxoaA_O0XEQ589cIrRvEBA9qcWpNqdOP17i47u6N9M5Xh0) in the “Find a Library” field, and choose “Select”.
Check https://github.com/tani/cheeriogs for details
3) Cloud Natural Language API
In the function retrieveEntities you need to add an API Key of Cloud Natural Language API. Go here https://console.cloud.google.com/apis/api/language.googleapis.com/overview and get one. This will generate some cost depending on how much you use the API. If you need help how to get the key let me know in the comments below.
var apiKey = "YOUR API KEY HERE";4) Run
That it. You can run the script with “Run” and start function being selected. You could in addition connect the script to some schedule…
The script is really a first version I’m still testing. Any idea how to improve it is welcome. If you found bugs please let me know.
The current solution divides the impressions and clicks of a URL equally to all found entities.
There might be problems with the NLP API not detecting the important entities.
