Track twitter followers over time using Google Sheets
Keeping an eye on how your twitter followers are changing over time is essential to most social media marketing. What is the effect of publishing tweets? or following / unfollowing other users?
Twitter don’t make this easy for you. On the twitter analytics page the only view of the history is a sparkline and the totals for the last 28 days.
A solution I use is to write some code for google sheets, to grab the latest total on an hourly basis and write it to a sheet. This is based on a neat hack by Kaspars Dambis which gets the number of followers for any user without messing with authentication on the twitter API.
Heres’s the spreadsheet. Make a copy and have fun.
You can get at the code via Tools > Script Editor… Press play to try it out. Or set it to run on a schedule via Resources > Trigger. Where you will see this:
The first time it runs you will need to give it access:
This next bit is optional. If you want to populate it with the last 28 days of data, you can get the data behind the sparkline on the analytics widget. If you right click on it and “inspect element” you can get the numbers that generate it.
If you copy them in to the spreadsheet title “Reverse Engineer Sparklines” (in the yellow box at top). Then copy your current followers and increase over 28 days in to the other 2 yellow boxes. It will give you a list of 28 data points you can add to the account history page.
That’s it. If you find this useful please shout.