I do it about once a year. It takes about five minutes from download to chart. What don't you understand?

Just out of curiosity, how many of you use Excel (or a Google Spreadsheet) to look at your account data?

What kinds of analysis do you do? What charts and graphs do you create?

ASA

I use the data primarily to check

1. Which pages have the maximum CTR rate and which have lowest
2. Which pages have lower eCPM

I work on the pages with the lowest CTR and see whats wrong with them like irrelevant ads and reduce the number of ad-blocks for that particular pages.

For lower eCPM pages, I just use the Google Adsense preview tool and see if any MFAs or garbage ads are there.

Finally, those pages which have the highest CTR rate, I see how I could monetize them further more without hampering the user experience.

I use Excel to graph the following over long term periods (usually 1, 2 or 3 years) with 90 days moving averages:

1- page impressions and earnings (impressions are divided so the figures are close to earnings so I can see them together)
2- EPC
3- CTR and eCPM

I usually just download the CSV file, copy and paste the data to refresh existing Excel files. Sometimes I will compare different sites together, stuff like that.

Hi Billy;
What don't I understand? How do you take a stream of data (all the rows in the report) and pull out every 7th day into a chart, for instance. I don't want to do it all by hand, do you use formulas with if functions to do it? Is there some other way to tell excel to chart every 7th day, or group by days of the week, etc?

Since ASA is reading this thread, a bunch of tick boxes looking at data by day of week, or by weekends or weekdays might be nice either in our reports or in Analytics. My site traffic varies greatly by day of the week.

Thanks!

Here is a simple solution:

1. Create your desired online report using adsense report painting tools

2. Hit CSV export link in your displayed report and either directly open it into Excel or save on local disk as SDF file and then open it into Excel (or your choice of spreadsheet app)

3 In your spreadsheet app (Excel in this eg.) select the entire first column listing data field and change the type of date format to includes day of the week along with full date. This step is actually not required but nonetheless would help you.

4. Now INSERT a column to the left of the column listing date field.

5. Enter following formula into A2 cell of newly inserted first column and copy it across the newly inserted column.
=TEXT(WEEKDAY(B2), "dddd")

This formula would now automatically insert 'day of the week' string into the first cell for corresponding date in second column for whole column.

You can now sort your data using DATA->Sort option on first column and choose data for ANY day of the week across your date time scale.

Hope this helps

After the end of every month, I download the "last month, by day" predefined report and add the data to the end of an Excel Table. This is used as the source for a pivot table, which makes for easy slicing and dicing of the data. For example, I can easily chart CTR for Mondays in June over the years, or any other combination of the data.

It also helpful to add a few columns of your own formulas to the source table to calculate your own metrics (e.g., earnings per unique visitor, which I get by combining Analytics data with the AdSense data). I wrote a blog post about how to get started doing this, but you'll have to do a search if you are interested.

That's a great approach. Thanks!

Now, how can I save that formula (and others) into a sheet so that I can import multiple reports from AdSense, or the same report multiple times and not have to redo all of the formulas each time?

Just out of curiosity, how many of you use Excel (or a Google Spreadsheet) to look at your account data?

Spreadsheet hours

Columns

Time
Impressions this day
Clicks this day
Earnings this day
Impressions since last time watching
Clicks since last time watching
Earnings since last time watching
Hours since last time watching
Impressions per hour
Clicks per hour
Ernings per hour
eCPM since last time watching
CTR since last time watching
EPC since last time watching

just right now in line 15783
built up since 2005

Spreadsheet days

Date
Imprssions at Webbarometer webhits.de
My Impressions
Webbarometer / My impressions
gliding everage of this for one week

I've shared this with a bunch of people here. Our engineers love to hear about this kind of stuff.

ASA

how can I save that formula (and others) into a sheet so that I can import multiple reports from AdSense, or the same report multiple times and not have to redo all of the formulas each time?

First save your final version of spreadsheet as excel workbook *.xls file (not as .csv) on local disk.

