#DearDuo 9 – Twitter Analytics

#DearDuo 9 – Twitter Analytics

Welcome to the 9th month of our Dear Duo project.  #DearDuo has been a fun way to get to know each other and to keep practicing visualizing foreign data sets. In 2017, we started publishing a single post each month written together rather than 2 separate ones.  The biggest reason is we want this project to be more collaborative.


The Data

This month we are looking at our own Twitter data.  Why Twitter data?  Let’s face it.  We were a little lazy this month and wanted a break from tracking stuff.  This is without a doubt going to be the cleanest data we analyze for this project.

Fun side note, I sometimes call it “the Twitter” to mess with Pooja.  This nonsense is generally met with 🤦‍♀️ a woman faceplaming emoji (it’s a cooler looking version now that Pooja FINALLY got an iPhone) and a barrage of old man jokes.

Adam – We enabled Twitter Analytics a while back.  We were able to get data back to April 2016 by exporting multiple files.  We could have gone back further but we wanted to start in the month when we announced The Data Duo.

Pooja gave me five csv files that were easily unioned in Tableau (does anyone remember when we couldn’t union?).  The data was really clean.  The only real data manipulation was working with the dates.

I converted the UTC date/time extracted from Twitter to Eastern time zone.  This also takes into account daylight savings time for the date ranges we are using.  Thanks Pooja for reminding me to adjust for daylight savings time (I think she secretly wanted me to send her the calc).

//[Time Parsed] = DATEPARSE("YYYY-MM-dd HH:mm +0000",[Time])

IF [Time Parsed] <= DATEPARSE('MM/dd/YYYY HH:mm','11/6/2016 2:00')
OR [Time Parsed] >= DATEPARSE('MM/dd/YYYY HH:mm','3/12/2017 2:00')

THEN DATEADD('hour',-5,[Time Parsed])
ELSE DATEADD('hour',-4,[Time Parsed]) END

I also did some custom date formatting to mirror the formats that Twitter displays on tweets.  The following custom format will produce a date/time like “2:58 PM – 8 May 2017”

HH:mm AMPM - DD MMM YYYY

The other thing I wanted to do was to create a wildcard search on Pooja’s tweet text (mostly because I wanted to see how many times she tweeted me). I was going to use RegEx, but Pooja still hasn’t taught me how to do that (maybe this 10th reminder will help…). Instead, I used a sting parameter and a Contains function. This approach has some benefits because I wanted to display the search text elsewhere on the dashboard and the search works on anything (i.e., handles, words, hashtags, etc.). The Lower functions fix any case sensitive issues (thanks Pooja for catching that).

CONTAINS(LOWER([Tweet text]),LOWER([String]))

Pooja – It was a lazy and a busy month for the data duo. Hence, the use of twitter analytics data. Adam provided me with 5 CSV, or I should say a CLEAN dataset (finally). Like Adam said above, the first thing I noticed was the fact that I’ll have to create a date field to accommodate for day light savings and a conversion to EST. But, I do get perks of being the fun half of the data duo 😂. Adam as usual was the first one to finish this month’s viz and I bullied him to copy and paste the date conversion formula on a text message. Problem solved! This alone must have saved me an hour of good night’s sleep. Total exaggeration.

Just like Adam did, I also wanted to use a ‘search your twitter handle’ functionality. So the calc I used is: contains(lower([Tweet text]), [Enter a word]). Drag to filter and selected ‘True’.

I created 16 worksheets and 16 calculations to make it all work. There are a lot of different calculations to dynamically change text based on if there is a text entered in a search box or not. Download the workbook to see various calculations if you’re interested. Because this is a lazy month, I am not going to type the calculations here 😉


The Design 

Adam – This is my first long form viz for the #DearDuo project.  There were too many things I wanted to do for a compact viz.  The first thing I did was make the dashboard 2000px tall.   I wanted to accomplish “three” visual elements in my design: 1) make the top half look like Pooja’s Twitter profile; 2) create a tweet text search and dot plot; and 3) create engagement charts similar to what Twitter uses on their analytics page.  Oh, and it goes without saying I would be designing in Twitter’s night-mode theme, right?  I am part of the Data Duo after all!

1. I created a worksheet to look like THE Twitter’s totals (tweets, likes, etc.) using a bar chart on a fixed axis and a highlighting action.  This got me thinking and I ended up making the entire top half look like Pooja’s profile including her photo, description and a play on her header image.  I even floated some pngs over the linked text so the hyperlinks work.  That’s a Pooja trick by the way.  One thing I now realize is just how much her work has inspired my own.  So many little things I do all the time came from Pooja’s designs.  I wonder if she realizes the impact she has had on so many authors!

2. I created a dot plot by hour and date.  This is the chart that uses the parameter text search.  You can type anything in here and see how Pooja tweeted it by hour and over time.  Go ahead and search your handle to see if she tweeted you.  I added a text worksheet below this to show how many tweets included the search text and what percentage of her tweets this represented.

3. I created five engagement charts inspired by The Twitter’s chart types and colors.  I also added the top tweet in each category and offset these sheets within five floating layout containers.

