Instagram Data Analysis using SQL, part the third: Identifying Top Followers

This post has been updated to reflect changes to Instagram's API (Facebook's Graph API). You will now need to switch to an Instagram Business account to perform this analysis in Panoply.

Welcome back to all the Instagram brand ambassadors, social media analysts and savvy influencers-to-be who have been with us since the beginning of this series. For those of you just joining us now, this is the third post in our series on how to analyze your Instagram data using SQL and Panoply. Be sure to check out our previous posts (best performing IG posts and follower engagement over time) if you haven’t already, since we covered some of the most basic of the basics at the beginning of the series. 

As a reminder, we’re going to be drawing mainly from three of the tables that Panoply creates when you collect your data from Instagram: 

instagram_graph_media: contains the like counts, comment counts, type of media, and other data about individual posts

instagram_graph_comments: contains the text of comments left on posts, the user who left the comment, and the time the comment was created 

instagram_graph_user: contains follower counts and other summary information about the user’s account 

With that, let’s dig into our next example query: how to identify your most engaged Instagram followers. 

Identifying your most engaged Instagram followers

Suppose you wanted to try to develop a profile of the type of person that is most likely to engage with your posts. Or maybe you want to identify your most engaged followers so you can give them something special as part of a promotion. Whatever your motivation, one of the best ways to do this with Panoply is by turning to the instagram_comments table, because commenters tend to be some of the most active fans you’ll find. 

Let’s look at the fields we’ll be using from instagram_comments first, though: 

• username: it’s what it sounds like. The username the comment is from. 
• media_id: this is a unique identifier that Instagram assigns to each piece of media posted to its platform. We’ll use it here to bin comments on the same post together

Try this SQL query to find your top Instagram commenters: 

SELECT * 
FROM 
  (
    SELECT 
      username, 
      COUNT(media_id) as nums_of_comments, 
      RANK() OVER(
        ORDER BY nums_of_comments DESC
      ) 
      /* Change “instagram_comments” below to whatever appears in your 
      Panoply instance */
    FROM instagram_graph_comments 
      /* Change this to your account’s to exclude comments left by the 
      account owner */
    WHERE username != '<your username>'  
    GROUP BY 1 
    ORDER BY 2 DESC
  ) as t 
WHERE rank <= 15 
  /* Change the rank value to whatever number of top commenters you’d like to see */

This will return a dataset with the number of comments left by each user who interacts with your account, in descending order. Use this data to identify your most engaged users so you can understand your audience better. 

So how does this query work, though? Here, we’re using a subquery to create a subtable that:

• Counts the number of comments left by individual commenters (COUNT(media_id) as nums_of_comments)
• Uses a rank window function to rank the results by number of comments in descending order (RANK() OVER(ORDER BY nums_of_comments DESC)
• And groups the counts by the user who left the comments (through SELECT from_username and GROUP BY 1, where 1 refers to column 1, which in this case is from_username) 

Then, using that subtable, we summarize by selecting only the top 15 (or whatever number you’re interested in seeing) with SELECT * FROM (subquery) as t WHERE rank <= 15. 

Now that you’ve got that all fired up, you can pull out your top commenters and start to dig even deeper. Want to try to characterize their activity even further? Try doing sentiment analysis on their body of comments. Want to try to build a profile of the types of users most likely to engage with your account? Try gathering the bios of the users who have left comments on your posts. This is a great jumping off point for further analysis.