Recently, I read an article on Neil Patel’s blog about using the data from Google Webmaster tools. In that article, he categorized URLs in a list using colour codes for CTR and rankings. It took me a while to actually find a way to do that. So I thought of sharing the method that others can follow to get the excel sheet in the desired format.
There are three steps attached to this:
According to the article, the first step that one needs to take is change the position of all the URLs/queries which rank below 10 to 10. This is easy. By using the IF function, one can easily get the result.
- IF(*cell which contains position* > 10, 10, *cell which contains position*)
In the next step, Neil asked us to "Highlight all rankings in the 1st, 2nd, 3rd, or 4th position in green. Highlight all rankings below that in red”.
It took me some time to figure it out but I finally managed to find the answer.
- First copy-paste the values from the last step into the positions column. Select, copy and paste (Alt+E+S+V).
- Click on conditional formatting -> New Rule
- Select the second option “Format only cells that contain”, add values in the boxes and then click on format to select the colour.
- Do it similarly for colouring a cell red for position between 4.1 and 10.
- Shortcut to use conditional formatting and select new rule: “Alt + H + L + N”.
This was the toughest task and again Conditional formatting came to the rescue.
In the next step, Neil said, “As for click-through rates, highlight keywords that rank in the 7th, 8th, 9th, or 10th spot and have a click-through rate greater than 3% in green, and, of course, use red for scores that are lower than 3%.
As for keywords that rank in the 4th, 5th, and 6th spot, highlight the click-through rate in green if it’s greater than 8% and in red if it’s lower than 8%.
For keywords in the 1st, 2nd, or 3rd spot, highlight them in green if the click-through rate is greater than 20% and highlight them in red if it’s lower than 20%.”
For this, you need to create rules in conditional formatting using formulas.
I used the formulas below to get the colour codes Neil was talking about.
Note: You need to add each formula individually and add the colour. Phew!
And this is how the sheet finally looks:
This is a great method to filter out the pages/queries which are working well and the ones you need to work on. Analysing and working on this should help you in improving your website’s traffic.
Global Association of Risk Professionals, Inc. (GARP®) does not endorse, promote, review or warrant the accuracy of the products or services offered by EduPristine for FRM® related information, nor does it endorse any pass rates claimed by the provider. Further, GARP® is not responsible for any fees or costs paid by the user to EduPristine nor is GARP® responsible for any fees or costs of any person or entity providing any services to EduPristine Study Program. FRM®, GARP® and Global Association of Risk Professionals®, are trademarks owned by the Global Association of Risk Professionals, Inc
CFA Institute does not endorse, promote, or warrant the accuracy or quality of the products or services offered by EduPristine. CFA Institute, CFA®, Claritas® and Chartered Financial Analyst® are trademarks owned by CFA Institute.
Utmost care has been taken to ensure that there is no copyright violation or infringement in any of our content. Still, in case you feel that there is any copyright violation of any kind please send a mail to email@example.com and we will rectify it.
2015 © Edupristine. ALL Rights Reserved.