For several years now, Google has shown ads on my website and when someone clicked on one of the ads, it shared with me some of the revenue it earned. For some time now I have had the impression that the Adsense revenues have been in decline – mine, not Google’s. {grin}
So, earlier this week I analyzed the performance of my Adsense account and concluded that it no longer made sense to show these ads. This post discusses my analysis and the role of Excel in it.
The data from Adsense system included, on a month-by-month basis, information on number of ads shown, number of clicks, and revenue (for me, not Google). It also included derived information such as the revenue per click, called Cost-per-click, or CPC, and the conversion rate (clicks/ads shown). Of course the last two are easily computed from the first three data items.
Before going further, it might help to understand the different drivers of Adsense revenue. I thought of 3 key elements:
1) The kind of ad Google shows. It has to be relevant to the website visitor.
2) The number of times a website visitor clicks on an ad, and
3) The amount that Google pays me for the click.
Interestingly enough, the website owner has no control over any of those elements!
OK, back to the Adsense data. Google Adsense has an option to download the data in CSV format. So, after downloading into Excel, I “normalized” the data so that all the numbers for January 2006 were 100. This had two effects: one could look at large numbers and small numbers in the same visual space, thus making trend comparison easier, and it masked the actual data.
[A technical note. The month column included the start and end date of each month, e.g., 2006-01-01 - 2006-01-31. So, I added a new column to get a month as Excel understands it, using the formula =DATEVALUE(LEFT(B4,FIND(" ",B4)-1)), and formatted it to show only yyyy-mm.]
Next, I created a PivotTable and PivotChart showing on a month-by-month basis the number of views, clicks, and revenue over time. Since Google provided data for two products (Adsense for Content and Adsense for Search), I filtered the PT to show only Adsense for Content.
As Figure 1 shows, the views (in blue) have gone up while the clicks (in red) and revenue (in green) have dropped. So, this makes it evident that item 2 above (number of clicks) has not fared well over the years.
Figure 1
Out of curiosity, I decided to check if the revenues-per-click (my share of what the advertisers paid Google) had changed over the years. As Figure 2 shows, item 3 in the list above (the amount Google paid me per click) remained steady until early 2009, dropped in 2009 and the first half of 2010, rose in the latter half of 2010 to 2.5 times the 2006 level and remained steady through 2011.
Of course, since the total revenue continued to drop, it must mean that the drop in the number of clicks was far greater than the increase in the CPC.
Figure 2
To test item 1 in the list above (relevance of ads), I checked 3 pages at random. The results were, frankly, surprising. Given Google’s reputation of delivering accurate search results, I would expect website ads to be related to the website content. That, amazingly enough, was not the case.
On a page that explains supply and demand curves, there were ads for power tools and plastic enclosures.
Figure 3
On the TM AutoChart page there were ads for “Find Autos Near You” and project management software.
Figure 4
And, on the TM Randomize Slideshow page there were ads for Google Chrome, Microsoft Private Cloud, reverse look up of cell phone numbers, and a network monitor.
Figure 5
I am sure the relevance of the ads to the website content must make sense to Google’s Adsense algorithms but it surely escapes me.
In any case, I decided to pull the Adsense ads, at least temporarily.
