Tracking LinkedIn and Twitter social media shares in Power BI

How to: Retrieve Twitter & LinkedIn share totals for a list of URLs in Power BI / Power Query / M

If you have a Power BI or Power Query report that contains a list of URLs, the following M functions can be used to add the number of times the URL has been shared via Twitter and LinkedIn.

I recently built a “Blogger Dashboard” internally at Microsoft for the roughly 8,000 MSDN and TechNet bloggers. As part of this project, I learned that the social media share metrics are not stored within the blog metric database, but instead are called from the client side upon each page load. By viewing the web page source code, I found the URLs that are used to query Twitter and LinkedIn to return the share total in JSON. With this, I was able to write functions in M in Power BI to call these URLs and obtain the counts, which can then be used in the Power BI reports. Special thanks to Data Platform MVP Imke Feldmann for the wonderful assistance in helping me learn the power of M and sharing her expertise in writing the LinkedIn function to get this project started.

So let's walk through this in Power BI. One of my highest shared blog articles is “12 Days of Power BI Dashboards”, which at the time of publishing this article has been shared 200 times on LinkedIn and 23 times on Twitter. I’ll use this URL for the following examples.

In Power BI, you can click on Get Data -> Web and provide the following URL to obtain the number of times the URL has been shared on Twitter:

https://public.newsharecounts.com/count.json?url=https://blogs.msdn.microsoft.com/samlester/2016/12/09/power-bi-12-days-of-dashboards/

This returns JSON in Power BI in the following format:

Similarly, by providing the following URL, we can obtain the number of times the blog entry has been shared on LinkedIn:

https://www.linkedin.com/countserv/count/share?format=jsonp\&url=https://blogs.msdn.microsoft.com/samlester/2016/12/09/power-bi-12-days-of-dashboards/

As you can see, the format of the JSON output is different between the two calls. Because of this, I added a function for each site (GetLinkedInShares and GetTwitterShares) that calls to the specific URL and parses the JSON results to obtain the number of times the URL has been shared. Once this is completed, we can add the results to our report.

The Power BI Desktop report (.pbix file), including the M functions highlighted below, can be downloaded here:

Tracking Social Media shares in Power BI

 

Borrowing Imke's outstanding send-off statement,
Enjoy and stay queryious!

Thanks,
Sam Lester (MSFT)

Comments

• Anonymous
  April 28, 2017
  Good article, download would be easier to follow if it included your CSV file, or you included a screen shot of its structure, so we could create our own.
  • Anonymous
    April 28, 2017
    Hi Jeremy, thanks for the feedback. I will make an update to expose the path to the spreadsheet as a parameter and to include the .csv for download.Thanks,Sam