How to Record and Analyze Your Page Views Using Microsoft Excel

While Associated Content does provide you with your total page views on a daily basis, as well as the total amount that you are due to be paid, and the percentage you have achieved towards the next Clout level, there is a lot more that you can achieve by using Microsoft Excel and a little knowledge of how to create formulas.

I have been recording my statistics daily for a number of months, and knowing the additional information that my Excel charts have provided me with, I have been able to set goals for myself and track my progress up the Clout chart.

Since some of these calculations require a knowledge of the Clout rankings in Associated Content and how they are calculated, if you are not fully aware of how this works, see HERE.

Basically the Clout level is calculated using your accumulated page views, plus a 1% bonus per article published (up to a maximum of 200 articles). For simplicity in my formulas, since I have published more than 200 articles, this means adding 200% to your existing page views. Therefore, if I have 50,000 page views and have more than 200 articles published, my Clout level is based on 150,000, or 50,000 times 3 (50,000 + 200%).

The image attached to this article is taken from my Excel spreadsheet. If you click on the image you should be able to see the detail and how it works. I recommend opening the image in another browser tab or window so that you can switch between the description and the spreadsheet image.

Assumptions

I am assuming that you already have a certain amount of basic knowledge of formatting cells and columns in Excel, and ideally also creating formulas.

The help text on Excel itself is not brilliant as regards formulas, but Googling for information should help enlighten you if you get stuck.

The data that you require to perform this page view analysis is all taken from the Reports tab on your Associated Content Dashboard.

Right, here we go with the definition of my Associated Content Page View Analysis Spreadsheet.

The Columns

A - Date
B - Daily Page Views
C - Articles Read
D - Month Page Views
E - Cumulative Page Views
F - Total Articles
G - Clout
H - Monthly Earnings
I - Average Page Views
J - Clout Percentage

The only columns that need to be entered manually are:

B - Daily Page Views
C - Articles Read
F - Total Articles.

Columns C and F do not form part of the calculations, they are for information only, and can be ignored if you desire.

For example purposes, I am going to show the calculations based on row 288, which is 26October.

A - Date

You only need to type the date on the first row of this spreadsheet, since it will be calculated for you on the following rows.

For maximum history, you might like to go back to the first day that is showing on your Reports, which is 3 calendar months.

The formula for calculating the date is:

=IF(B288=0,"",A287+1)

Column B is where you manually type the number of page views for a given day. The IF statement checks to see if the total page views are zero on the current row (cell B288), and only displays the date (value on row A287 + 1) if B288 is entered, otherwise it displays blanks.

The use of the IF statement is nice, since that means you can add a formula to columns for rows that you have not completed yet, and the results will only appear once you type data into the test cell, in this case B288.

If you go back and look at the formula again, it basically says "If B288 = 0, display blanks, else display the value of cell A287 + 1".

Hopefully that makes sense.

B - Daily Page Views

Nice and easy. Type the value of "Est. Page Views" for the day from your AC Reports table.

C - Articles Read

Type the data from "Total Content Viewed". This is not used anywhere in my calculations, it's just for information purposes.

D - Month Page Views

This is the accumulated page views for the current month:

=IF(B288=0,"",D287+B288)

Similarly to the date calculation in Column A, if a value has been entered for page views on this row (B288), then calculate the accumulated page views for the month by adding this to the value for the previous day (D287).

The only difference is on the first of the month, when you need to make the monthly total equal to the current day:

=IF(B263=0,"",B263)

If you look at the spreadsheet image, the 1^st October is row 263.

All clear? Hopefully it is, so let us move along.

E - Cumulative Page Views

This is similar to the previous column, except it is calculated from the time you started publishing on Associated Content.

In the first row of your spreadsheet you will need to type the accumulated page views up to the date you are starting with. If you have been on Associated Content for a while then you are unlikely to know this, however you can calculate it by determining the difference between what Associated Content says your total page views are, and what the spreadsheet says, once you have entered your data up to date.

For all other rows, the formula is similar to column D:

