So you’ve just finished a big campaign, your posts on Instagram have some great engagement. Time to grab a cup of tea and put your feet up right?
Well, no… time to get your own social media reporting in order!
Brands and PR’s are always impressed if bloggers/Instagrammers are prepared with the right information when asked for campaign stats and even more so when they haven’t asked!
If you’re on the ball you can even set this up before you start a campaign and update it as you go to minimise the work needed when you get home (take a look at this great post on getting work done on a press trip by Monica from The Travel Hack).
Collecting stats manually can take ages, going through all your posts and taking down likes and comments. If you don’t have enough cash to splash on a fancy bit social media analytics software, there is an easier way to gather your basic but ever changing, dynamic stats from your Instagram account.
Here’s how you do it!
There’s a lot of information you can gleam from meta tags on web pages, so we’ll be using some really simple formulas in Google Sheets to pull the information. The beauty of this is that once you’ve set it up the Google Sheet will automatically grab the latests stats, so if your likes or comments increase you’ll have the most up to date information.
If you’re not technical or have never worked with formulas etc, don’t worry you can get by with a bit of cut and pasting.
Start with a new Google Sheet, create it.
Instagram Post Data
Firstly, paste your first Instagram post URL into cell A2 if you have headings across row 1.
See screenshot: Grab the Instagram posts URL from your browsers address bar and paste it into cell A2 of your Google Sheet.
Grab the meta data description
So first up you can grab some meta data from your Instagram post page which contains the likes and comment counts. Paste the following formula into the formula field for cell B2. Once you click off the cell you should see the field populated with the meta description field.
=IMPORTXML(A2,”//meta[@name=’description’]/@content”)
Extract the Likes
Once you have the meta description in cell B2 you can use regexextract to find and extract the Likes. Copy and paste the following into cell C2.
=iferror(IFS(REGEXMATCH(B2,”m Likes”),value(regexextract(B2,”(\w+.?.?)(?:m)”))*1000000,REGEXMATCH(B2,”k Likes”),value(regexextract(B2,”(\w+.?.?)(?:k)”))*1000,REGEXMATCH(B2,” Likes”),value(regexextract(B2,”\w+.?[0-9]?[0-9]?[0-9]?”)),B2>1,B2),”Error”)
Extract the Comments
Secondly, use the following formula to find and extract the comment count. Copy and paste the following formula into the formula field for cell C3.
=iferror( if(regexmatch(B2,”k Comments”),value(regexextract(B2,”Likes, ([0-9]+\W?[0-9]?)(?:k?) C”))*1000,value(regexextract(B2,”Likes, (\w+.?[0-9]?[0-9]?[0-9]?) C”))),”Error”
Then you’re all set, you can start adding your Instagram posts as you publish them or in one go once the trips over. You can download the spreadsheet or keep it live and updating whilst you share it with your client (brand or PR).
Download your own Google Sheets workbook
UPDATE October 2020: Unfortunately Instagram has updated and doesn’t now reveal about likes and comments, so we are unable to use these formulas for reporting.
Hope this was helpful to some of you, any feedback just leave me a comment below!
Paul is co-founder/director at Traverse and also a long term travel blogger at www.travmonkey.com. His new project is all about learning to surf and leave the stresses of the city behind at www.surfandunwind.com as well as his new project crowdsourcing airport information indepartures.com.
6 Comments