Analyze Google AdSense Data Using Pivot Tables

Posted on March 16, 2008

Webmasters who use Google’s AdSense service to place ads on their site know that the analytical tools that AdSense supplies are pretty weak. Fortunately, it is easy to get report data from the site and into Excel where we can use all of the analytical tools that Excel provides. The purpose of this post is to demonstrate how webmasters can use PivotTables to analyze AdSense data. There are many reports that you can create in AdSense, and they are all candidates for analysis within Excel, but I will be demonstrating how to use pivot tables to analyze the "This month, by day" report that is standard.

PivotTables are an excellent tool for slicing and dicing data, but many Excel users aren’t aware of them. If you aren’t familiar with them then I would suggest that you check out the excellent Pivot Table Data Crunching for Microsoft Office Excel 2007 (or the previous edition for older versions of Excel) by Bill Jelen and Michael Alexander. Hopefully, this tutorial will get you started and let you see the potential for pivot tables.

The instructions given here will apply to Excel 2007, though I will give enough information for you to implement the worksheets in Excel 2003 and earlier.

The graphics shown in this post may be hard to read because I have to size them to fit the space available. You can download the example spreadsheet (Excel 2003 version) and follow along.

Goals of this Project

The goal of this project is very simple. I want to be able to analyze AdSense statistics so that I can see the performance of a website by month, day of week, week of month, etc. For example, I may want to know how well the website performs on Mondays in the first half of the winter months. The PivotTables developed here will do that and more.

Please note that I am not an expert in Web Analytics, AdSense, or webmastering. So, it is entirely possible that I am missing some obvious ideas. If so, please leave a comment and let me know.

Getting the AdSense Report Data

As noted above, you can get a monthly report that shows the date, number of page impressions, number of clicks, the page click-through rate (CTR), the page eCPM (earnings per thousand impressions), and total earnings. The picture below shows that report for the first five days of March:

Take particular note of the CSV link in the upper-right corner of the picture. That link allows you to easily download and open the report in Excel as a .csv (comma-separate values) file. So, the first step in our exercise is to grab the data from your account and get it into Excel. Log on to AdSense and then generate the "This month, by day" report. You will probably want more than just this month’s results, so you can change the date range on the Advanced Reports page. When the report is displayed, click the CSV link to open the data in Excel.

In the future, you can run this report once a month and just paste the new data at the bottom of your spreadsheet. The way that we are going to set up the worksheets using Tables (Lists in prior versions) means that the analysis will automagically include the new data without any extra work.

Setting Up the Worksheet

Select all of the data and convert it to a table. In Excel 2007, click on the Insert tab and choose Table. (In Excel 2003, click the Data Menu and then choose List --> Create List.) You will now have a Table with an AutoFilter on the headers. While the AutoFilter can be useful in its own right, the best things about using a Table are:

