| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
1 | DEPT || Pixel Rank & SERP Features Extractor || DataForSEO | ||||||||||
2 | |||||||||||
3 | Introduction | ||||||||||
4 | • | Welcome to the Pixel Rank & SERP Features Extractor. | |||||||||
5 | • | The goal of this sheet is to provide you with live SERP insights for your list of keywords. | |||||||||
6 | • | Please read this page before starting an analysis. | |||||||||
7 | • | After setting the sheet up, you can extract information about SERP features and pixel ranks for each keyword and even personalise some metrics based on your individual project context. | |||||||||
8 | • | The main data source for this SERP analysis comes from the tool DataForSEO and its SERP API: https://dataforseo.com/apis/google-serp-api | |||||||||
9 | • | Feel free to reach out with feedback and ideas or feature suggestions: https://www.linkedin.com/in/johannamaier/ | |||||||||
10 | |||||||||||
11 | How to Use It | ||||||||||
12 | • | Make a copy of this view-only template and make sure to also copy the attached Google Apps Script project. | Click here to make a copy. | ||||||||
13 | • | Important: › Set sheet location to Germany. Go to File > Settings > Country/Language > Germany. This is important for the script to work properly. › Rename the Google Apps Script project. Go to Extensions > Apps Script. Change the name of the script project (see screenshot). This can help to avoid that DataForSEO temporarily blocks your account. See row 32 for an explanation. |  | ||||||||
14 | • • | Sign up for DataForSEO and get 1$ of starting credit. This will be enough for a test run with up to ~200 keywords. In the dashboard you also have the option the set daily spending limits for specific APIs or the entire account. Make use of this option to control your spending. | DataForSEO SERP API: https://dataforseo.com/apis/google-serp-api Signup: https://app.dataforseo.com/register API spending limits: https://app.dataforseo.com/api-settings/api-limits | ||||||||
15 | • | Enter your DataForSEO username and password in cells F4 & F5 of the tab Settings. | 🔗Settings F4 & F5 | ||||||||
16 | • | Enter your domain name without https:// but with its subdomain (if it exists) like www.domain.com or de.domain.com in cell B4 of the tab Settings. | 🔗Settings B4 | ||||||||
17 | • | Enter a list of irrelevant domains starting from cell G8 in the tab Settings. Think of the domains that often show up in the search results for keywords that you want to target. What are the domains that you cannot or do not want to compete with? Depending on your project this could be sites like Wikipedia, Amazon, social media platforms or dictionaries. If those occupy the first search results, maybe this keyword should not be your priority. This idea will be represented in the output metric "Pixel To First Relevant Rank" for which a definition of irrelevant domains is the basis. | 🔗Settings G8:G | ||||||||
18 | • | Choose if you want to query the data for mobile or desktop SERPs in cell D5 of the tab Settings. This will result in different SERP integrations and also different Pixel Rank values (due to the different mobile/desktop device layouts). | 🔗Settings D5 | ||||||||
19 | • | Enter your keyword list starting from cell A8 in the tab Settings. Check the cost calculator in cell G5 for an estimation of costs to process the entire list. These slightly increase, if you also want to query Google Ads data (info below). Important: Avoid to change the keyword list after starting the script. In theory, it should still work but too much editing might cause unwanted errors. | 🔗Settings A8 🔗Settings G5 | ||||||||
20 | • | How long can your keyword list be? In theory indefinitely long. The longest list tested was ~8.000 keywords. › Google Sheets has a timeout for scripts after a few minutes. › If your keyword list is long enough so that the script needs more than 4 minutes to query the SERP data, a so-called trigger is set in Google Apps Script. › With this, the SERP analysis script is triggered every 5 minutes to continue processing the remaining keywords of your list (see column E of the tab Settings). › This way, you can also close your sheet and let the script run in the cloud without supervision (e.g. during the night). › To keep track of the already processed keywords, they are moved to column D of the tab Settings. | 🔗Settings E8:E 🔗Settings D8:D | ||||||||
21 | • | What to keep in mind with triggers and how to control them? › Active triggers mean that your DataForSEO account will be queried automatically for all open keywords - incl. the costs using up your credit balance. › The max. N° of script iteration is limited to the N° of keywords in column A of the tab Settings - see amount in cell F1. In cell F2 you see how many times the script was executed already. The script throws an error and any triggers will be deleted if the maximum of cell F1 is reached. You can overwrite the values manually. › Remember that you can also control your credit spending by setting API limits directly in the DataForSEO UI: https://app.dataforseo.com/api-settings/api-limits › You can use the DataForSEO menu (🔍 DataForSEO > 🕑 Update Trigger Status ) to check if a trigger is running. The status will show in cell D2 of the tab Settings. › You can use the DataForSEO menu (🔍 DataForSEO > 🗑️ Delete Trigger ) to delete this trigger in the backend. › You can also see & delete triggers directly in the code editor: Extensions > Apps Script > Trigger (see screenshot). › Also check the tab Extensions > Apps Script > Executions below Triggers (see screenshot). › Here you see a line for each time the script was executed - and if it is currently running. You can delete an active execution to stop the script. |  | ||||||||
22 | |||||||||||
23 | • | What menu options does the 🔍 DataForSEO menu have? | |||||||||
24 | › 📐 Analyse SERPs: This script analyses your keyword list. It returns SERP integrations data and Pixel Rank metrics. For long lists a trigger is set after 4 minutes. Two new tabs should be created and filled with data step by step: SERP Layout Details & SERP Layout Overview | ||||||||||
25 | › 🕑 Update Trigger Status: This script updates the trigger status in cell D2 of the tab Settings. If any are running in the backend, it changes to: "⚠️Triggers running". | ||||||||||
26 | › 🗑️ Delete Trigger: This script deletes any active triggers. Also, see instructions above on how to stop any script executions in the backend. | ||||||||||
27 | › 💲 Get Google Ads Data: Get bid data from Google Ads as proxy for how 'commercial' the keyword is - and how likely ads will be displayed further pushing down organic results. This proxy is useful because paid SERP elements (ads, shopping) are quite volatile and by default excluded from the pixel rank analysis. One new tab will be created and filled with the data: "Search Volume", "Highest Bid", "Lowest Bid" and "Average Bid". The first three values come directly from the Google Ads API (historic metrics) while "Average Bid" is a simple calculation of (Highest Bid + Lowest Bid)/2. | 🔗Google Ads API (Historical Metrics) | |||||||||
28 | › 🔗 Connect Bid Data to SERP Overview Sheet: Use this function to add the "Average Bid" data to the analysis sheets created by the "Analyse SERPs" script. | ||||||||||
29 | › 🗑️ Reset Processed Keywords: Use this function to reset the already processed keywords in column D of the tab Settings. | 🔗Settings D8:D | |||||||||
30 | • | Authorise the Script: This step will likely pop up once you use the options in the menu for the first time. Follow the steps to be able to run the script with your Google mail account. Often, this includes a safety warning which is standard for all scripts not officially authorised by Google. You can find a detailed explanation in this blog post: https://yagisanatode.com/2020/08/17/running-google-apps-script-for-the-first-time-whats-with-all-the-warnings/ If you would like to check the script before authorising it, have a look at my Github page: https://github.com/johanna-maier/google-apps-script-pixel-rank |  | ||||||||
31 | |||||||||||
32 | • | Error connecting to DataForSEO Google SERP API It can happen that DataForSEO blocks your account due to "suspicious" traffic. This happened during testing, especially for newly registered DataForSEO accounts. It is likely a policy to avoid that too many accounts are created just to receive the 1$ trial credit. If you get this error message, please log in to your DataForSEO dashboard and ask the customer service in the chat to unblock your account. If you ran out of your credits and would like to continue testing, they usually another 1$ to your balance. | 🔗 https://dataforseo.com/help-center/why-is-my-account-blocked | ||||||||
33 | |||||||||||
34 | Analyse your Data | ||||||||||
35 | • | 🔍 DataForSEO menu > 📐 Analyse SERPs Output: As mentioned above, this script will create two new tabs with all kind of raw data for each keyword in your list: SERP Layout Details & SERP Layout Overview. In the following Google Sheet, you can find some sample data: Analysis Examples (Copy of DEPT || Pixel Rank & SERP Features Extractor || DataForSEO) | |||||||||
36 | • | SERP Layout Details: This tab includes the top 10 rankings for each keyword, the ranking URL, its position & pixel rank as well as titles & meta descriptions. You could use these details for a project-specific search intent analysis as explained in this article (only difference: DataForSEO as data source for URLs, titles & descriptions, so you can skip the setup of Google's Programmable Search Engine): https://www.oncrawl.com/technical-seo/custom-search-analyzing-search-intent-googles-programmable-search-engine-json-api/ | |||||||||
37 | • | SERP Layout Overview: This tab summarizes the SERP insights in one row for each keyword. This includes the following metrics: › Input Settings: The first columns A-F note which settings were used for the analysis, including language, location, keyword, device, domain as well as the timestamp and SERP URL that DataForSEO used to extract the insights. › SERP Feature Info: There is one column (H) that gives you a comma-separated list of all SERP features for the keyword. In columns AT-BS you have one column for each possible SERP feature and for each keyword a "1" if it triggered this specific feature in the SERPS or a "0" if this was not the case. Columns O-R summarize this SERP feature count into 4 categories. In the hidden tab "SERP Feature Details", you can see to which category each feature was assigned. › Pixel Rank Info: There is one column (I) that gives you a comma-separated list of the pixel ranks of the snippets in position 1-10. SERP features for the keyword. In columns T-AC you have the same list split into once column per position. In addition, there are the following metrics that analyze the Pixel Rank: - Pixel Sum Top 3/10 (columns J/K): These values sum up the pixel ranks for the top 3/10 positions. You can use this metric to see "how crowded" the SERPs are. - Domain Position/Pixel Rank (columns L/M): If the specified domain is ranking, you can see its position right next to the pixel rank of its snippet. - Pixel to First Relevant Rank (column S): Here you see the first organic pixel ranking, after the specified "irrelevant domains" (see info above) were excluded. - Pixel to First Rank (column T): The pixel rank where the first organic ranking shows up. › Custom Columns: In columns BT-BW you can find some custom information that is not part of the default output of the script. (1) The Custom Tag is used to separate the analysis output, (2) Custom Date is used to format the timestamp into a date, (3) Above the Fold? checks if the ranking domain shows up in the viewport and (4) Last Position Above the Fold? gives back the last position where a snippet still is visible in the viewport for a keyword's SERP layout. | |||||||||
38 | |||||||||||
39 | • | The sheet also includes some example analysis that show how you could analyze the data output with different pivot table settings. Analysis Examples (Copy of DEPT || Pixel Rank & SERP Features Extractor || DataForSEO) | |||||||||
40 | › Pivot | Tracking over Time: Example of how to visualise (pixel) ranking changes for a domain side-by-side (uses extra column/formula Custom Date). | ||||||||||
41 | › Pivot | Keyword Analysis: Example of how to filter specific metrics that might be relevant for a project-specific keyword analysis & prioritisation. | ||||||||||
42 | › Pivot | Above the Fold: Example of how to analyze for which keywords a domain ranks within the first viewport (uses extra column/formula Above the Fold?). | ||||||||||
43 | › Pivot | DE vs. US: Example of how to analyze specific SERP features by country (in this case Popular Products & Product Considerations). | ||||||||||
44 | |||||||||||
45 | Advanced Settings & Edge Cases | ||||||||||
46 | • | Custom Locations: DataForSEO provides an exhaustive list of over 180.000 possible locations, including regions and cities. The default version this sheet - for performance reasons - only includes a location dropdown with the country options in cell B2 in Settings. This selects the country code in cell C2 in Settings. If you wish, you can overwrite the formula in C2 with the location code of your choice. This code will then be picked up by the script. Also add the name of your selected location in B2 so that it also appears in the output. | 🔗DataForSEO 🔗Settings B2 & C2 | ||||||||
47 | • | Force Paid Results: By default, "paid SERP features" like Google Ads or Shopping integrations are filtered out from the SERP analysis results. Enable this option in cell B5 in the tab settings if you want to include paid results and force Google to display ads with the search parameter adtest=on. Why are paid results excluded by default? › Paid SERP integrations fluctuate often and can be personalised. But only a difference between 1 or 2 displayed ads affects Pixel Ranks of the organic results. › In addition, Google tends to detect bots like DataForSEO and then blocks the display of any ads to avoid inflating any impression metrics. › Filtering out paid results improves the informative value of and consistency of a SERPs Pixel Ranks. › Use the option to get Google Ads bid data as proxy for how 'commercial' the keyword is - and how likely ads will be displayed further pushing down organic results. › Alternatively, you can force the inclusion of paid results as described above. DataForSEO Customer Service Feedback on the Issue >>Yes, we are familiar with this situation and the reason lies on Google's side precisely. The fact is that paid results are extremely unstable and Google tries not to show them for our bots. Moreover, the appearance of paid results also depends on a certain Google account and its browser history. In order to somehow increase the frequency of ads, we can only advise you to set a narrower location. You can also try to use the following parameter in the post_body: "search_param": "adtest=on"<< | 🔗Settings B5 | ||||||||
48 | • | Edge Case "too little results": When analysing the term "hertha bsc" (a Berlin football club) on mobile, an interesting edge case showed up in the past. The DataForSEO API could not find a minimum of 10 organic results and threw an error. This can happen when Google filters out irrelevant results, e.g. if they are too similar to content that is ranking already. If this edge case happens, you will now see a clear note in the SERP Layout Overview in columns H-K saying: "1) < 10 organic rankings in top 100 SERP items.", "2) New analysis with filter=0 needed.", "3) Select cell B6 in tab 'Settings'.", "4) Run keyword again.". You can force Google to not apply any filters to the results by checking "Force Filter = 0?" in cell B2 of the Settings tab. | 🔗Settings B6 | ||||||||
49 | |||||||||||