Sentiment & Phrase analysis using Cognitive Services via MSFlow

In this digital transformation era implementing AI and Cognitive services into any of our service and application where ever possible plays one of crucial role improving end-user/customer/business decision and overall experience.

Let's us see a scenario for Ratsub Tech (A Fictional company) is B2B & B2C e-commerce retailer who is interested to understand "UserVoice" e-Mail by tracking its mailbox to understand user experience/sentiment towards their purchases.

Create a form to capture User recent purchase and provide a placeholder to fill their thoughts or you can also use adaptive forms will see about this later. At this point assume there is an interface for end-user to fill and send back there 'UserVoice'. Setup an e-Mail rule and move these emails(i.e. To:uservoice@ratsubtech.com) to "Support" folder.

//Sample e-mail template for this usecase
From: rathanavel@live.in
To: uservoice@ratsubtech.com
Subject: CustomerID^639~ProductNo^HY-U509~Product^Sport-100 Helmet, Black~sub^Awesome product
Body: This is cool product and i loved it...

Here starts the MSFlow Part. Add Outlook webhook - Trigger to "Support" folder


Get email


Assign values to variables

• Customer ID
• SkuID (Product ID)

split(split(body('Get_email')['Subject'],'~')[0],'^')[1]

split(split(body('Get_email')['Subject'],'~')[1],'^')[1]

Now, Extract three parameter that Text Analysis services offerx

1. Language detection
2. Key phrases
3. Sentiment analysis

Check this article to configure Text Analysis API in Azure Language Detection

Key Phrases

Sentiment analysis

Create variables in MSFlow

Setting up new variable for later use


Check condition and configure values

I have created a Stored Procedure to lookup and insert Product & Customer anonymous information to "TA_Uservoice" table.

	-- =============================================
	-- Author: <Rathanavel Subramaniam - https://ratsubsharewall.blogspot.com>
	-- Create Date: <7-May-2018>
	-- Description: <To store Sensitive/Phrase/Language - Analysis using Cognitive services>
	-- =============================================
	ALTER PROCEDURE [dbo].[CreateTAUserVoice]
	(
	@ProductNumber nvarchar(30),
	@CustomerID float,
	@TALanguage nchar(10),
	@TASentiment float(53),
	@TAPhrases nvarchar(max)
	)
	AS
	BEGIN
	declare @UserState nvarchar(30)
	declare @UserCity nvarchar(30)
	declare @UserRegion nvarchar(30)
	select @UserCity = City, @UserState = StateProvince, @UserRegion = CountryRegion from SalesLT.Address
	where AddressID = (select AddressID from SalesLT.CustomerAddress where CustomerID = @CustomerID)
	insert into TA_Uservoice (ProductNumber, ProductName, ProductDescription, ProductPrice, UserAge, UserGender, UserRegion, UserLanguage, Timeline, Date, TALanguage, TASentiment, ActualSentiment, TAPhrases, UserState, UserCity) values
	(
	@ProductNumber,
	(select SalesLT.Product.Name from SalesLT.Product where ProductNumber = @ProductNumber),
	((SELECT Description
	FROM SalesLT.ProductDescription
	WHERE (ProductDescriptionID =
	(SELECT ProductDescriptionID
	FROM SalesLT.ProductModelProductDescription
	WHERE (ProductModelID =
	(SELECT ProductModelID
	FROM SalesLT.Product
	WHERE (ProductNumber = @ProductNumber))) AND (Culture = @TALanguage))))
	),
	(select StandardCost from SalesLT.Product where ProductNumber = @ProductNumber),
	null,
	null,
	@UserRegion,
	null,
	null,
	GETDATE(),
	@TALanguage,
	FORMAT(@TASentiment, 'N1'),
	@TASentiment,
	Replace(Replace(Replace(@TAPhrases,'[',''),']',''),'"',''),
	@UserState,
	@UserCity)
	END

view raw sp_AI_sentiment_analysis.sql hosted with ❤ by GitHub

I don't have data for UserAge, UserGender, UserLanguage in my 'Adventure Works DB'. So i am passing NULL value by default.

Execute Stored Procedure from MSFlow

Select your stored procedure


The connector would automatically request parameters values

Configure them with respective variables

We have analysed and inserted data so far will see how to use these for business decisions and use this functionality for more use-cases. let me know your thoughts in comments will try work out those use-cases too with the given data.

So, stay tuned!

-Ratsub