You’re already familiar with the concept that 1 in 4 search queries have never been searched before but do you know how this affects your niche?

My boss recently asked me to come up with an estimate for the total size of our market. I could have just grabbed search volumes for all terms from the Adwords tool but as I’m gunning for a pay raise I thought I’d go one step further and provide an estimate of longtail traffic as well.

Why is this significant? Well if like me you have a list of over 1000 misspellings of your brand name (my brand really doesn't have a complex name either) you’ll know that these unique queries can quickly stack up into a significant figure.

This is just a back-of-a-napkin type calculation so it's not very accurate but it doesn’t take long and gives you a handle on the significance of your extreme longtail. I’ve even provided a template spreadsheet so that you can see what I’ve done without having to replicate it.

Apologies in advance if my use of Excel is a little hacky or my description a little patronising – I’m not an advanced user so I have to go slow.

Step 1 - Gather your data.

You will need several months of keyword data for your site(s) including visitors and sales figures. You really do need a good way of modelling your sales for this to work so I used an in-house attribution model that we've built into our analytics package.  

Sort your data into columns, add a month column and you’re ready to go.

Step 2 – Identifying the longtail

This is the important bit. To do this I’ve assumed that if a search query generated traffic in one month but not in the preceding or following months then it’s a longtail term.

To do this I’ve used a custom sort and a couple of IF statements.

The custom sort distributed terms in alphabetical order and then by month. Like this;

And then the IF statements check to see if the term is duplicated anywhere in the list and therefore doesn't qualify for my definition of extreme longtail. If you’re not an Excel person it looks more intimidating than it actually is but don’t worry it’s all been included in the template.

         TIP - If you’ve got pro Excel skills then I suppose you could add a VLOOKUP to exclude certain terms (like a list of known significant search terms that you don’t rank well for) but this type of refinement is heavily dependent on the accuracy of those excluded terms which is a whole other post.

Step 3 – Sorting

Now you’ve identified your longtail terms the analysis is up to you. Here’re a couple of figures you can pull out of it;

• Average percentage of sales that come from these longtail terms.
• Average percentage of visitors that come from these terms.
• Average value per visitor – longtail vs core terms
• Count of longtail terms vs. core terms – i.e. 200 terms account for 95% of our sales but 2000 terms provide 25% of our traffic.

I ended up with some graphs like this one showing the sum of unique visitors from both sources. 

Or this one showing the count of terms sending traffic.

I hope that little lot helps you with your analysis. Like I said at the start it’s pretty basic stuff and I’m not an advanced user of Excel so feel free to add comments and suggestions for how to improve or refine the process.

You can find my template here.