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:
(With the permalink in C3)
=MID(C3,FIND("status/",C3)+7,100)Twitter account
If dealing with data from multiple accounts, you can extract the twitter username as well.
(with permalink in C3, and Twitter ID in A3).
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C3,"/status/",""),"https://twitter.com/",""),A3,"")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:
(with above date text in cell G3)
=DATE(LEFT(G3,4),MID(G3,6,2),MID(G3,9,2))Time can be extracted using:
(with above date text in cell G3)
=TIME(MID(G3,12,2),MID(G3,15,2),0)