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

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.

PASS Business Analytics Conference Keynote Day #1

Reblogged from Paras Doshi - Blog:

• 
• 

In this post, I'll summarize the PASS Business Analytics Conference's Keynote Day #1:

The structure of the Keynote:

One of the NEW challenges that Data Pros face today is complexity involved in building a BI solution. Following slides nicely represent the challenge from the Tools standpoint:

Image Courtesy: https://twitter.com/SQLGal/status/322342662013321216

Microsoft’s Goal is to SIMPLIFY the above situation

We need to do the same thing with BI that PowerPoint did to the slide projector.

Read more… 193 more words

Really interesting perspective on BI with the powerpoint example- yes, it ultimately boils down to simplifying the knowledge. Thank you for introducing two new tools to your data enthusiast readers like me- Data Explorer and Geo Flow. Looking forward to learn more through your series of blogs on PASS Business Analytics Conf. '13 this week!

When Google comes to rescue!

This morning, I was working on a web analytics report  and badly needed Power view to help me deal with the data (mainly to visualize it!). Just when I opened the super powerful excel ’13 I realized my Office ’13 license has expired; I started thinking what shall I do?

Here is what helped me:

I exported the Google Analytics data as Google Spreadsheet and inserted a chart for the required data columns

Voila! Mission accomplished!

*Update*

DIY: Analytics Driven Social Media (Facebook)

With this blog, we will try to see how you can drive your social media strategies based on your own analysis. The whole blog is divided into two parts: Data Preparation and Data Analysis.

Data Preparation:

1. Go to Facebook insights
2. Download Post level data for last six months
3. Get rid of unnecessary columns
   1. If you own a local business, you can delete columns like Country and Language
   2. If you are looking into what’s working rather than what’s not, you can delete columns like Lifetime Negative Feedback
   3. If you haven’t run any ads, you can delete all columns related to paid promotions
   4. You can also choose to delete Permalink column
4. After removing unnecessary columns, add self configured columns that you’ll need for analysis
   1. If you’d want to know which day of the week the post was published on- add a new column with formula- =TEXT(POSTED_CELL,”dddd”)
   2. Round up the time using – =ROUND(E3/(1/24),0)*(1/24)
   3. Separate Date and Time using- Data > TextToColumns > Fixed Width > insert a column break between the two
      1. You may need to format cells and select Date and Time respectively.
      2. Copy all these new columns (Day, Date, Time) and select paste special as values to free it from reference (of Column Posted).
      3. You can delete the Posted column now.
5. If you wish to drive your social media through insights, make sure you have a theme, topic and purpose well defined for each post. (and add columns for the same)
   1. For example, you can have few themes like Contest, Donation or Discussion
   2. Purposes like Engagement, Branding, Product selling or Survey can be defined
   3. Similarly, you can also have topics of posts
6. You can also add Character column if you want to see what length of the posts works best for your audience using- =Len(Post Message_Cell)
7. Add one final and important column Engagement Rate  by calculating it using- =(Lifetime Post Total Impressions/ Lifetime Engaged Users)

Once you have your data prepared, you can start the analysis!

Data Analysis:

1. Go to Insert>Pivot Table
2. Select columns you want to do analysis for
3. Figure out what metrics is most useful to you thinking what is that you want to get out of data
   1. For example, I would want to increase Engagement Rate for my brand. For that I will look into (select/drag-drop) columns- Theme (theme that gets me good engagement rate), Day (best day for specific posts), Type (what works better- photos, links, videos, questions) and of course the Engagement Rate column. 
4. Here’s what you’ll see after getting your selected data columns on PivotChart
5. Play around with the dataset and filters, select columns you’d like to analyze with a view to increase the engagement rate  for your Facebook Page.

You can also target some other KPI than Engagement Rate, sometimes you’d like to see how did your paid posts do- in that case, you will have to focus on columns related to paid posts.

This blog is based on Courtney Livingston’s presentation Social Content Analysis.

Web Analytics basics: Data Collection Mechanisms

Data collection mechanisms for web analytics simplified with an InfoGraphic.

 

Interesting Read:
The Great Web Data Capture Debate: Web Logs or JavaScript Tags?

 

Six Social Media Analytics Technologies You Should Know About

With this blog, we will discuss a few important technologies that’s being used to analyse Social Data. If we generalize the tools, they can be categorized in two parts: Open source and the ones that’s commercially available.

Following is a brief on few of the important technologies in the Social Media Analytics domain:

Social Media Analytics by SAS

So, the first thing we would be introducing is the dashboard of the SAS SMA and then we will explore its capabilities.

