
<p>There were many unique aspects of the 2016 U.S. presidential election, with one of the most interesting being Donald Trump’s use of Twitter. As luck would have it, tweets happen to be very easy to extract and put into a relational database format. Being the data junkies that we are, we couldn’t resist downloading President Trump’s entire tweet history and loading it into Sisense for Cloud Data Teams. It’s an excellent opportunity to slice, analyze, and quantify presidential communications while also exploring methods for analyzing the text in SQL at the same time. </p>



<p>So let’s get started.</p>



<h2 class="wp-block-heading">The Data Set</h2>



<p>First, we formatted Trump’s tweets into 5 columns of data:<br></p>



<ul><li>Tweet Text</li><li>Tweet Date</li><li>Favorites</li><li>Likes</li><li>Tweet ID</li></ul>



<p>With these columns, it was easy to do some quick exploration. <a href="https://twitter.com/realDonaldTrump?lang=en" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)">@realDonaldTrump</a> tweeted for the first time on May 4, 2009, with a tweet he almost certainly didn’t write.</p>



<pre class="wp-block-code"><code>select
   tweet_text as first_tweet
from
  trump_tweets
order by tweet_date 
    limit 1</code></pre>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/first_tweet.png" alt="First tweet" class="wp-image-77243"/></figure>



<p>Around July of 2011, Trump’s tweet volume quickly escalated. It’s not clear what triggered this increase in activity, but based on his first tweet a reasonable guess is that Trump began tweeting himself, either in addition to or in lieu of staff. In total, Donald Trump has tweeted over 34,000 times. At his peak he was tweeting over 30 times per day. A remarkable number by any measure.</p>



<p>A look at Trump’s tweet frequency over time looks like this:</p>



<pre class="wp-block-code"><code>select
 month(tweet_date) as mnth
  , count(*)
from
  trump_tweets
group by 1</code></pre>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image06.png" alt="Tweet frequency over time" class="wp-image-77248"/></figure>



<h2 class="wp-block-heading">Keyword Frequency: A History of MAGA</h2>



<p>Trump first used the phrase ‘Make America Great Again’ long before his announcement to run for President in the 2016 election as a Republican candidate. He filed papers in Iowa in 2012 for the “Make America Great Again” Party to begin a run as an independent candidate. His first use of the phrase on Twitter:</p>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/first_maga.png" alt="First MAGA tweet" class="wp-image-77253"/></figure>



<p>His use of the phrase has obviously increased since. If we sum the number of tweets containing the words ‘Make America Great Again’ or ‘MAGA’, and divide that number by the count of all tweets, we can easily define Trump’s MAGA rate. With a simple query we can look at the MAGA rate over time. Trump’s MAGA rate has a slightly bimodal distribution centered first around the announcement of his candidacy in 2015, then more strongly after his victory in the presidential election. Trump held an astounding peak monthly MAGA rate of 14% in September 2016.</p>



<pre class="wp-block-code"><code>-- Note the use of ilike and like. Ilike compares all strings
-- as lowercase, while like is case-sensitive. I don’t want to
-- count magazines.
-- Here we will use a float conversion to avoid integer division.
-- This is Redshift specific. Adjust accordingly.
select
  month(tweet_date) mnth
  , sum(
     case when tweet_text ilike '%make america great again%' or
     tweet_text like '%MAGA%' then 1
          else 0
     end
  )
  / count(*)::float as maga_rate
from
  trump_tweets
group by
  1</code></pre>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image07.png" alt="MAGA rate" class="wp-image-77258"/></figure>



<h2 class="wp-block-heading">Sentiment: The Anger and Joy of Donald J. Trump</h2>



<p>Given Trump’s prolific use of Twitter leading up to and throughout the campaign, we can apply a quantitative approach to measuring the tone and sentiment of his campaign with text analysis.</p>



<p>SQL is not the first tool people think of when doing text analytics, but it has some interesting advantages. It’s fast, well-integrated with existing ETL processes, and easy to build into regular reports. We will use a simple but effective approach, by relating individual words to sentiment scores to get a quick read on the emotional state of DJT.</p>



<p>This technique we’re using was explored in the paper <a href="https://arxiv.org/abs/1103.2903" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)">“A new ANEW: Evaluation of a word list for sentiment analysis in microblogs.”</a> Using Twitter as a foundation, Finn Årup Nielsen from the Technical University of Denmark (DTU) scored 2,477 words from -5 to 5 based on their perceived sentiment, with -5 being a word linked to a strongly negative emotional content. By joining the words in Trump’s tweets to this list, we can quickly get a per-tweet sentiment score. The data set for this topic is published by the Department of <a href="http://www2.imm.dtu.dk/pubdb/views/publication_details.php?id=6010" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)">Informatics and Mathematical Modelling</a> at DTU.</p>



<p>The data is in the form:</p>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image04.png" alt="Sentiment score form" class="wp-image-77263"/></figure>



<p>In order to begin this analysis, the tweets must be broken down into individuals words to join to our sentiment table. The method to do this is outlined in our blog post about <a href="https://www.sisense.com/blog/net-promoter-score/">NPS and survey analytics.</a></p>



