How to automatically create Free Instagram Analytics with a Google Sheets Script
Update 20/07/2018: I have now officially “launched” https://viralinsight.io, a hosted (cloud) tool for Instagram analytics using the methods mentioned below.
P.s.: you’ll find a discount code at the bottom of this story ;-).
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).
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
Adain added the possibility to gather the amount of Followings a user has. Bringing our total tracking to:
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:
- 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):
- Total Likes
- Total Comments
- Engagement Ratio
- Followers gained
- 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):
18–05–2018 update: Script was updated to minimise calls and reduce error margin.
- 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 in to 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: Adding automation
Of course, Analytics are 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 5: 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:
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 6: 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 visualisations 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 drop downs 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 7: avoiding troubles and known issues
Due to the timezone you may be in, the Sheet could possible 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.
Bonus: a Viral Insight Discount Coupon
Because of the big popularity of this story, I have decided to build a more “professional” version of this sheet into a cloud based website.
As mentioned in the introduction, the url is https://viralinsight.io. But if you made it this far down the post, I really want to provide you with a bonus. Therefor I’ll gladly offer you the following promo code that will give you a 15% discount FOR LIFE on all plans. If you don’t want to pay that’s fine, you can track 2 accounts totally free. The promocode is:
How would you improve this sheet?
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.