At New Internationalist we have been using a self hosted ThinkUp instance to track our social media work for the last several years. If you haven’t checked it out yet, you should definitely take a look.
One thing that we occasionally need to look at is the amount by which our follower or like count has grown over a year, expressed as a percentage. This is something that normally takes my brain a little while to figure out, hence recording it here.
We will first need to know the network_user_id for the account we are interested in. In thinkup you can look that up in the tu_instances table.MariaDB [thinkup]> select network_username, network_user_id from tu_instances where network="twitter";
+------------------+-----------------+
| network_username | network_user_id |
+------------------+-----------------+
| newint | 595.... |
…
Now we can use some gnarly SQL to show the growth percentage from the account we are interested in. Let’s say it was the @newint twitter account and we wanted to see how much it had grown in 2014, for example.MariaDB [thinkup]> select ((end.count-start.count)/start.count*100) as twitter_growth FROM (select count from tu_count_history where date = "2014-01-01" AND network_user_id = 595.... AND type="followers") as start, (select count from tu_count_history where date = "2014-12-31" AND network_user_id = 595.... AND type="followers") as end;
+----------------+
| twitter_growth |
+----------------+
| 55.9293 |
+----------------+
1 row in set (0.00 sec)
So, the number of folks following @newint on twitter grew by about 56% in 2014. You can obviously tweak the dates and the network_user_id to see the growth for different periods.