Brand New Excel Add-in: Data Explorer (And How to: Social Media Analytics)

This blog is about a new excel Add-in Microsoft introduced in PASS Business Analytics Conference 2013 this week.

The name of this powerful Add-in is Data Explorer Power Query (Download Here):

We will go through the general introduction of this add-in and then, we will see how it can be used for Social Media Analytics.

First part of the add-in is ‘Get External Data’. As the picture above depicts,  there are four sources you can fetch data from using this add-in:

a. From Web

b. From File

c. From Database

d. From Other Sources

Being a Social Analytics enthusiast, the first thing that caught my attention was nothing but ‘Import data from Facebook‘!

For next few hours, I played with ‘Data Explorer’ on Social Media front and the results were mind blowing!

Fundamentally, this add-in uses Facebook Graph API to fetch data for you and it starts with asking for your Facebook credentials.

Signing in will lead you to ‘Microsoft Data Explorer’ Facebook application; it will ask you to grant access for the application to fetch data for you.

Hit the ‘save’ button and you are all set for data exploration with this awesome add-in.

I started with fetching data from my own Facebook profile. By default, for Graph API, my username would be ‘me’. Here is a snapshot of my first query. I asked “Data Explorer” to fetch dataset containing all the movies I have ‘liked’ on Facebook.

Hitting the OK button, you will get a Facebook Graph Query window like this:

Hit the ‘Done’ button and you will get all your data loaded in an excel worksheet.

(You can fetch the same kind of data about your friends ‘likes’ using your friends’ user name or object id in the query)

Now, at this point- I thought all I can get is data from my own profile, my friends’ profile or from Facebook pages I manage.

Here is something I tried doing and the results amazed me!!

I tried to fetch the Post level data of Starbucks Facebook Page (which is not an easy task to carry out without having admin rights of Starbucks Facebook Page, in my view). This add-in made this task a piece of cake!

Hitting the OK and Done button, I confirmed the query and this is what I got:

Incredible, isn’t it?

Further, I tried to see if I can get the Fan feed.

To my surprise, the next thing I saw was this!

Now, here is something I noticed that was beyond incredible for me! The feeds I was going to load by hitting the ‘done’ button were more in numbers than what I could see on Starbucks Facebook Page. Guess what! I could see the negative feeds posted by Starbucks Fans which Starbucks chose to hide from their page!

In a few minutes, Data Explorer fetched more than 12,000 rows of Starbucks Facebook Page feed for me!

‘Data Explorer’ also provides you with a ‘Refresh’ button which fetches the latest data for the same query with just a single click!

There is also another way to do it… in the query section under ‘Table Tools’, you can hit the “refresh” button!

On the same panel, one can also merge or append multiple queries.

In other functions, there is a ‘Design’ option available under the same ‘Table Tools’ segment.

With one of the queries I tried, “Data Explorer” succeeded in fetching more than 15K records of data from Starbucks Facebook page!

So, with this blog we saw how powerful this add-in can be! One of the most difficult parts is getting the data from a Facebook Page (especially when you don’t own it), but this add-in makes Data Collection so easy that anybody can do it.

Now, what can we do with the data? A lot many things! If I want to analyse how my business is doing on Facebook, I can do it with this add in. Once I get the data, I can run sentiment analysis and see how things are going for me. At the same time, I can also do competitive analysis by getting data from my competitor brand’s Facebook page. If I have a Facebook Profile for my business (and not a Facebook Page), I would be able to get a valuable set of data about my brand’s friends (not fans) with this add-in.

One may not have the capability to fetch statistical data (Likes, Reach, Talking about) from a Facebook Page with ‘Data Explorer’, but all the interactions and post level data (Post type, Post time and Date, Comments etc.) are just a few clicks away!

Please drop a comment on what you think about this add-in or how it may help you / your organization to do Social Media Analytics. I would be more than happy to explore all possible use cases.

*Update: Tackling the ‘limit=1000’ error*

Sometimes, due to the limit imposed on the amount data you can pull from Facebook, you will get this error:

The solution is to eat the elephant one bit at a time:

After you fetch ‘feed’ from a business page, the default query would look something like this:

= Facebook.Graph(“https://graph.facebook.com/disney/feed“)

As you can see, there is no limit parameter included, and hence, Power Query is trying to pull as much data as possible (and faces the limit=1000 error).

All you have to do to tackle this error is – add ‘limit’ parameter:

Here I typed in a new parameter supported by Facebook graph API and this is how it looks:

= Facebook.Graph(“https://graph.facebook.com/disney/feed?limit=100“)

This query will fetch you 100 updates starting from the latest update published on the page.

Now, because you may want more than 100 updates, we will have to run this query again… but with one more Facebook graph API parameter: ‘until’.

Just like the ‘limit’ parameter, you can type in ‘&until=2012-10-30′ after the ‘limit’ parameter. This addition will fetch you 100 updates until 2012-10-30 (YYYY-MM-DD). Sometimes, I add ‘since’ parameter: The ‘since’ parameter fetches you updates since the date specified by you in the query.

If you want to pull ‘feed’ from specific time period: you can use both ‘until’ and ‘since’.

= Facebook.Graph(“https://graph.facebook.com/disney/feed?since=2012-10-20&until=2012-12-31“) 

If you get the ‘limit=100’ error (in case there are more than certain number of updates made during this time period), you can add limit parameter to this query:

= Facebook.Graph(“https://graph.facebook.com/disney/feed?limit=100&since=2012-10-20&until=2012-12-31“)

Note: You should be able to see two columns carrying date when you pull your data: ‘created_time’ and ‘updated_time’.

Here, it is important to understand that the time period specified in your query is in reference to ‘created_time’ (column) and not ‘updated_time’ (column).

I hope this proves to be helpful when you encounter the ‘limit’ error; please feel free to write in your comment if you face any other type of errors while pulling the data from Facebook, or if you have certain interesting use-cases to share about this Power Query feature.

Related Articles:

DIY: Analytics Driven Social Media (Facebook) [Parth Acharya – Blog]

Six Social Media Analytics Technologies You Should Know About [Parth Acharya – Blog]

Social media and customer dialog management at Starbucks [Parth Acharya – Blog]

I would end this post by thanking Paras Doshi who blogged about his first day at the PASS Business Analytics Conference 2013 in which  this add-in was mentioned.