.png)
I know, I know, I teased a super sweet tutorial of a calculator/statistic process that would help us really understand what is workin on instagram and what isn’t before Christmas time. Here’s the thing, this was my final project for a marketing analytics class for my MS in Marketing degree, I wanted it to be perfect before I submitted it for the class and I REALLY wanted it to be perfect before I published it for you. That means I took into consideration the feedback my professor gave and now…. drum roll please… Here it is.
While this is a super amazing tool that uses your analytics to determine what is working, what isn’t working and to estimate the engagement on future posts, we all know that instagram is wild and honestly does what it wants. BUT hopefully by going through this worksheet that I am giving you fo’ free and the tutorial video that is 12 minutes and 30 seconds long (also fo’ free), you will be able to really understand what elements of your posts or of your blog get you engagement.
You’re smart, so you’re probably thinking now “Kaitlin, I know what post do well and what content buckets on my blog do well because they get more engagement” and I hear you. That is true. But think more minute, think content creation, think “hmmm, I wonder if this shot for this campaign where I am looking at the camera would do better than this shot for the same campaign where I am looking off into the distance like one of those really cool bloggers will do better” - THIS is where I’ve got you.
At this point, you’re probably already on minute 6 in the tutorial video. I don’t blame you, I’m long winded sometimes. Well here I am going to roughly give you the step by step of what you need to do to build your own instagram engagement calculator. So, if you’re already deep in the video, cool, come back to this when you need a refresher or if you’re stuck and wondering what step is next.
1. Make a copy of this google sheet. You don’t have editing access to this document and no, I will not give it to you. Go to file > Make a copy > Name it what you want and save to your Google drive.
2. Feel free to remove the first two sheets, this is the example I went through but also feel free to keep them around incase you want to reference what I did… or scrutinize my engagement (don’t laugh at me, that’s just rude).
Collecting your data - Aka Inventorying your posts
1. In the first sheet you are collecting data - in Column A will be the number of likes, Column B will be the number of comments Column C you will add together the first two columns (if you use the sheet I gave you, I already did this calculation for you, at least until row 100)
2. The next columns are all up to you, in my example I chose things that make up my content or things that I think would align with my brand. These need to be “yes/no” type of categories. So for me I have things like “full body shot” “looking at the camera” “lifestyle” “just me”
For example if you are a home decor blogger you could have things like “DIY” “Living room” “Kitchen” “Human in the shot” “Decor under $100” “referring to a blog post” - these could all be things that you post about, want to post about, are core to your brand. They just need to either be YES this is a DIY post or NO it isn’t. This will make things easier later on.
3. Now as you’re going through your instagram feed or blog posts, note the number of likes, number of comments, then dissect your post. If you can answer YES this post has whatever the column is alluding to, put a 1 in the cell, if the answer is NO, put a 0.
I cataloged 60 of my most recent posts, I recommend you do somewhere around there, the more posts you inventory, the more accurate your calculator will likely be.
Then, as you go forward make a point to add in your posts each week or month so you can re-run your equation and see how things change… because it will… because it’s instagram.
4. WOOOO Now you have a dance party because the hard part is done!!
Creating the calculator/running the analysis
1. Download a “ANOVA” or “Regression analysis” calculator, or they have a bunch you can just do on the web.
Or if you’re fancy and you have excel on your computer and you’ve already done your data collection there then you can just go to “Data analysis” and run a regression analysis or a multi linear analysis
I used a free tool called StatPlus that has a multilinear regression analysis function.
Copy over your engagement column through all of your content columns, including the headers and paste it into your regression analysis tool.
2. Select regression analysis or multilinear analysis
BOOM you did it. Now let’s interpret the results (this is where the video tutorial comes in super handy, I highly recommend you watch it)
The regression analysis equation it will look something like:
# Engagements = 189.57097 - 4.97389 * Full Body - 0.50263 * Just Me + 34.30436 * #ad/#sponsored + 108.78468 * Edits + 24.39513 * Outdoors + 2.60703 * Lifestyle + 8.59921 * Big Smile + 41.29199 * Looking @ the camera - 60.76325 * No face in frame/landscape only + 7.34723 * Travel
3. Don’t freak out.
4. The first number is our “baseline” this is the amount of engagements we can expect on any post
5. From here we will see a number * (times) a quality. This means that the number in front of that quality is the affect on our engagement
Example: -4.97 * full body means that my posts generally get almost 5 fewer engagements when its my whole body in the picture. (I said it in the video, I will say it again… OUCH MY EGO).
Another example: +34.30 & Ad/sponsored, this means my sponsored or paid posts generally get about 34 more engagements. (Thanks for the support gang, love you!)
Now we are going to take this equation and turn it into a calculator… on to the second sheet!!
6. So we are now going to use column A as our labels, so go down this column put “baseline”, then go down the cells inputting the content qualities you used for the analysis
7. Next to the respective quality in column B you’re going to put the affect that the equation gave you (-4.97 or +34.30 if we are talking about the above).
In column c, you’re going to leave this blank, this is where we will fill in 1’s and 0’s for future posts.
8. Now to do some #math. Again, watch the video for this, it helps.
In Column D you will multiply the cells in Column B and C - so your affect and your 1 (yes) or 0 No
Except for the first row which is the baseline, this is just what it is, so bring it on over
At the bottom of this column is your total engagement cell, in this cell, sum up all the outcomes
9. If all was done right, you should now have your calculator. Now you can go in and give 1’s and 0’s for potential posts and the calculator will spit out an estimated engagement rate.
Wow… look what we’ve done. If you’ve done all the steps and something doesn’t look right, feel free to reach out to me and share your doc, I would love to help troubleshoot with you.
If you’ve watched the video or (worse) read through this without watching the video and have no idea what do to do, or you think this sounds cool but like you don’t want to do it, you can also reach out to me and we can talk about how I might be able to build the calculator for you. To be noted with this route: I already gave you the tools and calculator fo’ free, so beyond this point… money talks.
I know that this nerdy numbers and analytics stuff isn’t for everyone but dang, this is the stuff I love, so it was fun getting to apply the stats world to the beautiful aesthetic world of instagram and blogging. If you end up doing this, please share your results with me or your feedback. I would love to hear it all! Thanks so much!
