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.

[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 (60)

  1. Anonymous says:

    Thanks for the post, can't wait to try this!

  2. Anonymous says:

    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 ?

  3. Rachna – this utility performs a bulk export, which is only available to network administrators. However, you could download the code a perform a recursive export for a specific group. The API for group messages is as follows (also takes a page parameter):

    /api/v1/messages/in_group/{GROUPID}.json

    -Richard

  4. Anonymous says:

    Absolutely and totally completely brilliant. BRILLIANT! Thank you so much!

  5. Anonymous says:

    How to we upgrade the app now that something ins Yammer has changed.  We are trialling it in house.

  6. Anonymous says:

    Thanks Richard for your brilliant job. I also enjoyed your session at SPC14! Accessing your website I'm getting an error message right upfront that says "This page or app is using an unsupported version of the yammer platform. Contact your site admin to ask them to update the page".

    Is that something you would have to fix or do I have to configure something within our yammer settings?

  7. Gabor – thanks for pointing out the issue. I have fixed the issue with an older yammer API reference…should work for you now.

    -Richard

  8. Anonymous says:

    Hi Richard,

    I have downloaded the code and built the app, deployed to a internal iis box, but am getting the same error as Gabor had.  Which reference do I need to fix or can you refresh the download.

  9. Anonymous says:

    Hi Richard and thanks for a great tool. I haven't got it to work though. When I try to refresh the connections I get the error "Failed to connect to the server. Reason: Unspecified error". Even though I browse to the file and point it out I still get the error. Do you know why this might happen?

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

  11. Anonymous says:

    That was quick response, I´m impressed and thankful.

    I checked all the files and none are empty. I have an other file called "Yammer Power BI (Basic) Codename Tosilog.xlsx" that i got from somewhere which doesn't have all the fancy stuff you provide and that gives me the same error.

    But at one Point I was able to double click a Connection and then it imported that file into a new tab called "users 1". I just cant understand what the problem is.

    I even tried to create a new file and setup a new Power Pivot model using the exact same files and then it imports them OK.

  12. Anonymous says:

    It throws this error in the eventlog:

    The description for Event ID 3 from source MSOLAP$LocalCube cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.

    If the event originated on another computer, the display information had to be saved with the event.

    The following information was included with the event:

    OLE DB or ODBC error: Unspecified error.

  13. Anonymous says:

    Richard – great talk at SPC. Like Chris above, I am getting that error when I run the app locally. Is there an update to the files posted here?

  14. Anonymous says:

    Hi Richard, I think this is great work. I've run this on a months worth of data and it gives me some good analtics. Now I want to try two months. The yammer.azurewebsites.net site is returning an error message 'error getting user tokens'. Do you know why this is? Thanks.

  15. Anonymous says:

    Hi Richard, you mentioned above for re:reporting for just a specific group that one could "download the code a perform a recursive export for a specific group."  Would you mind providing more detail?  I am trying to build a report for just one group.  Also, the group is private – does that impact things?  Thank you for this great post!

  16. Anonymous says:

    Hi Richard thanks for the great tutorial. My company only has Excel 2010 (PowerPivot is enabled), is there a way for the file to work?

  17. Anonymous says:

    Hi Richard.  I'm an Excel Mac 2011 user and unfortunately can't use Power Pivots or the BI tool.  Is there any way you can make a simple version based on basic excel?  I've managed to do all the extracts just fine.  It's a shame as all the work you have put in would be great to try out.

  18. Anonymous says:

    Richard – is this Azure site still active?  I get a Runtime Error – "Server Error in '/' Application" error page when I get to the export piece.  Please advise.  Thanks!

  19. All – there was an update to the Yammer API that was causing issues with the exporter…should be fixed now.

  20. Anonymous says:

    hi Richard  – has the download been updated as well? It looks like it is still generating errors

  21. Anonymous says:

    Hi Richard – We're still using Excel 2010, how can we make this work? It would be great if we can use this absolutely great tool! Thanks, Prateek Bhardwaj (www.ezeetraining.com)

  22. Anonymous says:

    hi Richard – As per what Pat says – I think the downloader still has issues – I get getting to 115% of Yammer working hard and then the zip file is available – and then its got a few blank csv files..

  23. Anonymous says:

    As per above, I'm getting some blank CSVs; namely Likes and Shares.

  24. Anonymous says:

    Hi Richard – I was trying out the extract by going to Yammer.azureweb site but I am consistently getting " error getting user tokens" and am not able to go past the "Terms and conditions" page. Any help is appreciated!

  25. Anonymous says:

    Hi Richard, the app is awesome and works perfectly, but some of the files are empty as per Dug's comments ie Likes and Shares. Unfortunately this is the data the business is most interested in

  26. Anonymous says:

    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!

  27. Anonymous says:

    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

  28. Anonymous says:

    Hi Richard,

    wow, great article & great tool!

    I tried the Yammer Export Processor once and it worked great.

    Today yammer.azurewebsites.net is no longer visible – an empty page is displayed (but there´s the page sourcecode existing in the browser), because there´s

    <body style="display: none;">

    in the page. Is this on purpose?

    Thx & pls. go on with your excellent work and tools!

    Toni

  29. Anonymous says:

    hi, please help! i'm  a v admin but i don't get as far as selecting a network, i get an error pop up box saying  'error getting user tokens', have you come across this? any tips? thanks v much

  30. Anonymous says:

    Hi Richard,

    Nice work here, i'm specificaly interested on the following indicators I saw on your chart sample : visits + visitors.

    How can you archive this with the raw data provided by Yammer's api ? or is it through some non official API ?

    I need, as so many Yammer clients, to get data about lurkers : how many visitors on the network, which have only browse the content and never like, post or comment ?

    If you have any ideas that can help my get that, I'll gladly take it !

    Thanks

  31. Anonymous says:

    Thanks for the post 🙂 very nice and useful

  32. Anonymous says:

    Hello Richard,

    absolutely very helpful, thanks alot.

    Can you please place the updated version on the onedrive?

    Thanks

  33. Anonymous says:

    Hi Richard,

    I tried it and it's very nice, the problem i have is that I am not getting all the groups/users that I know for sure that are part of my network, I suspect that Isome of them I get as blanks.

    Any idea why I get blanks?

    Also which data the export utility tool is pulling  ? it it all the network information or only groups/users that did actual activities in the system (without viewers)?

    Thanks You in advance,

    Tomer

  34. Anonymous says:

    Hi Richard,

    The solution looks amazing, however, every time I try to export it throws a runtime error on the server, without showing any details. Is there an update running on the server or so?

    Many thanks,

    NM

  35. Anonymous says:

    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>

  36. Anonymous says:

    Hi Richard,

    Since we found this post on Yammer Analytics, we used the export functionnality and Excel workbook. It's really a cool tool.

    For a few days, the website does not work properly. It seems a XMLHttpRequest is not allowed anymore (http://www.yammer.com/…/login_status.json…..) due to missing 'Access-Control-Allow-Origin' header.

    Did you ever see this error ?

    Blm

  37. Anonymous says:

    Hi Richard : Source code posted is very old while you kept azure hosting code up to date 🙂 Can you please release new source code?

  38. Anonymous says:

    Hi Richard,

    I'm joining the request above me.

    Can you please update the source code?

    Thanks,

    Jack

  39. Anonymous says:

    Hi Ricard. Joining the choir. Can you please update the source code?

    Best

    Jacob

  40. Anonymous says:

    Hi richard,

    A great blog, but at the page 'select network'. I don't see any networks. I am a verified admin, so what do you think is the problem?

    Amy

  41. Anonymous says:

    Hi Richard,

    I've tried this tool on december 2014, and it's really a good job, but as Amy, in february, at the page "Select Network", I d'on't see any network ? There is a new version ? For information, we connect with ADFS credentials, maybe it's a problem.

    Sébastien

  42. Anonymous says:

    Hi, Richard,

    First of all, thank you for this approach! It's really promising to have a deeper view into what's happening yammer-wise.

    I do, however, have an issue. I was able to successfully use the export processor in November. Now, however, it's not working for me — when prompted for the network (of which I'm an admin), I get a blank box. If it helps, I'm running Windows 7 and have tried numerous browsers (and computers!) with the same results. I've also spent a fair number of hours on the line with our IT support and they were also unable to resolve.

    Do you have any ideas?

    Any help is most appreciated. Thank you kindly!

    Best,

    ~Eric

  43. 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!

  44. Anonymous says:

    Hi Richard,

    I followed the steps, created the 'Exports' folder under C but get an error message "OLE DB or ODBC error: 'C:Exports' is not a valid path. make sure that the path name is spelled correctly and that you are connected to the server on which the file resides" any idea?

    Many thanks in advance!

  45. Anonymous says:

    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?

  46. Anonymous says:

    Hi Richard,

    when I am selecting a network for verifying the network administrator I am getting an error in validating the user as network admin. did you have any idea related to this issue.

    Your Help would be appreciable

  47. Anonymous says:

    I too cannot connect to my network after authenticating and selecting the network.  Suggestions?

  48. Rey Norbert Besmonte says:

    Hi,

    I cant seem to find the YammerUtil.cs that you used in the Yammer tutorial in this video http://www.youtube.com/watch. I really like to follow how you did it step by step but im missing the YammerUtil.cs class. Help please

    Thanks,

    Rey

  49. Anonymous says:

    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 !

  50. Anonymous says:

    hi, has this site been removed? I can't access it, i get a general microsoft azure notice

    maireaddeblacajewellery.com

  51. Anonymous says:

    yammer.azurewebsites.net is not working anymore. any idea?

  52. Anonymous says:

    yammer.azurewebsites.net is not working. Why ? Can you help me ?

  53. Anonymous says:

    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?

  54. Mauricio Nunes says:

    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

  55. Kieran Kelly says:

    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.

  56. Paul Matthews says:

    Hi Richard, I have been playing about with this for the past 2 days. Watched your SPC 2014 session "Yammer mining" on Channel 9. Finding this very interesting, I'm still at the stage where I'm writing code (using some of your examples) to get the csv files.

    The bit I don't get is the refreshing of new data. So if I perform an export from Yammer, I would do an export since a particular date. So that would give me the data between said date and today's date. Then tomorrow I would do another export since the last export. If so, do I need to merge the csv files, or do I need to do something in excel so that PowerPivot imports the new data to add to existing data?

    I'm just not understanding how once I have created the YammerBi.xlsx with data up to today, how I can continue using it with new data.

    Any links or explanations would be very helpful.

    Thanks.

  57. Siddharth Fadnis says:

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

  58. Ilanna Keyslay says:

    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?

  59. Shams El Nahar says:

    Is the export data feature available for free network ?

  60. Shams El Nahar says:

    Is Data Export Feature available for free network ?