ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Use Excel to track hubpages stats in real time

Updated on May 26, 2012

Use excel to track hubpage statistics (Part 1)

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.

Step 1: Create a data link between excel and 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.

Click on the data tab, then click on the "From Web" option.
Click on the data tab, then click on the "From Web" option.

Step 2 Import from Hubpages

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:

Step 3: select data to import

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.

Step 4: select excel worksheet

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.

Select cell A1 and click OK.
Select cell A1 and click OK.
Your hubpages data is now imported into excel
Your hubpages data is now imported into excel

Step 6: Your Imported raw data in excel

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.

Step 7: Tidy up the statistics data

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.

link the data on this worksheet to the data imported.
link the data on this worksheet to the data imported.
Copy the formulas down and add sumtotals.
Copy the formulas down and add sumtotals.

Step 8: Fix the date problem

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.

Step 9 Completion

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.


Submit a Comment

  • Marcy Goodfleisch profile image

    Marcy Goodfleisch 5 years ago from Planet Earth

    What a great tutorial! I'm imagining we can do this with keyword searches, too? I have never been great with Excel, and I need to learn its features. Thanks for this information!

  • Theeyeballkid profile image

    Theeyeballkid 5 years ago

    Thanks Marcy,

    Yes You can do i with a lot of web searches. Basically any webpage which has numbers in a table can be imported to excel to create this live link using the same method.

  • jaswinder64 profile image

    jaswinder64 5 years ago from Toronto, Canada.

    I never know that we can create this link. Very interesting and informative article.

  • Theeyeballkid profile image

    Theeyeballkid 5 years ago

    Thanks Jaswinder, the web query is a useful function.

    Since I wrote this hub theformatting of the hubpages stats page has changed slightly so some of the steps may not work exactly as desribed, but the main idea of using a web query lookup still applies.

  • Gina145 profile image

    Gina145 4 years ago from South Africa

    Well I've already learned some things about Excel that I didn't know. Off to part 2.

  • Theeyeballkid profile image

    Theeyeballkid 4 years ago

    Thanks for reading Gina.

Click to Rate This Article