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:

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

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.

Comments

Submit a Comment

  • Theeyeballkid profile imageAUTHOR

    Theeyeballkid 

    5 years ago

    Thanks for reading Gina.

  • Gina145 profile image

    Gina145 

    5 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 imageAUTHOR

    Theeyeballkid 

    6 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.

  • jaswinder64 profile image

    jaswinder64 

    6 years ago from Toronto, Canada.

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

  • Theeyeballkid profile imageAUTHOR

    Theeyeballkid 

    6 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.

  • Marcy Goodfleisch profile image

    Marcy Goodfleisch 

    6 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!

working

This website uses cookies

As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

Show Details
Necessary
HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
LoginThis is necessary to sign in to the HubPages Service.
Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
AkismetThis is used to detect comment spam. (Privacy Policy)
HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
Features
Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
MavenThis supports the Maven widget and search functionality. (Privacy Policy)
Marketing
Google AdSenseThis is an ad network. (Privacy Policy)
Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
Index ExchangeThis is an ad network. (Privacy Policy)
SovrnThis is an ad network. (Privacy Policy)
Facebook AdsThis is an ad network. (Privacy Policy)
Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
AppNexusThis is an ad network. (Privacy Policy)
OpenxThis is an ad network. (Privacy Policy)
Rubicon ProjectThis is an ad network. (Privacy Policy)
TripleLiftThis is an ad network. (Privacy Policy)
Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
Statistics
Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)