WordPress Statistics using Excel Data Explorer

In my search for accessible data sources to use in order to demonstrate the power of Data Explorer for Excel (Download here) and off course my interest in following my own blog stat.

I found out that WordPress has an API that enables me to download statistics – http://stats.wordpress.com/csv.php

The API requires you to get an API Key – and you can get yours here – http://en.support.wordpress.com/api-keys/

Okay – let me go through the steps

First we need to tell Data Explorer we need data from the web.

Enter the URL

http://stats.wordpress.com/csv.php?api_key=#YOURAPIKEY#&blog=#LINKTOYOURBLOGWITHOUTHTTP#

Read more about the possible parameters here – http://stats.wordpress.com/csv.php

This will retrieve Table.FromColumns – and you can start using Data explorer to extract and transform the data.

First Lets specify to use the First Row as Headers

Then right click the column and choose Split Column – By delimiter

Then format the columns Date, Post_id and views as Date, Number and Number

 

And then rename the columns to relevant names

 

When clicking done – the data will be loaded to your worksheet.

Now rename it by double clicking the “Query1” in the Query Settings – Action panel

 

You can choose to load it to your data model and/or load it to worksheet – Tip – Rename the Query before loading it to the data model.

And now I can analyze my different post with all the nice BI Capabilities in Excel.

 

Or a small dashboard like this

Pretty cool – now I don’t have to leave Excel to check my Blog Statistics.

PS – If you like this post and it moves to the top 20 posts – I will write a post about how I created the dashboard in Excel J