Charlie Harvey

Tip: Use SQL to find percentage socia media growth from Thinkup

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. thinkup logo

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.


Comments

  • Be respectful. You may want to read the comment guidelines before posting.
  • You can use Markdown syntax to format your comments. You can only use level 5 and 6 headings.
  • You can add class="your language" to code blocks to help highlight.js highlight them correctly.

Privacy note: This form will forward your IP address, user agent and referrer to the Akismet, StopForumSpam and Botscout spam filtering services. I don’t log these details. Those services will. I do log everything you type into the form. Full privacy statement.