Twitter export cleaning and data conversion

This is another note to self, on how to clean performance data extracted from Twitter Analytics.

Tweet ID

Due to the length of Twitter ID’s Excel turns the ID into scientific notation and so the ID is not exactly right.
Luckily, that can be extracted from the Permanent Link using the following excel formula:

<code>(With the permalink in C3)

=MID(C3,FIND("status/",C3)+7,100)</code>

Twitter account

If dealing with data from multiple accounts, you can extract the twitter username as well.

<code>(with permalink in C3, and Twitter ID in A3).

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C3,"/status/",""),"https://twitter.com/",""),A3,"")</code>

Date and Time

Twitter formats data and time as 2020-03-31 20:06 +0000 – which is not stored as a number, or a date. It is a text box.

Date can be extracted using:

<code>(with above date text in cell G3)
=DATE(LEFT(G3,4),MID(G3,6,2),MID(G3,9,2))</code>

Time can be extracted using:

<code>(with above date text in cell G3)
=TIME(MID(G3,12,2),MID(G3,15,2),0)</code>

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

  

  

  

This site uses Akismet to reduce spam. Learn how your comment data is processed.