Using Twitter analytics data in Power BI – Part 1

I have been using Twitter for over 10 years but I never paid a lot of attention on engagements or impressions statistics but after listening to the Microsoft flow with Jon Levesque podcast  from @nz365guy I decided to take a look at what are drivers for more impressions or engagements on my Twitter account. So I decided to create some Power BI reports based on Twitter activity exports.

I only used Power BI in proof of concepts up until now so this was a good opportunity my Power BI skills which got a little bit rusty after not using it for more than a year. To get started I first exported my tweet activity report in CSV format from Twitter Analytics  (I did it manually but there is a REST API available as well). Next I combined the different CSV files while loading it into Power BI (I followed these instructions - How to load data from a folder in Power BI). After the usual data cleansing (remove unused columns, rename columns, setting appropriate date types) and data transformation I started extending the data model. Since I also wanted to know whether there is a difference in engagements/impressions based on the day of the week the tweets was sent, I created a custom date dimension. Power BI creates a default date dimension as well but I decided not to use this – see Power BI Date Dimension: Default or Custom? Is it confusing? for more info.

I also wanted to remove the urls/hyperlinks from my tweet text before building up a word cloud with the most common terms. Luckily Power Query supports some interesting transformation, you can temporarily transform a text into a list using Text.Split(text, “”), perform operations on each word and then reassemble it again using Text.Combine(list, “ ”)  (Trick found on Multiple replacements or translations in Power BI and Power Query)

I used a similar trick to found out the number of hashtags used in a specific tweet.

The Power BI report is still a work in progress but if you already want to have a temporary copy - DM me on Twitter

References:

• Create a Date Dimension in Power BI in 4 steps – Step1: Calendar columns
• Power BI Date Dimension: Default or Custom? Is it confusing?
• How to load data from a folder in Power BI
• Visual awesomeness unlocked – the word cloud
• Count the number of words in a text – Magic Mondays with Power Query and Power BI
• Multiple replacements or translations in Power BI and Power Query