AdWords + Power BI Part 5 – What does this all tell us?

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  – “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). “

Microsoft Power BI for SEO

For us we had a few questions…

  1. How can we determine where it is worth creating Adwords Campaigns based on revenue and rank.
  2. How can we best allocate campaign spend taking into consideration competition (AdWords & SERPS rank) and expect return on investment
  3. 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…

8 Replies to “AdWords + Power BI Part 5 – What does this all tell us?”

  1. Excellent blog! Do you have any suggestions for aspiring writers?
    I’m planning to start my own website soon but I’m a little lost on everything.
    Would you advise starting with a free platform like WordPress or go
    for a paid option? There are so many choices out there that I’m completely confused ..
    Any suggestions? Thanks a lot!

  2. Nice post. I was checking continuously this blog and I am impressed!
    Very useful information particularly the last part 🙂 I care for such info a lot.
    I was looking for this particular info for a very long time.
    Thank you and good luck.

Leave a Reply

Your email address will not be published. Required fields are marked *