Here is the architecture of this solution template, where your Microsoft Azure subscription and Twitter account will provide you with access to analytical data based on Twitter handlers and hashtags of your choice.
- Logic App passes tweet into an Azure Function
- Azure Function enriches tweet and writes it to Azure SQL
- Azure Function also calls Azure ML experiment running Python script to work out sentiment of tweet
- Power BI imports data into it from Azure SQL and renders pre-defined reports
You can find more details on how to customize this solution template here: Brand & Campaign Management Solution Template Documentation
However, with my recent customer Power BI project implementation with this template, a request was made to add a filter for twitter messages based on their geo locations; customer wanted to see tweets only from the Canadian Twitter audience. Then I realized that all this information was not easy to obtain, because it's optional for Twitter users to populate it and even update their profile location is also optional (Twitter Geo Guidelines). But I still decided to try parsing user profile location in order to separate tweets from the Canadian users and users from other countries.
For this blog post I have used @canada150th twitter account messages and messages from all other accounts that contained #canada150 hashtag as well.
Here is my final report:
And here is how I was able to add twitter user profile location data into my report.
1) I added a new location column to the table that holds all the processed tweet message in my Azure SQL database:
alter table [pbist_twitter].[tweets_processed] add location nvarchar(100);
2) Made a few changes in the code of the Azure function (TweetFunctionCSharp.cs) that processes tweet messages:
private Dictionary<string, string> processedTweets = new Dictionary<string, string>()
{
{"tweetid", null},
{"masterid", null},
{"image_url", null},
{"dateorig", null},
{"authorimage_url", null},
{"username", null},
{"hourofdate", null},
{"minuteofdate", null},
{"direction", "Text"},
{"favorited", "1"},
{"retweet", "False"},
{"user_followers", null},
{"user_friends", null},
{"user_favorites", null},
{"user_totaltweets", null},
{"location", null}
};
processedTweets["location"] = tweet.UserDetails.Location;
3) Created a function in the SQL Azure database that would try to parse a text from the user profile location column and check if that profile belongs to Canada:
CREATE FUNCTION [dbo].[ufnCheckTwitterProfileCountry]
(
@location nvarchar(100)
)
RETURNS nvarchar(30)
AS
BEGIN
-- Declare the return variable here
DECLARE @Country nvarchar(30)
SELECT @Country = 'Non-Canada'
DECLARE @TempTable as TABLE (location nvarchar(100));
INSERT INTO @TempTable (location)
select ' AB' as Location
union select ' BC'
union select ' MB'
union select ' NB'
union select ' NL'
union select ' NS'
union select ' NT'
union select ' NU'
union select ' ON'
union select ' PE'
union select ' QC'
union select ' SK'
union select ' YT'
union select ',AB'
union select ',BC'
union select ',MB'
union select ',NB'
union select ',NL'
union select ',NS'
union select ',NT'
union select ',NU'
union select ',ON'
union select ',PE'
union select ',QC'
union select ',SK'
union select ',YT'
union select 'Alberta'
union select 'British Columbia'
union select 'Manitoba'
union select 'New Brunswick'
union select 'Newfoundland and Labrador'
union select 'Nova Scotia'
union select 'Northwest Territories'
union select 'Nunavut'
union select 'Ontario'
union select 'Prince Edward Island'
union select 'Quebec'
union select 'Québec'
union select 'Saskatchewan'
union select 'Yukon'
union select 'Canada'
-- Add the T-SQL statements to compute the return value here
SELECT @Country = 'Canada'
WHERE
(
SELECT SUM(CHARINDEX(t.location, @location))
from @TempTable t
) > 0;
-- Return the result of the function
RETURN @Country
END
GO
4) Updated a SQL view code to bring a Country code that is used in my Power BI report:
CREATE VIEW [pbist_twitter].[vw_tweets_processed]
AS
SELECT tweetid AS [Tweet Id],
dateorig AS [Original Date],
Convert(date,[dateorig]) AS [Date],
hourofdate AS [Hours],
minuteofdate AS [Minutes],
latitude AS [Latitude],
longitude AS [Longitude],
masterid AS [Master Id],
retweet AS [Retweet],
username AS [Username],
usernumber AS [User Number],
image_url AS [Image URL],
authorimage_url AS [Author Image URL],
direction AS [Direction],
favorited AS [Favorited],
user_followers AS [User Followers],
user_friends AS [User Friends],
user_favorites As [User Favourites],
user_totaltweets AS [User Total Tweets]
,location
,dbo.ufnCheckTwitterProfileCountry(location) as Country
FROM pbist_twitter.tweets_processed;
GO
Problem solved! Feel free to comment if you find this approach to add location details to your Power BI Twitter template solution helpful!
Happy 150th Canada day and happy data adventures!