<p>Once we have a resulting table of individual words, we can begin joining our tweets to our sentiment scores.</p>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image09.png" alt="Sentiment scores" class="wp-image-77268"/></figure>



<p>With a few simple queries, we can find Trump’s most celebratory and antagonistic tweets. First, we sum the sentiment scores by tweet.</p>



<p>His most positive:</p>



<pre class="wp-block-code"><code>with
  sentiment as (
    select
     tweet_text
      , tweet_date
      , sum(sentiment_score) 
      as sentiment_sum
      , count(*)::float as words
    from trump_words
    group by 1,2
  )
  select
    tweet_text
    , tweet_date
    , sentiment_sum
  from
    sentiment
  where
    sentiment_sum is not null
  order by sentiment_sum desc
  Limit 10</code></pre>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image05.png" alt="Most positive tweets" class="wp-image-77273"/></figure>



<p>And his most negative:</p>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image01.png" alt="Most negative tweets" class="wp-image-77278"/></figure>



<p>These are Trump’s most emotionally charged tweets, but since we have summed the sentiment scores we are biased towards longer phrases. Simple declarations such as “Great!” or “Sad!” convey a lot of emotional content without summing to large numbers. As we dig a little deeper we will divide the summed scores by the number of words in the Tweet to gauge the net sentiment.</p>



<p>Let’s look at Trump’s sentiment over time. This has been normalized to his average sentiment over the data history so that it is centered on 0. This is done because the sentiment data has little absolute meaning without a reference point.</p>



<pre class="wp-block-code"><code>with
  monthly_sentiment as (
    select
      month(tweet_date) as mnth
      , tweet_text
      , sum(sentiment_score) / max(num_words)::float
      as sentiment_score
    from
      trump_words
    group by 1, 2
  )
, avg_sentiment as (
    select
       avg(sentiment_score) as sentiment_avg
    from
      trump_words
  )
select
  mnth
  , avg(sentiment_score) - sentiment_avg as sentiment 
from
  monthly_sentiment
-- I want the average sentiment available for every row. 
-- You could use a window function as well. 
left join   avg_sentiment on true</code></pre>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image08.png" alt="Trump's monthly sentiment" class="wp-image-77283"/></figure>



<p>As can be seen, Trump had some serious highs and lows before 2013. The highs are often associated with the Miss USA and Miss Universe pageants or were tweets written by people (we suspect) other than Trump. The very low scores in 2011 &#8211; 2012 are mostly political in nature. Let’s take a closer look at some of these:</p>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image03.png" alt="Political tweets" class="wp-image-77288"/></figure>



<p>Most worrying though, Trump has only become more negative over the past 4 years, especially from 2015 and onwards. Zooming in on the period of the election we can see that Trump generally trended downwards, exhibiting increasing negativity over the course of his Presidential campaign. There were some high points, such as the week of his victory in November, but the overall sentiment of Trump’s tweets continues to decline. We can easily summarize Trump’s tweets with a single word: Sad!</p>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image02.png" alt="Trump weekly sentiment" class="wp-image-77293"/></figure>



<h2 class="wp-block-heading">Sentiment by Time of Day: Angry, Early Morning Tweets</h2>



<p>Another interesting perspective is Trump’s sentiment as a function of the hour of the day. Much has been made of his proclivity for early morning tweeting and what the ramifications of this might be. With a bit of SQL, we can look at this quickly and easily. We use the same query as above, continuing to limit our timeframe to the presidential campaign, but we use a 3-hour rolling average and use an extract, like so:</p>



<pre class="wp-block-code"><code>with
  monthly_sentiment as (
    select
      extract(hour from (tweet_date)::timestamp) as hr
      , tweet_text
      , sum(sentiment_score) / max(num_words)::float
      as sentiment_score
    from
      Trump_words
    where date >= '2015-06-16'
    group by 1, 2
  )
, avg_sentiment as (
    select
       avg(sentiment_score) as sentiment_avg
    from
      trump_words
  )
select
  hr
  , count(*) as tweets
  , avg(avg(sentiment_score)) over(order by hr rows between 1 
  preceding and 1 following) - sentiment_avg as sentiment
from
  monthly_sentiment
left join 
  avg_sentiment on true
group by </code></pre>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image00.png" alt="Hourly sentiment" class="wp-image-77298"/></figure>



<p>He’s at his most negative in the morning. On average, between the hours of 4 am and 7 am, Trump unleashes a high volume of negative tweets. Our data does reveal a counterbalance to this finding. Trump’s tweets become more positive throughout the day and this positive sentiment peaks at night, along with his overall tweet volume.</p>



<h2 class="wp-block-heading">Wrap Up</h2>



<p>Many companies have an abundance of text data but don’t have a strategy for fully making use of it. After a few simple joins in SQL, keyword frequency and sentiment analysis are two excellent methods for gaining context and insight into text data. Both are broadly applicable beyond tweets. Survey data, comments, competitive information, or web copy can all be explored with the methods discussed above.</p>


Text Analysis with Donald Trump’s Tweets

LinkedIn

Twitter

GitHub

curve-image-unique-image-unique

curve

3-dark-2-image-unique-image-unique

3 DARK 2

Get the latest in analytics right in your inbox.

Article