If you open the excel document attached, you'll notice there are 5 columns: queries, clicks, impressions, CTR (click through rate) and position for a fictional bike company called "example".
This is example data downloaded from Google Search Console about a fictional bike company called "Example". Google Search Console allows us to download data about our website to see how we are ranking for given search terms.
To explain the columns if you are not familiar; queries are the actual search term that has been typed into google, clicks are the amount of clicks we rank for for that search term, impressions are the amount of times we show up on Google for that search term, CTR stands for click through rate and is a percentage of clicks to impressions and position is the average position we rank for the query on Google.
I'd like to be be able to upload any list with the same 5 columns and have it automatically tell me the following:
Average click through rate for positions 1-20 for branded and non-branded queries. See image attached for table to make it more obvious.
In essence, a "branded" query is any query with the brand name in - so in this example any query with the term "example....." in.
A "non-branded" query would be anything else, like "mountain bike" for example.
At the moment when I have my list I do the following:
1) Apply a filter on the "position" to filter the range I want. In the first instance this would be filtering the positions to only show those from 1 to 1.9. I would then apply an additional filter on the queries to exclude the brand name, in this example the brand name is "example". I then average the CTR with these filters on. I then repeat this process for "branded queries" in positions 1 to 1.9 (so queries where "example" is written in the query).
2) I then repeat this for positions 2 to 2.9
3) I then repeat this for positions 3 to 3.9
etc etc all the way to 20.
As a bonus point, sometimes there are a few misspellings of the brand name. I'd like the sheet to cross reference a list of branded terms I've typed in and it lumps all of them together. For example, our brand "Example" might show up for "eaxmple", "exmple", etc. I'd like to scan down the list, see which are commonly misspelled but which are clearly intended to be a brand search term and also exclude these on the non-branded queries report.
Any questions let me know,
65 freelancers are bidding on average £105 for this job
Hi, the idea is globally clear, but we need to discuss few point specially how you want to display thee filters and so on.. Can we have a short discussion please ? Thanks
i have good command over Ms excel especially pivot table, charts, advance formulas, formatting, macro and analysis as well, Please contact with me for more [login to view URL]