How to automatically create Free Instagram Analytics with a Google Sheets Script
As someone who is very active with all things Instagram, tracking accounts is one of those things that you need to do, but often don’t want to (or have the time to).
Last updated on 22/02/2021 to fix Instagram issues (again :) ).
Part 1: How the Script came to be
As I’m a real ‘nerd’ when it comes to using Google Sheets and Excel (seriously, I often times create excel sheets and use formulas to calculate even the most easy things, over using a calculator) I recently stumbled upon this post by Nick Boyce on how to track your Instagram followers over time with Google Sheets I was immediately amused.
The post by Nick showed us how to create a Google Sheets that tracked 2 basic things:
- The amount of Followers an instagram user has
- The amount of Posts an Instagram user has
This was a good start, but it felt like his method had a lot more potential. After implementing Nick’s method I stumbled on another Medium user who had his swing at Instagram Analytics combined with Google Sheets: Aidan Heron with his post: How to Track Instagram Metrics For Free
Aidan added the possibility to gather the amount of Followings a user has. Bringing our total tracking to:
- Followers
- Followings
- Posts
But Aidan Heron’s post brought up something interest, being the url where he gathered the data from:
https://www.instagram.com/yourHandle/?__a=1 (replace “yourHandle” with your username to see some funky stuff).
The ?__a=1 doesn’t work anymore after the april changes to the API/Endpoint, however you can just right click the page on Instagram and view the source. The data is still there!
What at first looks like just a random bunch of text, actually shows data from your most recent 12 posts, and includes the amount of likes and comments a particular post received! This is exactly what I was missing from both Nick’s and Aidan’s posts, the possibility to measure the engagement received!
After toying around with additional scripting (together with my good Web Developer Friend, Wim) and managed to get all data structured and were able to properly format it to use within Google Sheets.
Part 2: available data
After some fiddling around you can now efficiently pull the following data from Instagram to your Google Sheets:
- Followers
- Followings
- Posts
- Total amount of Likes (of the last 12 posts)
- Total amount of Comments (of the last 12 posts)
I added in additional scripting to calculate the engagement ratio, and a bonus formula to calculate the daily followers one has gained. In summary: this script turns a regular plain old Google Sheets into a free Instagram Analytics tool.
Part 3: Let’s get to work!
Now that we have covered how this Script came to be, and what data it can pull, it’s time to get down to work and build the Google Sheet!
- Open up a new Google Sheet and add the following headings to the columns on the first row (1):
- Date
-Followers
-Followers gained
- Following
- Following gained
- Media
- Total Likes
- Total Comments
- Engagement Ratio
- Engagement ratio increased
- Next, in the menu open Tools -> Script
- Copy the script and change two variables:
- sheetName (change the values between the “”, this should be the name of the sheet you want to import the data to)
- instagramAccountName (this should be the user you want to collect data for)
Make sure the sheet’s name is 100% correct with the tab’s name (at the bottom of the sheet):
Part 3b: getting the cookie
Due to changes in February of 2021, we now also need to update the cookie and send that with the data request to Instagram.
These cookies expire, so it’s very likely that you will have to redo the procedure below from time to time.
To get the cookie data, do the following:
- Open Google Chrome, and press F12 to go to the developer tools.
- Click on the Network tab:
3. Now go to instagram.com. You’ll see a lot of data loading up in the network tab.
4. Locate the event that starts with “?query_hash=” and click on it. On the right, you will now have to click on the headers, and look for the “cookie” value under the request headers.
5. Copy the whole value and paste it in the code above, next to the ‘cookie’ value, in between the brackets:
- Save the script (you’ll be prompted to enter a name).
- Open Run -> Run Function -> insertFollowerCount
Because of the limitations of this method, there is no historical data available. However, importing everything into Google Sheets will gradually build up the historical data for you. Just give it some time! :).
If you now switch back to the Sheet, you should see something like this:
Part 4: Tracking multiple accounts
For those of you looking to track multiple accounts, replace the entire code with the following code:
function MultipleAccounts() {
//Set up multiple sheets and instagram accounts
insertFollowerCount ("sheetName1", "25945306");
insertFollowerCount ("sheetName2", "232192182");
}
var instagram_base_url = "https://www.instagram.com/graphql/query/";function insertFollowerCount(sheetName, user_id) {
Logger.log(user_id)
Logger.log(sheetName)
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(sheetName);
Logger.log(sheet)
Logger.log(ss)var followers = getFollowers(user_id);
var following = getFollowing(user_id);
var medias = getMedias(user_id);
var engagement = getEngagement(medias, followers);sheet.appendRow([Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd"), followers, following, medias.count, engagement.totalLikes, engagement.totalComments, engagement.EngagementRatio]);
};function getFollowers(user_id) {
return parseInt(fetch(instagram_base_url + "?query_hash=37479f2b8209594dde7facb0d904896a&variables=%7B%22id%22%3A%22" + user_id + "%22%2C%22first%22%3A24%7D")['data']['user']['edge_followed_by']['count']);
}function getFollowing(user_id) {
return parseInt(fetch(instagram_base_url + "?query_hash=58712303d941c6855d4e888c5f0cd22f&variables=%7B%22id%22%3A%22" + user_id + "%22%2C%22first%22%3A24%7D")['data']['user']['edge_follow']['count']);
}function getMedias(user_id) {
return fetch(instagram_base_url + "?query_hash=f2405b236d85e8296cf30347c9f08c2a&variables=%7B%22id%22%3A%22" + user_id + "%22%2C%22first%22%3A12%7D")['data']['user']['edge_owner_to_timeline_media'];
}function getEngagement(medias, followers) {
var totalComments = 0,
totalLikes = 0;
for (var i = 0; i < 12; i++) {
totalComments += parseInt(medias.edges[i].node.edge_media_to_comment.count);
};
for (var l = 0; l < 12; l++) {
totalLikes += parseInt(medias.edges[l].node.edge_media_preview_like.count);
};
var engagementRatio = (((totalLikes + totalComments)) / followers) / 12;
return {
mediaCount: parseInt(medias.count),
totalComments: totalComments,
totalLikes: totalLikes,
EngagementRatio: engagementRatio
}
}function fetch(url) {
var ignoreError = {
"muteHttpExcecptions": true
};
var source = UrlFetchApp.fetch(url, ignoreError).getContentText();
var data = JSON.parse(source);
return data;
}
Afterwards, run (or set your trigger to:) “MultipleAccounts” (instead of InsertData).
Part 5: Adding automation
Of course, Analytics is only as good as the regularity by which they are collected. Thanks to Google we can fully automate the execution of this script.
- Click on Edit -> Current project’s triggers
- Click on “no triggers set up. Click here to add one now” and choose a sequence for the script to collect the data. Personally, I prefer to automatically pull data every day from Midnight to 1 am:
- Click Save to exit the pop-up.
There! Now you’re done, every day the script will pull the newest data from your profile to your Sheet.
Part 6: Calculating Daily Growth
While tracking all the above metrics is a very good start, I also like to know how many new followers I gained in the last 24 hours.
In order to calculate this I added the following formula to H3:
=if(B3<>””,B3-B2,””)
Note: komma’s and quote signs might vary depending on your local settings in Google Sheets.
This if formula will check if there is a value within the Followers column. If it is not blank, it will take the value in the cell above, and deduct the new value. If there is no new value in the Followers column, the cell will be left blank (and that’s useful if you create charts with this data, we don’t want to see 0’s, we will want blanks then!)
You can now copy down this formula to the bottom of the sheet, it will automatically calculate the new daily gains every time a new line of data is added.
Part 7: Visualising
Data is all good, but now you should create another tab within this Sheet and create charts using this data. As the Sheet fills up and we get more and more data every day, we’ll want to use visualizations to be able to see trends and changes within the blink of an eye!
Toy around with bar charts, trend lines,… see what works for you and provides the best insights the fastest!
Alternative visualization: Google Data Studio
Putting a lot of chart inside Google Sheets will lower it’s performance when opening the spreadsheet itself. However, you can use the Sheet itself as a data connector inside Google Data Studio. It’s really awesome and free!
The benefit of using Google Data Studio is that you can easily create dropdowns for date periods and select months, weeks, days… You can also embed the reports inside of your own website (even works inside Canva files! ).
Part 8: avoiding troubles and known issues
Due to the timezone, you may be in, the Sheet could possibly pull data multiple times a day, even when you’ve set it to only do it once.
In order to avoid this issue, you need to put your Sheet in the UK time zone (if you check the script it’s using the GMT time zone, although you could probably change this). Alternatively, if you’ve set another timezone in the script, make sure the sheet settings are matching!
- Go to File -> Spreadsheet Settings and make sure it’s in the UK time zone.
Error: typeError: Cannot read property “node” from undefined. (line 31, file “Code”)
Although the script will indicate this on another line, the main cause of this issue is either the username that’s incorrect (line 4), or that the script can’t retrieve enough data (this would be the case when your account has less than 12 posts for example).
Error: 429 truncated server
This is mainly because Instagram is refusing the connection (due to overuse, speed…), and occasionally this will make the script fail. The only ‘real’ solution I found is to try again a few minutes later.
Bonus: Compare with your competitors
Since there is no Instagram login information required to gather this data, so that also means that you can track your competitors.
To do so, just create a new tab within the Google Sheet. Install the script, and adjust the sheetName and instagramAccountName.
You can then also add them to your visuals and see how you stack up compared to your competitors.
How would you improve this sheet?
If you’ve made it this far down the post, I want to thank you! I also want to thank Nick Boyce and Aidan Heron for getting me on the way.
Now comes the inevitable question: How would you further improve this sheet? What’s lacking? Are you adding in more formulas to calculate metrics? Please let me know! I’m dying to find out!
If you need help with implementing it, please don’t hesitate to reach out to me through my website.