The dashboard consists of following rich social media analysis modules:

• Overall Sentiment
• Social Sites by Volume
• Twitter Hashtag volume

Let’s discuss each of these modules and how it helps Businesses.

Overall Sentiment:

The ‘overall sentiment’ feature in SAS here will offer the analyst a real time analysis of sentiment for that business- based on the interactions happening on their social media identity (Facebook Page, Twitter, YouTube channel etc.)

Technology:

A simple way to do sentiment analysis is to have a lexicon of positive and negative terms; and then differentiate statements (for social media- Facebook comments, tweets, YouTube comments) based on the terms they consist of and classify them as positive or negative statement.

Social sites by volume:

This feature of SAS SMA offers your business an insight about the traffic on all your social media identities.

Technology:

Once you decide to manage your social media using SAS SMA tool, the tool asks you to register your social media identities for analysis purposes. As soon as SAS SMA gets your social identities in its system, it starts tracking the traffic on your social media platforms.

Twitter Hashtag Volume:

It also includes a feature that shows the real time volume of the hash tag initiated by the businesses social media campaigns.

Technology:

SAS SMA basically does text mining based on the term used as your hashtag. And based on all the public tweets with that hashtag, SMA will analyze and display the volume of that hashtag.

Along with these 3 features, following are the key entities of the SAS SMA tool:

Competitors:

It is very important for your business to know your competitors. Sometimes, it becomes really critical when you face intense competition from your rival companies on the social media.

In such cases, SAS SMA offers an incredible solution. Along with detailed analysis on your social campaigns, it also provides you with all the information about your rivals activities on social media.

Influential Authors:

“Managing social media campaigns is all about influencing the friends of your fans.”

It becomes essential to know what part of your fans have the most influential social identity on sites like Facebook and Twitter. SAS SMA identifies such active users based on its analysis. It basically finds people who tweet a lot about your business/brand/product and people who react the most to your Facebook posts. Depending on the SAS SMA’s results with this feature, you can decide and take actions having this special cluster of people’s information in mind.

Phrase Clouds:

Phrase Cloud is a simple way of visualizing your social data. It, as a result, provides you with the terms/words/phrases being used on/for your social identity. It clusters and divides the terms/words/phrases based on the topic and the size of the terms is based on the volume of those terms being used.

Real time tweets- sentiment and velocity of live events:

SAS SMA gives you a real time graph of the tweets/posts displaying the sentiment and velocity of live events.

A classic example where this tool is used is seen during the election hours or major sports seasons when your fans/followers are continuously tweeting/posting about that particular live event.

–

At this point, let us also take a look at a unique social site within the stock market domain.

www.stocktwits.com – It can be best described as the twitter for Wall Street. It actually replaces Twitter when users want to talk about stocks and the role of different companies in the stock market.

–

R

By definition, R is an open source programming language and software environment for statistical computing and graphics. R language is widely used among statisticians and data miners for developing statistical software and data analysis.

We will now discuss how R is used for social analytics.

With R programming language, programmers can develop different packages for all different applications. For an application in which user wants to analyze text or to be precise, analyze tweets, there are certain packages available in R that makes it possible.

One of the widely used package of R is ‘TwitteR’. It provides an interface to the Twitter web API. Once the TwitteR package fetches all the required public tweets from Twitter, packages like ‘RCurl’ and ‘rjson’ makes it possible for us to do text analysis. We can also remove stop words using R programming language.

There is also a package available called ‘smalltm’ which is exclusively used for text mining.

With certain packages like ‘ggplot2′ available to the user, the R programming language can also be used as a data visualization tool quite efficiently.

Below are few results of instructions I followed from this site to analyse US Presidential election using tweets:

Word Cloud Comparison

–

NodeXL

NodeXL is a free open source template for MS Excel 2007 and 2010 that makes it easy to explore network graphs.

It’s a product of an initiative taken by Social Media Research Foundation. It has become an essential tool for social media data visualization.

Using NodeXL, one can import Flickr, Twitter and Youtube data as shown in the figure below.

One can also group/cluster the nodes in the graph based on different categories. It becomes one of the key features when we want to find outliers.

There is also an option to initiate different graph metrics as shown in the above image.

Social Media Research Foundation also has an online community on which NodeXL users share interesting network graphs for other users to see. One can also download those graphs as a NodeXL file and do experiments with them.

NodeXL also consists of powerful dynamic filters for efficient analysis.

–

NetVizz and Gephi – A Powerful Combination

Ever wondered how your Facebook Friend-list would look like visually? Who is connected to whom? What are the things they ‘like’ in common?  Or if you are running a business, have you ever been thinking hard about your social media strategy?

