08/02
2008
Google Quality Score and Excel fun (3,592 views)
I really like my toys. If they are geeky. Like Excel.
Yes, I admit it, I actually like Excel.
Excel 2007 particularly has some neat features.
One gimmick I especially favor when working with keywords and AdWords:
Color Scales in Conditional Formatting.
It makes visualizing data a lot easier, everybody knows how boring hundreds of lines of cell data can be (unless you are a true geek).
Although Google’s Quality Score is pure theory for all of us since we have no idea how it is being compiled, its basic algorithm can help you when trying to optimize your bidding strategy. Especially when you can visualize the data.
Let’s assume you have a certain amount of competitors for a certain keyword. In the following screenshots let’s assume we are Advertiser B:
Your CPC has been pretty much the same for a while, and all of sudden things change:
Your ad either drops a couple of positions, or your CPC increases steeply.
What happened? A new competitor? A higher bidder?
It’s not necessarily bid-related (though it could be, and you can play around with this option yourself), but let’s assume the following:
One of your competitors has sprung for a copywriter, and this guy revamped the ads and maybe they did a little housekeeping in the account structure. But no bids were changed.
Google noticed the changes and increased his QS by a couple of points because the new ads are better and the landing page is now considered to be more relevant as well.
So instead of a 1.2 (or whatever the numbers Google assigns to the QS may look like) he now gets a 1.6.
Let’s look at this sample in Excel:
You were holding your #2 position, you never were really interested in #1. And this lineup has been the same for a while.
Now Advertiser C’s QS jumps from 1.2 to 1.6 and your CPC increases from $1.14 to $1.52.
Advertiser C’s CPC decreases from $1.61 to $1.21.
This is what Google’s *instant reward* is all about. And it’s noticeable.
They take it one step further and optimize the landing page for this particular keyword as well, and Google again raises his QS, from 1.6 to 2.0.
All of a sudden you get pushed off your #2 spot, because your max CPC was $1.70 and would now be $1.90, so the list of advertisers gets resorted, and this is the outcome:
Advertiser C is now in #1, you are in #3.
While your CPC has decreased again to $1.08, Advertiser C now pays $1.66 for the #1 spot (more than Advertiser A used to pay before the first change).
You can now raise your CPC to get #2 back, let’s say to $2.00:
This would kick Advertiser A off the #2 spot, because #2 now costs more ($1.65) and is higher than his max CPC of $1.50, and would actually put you in the #1 spot.
If this is not what you want, set your position preference to 2 and below.
It’s all theory, but it’s a fun (and geeky) thing to play around with.
Download the Excel 2007 version here
Download the Excel 2000/2003 version here