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

 

How to use Data Explorer for Excel to extract data from Statistics Denmark

StatBank Denmark contains detailed statistical information on the Danish society, and they are exposing a lot of interesting statistics about Denmark.

Unfortunately they do not expose these data in oData feeds, public web services but only via an interface where you manually export the tables in excel, csv, html or similar formats. But then I noticed the other day that you have the possibility to access the your saved tables as XML and as Microsoft “Data Explorer” Preview for Excel handles this perfectly – I thought that I would give it and try so let me take you through the process.

Step 1 – Log on to Stat bank Denmark

To select the data you need to logon to StatBank Data – http://www.statbank.dk/statbank5a/default.asp?w=1920 and you should create a use account so you can extract large tables and most important – you can save your queries and that is important.

Now you have access to all kind of interesting things about Denmark.

Step 2 – Create a query

Let try and analyze the “Live births by age of mother and sex of child”.

I choose all the elements in the different available filters and click Show table. The result is this table

And if I choose to export this to CSV or Excel I get a table that is not very useful for further analysis.

Step 3 Save the query

At the bottom of the page you can select to save your table for future use and also specify whether the timeperiods should rolling, fixed or only latest updated periods.

Give the report a name and click “Add as new saved table”.

Then you will get a id for the table in the next picture and this ID is important for that will be used to generate the xml file later on.

You can also find the ID in the link info at the status bar.

Step 4 – Test the XML feed

Now can get the XML via calling

http://www.statistikbanken.dk/xml/167000

Step 5 Get the data into Excel

So choose Import XML from the Data Explorer tab and enter the http://www.statistikbanken.dk/xml/167000

This takes 10 – 15 seconds for the file to be loaded and data explorer to appear.

Then you can click the Data – in the navigator and see the table data.

Now we can format and rename the columns.

So final result

Click Done and the data will be returned to Excel

Step 6 – Load the data to Power Pivot

In Query Setting Task pane – Select not to Load to worksheet and click load to data model

And now I can refresh data directly from PowerPivot

Step 7 Analyze the data

And now that you have the data in Power Pivot you can start to analyze and visualize the data using Power View or just the standard Excel charting tool

Or

Step 8 – More data

And now you can import other relevant data from Statbank to see what might have an impact on the birth rates.

You try

You can find the example file here – and the cool thing is that you can use the same query that I have created in my user profile on Statbank.

Links

Download Data Explorer

Stat bank Denmark

The xml data