If your answer is yes to any of this question, here is a post you would be interested in!

Gephi is tool for data visualization which creates a visual image of a user’s Facebook data from a particular perspective.

The main concern here is to create a dataset for analysis. That is where NetVizz comes in to the picture. NetVizz is a Facebook application that can be easily found by doing a search on Facebook.

What can NetVizz help us to achieve?

It can help us to get a dataset of:

1. User’s personal network

2. User’s ‘Like’ network (Page’s Like Network)

3. User’s group network

NetVizz

In the above example, “Networked Life” group (which is a group consisting people who is taking that class on Coursera) was used. The group has 365 people (read: nodes) and they all are from different background and countries. So it would be interesting play with its dataset.

Once a Facebook group (network) has been selected, Netvizz will start doing its job. The process takes a few minutes to several minutes depending on the number of people in the group (size of the network). Once Netvizz is done, download the Gephi file (illustrated in the pic).

Gephi

Using the Facebook dataset downloaded via NetVizz, Gephi can help us visualizing our Facebook network. The figure below is the visualization of one of the Facebook groups called “Social Networks Analysis”.

As you can see in the snapshot, there are multiple options and features available in Gephi which help us in visualizing the data in the best possible way. It has all the features that NodeXL carries but with better GUI and its independence from MS Excel makes it a better tool.

This visualization displays people within the Facebook group “Social Network Analysis” differentiated by their Nationality shown with different colors. As one can see it also displays who is connected with whom through edges between the nodes.

The Preview feature of the tool will show a detailed graph with edges connecting various nodes as shown in the figure below.

The following figure shows how powerful Gephi is when it comes to filtering the data. One can run different types of queries in order to visualize the required filtered network graph.

Here is a preview of the filtered network graph.

For a detailed video on how to carry out Social Network Analysis using Gephi, check out this blog:

http://vtalktech.wordpress.com/2012/09/23/facebook-analytics-netvizz-and-gephi/

–

PowerPivot – Analytics for Twitter

An excellent blog on this tool is here:

http://parasdoshi.com/2012/11/13/a-social-media-analytics-sample-dashboard-in-excel-powered-by-powerpivot/

–

Web based Social Analytics Tools

With the birth of Social Media Analytics, many web based applications came into the market offering social media solutions.

I have been able to use the trial version of one of the many available tools to us.

SocialMotus

SocialMotus is a Social Media Management and Monitoring Tool for Business. It allows users to manage your Facebook and Twitter pages/accounts. An advantage of this tool is that it allows multiple team members to access the data efficiently.

It helps discover targeted leads and track priority messages for efficient support.

The above snapshot displays the statistics of a user’s Facebook pages.

SocialMotus displays statistics on all the posts that the user has posted on Facebook/Twitter.

Twitter Insight: This reports statistics on volume and various tweets on the twitter account.

Tracking campaign:  This feature tracks the success rate & analysis of a campaign being run by the user/business.

Reports: This feature displays report on historical and current run time data.

The Publish section allows the user to publish a post/tweet on any of his Facebook/Twitter account attached to SocialMotus.

The Monitor section allows the user to monitor the inter-customer dialog.

The Engage section provides statistics on the user’s/businesses’ engagements with his fans/followers.

The Measure section contains all the features related to statistics of the accounts.

–

–

ROI and Social Media (Value of a ‘Like’)

‘Like’, ‘Share’ and ‘Follow’ are modern world’s three most powerful buttons. One cannot imagine to build a brand without these three buttons in this Social Media driven world. Yes, there are other buttons as ‘pin’ or ‘+1′, but when it comes to high-end digital marketing, ‘Like’, ‘Share’ and ‘Follow’ are still dominating the Social Media Universe.

Now with the rise of immense power gained by businesses on Social Media, Return on Investment becomes as important as Brand Management. And here is when value of all the “likes’ and ‘shares’ comes into the picture.

The idea here is to figure out whether your Social Media strategies are generating revenues for you or not! How do you do that? There are white papers and books available to explain ROI with Social Media, but I found something beyond the scope of regular articles.

It’s a formula called Value of a Like (voal),

developed by Dan Zarrella, a Social Media Scientist at HubSpot.

Below is what each term in the formula means:

L – Total number of people you are connected to. That means the number of people who ‘like’ your Facebook page or who follows on Twitter.

UpM – The term means Unlike-per-month. If you go to your page insights and export the data as an excel file (for past 1-3 months), you will be able to figure out the average number of ‘Unlike’ per day. (For twiiter, ‘Unfollow’)

