Share via

Yammer Analytics with Excel and Power BI

  • Article

Congratulations, your organization has rolled out Yammer, the best darn enterprise social platform on the planet! You probably already have some great adoption momentum, exciting new communities of knowledge, and employees/customers collaborating across organization boundaries like never before. But now it’s time to start analyzing the information contained within Yammer, identify key trends/insights, and use those trends/insights to become a more responsive organization. You might even have your boss (or their boss) on your back to start measuring ROI from the Yammer investment. Where to start…

Sure, Yammer provides high-level metrics, exports, and APIs that together, contains most of the raw data you would use to perform social mining on the enterprise. However, exports and API make most Yammer Administrators feel like the information is still locked far inside Yammer. They need simple and flexible reporting tools that are familiar and easy to use. Fortunately, the Microsoft BI stack with Microsoft Excel and Power BI are here to the rescue!

In this post, I will outline the step to take standard data exports from Yammer and convert them into detailed reporting models with rich data visualizations. Other than a few data enhancement utilities (that I'll provide for free), we'll achieve everything using Microsoft Excel and Power BI. The steps outlined in this post are also illustrated in the video below and in a related session I delivered at the 2014 SharePoint Conference titled Yammer mining - dig in and "listen" to what your big *social* data is saying.

[View:https://www.youtube.com/watch?v=BcKM7vIRwO8]

Collecting Raw Social Data

We will use a combination of Yammer data exports and APIs to collect the data for our reporting model. Yammer Network Administrators can collect data exports from Yammer’s Network Admin portal.  The data export interface only has a few parameters such as the export start date and checkbox options for attachments and external networks. Anything more granular will need to be achieved through post-export filtering.

  1. Login to Yammer as a network administrator (only available to network admins)
  2. Navigate to the Network Admin portal within Yammer
  3. Select “Export Data” from the “Content and security” section of the side navigation
  4. Select a start date for the export (read: all additional filter must be completed after the export)
  5. Optionally include attachment and external networks

What You Get

Yammer exports include most of the essential data elements needed to build the baseline reporting model and will serve as the basis for collecting additional data attributes.  Below is a comprehensive list of elements (aka - “dimensions”) included in the export and a diagram of how they relate to each other:

  • Admins
  • Files
  • Groups
  • Messages
  • Networks
  • Pages
  • Topics
  • Users

In the relationship diagram below, notice that Files and Topics do not have a direct relationship with Messages. These dimensions ARE related, but data returned from Yammer does not support the creation of relationships without additional data manipulation. Don’t worry, we’ll investigate data manipulation shortly.

For this post, we will concentrate on building a reporting model with Messages, Users, and Groups. The other dimensions are interesting, but Messages, Users, and Groups are likely the most valuable to start with. The methodology applied to these can be replicated to incorporate the other dimensions for a more comprehensive social reporting model.

What is Missing

Although Messages, Users, and Groups encompass the primary dimensions in our reporting model, some dimensions and attributes aren’t provided in the data exports. I’ve listed some of the major gaps below, but I’m sure you will find others.

  • Detailed Date Dimension – although most of the exports have date/time attributes, date/time values can be challenging to query against. Providing a formal date dimension is much more user friendly.  For example, “Show me Message Counts by Group between 1/1/2014 and 1/31/2014” can be simplified with a date dimension to “Show me Message Counts by Group in January”
  • Mentions – User and Topic mentions are embedded in the body of the exported messages (ex: “I am preparing for my [Tag:3422:SPC14] with my co-presenter [User:773833:nmiller]”). This makes mentions impossible to effectively query. Beside the challenge of being hidden in a unstructured message body, Mentions actually have a 1:many relationship with messages, meaning a single message can (and often does) have numerous mentions. Two support this relationship, mentions should be broken out as separate dimension(s)
  • Following – the data exports do not contain any details on who follows who, who follows what group, or even general follower/following counts for a user
  • Likes/Shares – the data exports do not contain any information on the number of likes/shares a message has or who performed the like/share
  • Message Sentiment – one of the hot trends in social mining is to perform sentiment analysis on social activity. Rolled up sentiment scores can provide a high-level monitor of positive/negative activity in a social network. This is general a “nice to have” and definitely not included in the standard Yammer data exports
  • Time to Reply – although the messages in the data export are easily grouped by thread, it isn’t easy to calculate the time between messages in a thread. This information can be helpful in comparing response time to traditional email communications or measuring community responsiveness
  • Detailed User Demographics – the data export for Users provides some very basic user demographics (job_title, location, and department). However, I have found the data quality of these attributes to be extremely poor in every network export I’ve worked with. It seems that only a small population of users (10-20%) bother to populate these fields in their profiles. This might improve once we have a more unified user profile between Yammer, SharePoint, and Active Directory. However, an HRIS system tends to be a more definitive source for user demographics information in an organization. It might make sense to work with Human Resources to get an acceptable export of demographics. Location/Geography is particularly useful as the Microsoft BI tools have some fabulous location-based data visual we can apply to it

Enhanced Exports

When I first set out to document my approach to Yammer analytics, I began to write detailed steps for filling in the gaps outlined in “What is Missing”. This involved calling Yammer REST APIs, using Office Apps, and complex Excel formulas. Ultimately, I felt like the effort was getting overly complex for the average Yammer Administrator to accomplish. Instead, I decided to build an export utility hosted in Windows Azure to perform all the export and augmentation for you. For those that are interested in the details of this utility (ex: for the purpose of adding additional enhancements), I have provide provided the entire Visual Studio solution HERE for download.

NOTICE: The Yammer Export Utility is a free tool offered warrantee-free and without support. In fact, it uses some undocumented Yammer APIs, which are not supported and could change without notice. The utility will perform a full Yammer export from the dates specified in the wizard. Although this may contain private messages and messages in private groups, the utility will completely ignore these records if you chose to exclude them. The Yammer Export Utility will not use your data for any reason other than to provide an enhanced export. The Yammer Export Utility will not provide your data to any 3rd party with the exception of an optional sentiment analysis service. Please be aware that Yammer imposes rate limits on API calls (“speed limits” on the information superhighway). These limits can slow export completion to hours or even days depending on the volume of export activity and users to process.

 

The Yammer Export Processor is available at https://yammer.azurewebsites.net. It provides a wizard that will allow a Yammer Network Administrator to configure and perform an enhanced export from a Yammer network (user MUST be an administrator of the network they select for export). The first step will ask you to log into Yammer:

Next, you will be asked to agree to the terms and conditions, which validates that you understand the terms of use outlined both in the utility and above:

After accepting the Terms and Conditions, you must select a network to perform the export on. You MUST be a verified administrator on the Yammer network you select in order to perform the export:

Next, the wizard will ask you to specify a start date for the export. The utility will export everything from this date forward. Be cautious in trying to export too much content at once…longer timeframes can significantly increase processing time:

After specifying an export timeframe, you can customize the enhancement activities performed on the export, including likes, shares, mentions, follows, and more:

If you selected “Process message sentiment” on the export options screen, you will prompted to provide an API from Mashape.com, which hosts the sentiment analysis engine for the export processor:

Finally, the Yammer Export Processor will display a summary screen to review before starting the export. This is your last chance to review the details before processing:

Once you start the export, it could take a few minutes to show progress. Complete processing time will vary greatly based on the export timeframe and the volume of content in the network (including users). Keep in mind that large exports could take days to complete. Bookmark the URL and check back later to get a status of the export:

Once the export completes, it will have a link at the top to download the enhanced export files:

If you want to use the pre-built Excel model (explained later in the post), you MUST copy all the export files to C:\Exports. The data connections in the provided Excel model are configured to this specific location:

Modeling Raw Social Data

Great, we have a bunch of raw data…now what? Excel has all the tools we need to import the raw social data, model it with relationships, and build rich/insightful visuals. Rather than building an Excel model from scratch, I’ve provided a pre-built model that is engineered to easily refresh against the output of the Yammer Export Processor. The important pre-requisite is that you have Excel 2013 with Power Pivot enabled and you have copied the data export files to C:\Exports on your local computer.

Download the YammerPowerBI.xlsx workbook to your local machine and open it in Excel 2013. Click on the POWERPIVOT tab in the ribbon (Power Pivot tab is missing? Enable it) and click the Manage button to launch the Power Pivot window:

Next, find the refresh button in the ribbon and click on the down arrow to select Refresh All.

This will launch the Data Refresh dialog, which will refresh the workbook with the data from the Yammer Export Processor that was copied to C:\Exports:

The data refresh could take time to complete depending on the volume of content in the exports. For very large exports, it is recommended you leverage the 64-bit version of Office 2013. This will allow Excel to leverage more local resources to work with the big data in memory. Once the data refresh is complete, you can close the Power Pivot window and experiment with some of the pre-built Power View dashboard (or build your own visuals).

Data Visualizations

The provided YammerPowerBI.xlsx workbook already contain a number of pre-built Power View dashboards. Power View is just one of many visualizations available in Excel and SharePoint. Here is a more comprehensive listing and examples:

Power View – Power View delivers highly interactive dashboards leveraging a number of unique visuals that are automatically connected to each other. Power View dashboards live within the Excel workbook and can be uploaded to SharePoint for online viewing:

Power Maps – if you have (or can get) accurate location information for users, Power Maps provides the premier location-based reporting, with rich visualization layers and time-based animations. Below is video recording of a Power Map report showing Message Count and Sentiment by Location over Time:

[View:https://www.youtube.com/watch?v=5S99w6UCkc0]

Power BI for SharePoint Online – SharePoint Online users can license the Power BI app for SharePoint. This provides a number of online BI services, including Q&A, a semantic BI search tool. With Q&A, users can simply ask questions in a search box and Power BI will display the appropriate visualization (ex: “Show me thread count by group for 2013”):

Excel Pivot Tables/Charts – Excel has traditionally provided interactive Pivot Tables/Charts, and Excel 2013 enhances that experience with additional chart visuals and enhanced slicers/filters:

Conclusion

I hope this post and the tools I’ve provided help you realize the social insights you are looking for with Yammer. If you want to better understand how to build some of these exports/models from scratch, I highly encourage you to watch my session at the SharePoint Conference.

Download the pre-built YammerPowerBI.xlsx workbook

Download the code for the Yammer Export Processor as a Visual Studio solution

NOTICE: The Yammer Export Processor has been tested with the best resources I have available to me. That said, I'm not an admin of any large networks so testing on large networks isn't as well tested as I'd like. Please reach out to me at richdizz at outlook dot com if you run into any issues running the utility and I'll do my best to debug.

Comments

  • Anonymous
    April 11, 2014
    Thanks for the post, can't wait to try this!

  • Anonymous
    April 14, 2014
    This is really great .. But is this restricted only to the network Administrators ? If I as a normal user wants to perform analysis on the groups where I am part of , can't the export tool be tweaked to get the data ?

  • Anonymous
    April 14, 2014
    The comment has been removed

  • Anonymous
    April 16, 2014
    Absolutely and totally completely brilliant. BRILLIANT! Thank you so much!

  • Anonymous
    May 07, 2014
    How to we upgrade the app now that something ins Yammer has changed.  We are trialling it in house.

  • Anonymous
    May 07, 2014
    The comment has been removed

  • Anonymous
    May 07, 2014
    The comment has been removed

  • Anonymous
    May 08, 2014
    The comment has been removed

  • Anonymous
    May 21, 2014
    The comment has been removed

  • Anonymous
    May 21, 2014
    Hey Fredrik - make sure none of the .csv export files are empty. I've seen this happen a few times and can cause refresh errors. You need all of the files to at least have headers. Let me know what you find.

  • Anonymous
    May 21, 2014
    The comment has been removed

  • Anonymous
    May 21, 2014
    The comment has been removed

  • Anonymous
    May 26, 2014
    The comment has been removed

  • Anonymous
    June 05, 2014
    The comment has been removed

  • Anonymous
    June 11, 2014
    The comment has been removed

  • Anonymous
    June 12, 2014
    The comment has been removed

  • Anonymous
    June 26, 2014
    The comment has been removed

  • Anonymous
    July 06, 2014
    The comment has been removed

  • Anonymous
    July 08, 2014
    All - there was an update to the Yammer API that was causing issues with the exporter...should be fixed now.

  • Anonymous
    July 15, 2014
    The comment has been removed

  • Anonymous
    July 20, 2014
    The comment has been removed

  • Anonymous
    July 30, 2014
    The comment has been removed

  • Anonymous
    July 31, 2014
    As per above, I'm getting some blank CSVs; namely Likes and Shares.

  • Anonymous
    August 05, 2014
    The comment has been removed

  • Anonymous
    August 27, 2014
    The comment has been removed

  • Anonymous
    September 01, 2014
    great post! I will try this asap. Did the export bugs/empty files get fixed? or is this by design (i.e. can't be exported anymore from Yammer)? Thanks!

  • Anonymous
    September 29, 2014
    Hi, was wondering these files are generated and stored somewhere. What happens to these files and how are they protected? Thanks Would love to start using this tool, but worried about the data at rest. thanks

  • Anonymous
    September 29, 2014
    The comment has been removed

  • Anonymous
    October 09, 2014
    The comment has been removed

  • Anonymous
    October 14, 2014
    The comment has been removed

  • Anonymous
    October 30, 2014
    Thanks for the post :) very nice and useful

  • Anonymous
    December 08, 2014
    The comment has been removed

  • Anonymous
    December 29, 2014
    The comment has been removed

  • Anonymous
    January 06, 2015
    The comment has been removed

  • Anonymous
    January 09, 2015
    I was very excited to read about this on YCN, and headed over to try it after reading fully. It asks me to log in, but does not give me a Yammer group to select. If I continue through the setup process, it throws the following. Man, I hope this is just temporary because I'd love to get some info of our first quarter of company wide use, quickly. --- error msg shown ---/ Server Error in '/' Application. Runtime Error Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed remotely (for security reasons). It could, however, be viewed by browsers running on the local server machine. Details: To enable the details of this specific error message to be viewable on remote machines, please create a <customErrors> tag within a "web.config" configuration file located in the root directory of the current web application. This <customErrors> tag should then have its "mode" attribute set to "Off". <!-- Web.Config Configuration File --> <configuration>    <system.web>        <customErrors mode="Off"/>    </system.web> </configuration> Notes: The current error page you are seeing can be replaced by a custom error page by modifying the "defaultRedirect" attribute of the application's <customErrors> configuration tag to point to a custom error page URL. <!-- Web.Config Configuration File --> <configuration>    <system.web>        <customErrors mode="RemoteOnly" defaultRedirect="mycustompage.htm"/>    </system.web> </configuration>

  • Anonymous
    January 13, 2015
    The comment has been removed

  • Anonymous
    January 17, 2015
    The comment has been removed

  • Anonymous
    January 26, 2015
    The comment has been removed

  • Anonymous
    February 02, 2015
    Hi Ricard. Joining the choir. Can you please update the source code? Best Jacob

  • Anonymous
    February 08, 2015
    The comment has been removed

  • Anonymous
    February 09, 2015
    The comment has been removed

  • Anonymous
    February 23, 2015
    The comment has been removed

  • Anonymous
    February 23, 2015
    All - I'm really taken back by the popularity of Yammer Analytics. I tried to make it clear that this tool was offered without warrantee. I'm working on several new tools that deliver even better analytics for server network administrators AND normal group owners. These will be made available in several configurations that do not run on my services. Look for something very soon and thanks for all the wonderful interest!

  • Anonymous
    February 23, 2015
    The comment has been removed

  • Anonymous
    March 23, 2015
    I am having problems getting going with this. My Yammer Network is accessed via single sign on and I keep getting this error 'error getting user tokens'. Is there a way around this?

  • Anonymous
    March 31, 2015
    The comment has been removed

  • Anonymous
    April 29, 2015
    I too cannot connect to my network after authenticating and selecting the network.  Suggestions?

  • Anonymous
    May 11, 2015
    The comment has been removed

  • Anonymous
    May 19, 2015
    Hi, Very interesting article ! Too bad, I encounter the same issue as Gary Ide and Piyush Jain, despite I am Network Admin on my network. Can't wait for it to work !

  • Anonymous
    May 26, 2015
    hi, has this site been removed? I can't access it, i get a general microsoft azure notice maireaddeblacajewellery.com

  • Anonymous
    June 02, 2015
    yammer.azurewebsites.net is not working anymore. any idea?

  • Anonymous
    June 03, 2015
    yammer.azurewebsites.net is not working. Why ? Can you help me ?

  • Anonymous
    June 10, 2015
    After deploying website getting error and Accessing website I'm getting an error message popup "This page or app is using an unsupported version of the yammer platform. Contact your site admin to ask them to update the page". Try to update the API but same error... any idea?

  • Anonymous
    September 02, 2015
    Abour the unsopported version, you must update the client script the current script is <script type="text/javascript" data-app-id="YOUR-APP-CLIENT-ID" src="c64.assets-yammer.com/.../script> Thanks

  • Anonymous
    September 09, 2015
    Superb post, thanks so much. Would you be able to share an example Excel file with dummy Yammer data? I'm particularly interested in what can be analysed on a per group basis. Thanks.

  • Anonymous
    September 17, 2015
    The comment has been removed

  • Anonymous
    October 26, 2015
    Hi I am unable to access the source code from the link provided. Is it possible for you to host this on  github/codeplex ?

  • Anonymous
    January 05, 2016
    when I follow the link provided to the yammer export processor I get the follow page "This web app has been successfully created There's nothing here yet, but Microsoft Azure makes it simple to publish content with GIT, FTP or your favorite development tool such as Visual Studio, Visual Studio Online or WebMatrix Tell me more" Has the yammer export processor been removed? When I run an export via Yammer's native export feature I am missing a few of the files that are shown in the above screen shot of c:Exports Has anyone else experienced this?

  • Anonymous
    January 20, 2016
    Is the export data feature available for free network ?

  • Anonymous
    January 20, 2016
    Is Data Export Feature available for free network ?