Twitter Sentiment in Power BI: Who is Vancouver’s Favorite Car Share?

Voice of the Customer Series – Part 3

Who is Vancouver’s favorite car sharing company?

That’s the question I set out to answer in today’s blog post – at least according to what people are saying about them on Twitter.

In part 1 of my Voice of the Customer series, I demonstrated how you can set up an automated process to collect tweets that mention your brand using Microsoft Flow. The data could be pushed to a SQL database, Google sheet, or even a streaming Power BI dataset.

In part 2 I showed how you can calculate the sentiment score of each tweet by calling the Azure Cognitive Services API in Power BI.

This final post ties it all together in a Power BI report. Combining tweets from Car2Go, Modo, and Evo car shares, the report track tweets to each brand, and analyzes how positive or negative each tweet is. You can interact with the results below:

What did I find?

According to the tweets posted between April 28th and May 7th, Modo car share appears to be the crowd favorite. The tweets mentioning their Twitter handle had the highest average sentiment score (a measure of how positive their tweet text), during this period.

Car2Go also received the most one day Twitter mentions – unsurprising considering 1 in 6 Vancouverites are Car2Go members!

How can we use this report to foster an action-oriented data culture?

First of all, a report like this makes it easy for us to spot outliers – in this case, very positive or negative tweets. Customers are sharing their product experiences online, and we can now easily spot instances where customers have been let down, or had amazing experiences.

Secondly, Power BI allows us to set up data alerts (hint: future blog post). Imagine receiving an email or slack message when a customer tweets above or below a certain sentiment threshold. Customer service reps could follow up with them, possibly turning their negative experience into a positive one.

Thirdly, we can now track how sentiment is trending over time. Are customer service efforts and product redesigns helping improve how positively people are talking about our products online?

What about mobile?

There is also a mobile version of this Power BI report. You can view it in action below:

Interested in setting something like this up for your organization?

Give me a shout on Twitter. We can create a real-time dashboard like this, set up automated data alerts (email/Slack/you name it), and help your organization take action when your brand is being mentioned online.

Twitter is just one example, but similar processes could be set up for Facebook/Glassdoor/Instagram.

#PowerBI #SocialMediaAnalytics #AzureCognitiveServices #BI #BusinessAnalytics #BusinessIntelligence

Twitter Sentiment Analysis with Azure Cognitive Services

Voice of the Customer Series – Part 2

In part 1 of my voice of the customer series blog post, I demoed how Twitter data can automatically be saved in Google Sheets using Microsoft flow.

In this post, I’ll show how you can leverage the technology from Microsoft Azure Cognitive Services to perform sentiment analysis on the tweets. This will allow you to determine how positively or negatively a brand or topic is being mentioned on Twitter. Continuing on from part 1, the demo will again include Vancouver car sharing companies.

Why Would We Use Sentiment Analysis?

For smaller organizations, sentiment analysis may not be useful for analyzing their social media data. Perhaps you only receive a few tweets per day, and you’re very aware of your brand’s online activity.

But what if you receive hundreds, or even thousands of tweets per day? What if you have multiple social media accounts from a variety of different platforms: Instagram, LinkedIn, Glassdoor, Yelp, etc. You likely don’t have time to read each message, and it would be very difficult to try and flag posts which require extra care.

You also wouldn’t be able to consistently measure how positively or negatively your brand is trending on the internet.

Enter sentiment analysis. By assigning scores to blocks of text, this analysis allows you to measure the sentiment of each tweet/product review/social media mention.

Introducing Azure Cognitive Services: Text Analytics

To demonstrate sentiment analysis in action, I’ve entered a negative message into the Azure text analytics API. It works by using machine learning to assign a 0% (most negative) to 100% (most positive) score based on text input it receives. As expected, my text results in a very low sentiment score of 2%.

No alt text provided for this image

Integrating Azure Cognitive Services API with Power BI

Cognitive Services will be natively integrated into Power BI premium, but not every organization can afford the $5,800+/month price tag. With a little bit of extra work, you can still leverage the machine learning power of Azure Cognitive Services, for free. Using Power BI, we’ll:

  1. Load the Twitter data,
  2. Send a call to the Cognitive Services Text Analytics API, and finally
  3. Return a sentiment score for each tweet.
No alt text provided for this image

*Note: this is only proof of concept (POC) demonstration. If building this into an enterprise/production solution, you’d likely want to integrate this API into an existing social media ETL process.

Grabbing API Keys

You’ll first need to create an Azure account and setup resource groups and Azure Cognitive Services from the Azure portal. This setup is beyond the scope of this post, but you can follow along here.

Lucky for us, the free tier of Cognitive Services allows up to 5,000 transactions per month. Once you’ve set the Azure account and services up, you can grab the API keys needed through your Azure Portal:

No alt text provided for this image

Creating Function to Call API

Next, we’ll go into the Power BI Get Data/Power Query editor to create a function. This will be used to call the API.