Lpd – The term means Links-per-day. It is the number of posts you publish on Facebook/Twitter.

C – It is the average number of clicks on the Links that you post on Social Media. With the data exported from the Page Insight, you can get this number.

CR – Conversion Rate is the traffic you are gaining through Social Media. It is the ratio of people who follow/like you to the people who clicked on the links you posted and came to your website.

ACV – Average Conversion Value is your lead value, your average sale price from people coming through Social Media.

Here, (L/UpM) is sort of a churn rate for your Social Media, (LpD * 30) is the number of posts that you publish on your Social Media, (C/L) gives you the ratio of people who actually clicks your links out of people you have as your fans/followers.

On the other hand, CR and ACV helps you calculate the actual Return on Investment based on the above mentioned terms.

Now, just when I thought it’s AWESOME, a link was waiting to amaze me!! And it was the link that motivated me to actually write this blog!

http://valueofalike.com/

The calculator on the link follows this formula and gives you the ROI. And not only it follows the formula, but also explains it and helps you get your numbers correct!

Do share what you think about it, I would be glad to learn and explore what you have got to say and share!

Follow me on Google+

Quickguide: How to import external data into SAS

Here is the code to get external data (csv, xls, txt) into your SAS environment.

The basic objective is to get your *.csv or *.xls file converted to *.sas7bdat format (which is the default extension for SAS EnterpriseMiner)

Open SAS (not EnterpriseMiner).

After opening SAS, go to the Editor and type the following line of codes:

–

IMPORTANT NOTE: After writing each block of codes, select the written code and click the ‘run’ button.

NOTE: The terms in Bold should be colored (mostly blue).

–

#We will first create an empty data set called ‘main’.

data main;

run;

—-

#Tell SAS about the Contents in our data set ‘main’ and print it.

proc contents data=main;
run;

proc print data=main;
run;

—-

#Now Import your excel/csv file

proc import datafile=’<<Enter the path to your file>>’

out=main replace dbms=csv;
run;

-

example for procedure: proc import datafile=’C:\Downloads\MIS6324BI\Titanic Data\train.csv’

Note: ‘out=main’ because the data set we created is named as ‘main’.

Note: If the file you want to import is Excel, type- “dbms=excel“

—-

#Create a new library

libname home “<<Path of your wish to the library>>”;

-

example: libname home “C:\Users\parth\Documents\My SAS Files\”

—-

#Run the following codes in order to get the imported file into your ‘main’ data set. And then to get your newly created ‘main’ data into the above created library.

data sas_format; set home.main;
run;

data home.sas_format; set work.main;
run;

—-

The resulting SAS format files will be in the library path you have given to SAS. In this case- ‘C:\Users\parth\Documents\My SAS Files\’

Please do comment on this post if you find any problem following the steps to import data into SAS.

Social media and customer dialog management at Starbucks

Here is presentation created for one of the courses I have taken this semester: Business Intelligence under Professor Zhiqiang Zheng.

It introduces how 3-M Framework (Megaphone, Magnet and Monitor) helps businesses create Social Media Campaigns/Strategies and focuses mainly on how Starbucks does the Customer Dialog Management through Social Media.

This slideshow requires JavaScript.

Would love to have your response on this one, share what you think about the 3-M framework! :)

Google Fusion Table & Data Visualization

Few days back, one of my Facebook Page got its 100th like and I could see that the likers  are from all different countries around the world. And I thought wouldn’t it be great if I can put them all on the map?

That was when I got to know the power of Google Fusion Table for Data visualization.

So, this is what I did and I loved the outcome presented by Google!!

1. The page is about the people of sun sign Libra (created with a vision to get all the Librans on Facebook together!!) and due to the openness of the subject the page was built on- it could attract people from different parts of the world.

2. With Facebook insights, I could get the data about people and their respective nationalities.

3. Now it was Google’s turn to show its magic!

3.1 Go to Google Docs, click on ‘Create’ and in the more section go for ‘Fusion Table’

3.2 Select the spreadsheet you want to visualize on map.

> In our case, I just created a spreadsheet with two columns, one for number of people and second for their Country.

3.3 Click on new tab ‘+’ button and select ‘add map’

4. That’s it! Google will do its job and you’ll have all your data about people and their country visualized! It also works on the bases of longitude-latitude! And apart from that, there are many things you can try to make  it look cool. Also, you can share, publish and embed your visualized data!

5. Below is the image of 100 awesome Librans I mapped using this feature! :)

And here’s the link to the map: http://goo.gl/YoqTs
Have a great day! And please drop a comment if you too have played with Fusion Table, I’d be very happy to learn new things! :)