|AdSense Report Analysis Toys|
What tools do you use?
I use Excel for the advanced reports CSV exported file..
It comes as:
Date - impressions - Clicks - CTR - eCPM - Earnings
and from that you an easily get your EPC, averages and graphs..
How do you graph/compare a day of the week (say Mondays) performance over a period of years?
What other tools/toys do you use?
Yep, I'm using Excel, too.
I'm tracking each individual data point that Google provides, i.e. individual site-targeted ads, contextual ads, ad blocks displayed, pages displayed, the respective clicks and earnings.
The smallest period I collect the data is one day, as this is the smallest period Google offers reliably to us. Apparently, after a few days, the daily data seems to be final and does not change any longer.
With this data set, I look at weekly figures (my week runs Saturday through Friday) as well as monthly and annual figures.
As most of the Adsense figures have a high variance (almost random), the smallest set of data I look at is weekly data, and 7-day-moving averages (on the daily data). That gives you a glimpse of the trends, but to really get going I analyze the 30-day-moving averages. THAT's where the beef is.
I think that especially the 30 day moving average on the total revenues is interesting. That value lets me know immediately when things are getting better (or worse).
When charting the data, I also use 90-day moving average, and 200-day moving average. But I hardly look at these as the downward trend is too depressing. :-(
In general, I check stats a lot less often. Every once in a while I download all the stuff as CSV and then I pop it into the Excel sheet. It's just too boring and too frustrating to even bother with such analysis. I just do it to understand the Adsense trends on my sites.
If you aren't already, you really should use a pivot table on that data. I have one worksheet that I paste the CSV report into every month. It has the date, then three calculated columns (year, month, day of week), and then the other stuff from the CSV (imp, clicks, ctr, ecpm, and daily total earnings). Every month I just paste the new data at the bottom.
Using the pivot table, I can see my average (or total, or whatever summary statistic) for each metric by day of week, month, year, etc. Furthermore, I can look at say Mondays in Novembers over several years if that is what I want. It is all very automatic and controlled through drop-down lists. One you have the data, it takes all of two minutes to set it up and then no maintenance other than adding new data every month.
You can also create pivot charts that can be helpful.
I like to use Excel for charting EPC, impressions and earnings together (using a column that takes the value of impressions divided by 1000 so the charts are close in numbers). I really like to use 30 days and 90 days moving averages and see the trend for my current earnings, ECPM, CTR, total or for a particular site. I probably spend a bit too much time with this stuff, but I do it on my free time when I'm tired and nothing's good on TV.
What I usually learn is that earnings are related to traffic. Yeah, surprising isn't ;)
Pivot charts sounds great, too bad I'm an Excel newbie. I still load it all and do my charts and calculated columns manually each time. Any good web page to read on that?
I also use the digital point graph tool for quickies. I wish Adsense would offer this kind of service, in conjunction with Analytics, ideally.
Koan, a good beginner's guide to pivot tables can be found on Microsoft's site [microsoft.com].
Are there any ready-made spreadsheets floating around for the lazy or Excel newbies to take advantage of? =)
(off to try Google search)
There are several ways. One easy way is to insert a new column to the right of the date column. In this column insert following formula in the B2 cell:
The assumption here is that A2 is the start of your first date (A1 would be the label line). Once you do this copy the formula for the enter column. You will see each day of the week listed.
Next click anywhere in your table and then from the menu select Data->Filter->AutoFilter, this will create drop downs for each column. You can then select only the day of the week you want (from the new column). You'll be left with only the data for that day of the week.
Note: using the Filter can get a little out of control if you are not watching what you are filtering. If you are finding things getting out of control then you can reset things by going back to the Data->Filter->AutoFilter and deselect the option.
I wasn't happy with the possibilities offered by Google itself for adsense so I used excel too but since a few months I use a tool called CSV Adstats 4 - which a friend tipped me about - and I have to say ... I never want to go back.
And oh yeah .. it's completely free ! Let me know what you think about it.
[edited by: jatar_k at 2:53 pm (utc) on Mar. 28, 2008]
[edit reason] no urls thanks [/edit]
Wow that required .net framework setup file crashed my computer.. for the first time in I don't know, years. And I can't download the file. That's a crappy start.
10 minutes later: could never make it to work. Gotta love complicated dependencies of the sort.