Go to the home menu, click Get Data > Blank Query, and then click on the Advanced Editor. Here, you will enter in the below function code (taken from the Microsoft Tutorial):

No alt text provided for this image
// Returns the sentiment score of the text, from 0.0 (least favorable) to 1.0 (most favorable)
(text) => let
    apikey      = "YOUR_API_KEY_HERE",
    endpoint    = "",
    jsontext    = Text.FromBinary(Json.FromValue(Text.Start(Text.Trim(text), 5000))),
    jsonbody    = "{ documents: [ { language: ""en"", id: ""0"", text: " & jsontext & " } ] }",
    bytesbody   = Text.ToBinary(jsonbody),
    headers     = [#"Ocp-Apim-Subscription-Key" = apikey],
    bytesresp   = Web.Contents(endpoint, [Headers=headers, Content=bytesbody]),
    jsonresp    = Json.Document(bytesresp),
    sentiment   = jsonresp[documents]{0}[score]
in  sentiment

We’ll now use this function to create a new column that returns the sentiment scores for each block of text. Go to your text data table, select the text column you would like to assign scores to, and then click Add Column > Invoke Custom Function:

No alt text provided for this image

Next, select your newly created function (SentimentFunction), and apply it to the text column:

No alt text provided for this image


Once you’ve applied these steps, the sentiment scores will appear as a new column:

No alt text provided for this image

We now have a consistent way to measure how positive or negative each tweet is! If you would like to view the completed Power BI file created for this post, you can download it from my GitHub repository here.

By bringing in the sentiment scores for our Twitter data, we can now perform a great deal of analysis to track how our social media channels are trending over time, and determine who our most unhappy customers are.

In my next post I’ll show how to create visualizations and create an interactive dashboard in Power BI with our Twitter data and sentiment scores.

If you have any questions, feel free to reach out to me on LinkedIn or Twitter.

Automatically Save Twitter Data to Google Sheets with Microsoft Flow

Voice of the Customer Series – Part 1

In this tutorial I’ll demonstrate how to set up a Microsoft Flow to automatically save tweets that mention your brand, product or service to a Google Sheet.

No alt text provided for this image

In a future post I’ll show how you can then explore this Twitter data and perform sentiment analysis on the tweets. This will allow you to evaluate how positively or negatively users are tweeting about your brand.

No alt text provided for this image

To start, navigate to the Microsoft Flow homepage and select the “Save Tweets to a Google Sheet” template.

No alt text provided for this image

Once you select this template, you’ll need to connect your Twitter and Google accounts. After you’ve authenticated these accounts, click continue.

No alt text provided for this image

Outside of Microsoft Flow, you’ll also need to set up a Google Sheet with headings for the Twitter data fields you’ll be collecting. I decided to collect the below fields:

No alt text provided for this image

Next, you’ll enter the search terms used to save tweets. I’m going to be analyzing tweets mentioning the Vancouver car share provider @EvoCarShare,but you can select whichever search terms you’d like to track.

No alt text provided for this image

Select the Google Sheet file in the “Insert Row” user interface, and then map each of your required Twitter data fields to the Google Sheet columns below. Microsoft Flow makes this incredibly easy, as the interface shows all of the fields available to you. Once you’ve mapped all of your required fields, press save.

No alt text provided for this image

Once you’ve enabled your Flow, you can check in the run history and perform a test to ensure the process is working properly. As you can see from the run history and the Google Sheet I set up, the automation is working correctly, and tweets are successfully saved whenever they mention @EvoCarShare.

No alt text provided for this image

In my next post I’ll show how you can add on from this integration, to analyze how positively or negatively users are mentioning your brand on Twitter.

Until then, you can explore the Twitter data in my Google Sheet below!

SQL Query to Find Database Columns and Data Types

Have you ever found yourself searching through database tables trying to find a specific column?

Many databases have hundreds (or maybe even thousands), of tables within them, so searching through these in SSMS can be a slow process.

If you recently started at a new company and you are trying to learn the database structures, it could take some to memorize things. One of the challenges I was struggling with when I first started my BI analyst job was finding all of the instances where a column was used.

I came across an SQL query that has been immensely useful in helping to quickly find all of the tables where a column is used within a database.

You can use the below SQL query examples to search for column keywords, data types, or exact column names, as shown below.

SQL query to search for columns with a keyword:

SELECT table_name [Table Name], 
       column_name [Column Name]

FROM   information_schema.columns 

-- replace invoice with the column name keyword you are looking for
SQL query to search for columns with a specific data type:

SELECT table_name [Table Name], 
       column_name [Column Name]

FROM   information_schema.columns

WHERE  DATA_TYPE = 'geography';
-- replace geography with the column data type you are looking for
SQL query to search for columns with an exact column name:

SELECT table_name [Table Name], 
       column_name [Column Name]

FROM   information_schema.columns 

-- replace DealerId with the column name you are looking for

Example Results Set Below: