How to conduct the ultimate social media audit [template included]

As a social media marketer or agency, your job depends on your ability to tie social activity back to business results. You need to be able to analyze which organic posts do well, which to promote, and how to balance your time and investment in each channel.

After all, you want to spend your resources wisely 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 each individual social platform provides and manually take a look at your Facebook, Instagram, YouTube, Twitter, and LinkedIn company pages.

But what if you want to compare Facebook and Instagram campaigns together in one dataset? Or what if you want to see how YouTube stacks up against LinkedIn? In this multi-channel world, you need an easier way to really dig into relative return on ad spend between the platforms. This post will tell you how to easily pull the information you need and consolidate your results, putting it all on a single, customizable dashboard.

To help you do that, we’ve built a Google Sheets and Data Studio template using Supermetrics.

For context

The team at JourneyEngine built this dashboard as part of a larger data pipeline to help B2B social media marketing agency, Sculpt, scale their data analytics service to 50+ clients, even with a lean analytics team.

Leveraging Supermetrics, Google Sheets, and Google BigQuery has enabled them to compete with much larger agencies, even with a lean analytics team. 

If you work on higher-level branding, or in an agency that does a social media analysis on either Facebook, Instagram, YouTube, Twitter, and/or LinkedIn company pages, this Data Studio template is for you.

Conducting your social media audit

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

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

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:

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 any action.

Impressions, reach, 1-second video views…

These metrics may make you feel better as a marketer, but let’s face it, they don’t move the needle.

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

These metrics enable you to take real action based on your wall post engagement data. A strong focus on them will: 

• Give you an idea of which content pieces in your content mix are hitting the mark with your audience, from a branding perspective.
• Show you the content that makes your audience want to join your brand’s conversation, put their stamp of approval on it and share it with their friends.
• Visualize the real social impact of each of your channels, side by side, to help you make decisions about where to spend your time.

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 12 months:

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?

Spotting outliers

To define outliers, we’re using the calculated fields feature in Data Studio to create a metric for 2 standard deviations away from the mean.

This calculation gives us an “upper fence”. In a normal bell curve, anything greater than this number is within the top 5% of your posts for the past year.

Media

Look for posts in the data set that exceed your conversation, amplification and applause outliers. What do you think made those posts particularly good at drawing a conversation? What appeal made them so share-worthy? How can the business leverage this in their post strategy going forward?

Focusing on this segment of posts will help cut the noise from your analysis and keep your insights juicy!

Social channel 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. 

So instead of having the data from each platform on separate tables, this allows you to stack the data from Facebook, Instagram, YouTube, Twitter, and LinkedIn 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. 

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.

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

To run this template, you’ll first need to install Supermetrics for Google Sheets. This add-on allows you to pull data from all of your favorite social channels, ad platforms, email software, CRM’s and more, directly into Google Sheets, on an automated schedule. 

Just trust me when I say it’s a marketer’s dream. There is a free trial, so there really is no downside. Go grab it now, before you forget. I’ll wait. ☕️

Got it? Great! Let’s proceed…

Step 3: Duplicate this sheet for your 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 Supermetrics. 

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

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

• Select the brand’s Facebook, Instagram, YouTube, LinkedIn, and Twitter Accounts you want to create the report for. (Note: if you’re not logged in, it will ask you to log in first). If, for example, you don’t have a LinkedIn account to pull data from, you can simply skip the data source by clicking “Next”. If you see a warning message, continue clicking “Next” until the new data source account selection appears. You need to connect to at least 1 data source for the template to work. 
• Finally, click “Duplicate this file”.

What it’s doing now is actually creating a new Google Sheet, a copy of the main master sheet that will be stored on your Google Drive.

Once the template is copied successfully and the accounts are connected successfully, it will refresh all of the Supermetrics queries that are in the base file. This can take some time…

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 2020 Cross-Channel Social | Data Input | 32 Mo” You’ll notice it has the new brand name at the end. Feel free to delete “Copy of Copy of”

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

Note that I have six separate queries here from Supermetrics:

• A Facebook posts insights query
• An Instagram insights query
• A LinkedIn company page query
• A Twitter posts query
• A YouTube posts query
• And a sixth tab is a Stacking query where I use the query function to basically stack the each of the social post tabs on top of each other.

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

The “Stacking Query” tab, the one that’s most important to us, is the one that puts all the social posts in one table. You can scroll down and confirm that they are all there.

Step 4: Make a copy of the Data Studio data source

Now, head back to the 2020 Cross Channel Social | Data Input sheet, to step #4, where you’ll navigate to the Data Studio Data Source.

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”:

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

Then click “Edit Connection”:

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.

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

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

Step 5: Make a copy of the Data Studio report template

Click the link on the 2020 Cross-Channel Social | 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”: 

Select the same data source you just created:

Click “Create report”:

And BOOM! Here you are, your complete Cross-Channel Social Wall Post Dashboard:

Make it your own

If you haven’t already, you can make a copy of the Sheets + Data Studio template here.

You now have a beautifully joined visualization of your Facebook, Instagram, Twitter, YouTube and LinkedIn company page 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 individual social engagement post data manually ever again, which can reduce carpal tunnel syndrome quite a bit. ?

This is just scraping the surface of what you can do with Google Sheets, Data Studio, and Supermetrics. I hope you enjoy the template and can pick up some analytical momentum with it.

You can test the template with a free 14-day trial of Supermetrics.