Odds are, if you’re reading this, you represent the part of the Venn diagram where my Microsoft data platform buddies and Premier League soccer fans intersect or you clicked the link in today’s Men In Blazers Raven because you were curious how the Premier League mood table works. Rest assured, if you can think of different ways to do this and tweaks to make, I’d love to hear about them in the comments (unless you’re an Arsenal supporter – mostly kidding)! I’m passionate about data solutions and am always trying to stay abreast of rapidly changing platforms and technologies. Feel free to reach out to correct me, congratulate me, or just to connect with me professionally!

Before we go any farther, I owe my buddy Bradley Ball (b|t) a tip of the cap and several cold beverages for the original blog that provided the inspiration behind the Mood Table. If you’re interested in a deeper dive than this high-level overview, click through to my deep dive post here or his original post here.

First off, the foundation of the mood table begins with creating an Azure Logic App (basically a workflow container) for each of the twenty Premier League clubs. Those logic apps contain a Twitter connector that, when active (more on that later), searches Twitter for tweets containing each club’s official Twitter handle. Those tweets are collected by the Twitter connector and fed into the Sentiment Analysis function of the Cognitive Services API found within Azure. The Sentiment Analysis function adds a sentiment score to the tweet (0 is completely negative and 1 is completely positive) and passes it on to the final couple of steps in the process.

Once the sentiment score of the tweet is calculated, the tweet and its corresponding data (text, score, originating account, date created, etc.) is fed into a loop container that separates each club handle in the tweet and connects the tweet to every club tagged within the tweet. Finally, all of this data is stored in an Azure SQL Database (basically a database in the cloud) for persistent storage, querying, and analysis.

It is important to point out that each club’s logic app runs for a 10-minute window around the end of each match. It captures roughly the last 2-3 minutes of match action as well as the 7-8 minutes immediately following the full time whistle. As Rog told me, the intention is to capture the sentiment of tweets akin to when you turn to your mates (or buddies, in ‘Merican) at the pub and offer your instant analysis on your club’s success, failure, or utter mediocrity in the match.

Before I get into a brief description of the query I use to provide this critical information to Rog and Davo, I do want to mention that there was originally a real-time component of the mood table as well. I had written a Power BI report that showed a live table throughout the matches as tweets and their data streamed into the Power BI streaming dataset. After chatting with Rog, though, that was deemed far too optimal for MIB (not to mention quite pricey for my personal Azure account) and we settled on the current sentiment information surrounding the final whistle. It may make an appearance at some point, though!

Once all this Azure coolness has done the heavy lifting, the actual query that compiles the results is fairly straightforward. It takes the average of the sentiment scores tagged to a specific club and then ranks those clubs (and their corresponding average scores) from the highest average to the lowest average. The only data it excludes is tweets that contain only an image (as the sentiment analysis cannot score images – yet). Sadly, your hilarious soccer GIFs are not yet understood by the mood table.

In a nutshell, that’s it. I look forward to comments, questions, and connections. As I mentioned, I have a much more detailed technical deep dive into that that you can read here. This has been a blast to work on and we have some potentially interesting plans for it moving forward. Finally, there is no truth to the rumor that Rog is paying the Russians to create bots to elevate Everton to the top spot. That is just the natural optimism and enthusiasm of Evertonians shining through! To the football…

• MIB Mood Table