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_media: contains the like counts, comment counts, type of media, and other data about individual posts
instagram_comments: contains the text of comments left on posts, the user who left the comment, and the time the comment was created
instagram_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:
from_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 from_username as 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_comments /* Change this to your account’s to exclude comments left by the
account owner */ WHERE username != ‘yourusername’ 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
GROUP BY 1, where 1 refers to column 1, which in this case is
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.