If you are wondering why I had “three” visual elements in bold and italics, it’s because there are 49 floating elements on this dashboard.  Yeah.  I counted.  I hope I counted right.

Pooja – I recently did a viz for #MakeoverMonday that utilized ‘cards’ as sheets to section the data for better readability and function. I used a similar approach here and created bar charts for different metrics like total tweets, retweets, likes and engagements Adam got on his tweets.

The top section of the cards show total metric number and percent of total if there is a text entered in the search box if not it simply says ‘Adam’s tweets got…’. During #IronViz time my entry contained lots of arrows to guide the user to see in a specific direction, so I followed that idea here as well.

I also used dual axes bar charts in a way that, when nothing is entered in the search box its all the tweets, but when something is entered in the search box it shifts the bars down and the gray bars still show for context of total metric. The calculation I used to do this is: sum(if contains(lower([Tweet text]), [Enter a word]) then [Number of Records] end) as the measure placed on the secondary axis. So this is how the bars look when you enter ‘drexelpooja’

I saw Adam’s viz on Tableau Public and thought I’ll do something similar to what he did with colors. I thought he did a fantastic job laying all the elements so perfectly on a rather small canvas. I used hour of day on rows and the day of date field on columns to distribute tweet texts accordingly. I noticed there was a field called ‘tweet id’ that I set on level on detail and used a URL action to take the user to the original tweet, should they be interested in reliving the moment. After all, who doesn’t want to relive Adam’s funny tweets, silly GIFS and clever responses? 😉 So there you have it.

The URL link I used is: https://twitter.com/acrahen/status/<Tweet id>

Finally on the top section, I included his twitter snapshot to make it slightly twitter-like.


What did we learn?

AdamI was on a mission to see how many times Pooja tweeted me in a year and what percentage of tweets she tagged me on.  I guesstimated around 40% of her tweets included my handle.  I am sad to say only 31.1% (or 977) of her tweets in this time period mentioned @acrahen.  But, this got me thinking….

Did I beat…?

  • @tableau- 14.6% (457) – YES
  • @tableaupublic – 8.7% (272) – YES
  • #MakeoverMonday – 5% (158) – YES
  • @thedataduo – 13.4% (420) – YES
  • 😂 – 9.2% (288) – YES
  • haha – 7.8% (246) – YES
  • @VizWizBI – 23.8% (747) – YES
  • @mix_pix – 9.1% (285) – YES
  • @Harris7Curtis- 6% (188) – YES
  • 🙂 – 17% (535) – YES
  • data – 36.1% (1,133) – NO
    • WHAT?!?!?!?!  I lost to data?
    • hmmm let’s exclude the @thedataduo
    • data (excluding @thedataduo) – 22.7% (713) – YES – Phew that was a close one!

As you can see, I enjoyed playing with the search box on this dashboard.  I’m not 100% certain, but I’ve seen enough to claim victory for @acrahen as Pooja’s most popular tweet text! 😂

Pooja’s top 5 most engaging tweets included three from #MakeoverMonday, her latest #IronViz entry and her @tableau New Year’s resolution.  No wonder she is a zen master.

Pooja – So just like Adam, one of the first things I saw was how many times did Adam tweet me? It seems like we talk a lot on social media but I was surprised to see that only 28% of his tweets were directed to me. He mentioned @tableau on 24% of his tweets. Only 17% of his tweets were directed to @VizWizBI despite his overly ambitious participation in #MakeoverMonday. Even more surprising was that he mentioned @acotgreave only 8% of the times. Didn’t both the Andys run #MakeoverMonday together in 2016? But maybe Adam and Andy K have their own banter on twitter that didn’t include Andy Cotgreave. @Trimydata was mentioned 6% of times and she only started hosting #MakeoverMonday this year.

Adam’s maximum tweets, retweets and likes occurred in November 2016 which is no surprise at all. It was the conference month and he was super busy campaigning for votes to win the coveted title of ‘who is more fun?’ as part of our TC16 survey. But ha! All in vain, unfortunately, Better luck next time, bud! 😂

So happy to report that maximum engagements on his tweets occurred in April 2017. What a great month that was, the data duo completed one year. What better can he ask for than that? Way to go, Adam! 😃

81% of Adam’s tweets got at least a single like and that count went up as the months passed in the last year. This makes me think, the more he started being visible and engaged with the community, the more people acknowledged his tweets.


Thank you for following along our quantified self journey.  Three more to go!  Thank you for reading, until next month!

Here are our vizzes:

5 thoughts on “#DearDuo 9 – Twitter Analytics

  1. Great, great post and great dashboard guys. For sure I am going to get inspired from them for somehitng I have in mind, the design and the calculations. Good job

    1. Adam Crahen

      Thanks Jorge! If you publish it, let us know!

    2. Pooja Gandhi

      Awesome, thanks for the feedback! Tag us when you publish it. We would love to see!

  2. Fantastic vizzes!! The style of these could easily be replicated in a business KPI dashboard. Really, really good work! And I can’t believe Pooja tags me that often in tweet. 🙂

    1. Pooja Gandhi

      Thank you, Andy! Ofcourse, I tag you a lot in tweets. After all I have done ALL #MakeoverMonday vizzes and I like making fun of you 🙂

Leave a Reply