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.
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.
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:
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.
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.
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.
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:
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:
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).
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:
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:
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