• Home
  • Blog
  • How Tos
  • Super Easy Press Trip Reporting for Instagram with Google Sheets

Super Easy Press Trip Reporting for Instagram with Google Sheets

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.

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).

Google Sheets Instagram data

You can view and make your own copy of the spreadsheet right here. There’s also some more awesome formulas and ways to get data from different platforms on Ben Collins’s blog… If you are keen to dig a bit deeper!

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.

TRAVERSE TEAM

4 Comments

  • This is such a useful spreadsheet! I know what I'll be doing for the rest of the day :) thanks for sharing!
    • Awesome! Glad it's useful!
  • Why are the comments off by one?

Leave a comment