Twitter is a very popular social media platform which generates millions of tweets a day. These tweets include very valuable data for marketers or social media analyzers because they can analyze these tweets with specific algorithms and find out the sense of social media users.
Sentiment analysis is the indispensable part of social media monitoring. For example, you can do a general sentiment analysis about your brand and define it with a certain score and follow it regularly in monthly, weekly or quarterly time periods. Nowadays, social sentiment analysis is as significant as measuring customer satisfaction, so it is equally important to follow this score correctly. Apart from the general sentiment analysis that you can do for your brand, you can even measure the sentiment analysis of a product or your brand, or even an advertising filename that you simply do. In this way, you can closely observe the effect of social media and take the right steps when necessary.
You can take these advantages from Sentiment Analysis
- It can be used among the basic success criteria of your campaigns or products
- As well as used to monitor your situation against your competitors
- It allows you to intervene quickly in crisis times and predict where the crisis is going
- It shows how healthy your brand is on social media
In social sensitive analysis, comments on social media are classified as positive, negative, ineffective, even slightly positive, very positive, slightly negative, and very negative. On this page, you will find a distribution of all these comments according to these categories.
After all this brief explanation of sentiment analyses, we will mention about the intent of this article. The goal of this article is to insert the new tweets text and details to SQL Server database when someone posts it on Twitter and also these tweets hashtags equal to sentiment analysis criteria. In this article demonstration, we will use another tool which name is Microsoft Flow.
What is Microsoft Flow?
According to TechTarget
“Microsoft Flow is a cloud-based software tool that allows employees to create and automate workflows across multiple applications and services without the need for developer help. Automated workflows are called flows. To create a flow, the user specifies what action should take place when a specific event occurs.
The most common use of Microsoft Flow is to trigger notifications. When a new lead is added to Microsoft Dynamics CRM, for example, an automated workflow can send sales representatives an email or text message with details about the lead.”
Microsoft Flow helps us to create a communication bridge between Twitter and SQL Server. And we will use Azure Cognitive Service. This service provides different type of machine learning API’s and services to us useful for emotion and video detection, speech and vision recognition and etc. In this demonstration, we will use text analytics service and it aids about text sentiment analysis.
Now we will illustrate the architecture diagram of this Microsoft Flow demonstration in the below image.
The short explanation of this diagram is; when someone posts a new tweet which includes particular hashtag we want to track and analyze it. Workflow handles this tweet and sends to text sentiment analysis and get the result of this process. And then workflow inserts this tweet and other details to SQL database.
Create workflow
We will open the Microsoft Workflow portal and log in. Click My Flows. In this screen, we can create blank workflow or we can use the workflow templates. We will use a template which is suited to our architecture.
When we search Twitter Sentiment double keyword, we will find a template which is explained as Process Tweets with Sentiment Analysis.
We will select Process Tweets with Sentiment Analysis template. The explanation of template describes every thing about this template. This template provides a twitter connection, sentiment analysis and insert all details of sentiment analysis results to SQL Server tables. It requires two tables in SQL Server database. The first one is to store tweet details and second one for key phrases. This phrases help us to understand trend keywords in the tweets.
In the first step of this template settings, we will log in Twitter with twitter account. And then we will configure SQL Server connection settings. For this demonstration, we will use Azure SQL database and configure connection settings. Click Create and then set the connection settings of SQL Azure.
The second step of database configuration is to create two tables which store the tweet details. You can use the below script to generate the Tweet and TweetKeywords tables in SQL Server.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
DROP TABLE [dbo].[Tweets] GO /****** Object: Table [dbo].[Tweets] Script Date: 10.09.2018 08:55:10 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Tweets]( [TweetText] [varchar](3000) NULL, [TweetedBy] [varchar](50) NULL, [CreatedAt] [varchar](100) NULL, [TwitterID] [bigint] NULL, [Favorited] [varchar](20) NULL, [USERID] [bigint] NULL, [Username] [varchar](50) NULL, [SentimentScore] [float] NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[TweetsKeywords] Script Date: 10.09.2018 08:55:59 ******/ DROP TABLE [dbo].[TweetsKeywords] GO /****** Object: Table [dbo].[TweetsKeywords] Script Date: 10.09.2018 08:55:59 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[TweetsKeywords]( [TweetId] [bigint] NULL, [Keyword] [varchar](50) NULL ) ON [PRIMARY] GO |
In the last step of connection setting, we need to Text Analytics connector. We can create this service in Azure Portal.
We will connect to Azure Portal and find the Cognitive Services.
We will find Text Analytics and click Create.
And we will find the text analytics and click create. For workflow connection, we need Account Key and Site URL of created text analytics. We can find URL and Account Key in the Overview tab of text analytics. When you click the Show access keys the Manage keys tabs will appear and you can copy/paste this keys to workflow connection settings.
And the finally connections icons have to be green. And then we can continue.
Now we will manage the workflow, in my thought, this screen is very handy. In the When a new tweet is posted step we will write the hashtag which we want to perform sentiment analysis. For this demonstration, I will select #BMW you can select any topic or title.
In the Add Tweet to Tweets Table, we will select a table which stores the new tweets. We have to match tweet detail and table columns.
In the For each Key Phrase step, we will select the table to store Key Phrases.
We will click the Save and navigate the My Flows page. In this page, we can find out the workflow RUN HISTORY this menu helps us to review the workflow transaction status and details.
When we look at the Tweets table we can find details about Tweets which were processed and inserted by the workflow.
|
1 2 3 4 5 6 7 8 9 |
SELECT [TweetText] ,[TweetedBy] ,[CreatedAt] ,[TwitterID] ,[Favorited] ,[USERID] ,[Username] ,[SentimentScore] FROM [dbo].[Tweets] |
The SentimentScore columns defines the numerical presentation of tweet emotion. This score range in 0 to 1. If values close to 0.5 it defines neutrality and if values close to 1 it defines positive results.
The TweetsKeywords table includes key phrases. If we convert this table data to WordCloud analyze it we can find out most used keywords.
Summary
In this article, we demonstrated sentiment analysis in the simplest form in Microsoft flow. We converted tweet details more meaningful and inserted to SQL Server database. We can use this stored data in various analyses and reports. Such as we can analyze this data in BI tools and produce actionable information for decision making.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023