Automated Facebook and Instagram Organic Dashboard

With Google Sheets, Data Studio and Supermetrics

I’d like you all to meet Justin Thomas, an acolyte of the Lazy Way and marketer over at JourneyEngine.

He’s been building pretty slick Google Sheets templates to power his business, so I’m happy to have him here to share them with you.

This template’s a clean and simple Data Studio dashboard for social media marketers - hope you enjoy it!

Take it away Justin…

– With love, The Commissioner

Why I Automated my Organic Social Analysis

As a social media marketer you need to be able to analyze which organic posts do well, which to promote, and how much money to spend on them.

After all, you want to spend your budget smartly and only put money behind content that actually has an impact, especially when you post content daily or multiple times a day.

How do you get the information you need to make those decisions?

You can use the native dashboards that Facebook and Instagram Insights provide.

But what if you want to compare FB + Instagram campaigns together in one dataset? To really dig into relative return on spend between the two platforms, you’ll want to join them up and look at consolidated results.

To help you do that, we’ve built a Google Sheets + Data Studio template modeled on the Lazy Way - it allows you to automate the daily process of joining together organic Facebook and Instagram results.

If you’re ready to dive straight into the template, pick up your copy on the CIFL Template Vault on Trello.

Read on to get set up with the FB + IG Wall Post Dashboard.

For Context…

In my growth marketing work, we model the customer journey into six stages, from cold prospect to buyer to advocate:

image alt text

This dashboard fits into Stage 1 - the “SEE” stage, where you first meet your customer, make an impression and (hopefully) create an initial positive sentiment.

If you work on higher-level branding and do a high volume of social media posting on Facebook and Instagram, this Data Studio template is for you.

Diving into the Dashboard

The template’s goal is to provide all the information you need at a glance to make decisions for the Facebook and Instagram pages you manage.

It provides some aggregate metrics, as well as a post-level table for deeper dives:

image alt text

Let’s dive into the components…

The Metrics

The star of the dashboard is, of course, the ability to quickly take a look at the metrics. Here’s what that looks like:

image alt text

Here I have to give a big shout out to Avinash Kaushik at Occam’s Razor, because the metrics we focus on come directly from his blog (one of the best blogs around on data analytics and marketing).

As he explains it, a lot of marketers focus on Vanity Metrics, which look impressive but don’t necessarily drive sales.

Impressions, reach, likes, 1-second video views - these “engagement” metrics don’t really move the needle.

image alt text

The problem is, you can’t make business decisions based on those metrics.

So instead, we focus on the key social media metrics Avinash Kaushik recommends:

  • Conversation: comments per post

  • Amplification: shares per post

  • Applause: likes or reactions per post

image alt text

These metrics enable you to make real decisions based on relevant engagement data. A strong focus on them will:

  • Give you an indication whether or not people are aware, interested, and ready for the next stage of the journey.

  • Show you how much value each of your individual ad sets, campaigns or/and ads contribute.

  • Give you an idea of which content pieces in your content mix are hitting the mark with your audience from a branding perspective.

Another advantage of these metrics is that they’re public information, so you can easily compare your content strategy with any competitor and benchmark on your individual competitive space.

Using the Median As A Baseline

Another feature on the dashboard is a section for the median engagement, as well as outlier data for the last 6 months:

image alt text

When you analyze social media metrics, it’s important to set a baseline as a goal to achieve and surpass.

This baseline is determined from the level of engagement you’ve received in the past - to find it, we use the median of the reactions for a certain time period.

For example, when starting to work with a client, I first want to answer two questions:

  1. What’s the baseline (median) for post comments, shares and likes?

  2. What does an outlier (successful post) look like?

Including these median baselines in the Data Studio template makes these easy to answer.

Facebook To Instagram Comparison In One Sheet

Finally, one of the main features of the dashboard is the “Select Platform” option where you can filter for either Facebook or Instagram individually, or both at the same time.

image alt text

This allows you to stack the data from Facebook and Instagram on top of each other so you can compare within your overall content mix which content is hitting the mark and how it compares to other posts.

