- \n
- HTTP Status:\u00a0<\/span><\/strong>=<\/span>IF<\/span>(<\/span>D2<\/span>=<\/span>301<\/span>,<\/span> “correct”<\/span>,<\/span> “incorrect”<\/span>)<\/span><\/li>\n
- Redirect Destination:<\/strong> =<\/span>IF<\/span>(<\/span>B2<\/span>=<\/span>E2<\/span>,<\/span> “correct”<\/span>,<\/span> “incorrect”<\/span>)<\/span><\/li>\n
- New Title Implementation:<\/strong>\u00a0=<\/span>IF<\/span>(<\/span>C2<\/span>=<\/span>F2<\/span>,<\/span> “correct”<\/span>,<\/span> “incorrect”<\/span>)<\/span><\/li>\n<\/ul>\n
![\"Defined](\"https:\/\/www.aleydasolis.com\/wp-content\/uploads\/2019\/07\/implemented-defined-redirects-validation-google-sheet-2V.png\")
<\/p>\nYou can copy the sheet here<\/a> to use as a reference for your own migration validation.<\/p>\n
2. Compare Search Console performance metrics between two time periods to identify rankings increases or decreases<\/h2>\n
You might want to compare the Google Search Console performance metrics from two different time periods, for example, before and after a Google update, an important Web release or SEO related implementation, in order to identify a potential increase or decrease in the top site positions, and the queries affected. Although the Google Search Console Performance report UI allows to compare two time periods, unfortunately it’s not possible to see directly the difference between them there.<\/p>\n
However, we can do this easily in Google sheets by using the “Google Sheets” export option that’s directly provided in the report, and add a comparison column after each of the two time periods for the clicks, impressions, average CTR and positions metrics, using the IFS function<\/a>\u00a0to compare them and include “increased”, “decreased” or “didn’t change” in the comparison columns cells as an outcome, as can be seen below for the clicks comparison:<\/p>\n
- \n
- =<\/span>IFS<\/span>(<\/span>B2<\/span>><\/span>C2<\/span>,<\/span> “Increased”<\/span>,<\/span> B2<\/span><<\/span>C2<\/span>,<\/span> “Decreased”<\/span>,<\/span> TRUE<\/span>,<\/span> “Didn’t Change”<\/span>)<\/span><\/li>\n<\/ul>\n
![\"Comparison](\"https:\/\/www.aleydasolis.com\/wp-content\/uploads\/2019\/07\/comparison-gsc-increase-decrease-1.png\")
<\/p>\nTo easily spot those keywords that decreased vs. the ones that increased you might want to also use the “conditional formatting<\/a>” functionality to change the cells color based on the text they contained, as shown here:<\/p>\n
![\"Conditional](\"https:\/\/www.aleydasolis.com\/wp-content\/uploads\/2019\/07\/conditional-formatting-google-sheets-1.png\")
<\/p>\nFinally, you might want to tweak the comparison formula to correctly support the validation of positions changes, by also including some nested AND functions<\/a>:<\/p>\n
- \n
- =<\/span>IFS<\/span>(<\/span>AND<\/span>(<\/span>K2<\/span>=<\/span>0<\/span>,<\/span>L2<\/span>><\/span>K2<\/span>)<\/span>,<\/span> “Decreased”<\/span>,<\/span> AND<\/span>(<\/span>L2<\/span>=<\/span>0<\/span>,<\/span>L2<\/span><<\/span>K2<\/span>)<\/span>,<\/span> “Increased”<\/span>,<\/span> K2<\/span>><\/span>L2<\/span>,<\/span> “Decreased”<\/span>,<\/span> K2<\/span><<\/span>L2<\/span>,<\/span> “Increased”<\/span>,<\/span> TRUE<\/span>,<\/span> “Didn’t Change”<\/span>)<\/span><\/li>\n<\/ul>\n
![\"Multiple](\"https:\/\/www.aleydasolis.com\/wp-content\/uploads\/2019\/07\/conditional-comparison-ifs-multiple-1.png\")
<\/p>\nYou can view and copy the Google sheet with the functions example here.<\/a><\/p>\n
3. Integrate organic search performance metrics along backlinks data to identify pages targeting popular queries needing links to improve performance<\/h2>\n
You can also use the free and useful Search Analytics for Sheets Add-On<\/a>\u00a0to obtain the top ranked queries along their ranked pages directly, to facilitate their analysis (as well as to create automatic backups).<\/p>\n
![\"Google](\"https:\/\/www.aleydasolis.com\/wp-content\/uploads\/2019\/07\/search-console-for-sheets-queries-pages-1-compressor.png\")
<\/p>\nThen you can easily aggregate other SEO metrics, such as links data that you might have in another sheet from a SEMRush export by using a VLOOKUP function<\/a>\u00a0and bring the backlinks data in an additional column along the Google search Console ranked pages and queries:<\/p>\n
- \n
- =<\/span>IFERROR<\/span>(<\/span>VLOOKUP<\/span>(<\/span>C2<\/span>,<\/span>‘SEMrush Backlinks’!$A$2:$J$2830<\/span>,<\/span>4<\/span>,<\/span>false<\/span>)<\/span>,<\/span>“”<\/span>)<\/span><\/li>\n<\/ul>\n
![\"Backlinks](\"https:\/\/www.aleydasolis.com\/wp-content\/uploads\/2019\/07\/backlinks-gsc-data-vlookup-1.png\")
<\/p>\nYou can do this type of aggregation to identify those pages targeting popular queries, not ranking as well yet, that are not yet attracting many links, to identify opportunities of pages that might need to improve their link popularity.<\/p>\n
4. Compare the mobile vs. desktop ranked queries & pages performance<\/h2>\n
The Search Analytics add-on also allows to include data segmented with Google Search Console filters, such as the device for every ranked query and page, which along conditional formatting can highly facilitate the analysis of mobile vs. desktop performance gaps, to identify optimization opportunities.<\/p>\n
![\"Mobile](\"https:\/\/www.aleydasolis.com\/wp-content\/uploads\/2019\/07\/mobile-vs-desktop-search-performance-comparison-1.png\")
<\/p>\nYou can copy the Google sheets to check it out here.<\/a><\/p>\n
5. Integrate Google Analytics organic traffic along search performance to identify opportunities to improve engagement and conversion on top ranked pages<\/h2>\n
You can also integrate organic traffic data from Google Analytics\u00a0to your existing search performance analysis to identify further optimization improvements, for example, for those pages already ranking well but with poor engagement or conversion rate.<\/p>\n
To facilitate the integration of Google Analytics data you can use the Google Analytics add-on<\/a>\u00a0for free, you can use the CONCATENATE<\/a> function to generate the full URL of every page and then use a VLOOKUP function to directly include the Users, Sessions, Bounce Rate and any other Google Analytics metrics as columns of the already existing sheet with Google Search Console and Backlinks data.<\/p>\n
![\"Google](\"https:\/\/www.aleydasolis.com\/wp-content\/uploads\/2019\/07\/google-docs-sheets-analytics-1.png\")
<\/p>\n<\/p>\n
6. Validate the metadata relevance vs. their ranked queries and indexing configuration of bad performing pages targeting queries with high potential<\/h2>\n
To facilitate the analysis of the ranked pages relevance vs. their targeted queries you can also aggregate the metadata along the meta robots and canonical tag configuration for each one of the pages. You can do this by doing a list crawl of them and importing the data in the Google sheets as new columns, however this can be facilitated if you’re validating a small number of page by using the IMPORTXML<\/a> function to directly extract (by using xpath<\/a>) and import the pages metadata into the additional columns. For example, if our URLs are in column C, it would be:<\/p>\n
- \n
- =<\/span>IMPORTXML<\/span>(<\/span>C2<\/span>,<\/span>“\/\/title”<\/span>)<\/span><\/li>\n
- =<\/span>IMPORTXML<\/span>(<\/span>C2<\/span>,<\/span>“\/\/meta[@name=’description’]\/@content”<\/span>)<\/span><\/li>\n
- =<\/span>IMPORTXML<\/span>(<\/span>C2<\/span>,<\/span>“\/\/h1”<\/span>)<\/span><\/li>\n
- =<\/span>IMPORTXML<\/span>(<\/span>C2<\/span>,<\/span>“\/\/meta[@name=’robots’]\/@content”<\/span>)<\/span><\/li>\n
- =<\/span>IMPORTXML<\/span>(<\/span>C2<\/span>,<\/span>“\/\/link[@rel=’canonical’]\/@href”<\/span>)<\/span><\/li>\n<\/ul>\n
![\"Metadata](\"https:\/\/www.aleydasolis.com\/wp-content\/uploads\/2019\/07\/pages-metadata-technical-configuration-validation-v2.png\")
<\/p>\nLike this it would be much easier to filter and see only those pages that are not yet in the best positions and directly verify if they’re indexable, how their titles, descriptions and H1s are optimized towards their targeted queries, as well as how many links they’re attracting… all in a single place.<\/p>\n
7. Identify content cannibalization issues between many pages ranking for the same queries<\/h2>\n
Another way to leverage the metadata integration along the ranked queries and pages Google Search Console information, is to identify content cannibalization issues by sorting or filtering per queries and identify which are the pages currently ranking for them at the same time, verifying if the actual relevant page meant to be ranked for them is doing it so above the rest, and the potential reasons why this might not be the case, by taking a look at the metadata relevance, number of backlinks and indexing configuration of the “competing” pages.<\/p>\n
![\"Content](\"https:\/\/www.aleydasolis.com\/wp-content\/uploads\/2019\/07\/content-cannibalization-issues-google-sheets-1.png\")
<\/p>\n<\/p>\n
8. Identify if the right categories pages are ranking for their relevant queries types<\/h2>\n
We can take the previous “relevance” query vs. page match analysis further to identify if the right categories are actually ranking for their queries types. We can do this by:<\/p>\n
- \n
- Using the FIND,<\/a>\u00a0IFS<\/a>\u00a0and IFERROR<\/a> functions to look and validate the usage of specific topics\/category names in the pages URLs.<\/li>\n
- Add an\u00a0ARRAYFORMULA<\/a>\u00a0to avoid having to include the same formula again and again in every single cell (!)<\/span><\/li>\n<\/ul>\n
Generating this, to look for the different category and areas names of the Remoters site (colivings, events, blog, tools, spanish, others):<\/p>\n
- \n
- =<\/span>arrayformula<\/span>(<\/span>iferror<\/span>(<\/span>ifs<\/span>(<\/span>not<\/span>(<\/span>iserror<\/span>(<\/span>find<\/span>(<\/span>“jobs”<\/span>,<\/span>C2:C<\/span>)<\/span>)<\/span>)<\/span>,
\n<\/span>“Jobs”<\/span>,<\/span>not<\/span>(<\/span>iserror<\/span>(<\/span>find<\/span>(<\/span>“colivings”<\/span>,<\/span>C2:C<\/span>)<\/span>)<\/span>)<\/span>,
\n<\/span>“Colivings”<\/span>,<\/span>not<\/span>(<\/span>iserror<\/span>(<\/span>find<\/span>(<\/span>“events”<\/span>,<\/span>C2:C<\/span>)<\/span>)<\/span>)<\/span>,
\n<\/span>“Events”<\/span>,<\/span>not<\/span>(<\/span>iserror<\/span>(<\/span>
- Add an\u00a0ARRAYFORMULA<\/a>\u00a0to avoid having to include the same formula again and again in every single cell (!)<\/span><\/li>\n<\/ul>\n
- =<\/span>IMPORTXML<\/span>(<\/span>C2<\/span>,<\/span>“\/\/meta[@name=’description’]\/@content”<\/span>)<\/span><\/li>\n
- =<\/span>IMPORTXML<\/span>(<\/span>C2<\/span>,<\/span>“\/\/title”<\/span>)<\/span><\/li>\n
- =<\/span>IFERROR<\/span>(<\/span>VLOOKUP<\/span>(<\/span>C2<\/span>,<\/span>‘SEMrush Backlinks’!$A$2:$J$2830<\/span>,<\/span>4<\/span>,<\/span>false<\/span>)<\/span>,<\/span>“”<\/span>)<\/span><\/li>\n<\/ul>\n
- =<\/span>IFS<\/span>(<\/span>AND<\/span>(<\/span>K2<\/span>=<\/span>0<\/span>,<\/span>L2<\/span>><\/span>K2<\/span>)<\/span>,<\/span> “Decreased”<\/span>,<\/span> AND<\/span>(<\/span>L2<\/span>=<\/span>0<\/span>,<\/span>L2<\/span><<\/span>K2<\/span>)<\/span>,<\/span> “Increased”<\/span>,<\/span> K2<\/span>><\/span>L2<\/span>,<\/span> “Decreased”<\/span>,<\/span> K2<\/span><<\/span>L2<\/span>,<\/span> “Increased”<\/span>,<\/span> TRUE<\/span>,<\/span> “Didn’t Change”<\/span>)<\/span><\/li>\n<\/ul>\n
- Redirect Destination:<\/strong> =<\/span>IF<\/span>(<\/span>B2<\/span>=<\/span>E2<\/span>,<\/span> “correct”<\/span>,<\/span> “incorrect”<\/span>)<\/span><\/li>\n