1. It will automatically expand when you add data at the bottom. This means that any formulas, charts, or PivotTables that reference data within the Table will automatically include the new data.
2. Formulas that are entered into a column within the table are automatically copied down the entire column, and are filled in when new data is added.
3. We can create formulas that reference data in the table using syntax like this: Table4[[#This Row],[Date]]. That formula gets the date from the current row in Table4.

Now that we have defined the Table, we are ready to add some formulas that will be helpful when we create the PivotTables.

Calculate EPC

We will start by adding a formula to calculate EPC (earnings per click). Since we are working with daily data, this is actually the average EPC for each day. To calculate EPC, we simply divide the days total earnings by the number of clicks. In G1 type EPC as the column heading. Notice that the Table expands to the right to include the new column. Now, in G2 type = and then click on F2, type a /, and then click on C2. Entering the formula in this way will cause Excel to create the formula using Table references. Your formula should be:

=Table4[[#This Row],[Earnings]]/Table4[[#This Row],[Clicks]]

and it should automatically fill down the entire column. If it doesn’t, then you should see a SmartTag that offers to fill down for you.

Break Down the Date

Next, we will break the date into its components (Year, Month, Day). This will allow the PivotTable to aggregate the data based on those time units. In H1 enter Year, in I1 type Month, and in J1 type Day. Now, in H2 add the following formula:

=YEAR(Table4[[#This Row],[Date]])

That will extract the year from the date column in the table.

The formulas for the Month and Day columns will be similar, but we need to set up a couple of lookup tables. Realize that the Month() and WeekDay() functions return numeric results. For example, if the month is June then the Month() function will return 6. Similarly, the WeekDay() function returns a number (1 through 7) indicating the day of the week. We want to convert the month numbers into month names, and the day of week numbers into the names of the days. In P2:P8 enter a series from 1 to 7, and in Q1:Q8 enter the day names starting with Sunday. Create a similar list in S2:T13 with the numbers 1 to 12 in column S, and the month names in column T. We will now use the VLookup() function to return the name of the month and the name of the weekday. Here are the formulas for these columns in the Table:

I2: =VLOOKUP(MONTH(Table4[[#This Row],[Date]]),$S$2:$T$13,2)

J2: =VLOOKUP(WEEKDAY(Table4[[#This Row],[Date]],1),$P$2:$Q$8,2)

Again, those formulas should be automatically copied down to every row in the table. You should now have the appropriate month names and day of week names in columns I and J.

Calculate Day of Month and Week of Month

Finally, we will add two more columns to help us really drill down into seasonality. We will add formulas to calculate the day of the month and the week of the month. For best results in the PivotTable filters, these should be converted to strings, so we will make use of the Text() function.

In K1 type Day of Month as the column header, and then in K2 enter the formula:

=TEXT(DAY(Table4[[#This Row],[Date]]),"#")

That simply uses the Day() function to get the day of the month, and then converts the result to a string.

Calculating the week of the month (First, Second, Third, Last) is slightly more complex. We will take the day of the month and divide by 7. Now, we need to convert that result into an integer in the range of 1 to 5 by rounding upwards to the nearest integer. For example, if the day is the 18th then 18/7 = 2.57. Round that upwards to the nearest integer (3) using the RoundUp() function. We now know that it is the third week of the month. We can now use the Choose() function to select an appropriate label for that week. In the Choose() function we supply an index number and an array of values. In this case, the 3 will get converted into Third.

In L1 type Week of Month, and in L2 enter the following formula:

=CHOOSE(ROUNDUP(Table4[[#This Row],[Day of Month]]/7,0),"First","Second","Third","Last","Last")

Notice that I have repeated "Last" in the Choose() function. That way, both week 4 and week 5 will be labeled as the Last week of the month. The reason for this is that there will be, at most, three days in the fifth week of the month. You could certainly change those to "Fourth" and "Fifth" if you so desire. Finally, realize that you could create another column to calculate the season of the year (Winter, Spring, Summer, Fall) using similar logic.

We have now completed the basic Table construction. Your worksheet should look like the one below:

Creating The First PivotTable

You can learn a lot by just using the AutoFilter in the Table that we have created. For example, if you want to look at all of the data for just Fridays, you could filter column J so that it just shows the rows that are Friday. However, PivotTables are much more powerful.

To create the first PivotTable, first click anywhere in the Table we have created. Now, click on the Insert tab and the click the PivotTable button in the Tables group. You should create the PivotTable on a new worksheet. A new worksheet will appear, and you will see the PivotTable Field List in a pane at the right side.

In the Fields section, select all of the fields to be added to the report except for the Date. We don’t need the date field because we have already extracted the relevant parts of the date. As you select fields, they will be added to the PivotTable in the worksheet. At first it will be relatively incomprehensible, but we will fix that now.

In the Areas section you will see buttons with the field names. We can drag these to different areas to rearrange the PivotTable. Start by dragging the Day, Day of Month, Week of Month, and Year fields into the Report Filter area. This is what allows us to make the PivotTable show only results for, say, Mondays in the Third week of the month. Or, we could show results for the 14th day of all of the months, or only certain months.

Next, drag the Month field to the Row Labels area. This will cause the PivotTable to summarize all data by the month of the year. You can have more than one field in the Row Labels area. For example, you might drag the Year field into this area (above the Month Field) to summarize the data by year and month. If you want to easily compare months from different years, just drag the Year field below the Month field.

By default, the data in the PivotTable is the sum of the values. However, for our purposes the averages of the values is more useful. So, in the Values area click the arrow next to each field name and select Value Field Settings. Choose Average for each of the fields. Note, though, that you can choose other functions to summarize the data. For example, we might want to see the standard deviation of the EPC by month. In that case you can change the Value Field Setting for the EPC field to StdDev. That would allow you to see if certain months (or times of the month) have more or less variable click values.

The PivotTable is now complete. Experiment with it by choosing different filters, and by dragging fields from one part of the PivotTable to another. Here is the PivotTable showing a summary of all of the data by month:

Here is the PivotTable showing the data for Mondays that are in the second week of the month:

In the example worksheet I have created a second PivotTable. It uses the same data, but it uses the day of the week for the Row Labels area. This second PivotTable isn’t really necessary, but I like to see the data summarized both by day and by month at a glance. However, you can use just one PivotTable and rearrange it as needed, depending on what types of patterns you are looking for.

I hope that you have found this to be useful. An Excel 2007 workbook is available for download (Excel 2003 version). It contains the sample data and the PivotTables created in this post. Please note that the data in the workbook is completely random. It is against Google’s terms of service to disclose your AdSense statistics.

Comments:

Name:

Email (will not be displayed):

Location:

URL:

Smileys

Remember my personal information

Notify me of follow-up comments?

Submit the word you see below:

<< Back to main