So instead of having the data from Facebook and Instagram on two separate tables, I needed a way to combine it into one and this fits the bill perfectly

Dashboard Set-Up

Now that you have some background into why it’s set up this way, let’s dive into how to set it up.

Step 1: Copy This Google Sheet

So the first thing you’re going to do is make a copy of this Google sheet, so click File—> “Make a Copy”. Name it whatever you’d like.

image alt text

This new sheet you’ve copied is going to be your base file. Use this as a starting point whenever you onboard a new client or whenever you want to make the automation in the report for a new account.

Step 2: Run Supermetrics

If you haven’t downloaded Supermetrics yet, you’re definitely going to need that in your toolbox, it’s one of the best software products I’ve downloaded in my career. I highly recommend it. There is a free trail, so there really is no downside.

image alt text

Step 3: Duplicate This Sheet for Facebook & Instagram Accounts

Next, you need to duplicate this sheet for your Facebook and Instagram accounts. We’re going to do that by clicking “Add Ons” —> “SuperMetrics” (launch it first).

Once Supermetrics is running, then go to “Supermetrics” —> “Duplicate this file for another account”.

image alt text

From there a box will pop up that will allow you to choose your Facebook insights account:

  • Choose the Facebook insights account you want to create the report for (if you’re not logged in, it will ask you to log in first).

  • Select the same brand’s Instagram account.

  • Finally, click “Duplicate this file”.

image alt text

What it’s doing now is actually creating a new Google sheet, like a spoke off the main hub sheet that you’ll have.

Once the files have duplicated successfully, it will refresh all of the Supermetrics queries that are in the base file. This can take some time…

image alt text

Once the refresh is complete, click “here” to access your brand new file.

Now, as you can see, the new file is named “Copy of Copy of FB/IG |Data Input | 32 Mo - Wheel of Fortune.” You’ll notice it has the new brand name at the end. Feel free to delete “Copy of Copy of”.

image alt text

Now you have your new data set for the new brand.

Note that I have three separate queries here from Supermetrics:

  • A Facebook posts insights query

  • An Instagram insights query

  • And a third tab is a Stacking query where I use the query function to basically stack the Facebook and Instagram post tabs on top of each other.

image alt text

So if you look under “Facebook Posts” you can see that all of the Facebook post data has populated correctly and same thing under “Instagram Posts”.

The “Stacking Query” tab, the one that’s most important to us, is the one that puts Facebook on top of the Instagram table. You can scroll down and confirm that they are both there.

And… it’s beautiful…

image alt text

Step 4: Make a Copy of The Data Studio Data Source

Now, head back to the FB/IG | Data Input sheet, to step #4, where you’ll navigate to the Data Studio Data Source.

image alt text

Make a copy of the data source by clicking on the two rectangular icons in the top right corner, and then choose “Copy data source”:

image alt text

Once that’s done copying, you can rename the file for the brand you’re working on:

image alt text

Then click “Edit Connection”:

image alt text

In the list you can see the name of the data sheet you just created. Now this is very important, you need to select “Stacking Query” and then select the blue “Reconnect” button in the top right corner.

image alt text

If you get a pop up like the one below, that’s ok, just click “Apply”:

image alt text

Now you have your new data source all set and ready to go:

image alt text

Step #5: Make a Copy of the Data Studio Report Template

image alt text

Click the link on the FB/IG | Data Input sheet under #5. Once that’s open, you’ll make a copy of the report. Click on the rectangles again and then choose the correct name under “New Data Source”:

image alt text

Select the same data source you just created:

image alt text

Click “Create Report”:

image alt text

And BOOM! Here you are, your complete Facebook and Instagram Wall Post Dashboard:

image alt text

Make it your own

If you haven’t already, you can make a copy of the Sheets + Data Studio template from the CIFL Template Vault on Trello.

You now have a beautiful visualization of your Facebook and Instagram post data - all of your engagement in one place, including links to each post.

The best part of this is that you’ll never, ever have to pull Instagram or Facebook engagement post data manually ever again, which reduces my carpal tunnel syndrome quite a bit.

This is just scraping the surface of the Lazy Way, but I hope you enjoy the template and can pick up some traction with it.