If you have followed my previous posts I collected data from 3 separate sources.
- Adwords via Google Big Query
- SERPS via our Data Warehouse & Google Big Query
- Revenue data also from our Data Warehouse via FileMaker & SalesForce
Now What? Generally this is where you are directed by a stakeholder to provide a question, that your data helps you answer.
The following is an excerpt from the brilliant article by Chris Liversidge – “use Power Bi to supercharge your SEO“.
“Why would that depth of data be useful? Well, it means we can assess the difficulty to perform in the traffic-driving positions (positions 1–3) and contrast it with the expected return (revenue per click behavior from AdWords behavior for the term historically).
That allows us to laser-focus on terms which provably drive revenue for a business and quantify clearly the impact of performance improvement in revenues gained and paid spend that can be tested for reallocation once a top position is achieved and SERP CTR behavior is higher than expected for the position (indicating searchers are overwhelmingly satisfied with the organic SERP call to action).
Of course, we can flip that analysis approach into reverse and use organic data to lead PPC insights. For example, we can show the revenue available for generic search term expansion where paid behavior is better than expected — indicating a tight match between searcher intent and client product offer — and organic behavior is less than expected (suggesting some level of paid cannibalization). “
For us we had a few questions…
- How can we determine where it is worth creating Adwords Campaigns based on revenue and rank.
- How can we best allocate campaign spend taking into consideration competition (AdWords & SERPS rank) and expect return on investment
- Where are there “low hanging fruit” opportunities, where small amounts of Ad spend can make big impact.
Our approach was to score some key measures against defined thresholds and we defined this as our AdWords Opportunity Score as described below…
The score ranges from 0-3, where 3 is the highest.
The Adwords Opportunity Score is calculated by scoring
1 point for each of the following criteria…
1. Lost of Business listed in the category:
Total listings in the category is greater then 30.
2. High value: The average revenue per cleint is
greater than $1000.
3. Strong SEO & Undersold: the average keyword
ranking is in the top 10 and there are less
then 20% paid listings in the category
OR
Weak SEO & Oversold: the average keyword ranking is
outside the top 10 (not showing on the first page of
organic search results) and the % of paid listing is
greater than 50%
Below shows the report I created with data hidden…
