Are your earnings truly taking a turn for the worse or do you just think they are?

For those of you concerned with your stats, the following should help you in determining whether or not you are having a "good" or "bad" day. That may be it, too, just a bad day. To really see what's going on, you need to look at multiple days exhibiting "badness" before you need to worry.

The first thing to do is import a sizeable period of data from your adsense advanced reports into Excel. If you are also using Google Analytics, I'd import either Visitors or Visits as well. The following assumes you have a basic working knowledge of creating graphs in Excel.

When you do this, you will probably have something like this for your columns (I hope so anyway because the steps that follow all assume that this is how your spreadsheet looks!):

A: Date
B: Impressions
C: Clicks
D: CTR
E: eCPM
D: Earnings
F: Visitors (from Analytics)

Insert a row between row 1 and 2 (right below the headings) so that your actual data starts with row 3.

To calculate how much your earnings per click are for a day, goto cell G3 and type =d3/c3 This formula simply divides your earnings by clicks. Copy G3 and paste it into G4 down thru to the end of your data.

The example I will provide here is how to look at your impressions in the "grand scheme" of things. Insert a column before and after Impressions. This will leave B and D as blank columns, and Clicks should now be in Column E.

A: Date
B: Blank
C: Impressions
D: Blank
E: Clicks
F: CTR
G: eCPM
H: Earnings
I: Visitors (from Analytics)
J: Earnings per click (you calculated this)

Now, in C2, type =stdev(c3:c500) In my own dataset I'm looking at data from the first of the year, so I picked 500 to make sure I was well below that. Choose a number that is at least as large as the number of rows of data you imported. If you are going to be using this spreadsheet in an ongoing fashion, choose a large number so that you don't have to keep editing the value to ensure that you include all the rows.

The formula you just typed is a measure of the variability of your data, or the standard deviation of the data.

In B3, type =c3-$c$1, and in D3, type =c3+$c$1 Copy those cells down like you did for the earnngs per click in Column E which, after you inserted the two columns, is now column J.

Now you need to plot these values. Select the range of dates from A3 down to the end of your data, and the impressions...from C3 to the end of your data and create a scatter graph from them. Select one of the points on the graph by right clicking, and select Add Trendline to add a Linear trendline.

Add a data series to the graph for the data in columns B and D and create trendlines for them. Then select each of the two series that you just added and set their marker styles to none so they do not appear on the graph and you are only left with the markers for actual impressions, and the three trendlines. You may wish to make the top and bottom trendlines dotted or dashed to give them less emphasis than the middle one which is the trend of your actual data.

Now, you should notice that most of your points fall between the two outermost lines. If, as you go along, you start to notice points continually falling below the bottommost line, then there is the possibility that something has happened and you are losing impressions. At this point it would be time to investigate a few things.

You can do the same for any of the other variables, though things like earnings or eCPM or earnings per click, do not be as concerned until you notice a couple or more in a row failing to reach the bottom line. This is because of the higher variability of those values.

Likewise, when you start to see a couple in a row falling above the top line, then things may be starting to take a turn for the better and it may indicate a trend change.

Now, each day all you have to do is type the values into the appropriate cells. In the cells that are calculated, go to the cell below the last value in that column and hit Ctrl-D That will copy the formula down into that cell. Make sure that your formulas in the second row (the standard deviation) include your new day of data, and make sure that your graph is plotting the new day as well.

Good luck!