Design Your Own Social Media Analytics Dashboard For Free With Power BI

An overview of how I got the data and the insights i garnered along the way

10May

There are many paid applications that can collate your various social media accounts into a dashboard. Some are expensive and some are inflexible so why not create your own with some #PowerBI magic and the use of inbuilt API's and few data dumps. The advantage is this application will be completely tailored to your own needs.

The below dashboard connects to Facebook, Google Analytics, Twitter & Instagram. In this article, I will explain how I got the data (so you can try it yourself) and the insights that can be garnered from the data.

Overview

This screen is meant as a snapshot for management, showing the weekly performance compared to the previous week. It also contains links on the bottom left to the top tweets and posts so you can interact with them. I looked for a long time for a chart that provided up or down arrows to give a quick view of performance but there were no standard charts that sufficed. I have to give credit to  Andrej and the team at  Zebra BI for there visual which did the job nicely.

Facebook

Insights

  • Use a population pyramid to understand your demographics. In the City of Casey, we can see that majority of our followers are women aged 25-44 so our posts reflect this to maximize engagement. Or would we like to see more male engagement and use this report to track our efforts?
  • The map shows us our reach by suburb if we are posting on an event in a suburb how many residents are we actually reaching there compared to other regions or can we promote our event on a date and then track to see if we are reaching in that area?

Get the data

Power Bi has a connector for Facebook which is limited to public facing data and this was used on the overview page, simply log in with your details, there's a good tutorial here .

To get the internal data you need to do a data dump from facebook business > page insights > export data. Save all facebook exports in the same folder and load as 'folder' in Power BI. Each week do an export and save it in the same folder and Power BI will include all files in the folder in its next refresh.

Facebook Comparison

Insights

  • This is used to see how our engagement is going in comparison to other councils. This week Knox City Council had fantastic engagement. What can we learn from there posts?

Get data

  • This data is under 'Pages to watch' under Facebook business insights. Unfortunately, it cant be extracted but a simple copy and paste combined with some excel coding does the trick.

Twitter


Insights

  • What day of the week or hour do we get the best engagement?
  • Word cloud, to help us understand what we actually tweet about.

Get the data

  • Unfortunately, the Twitter API is not built into Power BI but the free version is very limited but like Facebook we can do a data dump by going to Twitter Analytics > Tweet > Export Data

Google

Insights

  • Understand which browsers people used to visit our website
  • Understand how people come to our website
  • Understand the day and time of our sessions are

Get the data

  • Power Bi has a connector for Google Analytics, once you have logged in, the trick is understanding which fields you need to use.
  • I have used the following: day by session, hour by session, date & source by users, date & browser by users

Conclusion

There is a lot of potential to customize these charts anyway you see fit and if you have any other suggestions on how I can improve these I would love to hear them. Adding LinkedIn is on the to-do list!

Dataviz

Read next:

Why We Need Data Visualization To Understand Unstructured Data

i