Yammer Analytics with Excel and Power BI
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.
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.
- Login to Yammer as a network administrator (only available to network admins)
- Navigate to the Network Admin portal within Yammer
- Select “Export Data” from the “Content and security” section of the side navigation
- Select a start date for the export (read: all additional filter must be completed after the export)
- 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:
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
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:
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:
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).
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.
|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.