Calculating the value of organic traffic

Each of us is a SEO salesperson, at least in part; it’s our job to help clients understand the value delivered by our services. There are a number of time-tested ways of doing this, the most common of which is to calculate year-over-year traffic and revenue increases from the organic channel.

Often, success shown by these metrics is enough to justify the existence of the SEO program. There are times, though, when this is not enough.

Many times, I have worked with retailers that have great brand recognition driven by big PPC and print advertising budgets. This sometimes leads to the belief that their organic traffic is all branded and only a side effect of their PPC or print efforts. There may also be an IT manager or Web Developer telling the marketing manager they’ve got SEO covered.

Setting aside the argument that branded traffic can be credited to SEO’s focus on indexation, organic site links and/or reputation management, it is easy to see that the rise of “keyword not provided” has made countering this argument more difficult, because there is little analytics visibility into the non-brand keywords that are driving conversions and revenue.

In today’s post, I would like to share a method I’ve successfully used in the past not only to differentiate brand from non-brand traffic, but also to assign a value to non-brand traffic that a PPC-minded manager can appreciate.

Google Search Console’s “Search Analytics” report

Within Google Search Console, it is no secret that SEO pros can use the Search Analytics report to fetch the past 90 days of organic search queries that have driven traffic to the client’s site. Although there are valid conversations about data discrepancies that appear within this report’s click count, we can absolutely use this report to:

• Pinpoint the relative volume of non-brand queries driving traffic to the client’s site.
• Discuss the value of that non-brand traffic with more detail than is available within analytics programs.

Here’s a quick recap on how to extract non-brand queries from this report:

Go to Google Search Console > Search Traffic > Search Analytics, and alter “Dates” to return the last 90 days.

Click the Download button at the bottom left of the table:

You’ll get a CSV that looks like this:

What we’re looking for are only the non-brand keywords. There are a number of ways to filter out branded keywords, but an Excel-only way is to use wildcards to delete any queries with the brand in it.

Click [CTRL+H] to open the Find and Replace dialogue box, and enter what I call the brand “root” between asterisks. (An example of a brand root for Panasonic might be “*panaso*” or the brand root for Toyota might be “*toyo*“). We’re going to replace the brand root with nothing.

Click “Replace All,” and all queries using the brand root will be blanked out:

Right-click on one of the now-blank cells and select “Filter by Selected Cell’s Value.”

That will show you only the rows with blank queries. Highlight them all and delete them.

Go back to the top row, click on the icon of the filter, click the “Select All” box, then click “OK” to re-show what’s left:

You may have to spot-check for brand misspellings or things that the wildcard deletion missed (like I missed in row 8, below), but you should now have a list of non-brand keywords and 90 days of click data (sorted by most clicks to least):

Create a new column named “1 Month Click Average” and divide the B column by 3:

Save this file as an XLS.

Adding “Suggested CPC” to non-brand clicks

Our goal is to now get Google’s Suggested CPC for these non-brand keywords, then multiply the 1 Month Click Average column by that Suggested CPC.

Add a new column (named “QueryForCPC” below) containing a formula to wrap the query with brackets.

Highlight this new column, copy and paste the values into Notepad. Once you do, you’ll be left with a txt file that looks like this:

Save it. The brackets are there to indicate we want the exact match for this phrase when we ask for the suggested CPC.

Open Google Keyword Planner here: https://adwords.google.com/KeywordPlanner

Click “Get click and cost performance forecasts,” then select “Choose file” under “Option 2: Upload file.” Select your .txt file, and then click the “Get Forecasts” button:

On the resulting screen, enter a bid of $10.00 in the “Enter a bid” input box and $10,000 in the “Enter daily budget” input box, then click “Get detailed forecasts.” (I’ve found that these amounts produce the best results.)

Click the “Keyword” tab and then the Download button.

Select “Excel CSV” when prompted, and Download.

You’ll get a file that looks like this. What’s important are the Keyword and Estimated Average CPC columns:

Go back to your original XLS, add a worksheet, and paste in the Keyword and Estimated Average CPC columns:

Add a column to the original worksheet with the name, “Average CPC.” In it, we’re going to do a VLOOKUP on the tab you added with the CPC data.

In the Avg CPC column’s first cell, type in =VLOOKUP(A2,Sheet1!$A$2:$B$1000,2,FALSE). Hit enter, then copy that cell and paste all the way down that column to the last row.

You’ve now looked up the keyword’s Estimated Average CPC from Sheet1 and pulled it into the original sheet, in the same row as the Query.

Finally, add a new column named “Est Cost/Mo,” and type in =G2*H2

Hit enter, then copy and paste that formula all the way down that row to calculate the 1 Month Click Average * the Avg CPC.

The sum of that Est Cost/Mo column is what the client would have had to pay AdWords to get those non-brand, organic clicks each month:

In this example, we see that it would have cost the client more than $634,000 each month if they had used PPC to drive these non-brand organic clicks (through Google only). While seeing the value of this non-brand, organic traffic in PPC terms may not sway those who are determined to tell you the site “would have gotten this traffic anyway,” it is a great way to frame non-brand organic traffic in a way that PPC-minded managers can appreciate.

Even if you argue that your ranking achievements have driven clicks for only select keywords within this non-brand mix, the value will still be clear.

Moreover, you can also go on to apply an estimated, non-brand conversion rate and average order value to the clicks column to provide a ballpark estimate of the revenue driven by these non-brand clicks.