Lately I’ve been playing with Excel and diving into the many different ways I can visualize data from the various tools I use. In this post, I’ll be walking you through how to identify long tail opportunities that may be getting missed.

Step 1: Export Your Spreadsheet

For this example I’ll be using a SEO blog that I like to frequent, SearchEnginePeople.com, but feel free to use whatever site you want to (although this works best with a site that’s well represented in the SERPs).

Organic Keyword Tab in SEMrush

Plug a domain in SEMrush and when the results come back, click on the organic keywords tab to expand the results.

Don’t worry about sorting anything for now, just export to Excel, and we’ll worry about that in the next step.

Step 2: Sorting and Creating Your Pivot Chart

Pivot Table Fields

When you open your spreadsheet, you will see several columns that were exported. The only ones we’ll need are Keyword, Position, and Search Volume.

Sort highest to lowest Search Volume. You will highlight the 3 columns and create a pivot chart with their contents. In this example, I took the top 100 keywords with the highest search volume, but depending on the size of your site, you may want to pick and choose on a keyword/search volume basis.

Next you want to correctly set your fields. In mine, I’ve renamed Position to Rank. Assuming you’re fairly familiar with Excel you can visualize this chart in a way that best represents the type of information that you’re looking for; below is my finished product.

Step 3: Reading The DataLong tail possibilities illustrated with pivot chart

With this data we’re looking for long tail keywords that we’re ranking for but maybe not actually optimizing for. A dead giveaway of a situation like this would be a low ranking keyword with a low-medium search volume.

There wasn’t any obvious standouts in SEP’s case, but some possibilities with this data are illustrated below.

Long tail possibilities illustrated with pivot chart

Because of the strength of their domain, these are keywords that SEP could optimize around and make significant movement in the SERPs.

Your Turn

But alas, we can’t all have super strong domain authority and leap keywords in single bounds like Search Engine People. Nevertheless, an SEO powerhouse like SEMrush will give you a sound visualization and make you well on your way to finding long tail and lower optimized keywords that you may have overlooked.

I’m anxious to hear about any discoveries you made with your properties. Let me know in the comments below and be sure to try SEMrush today, it’s free for a full week. Click the link below to get started.