=IF(B288=0,"",E287+B288)

Note that we are always adding the page views for today (B288) to our previous total.

F - Total Articles

This is another manually entered field, and is the number of articles that you have published to date.

I do not currently use this in any of my calculations, however if you have published less than 200 articles, this will come in handy in calculating your Clout page views in the next column.

G - Clout

The Clout as was stated earlier is a formula that is based on your accumulated page views, plus 1% for each article that you have published up to 200 articles.

Since I have published more than 200 articles, my formula is:

=IF(B288=0,"",E288*3)

In other words, 3 times the accumulated page views in column E

If you have not yet published 200 articles, you can use this formula instead:

=IF(B288=0,"",E288+(E288*(0.01*F288)))

H - Monthly Earnings

The monthly earnings is based on your monthly page views (column G) and a rate that depends on your Clout level.

Since my current Clout level is 8, the rate is 1.60. You can see the rates in the Clout calculations guide as detailed earlier in this article.

The formula for the monthly earnings is:

=IF(B288=0,"",D288*0.0016)

This figure should agree with the value shown on your Dashboard to the penny. The nice thing is that on those occasions when Associated Content has a problem and fails to update this, your spreadsheet will show it accurately.

I - Average Page Views

This is for me a "nice to know" piece of information, and it shows the average daily page views that I have had for the month.

Using this, I can try to set myself goals, such as beating the average page views for the previous month.

Obviously your page views depend on the number of visitors and how successful your articles have been, but if you feel that your daily average is slipping, this is a good indication that you need to get writing. Nothing like a good incentive to write more is there!

=IF(B288=0,"",SUM(INDIRECT(ADDRESS(ROW()-DAY(A288)+1,2,4)):B288)/DAY(A288))

This took me a while to figure out and involved a lot of Googling, but it basically takes the sum of the page views month to date and divides by the number of days that have passed in the month.

If extracts the Days portion of the date in Column A to determine how many days to divide by.

It's rather tricky, since you can't just divide by the value of the accumulated page views, since that is a formula. It actually works by accumulating the values that you entered in Column B throughout the month instead.

It is kind of nice though.

J - Clout Percentage

This is really nice to know as well, since it tells you how close you are to achieving the next Clout level, and how far you progressed since the previous day, or so far this month.

The formula does require you to manually alter it as you go through the Clout levels though. I did not deem it worthwhile to add the complication of determining what Clout you were at and then figuring this out.

The formula is:

=IF(B288=0,"",((G288-100000)/400000)*100)

Where G288 is our calculated Clout page views.

I am currently on Clout 8, which starts at 100,001 page views and goes up to 500,000 page views.

To determine how far into the Clout I have gone, I need to deduct the starting point for the Clout, which is 100,000, and then divide by the number of page views in the Clout (500,000 - 100,000 = 400,000).

Makes sense I hope!

To give you another example, if I was on Clout 6, which begins at 20,001 and ends at 50,000, the formula would be:

=IF(B288=0,"",((G288-20000)/30000)*100)

In this case we have to deduct 20,000 (the starting point) from the page views, and divide the page views that we have accumulated into this Clout by 30,000, which is 100% of the page views for this Clout.

Other Calculations You Can Create

I do also have 2 other tabs on my spreadsheet that perform other calculations, but it would be too complicated to go into these here.

One calculation takes my Clout Page views and the daily average for the month as of today, and determines how many days it will take me to reach the next Clout level.

Again this is a "nice to have" and seeing the date change up or down on a daily basis gives you incentive to try and write more, and to promote your articles better.

I also have a graph that shows my daily average page views month by month, so I can see in a picture whether this is going up or down and how well I am doing.

This is how the data looked as of yesterday:

12-May-10 I started Clout 8
20,848 Page Views To Go
798 Current Daily Average
26.13 Days To Go
23-Nov-10 I reach Clout 9

I hope that this has given you some ideas, and if you don't have Excel, you can download a free version of Open Office, which works mostly the same as Excel, at least for the formulas that I have shown here.