Now import your new report from adsense report generator into excel as new worksheet and simply copy-paste all data-rows from this worksheet into your final version of worksheet leaving first column untouched. Save your work as a new workbook or append it as new worksheet under a workbook if you like to compile ALL your reports in a single workbook.

unLTD.

I will be happy to send you the sheet that I use, All you have to do is paste LY's data in the 2008 Tab, Paste TY's Data in the 2009 Tab and it will chart out a lot of data and trends.
To me Trends by Day of Week give the best picture of the health of your site.
Other comparisons are TY LY by Month and a few others.
Beware, it also charts out Revenue per Click and that has been a scary trend lately.
I guess I can send it to you if you message me with your email.

5. Enter following formula into A2 cell of newly inserted first column and copy it across the newly inserted column.
=TEXT(WEEKDAY(B2), "dddd")

Goodness!
Just type Monday or Mon or which day it is (Tuesday or Tues) into A2, then pull that down. Excel has built in lists called 'custom lists' that repeat commonly used items.

Then in a new column type =if(A2="monday",B2,"")
So if A2 equals monday, put in the income field (b2) otherwise put nothing.

Then you are on the way to separating out income from days of the week into separate columns.

RonS -

I'm not sure why you'd like to only chart every 7th object except if you want to compare Mondays...

If you're worried about the X axis looking too busy, then you can change the way it looks. For example, if you right click the X axis, (Format Axis), then go to the Scale tab. There you can change Major Unit to 7 (Days).

Here's another approach I use because my site has a weekly pattern (weekends drop off). What I do is chart a rolling 7 day average. You just use =average(B2:B8), then copy down the formula (using a double click in the corner of the cell <-- another tip). This is a smoothing technique you can use.

Billy, that's exactly what I want to do, chart all Mondays, chart all Tuesdays, etc.
Chart the first full week of the month.

My traffic varies during the week, I'd like to see how my AdSense stats vary during the week.

Okay RonS, let's solve this problem...

You can follow the advise above given by unLTD above, but you do not need to sort the data and pull it apart.

Let's say you have three columns of data:

Date, Day (Monday...), and Value.

Create a chart (Line?) of the data you want to plot (all the data). Then go back to the table of data and select the columns you charted.

Then Data>Filter>Autofilter.

This will place pull down tabs on all the column headings. Go to the Day filter and select the day you want to show in the chart (Deselect others...).

Your chart will automatically update and only show the Day you've selected by the filter.

I've played with the spreadsheet sent (Thanks golocal) and with suggestions made by many folks.

The autofilter approach worked VERY nicely to look at data by day in a graph, but I have problems with things like cells with formulae and averages and so forth.

I guess what I am looking for is some sort of a macro that can be run from within Excel that will open up the downloaded Google CSV file and import the into a sheet, and perhaps break it out into separate columns by day. Or more.

For right now I have a large sheet and daily I'm having a report emailed, and I'm opening that sheet, and opening my sheet and copying data from the new sheet to the master sheet, extending formulae to the new rows, then adjusting ranges on the charts so that the new data appears. It's quite a cumbersome task that should be able to be modified.

[edited by: RonS at 12:35 am (utc) on June 4, 2009]

Since you originally brought it up, I have been simplifying the spreadsheet I sent you. Basically I started from scratch.
Reason: I used to work for an engineering company. Engineers think like engineers, Google has a lot of engineers.
The CTR and eCPM numbers remind me of me, a non-engineer working with a bunch of engineers.
Frequencies and Ratios make perfect sense in their world. To a business man it can be considered jiberish. Bottom Line is what we understand. Am I making improvements over Last Year, Last Month,last week etc.
If I am making improvements then I should be making more revenue etc.
You said,
I guess what I am looking for is some sort of a macro that can be run from within Excel that will open up the downloaded Google CSV file and import the into a sheet, and perhaps break it out into separate columns by day. Or more.
My new sheet does that except for the CSV Macro,
You sill have to copy and paste from the CSV to Excel.
I will send you the new one tomorrow.