Use Excel to track hubpages stats in real time

This hub explains how to get started using excel to analyze hubpage statistics. This hub (Part 1) will show how to set up a link between hubpages and excel which will automatically retrieve and refresh the statistics in excel.

Why would anybody want to use excel to analyze hubpage statistics you ask? (Well aside from being one of those people who have a sick urge to run everything through an excel spreadsheet) once you have the data in excel, it is very easy to then create a whole load more performance statistics tailored to your own personal requirements. For example you might be interested in taking a look at the following stats:

- Average hubviews per day

- Average hubviews by category

- Hubviews vs revenue potential

Or you might be interested in using excel to easily identify your best and worst performing hubs and to help you target your writing to the categories that are achieving the most success.

Whatever the reasons using excel to look at hubpage statistics can provide a lot of useful performance indicators and it can also be used to guestimate future earnings.

This hub will show how to retrieve the raw data from hubpages into excel. The idea here is to set up an excel worksheet which requires no manual input and will retrieve the data directly from hubpages.

The first step is to create a data link between excel and hubpages so that our hubpages statistics will update in excel in real time without having to manually input data or worry about downloading any csv files.

First click on the data menu tab in excel as per below.

Once you have clicked ( Data - From Web ) you will then see a new window open with a webpage appear (probably google). Type the hubpages address into the address box:

http://hubpages.com/my/hubs/stats

Once you have the hubpages my account webpage in the import box, click on the yellow box in the top left hand corner, once you have clicked on the box it will turn to a green tick.

Select the excel worksheet to put the data. Select tab A1. Click OK and the hubpages statistics will import into excel. The data is unformatted and is a little untidy, but don't worry about that yet.

You will now have the raw data imported from hubpages into excel with a direct link to hubpages. To refresh the data in excel click on the refresh all button. This will automatically update the statistics in real time.

We now have the raw data imported from hubpages but it looks a mess and you will notice that every time you hit refresh button, any formatting changes will undo and column widths will revert back to default. To get around this we set up a new worksheet with the same headings as the hubpages statistics, copy over the column headings from the imported data, these should be as follows:

Title-score-comments-1 day-7 days-30 days-ever-Date Published-Date Changed

Then copy the data from the import tab by linking the data directly see below.

When you import the data from hubpages the date formats screw up and need to be fixed. To do this set up 2 additional columns for the fixed date published and date changed columns. To fix the date problem we need to use the "right" formula and take the 8 right sided digits from the original date published and date changed columns.

The formula should look like this =RIGHT(cell reference of date published column,8) See screenshot below.

Once you have isolated the 8 right hand digits, copy this down the remaining rows to populate the columns.

After fixing the date problem and copying down the formulas what we should have is an identical excel version of hubpages statistics page which refreshes in real time any time we hit the "refresh all" button

What the hell is the point in doing all that you ask? - This is just the beginning, the next few hubs will show a few things we can do with this data once it is in excel. Next time I'll take a look at how to calculate average hubviews per day and some additional performance measures.