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