Have you heard about Yammer? It is a social network for organizations, kind of a “private Facebook”.

Let’s imagine your organization is using Yammer as an internal platform for digital collaboration, engagement, knowledge sharing etc.

It is a large international organization with employees around the globe (think big).

I have access to Yammer in one of such organizations since 2014 and in November 2014 I founded a group dedicated to “Data Analysis & Reporting” related questions.

IMHO, any large organization needs something like this. There are usually tons of company-specific questions around data & reporting: where to get data from, how to clean / transform, how to interpret etc.

With the good thoughts in my mind I started utilizing Yammer to spread the knowledge, share tips and tricks on various topics: Excel, VBA, Power BI, O365 services, SharePoint, MS Flow etc..

Why not in public place? Because of company-related technologies/data on screenshots and videos.

After a certain time I was obviously keen to know stats about my group. Does anyone read my posts?

Back in the days we used https://analytics.tryane.com for that. Great tool I have to admit!

Below are stats of my group after 1 year

It is not a secret that adoption of internal social media in any organization takes time.

That screenshot was made in Nov-15, pretty much one year since Yammer was widely introduced in the organization.

People were reluctant to use Yammer in the beginning. As of today, many are still considering it as evil, “waste of time” etc. But this is not something I want to talk about today.

You are here to know how to get data from Yammer to Power BI in the end.

Before pulling data to Power BI and reinventing the wheel, you might want to consider existing options like Tryane analytics or something similar.

For example, by default we have Yammer’s Group Insights, which it not a bad thing I should say, but too high-level.

Below is the last 12 months report for my group with 556 members at the time of writing.

Default ‘Group Insights’ feature gives a very high-level overview as you may see.

And we always want more, aren’t we? 🙂

Another option would be a solution from tyGraph for Yammer! Yes… but I couldn’t get it working. Perhaps, because I’m not a Verified Admin.

Ooooookay, but I just need the info about messages in my group and my personal messages (how many likes do I have, eh?)

Building Yammer analytics on Power BI

Fortunately, Yammer has the REST API.

https://developer.yammer.com/docs/rest-api-rate-limits

Documentation is far not perfect, but it provides an idea on where to start.

There is also a link to mass Data Export from Yammer, but again – for verified admins only.

Good news! If you are not a verified admin, you still can use GET requests to API.

Firstly, we need to connect to Yammer API. One of the easiest options would be using Microsoft Flow and export necessary data to CSV / Excel / Dataflow

However, O365 admins can limit list of allowed for MS Flow connectors.

As you may guess, for me this option doesn’t work. But you can give it a try!

Alternatively, consider making GET requests using Power Query to get data from Yammer

Explanation of methods in that documentation is not very detailed and not everything is covered.

For example, I was looking for a way of exporting all my messages.

In other words, “all messages from a user with a certain ID”.

Ended up Googling for that query.

Crikey! Seems like Queensland government uses Yammer!

Google has indexed one of their documents

Link to that doc: https://www.forgov.qld.gov.au/file/21891/download?token=5DVxAoBr

It covers following

1. export all messages from a group

2. export new messages

3. export group members

4. export all messages from a user

5. export all messages in a conversation

Querying Users of a Certain Group

Shortly, for getting users from Yammer group we can use following pattern

https://www.yammer.com/api/v1/users/in_group/[groupid].json

To get response in JSON format, or

https://www.yammer.com/api/v1/users/in_group/[groupid].xml

To get response in XML format.

For paging we need to add “page=N” as a query option

https://www.yammer.com/api/v1/users/in_group/[groupid].json?page=1

Using paging is as important as breathing, because by default Yammer returns only limited number of records.

For example,

https://www.yammer.com/api/v1/users/in_group/[groupid].json

Returns only 50 users per page.

If you are eager to test this yourself, open your group page in a browser and get Group ID from the URL.

Build a query string using pattern above and navigate to it in the same browser, just in a different tab.

That way you don’t need to enter credentials second time as you has been authorized in that browser (when opened your group page).

On the screenshot below you may see fields provided by a standard response to https://www.yammer.com/api/v1/users/in_group/[groupid].json

As it is not an OData service, we don’t receive NextLink in the response, hence cannot utilize code from TripPin Tutorial (best PowerQuery vs OData tutorial available on the web today).

The only indicator of the next page existence is “more_available” tag, which keeps “true” value until we get to the last page.

It turns to “false” once we reach the last page

I have to add here, that current version of Yammer API doesn’t allow us to define list of fields we need, so we receive a standard response (leave a comment if I’m wrong).

What could be good to know about user for the resulting analysis?

User ID – can be used to count # of unique users

Name – to display user names when we drill down to user level

Job Title – allows to roughly count number of managers reading post

Mugshot_Url – link to an employee photo.

Activated_at – allows to split users on new/old employees.

Stats – users with the large (higher than average) number of followers can be treated as “VIP-employees”, so we can count likes from them as “super-likes”.

Web_Url – could be useful if you need to provide a link to user profile from your report

Rule of thumb: “keep your data model lean – don’t import columns that you don’t plan to use”.

As it is an “intro” post, I won’t publish queries with paging here yet, it will be in the next post.

From Theory to Practice

“Enough theory, show us practicalities!” – I clearly hear from you, dear reader. Fair enough!

Querying API from Power BI is not the same as navigating to the URL in a browser because we have to provide authorization token with each individual request.

There is a separate section about authentication in the Yammer API documentation

https://developer.yammer.com/docs/api-requests

IMHO, the simplest way is to use “Authorization” query option and provide a “Bearer value”

Where can we get a token value from?

Navigate to: https://developer.yammer.com/docs/userscurrentjson

Find a “Key icon”, Log In, copy token

This is a temporary token, that will expire after a certain time, but this is the simplest option for getting started.

Alternatively, you can register an application and get developer’s token, read more here: https://developer.yammer.com/docs/test-token

Finally, we can create a query in Power BI.

Query to Yammer in Power BI

Go to New Query -> From Web, switch to Advanced Mode

Use https://www.yammer.com/api/v1/users/current.json to test your connection.

Paste URL and add Header “Authorization”.

Type and paste “Bearer token” (don’t miss a space between “Bearer” and “token value”)

Click “OK” to test your query, you should receive an info about yourself

That’s all for today.

In the next post I’ll explain how to build a query with paging through the group members and messages, and through specific user’s messages.

Stay tuned and thank you for reading!