Have you ever wished you could understand the users who ended up converting in your campaigns better? What drives them and what queries they were asking Google before turning into your customer?I’ve built an online tool to tackle this challenge: keyword frequency web application

But first – let’s understand the ideas behind this:

The Good news is: In the world of Search Engine Marketing a Search Query Report pretty much tells us the exact query a user asked Google.
The Bad news is, Without the proper tools like above, a whole mother-load of information is actually buried in an SQR report:

Example of a Cafe’s Search Query Report.
Match type Search term Clicks Impressions CTR Avg. CPC Cost Avg. position Converted clicks Cost / conv. Conv. rate All conv.
broad where to eat at macpherson 4 12 33.33% 0.59 2.36 1 6 0.39 150.00% 6
broad cafes near aljunied 3 10 30.00% 0.34 1.01 1.2 6 0.17 200.00% 6
broad nice food at macpherson 1 1 100.00% 0.74 0.74 2 6 0.12 600.00% 6
broad taiseng cafes 7 36 19.44% 0.39 2.76 1.1 5 0.55 71.43% 5
exact good food in macpherson 4 32 12.50% 0.28 1.14 1 5 0.23 125.00% 5
broad food places near taiseng mrt 1 2 50.00% 0.75 0.75 1 5 0.15 500.00% 5
broad western food at macpherson road singapore 1 1 100.00% 0.77 0.77 1 5 0.15 500.00% 5
exact macpherson cafes 12 134 8.96% 0.2 2.42 1 4 0.6 33.33% 4
broad cafes at payalebar 11 56 19.64% 0.29 3.17 1.1 4 0.79 36.36% 4
broad cafes at macpherson 14 51 27.45% 0.13 1.78 1 4 0.44 28.57% 4
exact cafes near macpherson 4 37 10.81% 0.35 1.41 1.1 4 0.35 100.00% 4
exact cafes in aljunied 4 27 14.81% 0.3 1.19 1.1 4 0.3 100.00% 4
exact good food near macpherson 3 17 17.65% 0.24 0.73 1.1 4 0.18 133.33% 4

… plus 1000+ more rows not shown!

Sorting by Converted clicks gives us a slight sense of what is important the campaign
eg: location themes like macpherson, aljunied, taiseng
Words signifying food such as eat, cafes, food

However, is there much more buried in this data in the thousand of rows beyond the first mere 20 rows. One useful technique is to mine SQR for unique keyword frequencies.

We isolate the unique keywords that appear in the search query report, then we sum up the number of times a particular unique keyword has contributed to a conversion.

This, in essence, lets us know how important a particular theme (unique keyword) is. Themes that were previously buried in the long list of long-tailed search results now would show up.
Apply the same techniques on other metrics also help reveal how useful a particular theme is.

fullkeywordfreq2

The length of the bar represents how many times a user has converted having have that particular unique word appearing in his/her search query.

The color of the bar represents how well the term converts – the lighter the color, the better.

Now the following actionable insights are quite apparent:

  • Of all dining themes, Food is the most important followed by cafe
  • Cafes is an important theme, however, it has significantly lower CVR compared to the rest of the themes – perhaps our website does not feel “cafe” enough to appeal.
  • While “eat” seemed important because it appeared 6 times for that search pattern, when we start to sum frequency up it is not as important a theme
  • For location macpherson & taiseng are the most important. While payalebar and aljunied were equally far away, payalebar has poorer CVR.
  • There is a small but substantial demanding for cakes, buffet, and breakfast. These terms are appearing even while we did not specifically bid on them!

Some actions we can possibly take:

  • Permutate and expand on important theme (After checking CPA)
  • Look into Paya Lebar overall CPA, if negative keywords could be added to cut costs.
  • Make website include cafe oriented landing page (Business is actually a Cafe, but website may not have felt like a cafe due to pages devoted to showcasing food rather than ambiance..etc)
  • see if additional product offerings of cakes/breakfast might make sense for incremental revenue.

Think this could help?

This is a “light” version – do reach out if you have a use case that requires something more heavy duty.