Analyzing Your Instagram Data with Panoply

Getting started

In order to analyze your Instagram data, you’ll first need to pull it into your Panoply database.

You can do this by clicking the Data Sources link on the left pane of your Panoply home page or just follow this link directly. Once there, you’ll see “Add Data Source” in the upper right.

Once you’re in the ‘Add Data Source’ pane, you should see several different groupings of potential data sources on the left-hand side of the screen. Select ‘APIs’, then scroll down until you see the Instagram logo.

Now you’ll need to enter your login info and select the data types you want to pull in from your instagram account. For the purposes of this guide, we’re going to pull it all in, but feel free to pick and choose on your end.

The process might take a minute or two, depending on how much data your Instagram account has generated up to this point, but when it’s done you can view the tables you generated by clicking the Tables link on the left pane of your Panoply page. Once the data ingestion process is complete, you should see several new tables in your list, all starting with ‘instagram_’. That’s it, you should be all set to explore your data.

Key Instagram metrics

Now we’ll switch gears for a second and discuss some key Instagram metrics you might want to look into, now that you have all your Instagram data at your fingertips:

• Engagement and reach: this is, in effect, a way to measure how much interest your instagram posts are generating, and you can measure that by looking at how many comments and likes each post is generating. You’ll also want to look at how individual hashtags are performing
• Follower growth: how many followers are you adding over time? Are there any patterns in your follower growth data? Do certain types of posts boost follower growth?
• Referral traffic: this metric will obviously be more important for social media managers and businesses using Instagram for promotional purposes, but you can easily integrate your Instagram and Google Analytics data using Panoply

Example queries

Now that you have your data loaded into Panoply and a sense of what data you’ll want to be tracking, you can start writing queries using the Panoply SQL editor. Follow the ‘Analyze’ link on the left pane of your Panoply page and start writing queries. See below for some examples to get you started.

Engagement

Let’s first look at how many comments and likes our posts are receiving. In Panoply, this data will be stored in the instagram_media table, where each post has a unique ID. This table also contains the time the image was posted and a couple other helpful data points, but any data you want that you don’t see here you can obviously pull in from other tables.

Likes per post

Let’s start by looking at how many likes each of our posts are attracting. We’ll do that with a query like this:

SELECT 
  id, 
  likes_count as "likes" 
FROM 
  instagram_media

Comments per post

Next we’ll collect comments per post:

SELECT 
  id, 
  comments_count as "comments" 
FROM 
  instagram_media

Best time of day for posting

Suppose you were wondering if there were a pattern to the engagement for each of your posts, based on time of day. Is there some time of day that’s better than others for your posts? To check this, let’s look at average number of likes and comments by time of day:

SELECT 
  to_char(created_time, 'HH24') as "time", 
  AVG(likes_count) as "average likes", 
  AVG(comments_count) as "average comments" 
FROM 
  instagram_media 
GROUP BY 
  time

This will show you the average number of likes and comments in one-hour increments. If you want more granular time data, you can change the SELECT statement to to_char(created_time, ‘HH24:MI’),but depending on how many posts you’re measuring, this might fragment your data more than you’d like. If you want to look by day, try to_char(created_time, ‘YYYY-MM-DD’). Looking by hour by day would just require combining ‘YYYY-MM-DD’ and ‘HH24’.

Engagement rate

Suppose you wanted to calculate the number of likes and comments per follower:

SELECT 
  instagram_media.id, 
  likes_count as "likes", 
  likes / cast(instagram_user.counts_followed_by as float) 
  as "like engagement rate", 
  comments_count as "comments", 
  comments_count / cast(instagram_user.counts_followed_by as float) 
  as "comments engagement rate" 
FROM 
  instagram_media, 
  instagram_user

Engagement by hashtag

Are some of your hashtags getting more likes and comments than others? Here’s how you’d find out:

SELECT 
  value as "hashtag", 
  likes_count as "likes", 
  comments_count as "comments" 
FROM 
  instagram_media 
  JOIN instagram_media_tags ON instagram_media.id = 
  instagram_media_tags.instagram_media_id

You’ll probably want to average the counts by hashtag if you tend to use the same hashtags a lot, though, so you could also do something like this: 

SELECT 
  value as "hashtag", 
  AVG(likes_count) as "average likes", 
  AVG(comments_count) as "average comments" 
FROM 
  instagram_media 
  JOIN instagram_media_tags ON instagram_media.id = 
  instagram_media_tags.instagram_media_id 
GROUP BY 
  value 
ORDER BY 
  "average likes" DESC

Other Ideas

The queries above alone or in combination should provide you with a basic starting toolkit for exploring your Instagram data, but there are tons of other questions you can ask about your Instagram data using modified versions of these queries. Try some of these:

• Using filters? You can see which perform best.
• Try analyzing the text and/or sentiment of your comments and captions to see how they affect engagement
• See whether pictures tagged in certain locations perform better than others (they probably do)
• Track your follower gains and losses over time