How We Automated Our Social Media Analysis

social media audit dashboard

David Krevitt

Lover of laziness, connoisseur of lean-back capitalism. Potentially the #1 user of Google Sheets in the world.

Your life as a social media marketer relies on striking the perfect balance. On any given day, you might need to:

That last point – switch back and forth between multiple platforms to grab data – is the real struggle, right?

Native dashboards in LinkedIn, Twitter, Facebook, etc are pretty, but they operate in a silo.

To be truly effective as a social media marketer we need cross-channel visibility.

That’s why we built this dashboard.

It serves as a social media audit and cross-channel report for clients.

social media dashboard

We built this template with Justin Thomas of JourneyEngine. He’s kindly opened up this template for public use.

Grab the ‘social media analysis’ workbook through the link below.

We’re not asking for your email address because I know that’s annoying. But if you do get value from this post, I’m hoping you will check out my YouTube Channel where I have even better tutorials.

Let’s get into it!

What’s in the social media dashboard?

The idea behind this social media audit template is to put all your analytics in one convenient place. You’ll no longer have to pan back and forth between YouTube, Facebook, Instagram, Twitter, and LinkedIn to analyze your pages’ results.

Not only do you get aggregate metrics, but you also get a space for post-level data.

A screenshot of a cell phone Description automatically generated

The best part? The sheet auto-refreshes data once daily, marking the end of “pulling” social wall post data. Permanently.

Pretty sweet, eh?

So your cross-channel template has four main components.

Picking your metrics

What would an audit template be without metrics?

Before picking your metrics, have a look at what they look like inside the template:

A screenshot of a cell phone Description automatically generated

Major hat tip to Avinash Kaushik at Occam’s Razor for the metrics we’ll use here.

Have you heard about vanity muscles? Some dudes only work out their glamour muscles – like biceps – and neglect other important muscle groups. As Avinash Kaushik puts it, too many social media marketers focus on their vanity metrics.

Vanity metrics give you a nice ego boost, but they don’t deliver any tangible benefit to your brand. These include stuff like your reach, impressions, and 3-second views.

A screenshot of a cell phone Description automatically generated

What good are 10k 3-second video views if no one is even watching long enough to see your CTA? You can’t use these metrics to make business decisions. And you certainly can’t use these metrics to prove ROI to your boss.

Instead, focus on metrics that provide the most insight into your social media strategy’s value to your bottom line. Avinash Kaushik recommends sticking with metrics like:

A screenshot of a cell phone Description automatically generated

These metrics show improvement in brand recognition, authority, and content effectiveness. You can use them to make judgment calls and understand:

Setting your goals and baseline

The social media audit template also provides a space for median engagement over the past year:

A screenshot of a cell phone Description automatically generated

This section comes in handy to establish a baseline for judging your social media strategy over a certain period.

Well, to do that, you need a baseline to compare your data to. Ideally, you want to meet or surpass the baseline.

Of course, the goal is always to improve and accumulate as much engagement as possible. However, you also want to keep your goals realistic and achievable. The easiest way is to look at ye olde metrics over a specific timeframe.

Let’s say you’re just getting started with a new client, for example. You might ask them (or yourself) questions like:

By nailing down those median figures, you can judge your progress along the way and prove the ROI for your clients.

Catching the outliers

Now you need to figure out a strategy for tracking your outlier posts. These are the crème de la crème of your engagement: The top 5% of your posts.

To define what these outliers look like for your brand, you can use the calculated fields feature in Data Studio. You’ll want to create a metric two standard deviations away from the mean.

A screenshot of a cell phone Description automatically generated

By creating this, you’ll have an “upper fence” to manage your high-performing posts or outliers. Thinking back to the bell curve in intro to statistics class, anything that falls above this figure is classified as the top 5% of your posts for that year.

Once you have your outliers, look for posts that fit this category in terms of engagement.

What conclusions can you draw from these superstars?

As a social media marketer or analyst, you’re no doubt chronically suffocated by data. Focusing on THESE posts can help you budget your time and energy.

Single-sheet social channel comparison

Finally, your social media audit template also contains a “select platform” feature. Using this allows you to filter your data by platform, such as Facebook or Instagram. Look at each platform individually or go nuts and analyze your best posts across all platforms!

A screenshot of a social media post Description automatically generated

This is what makes our social media audit template so special: the cross-platform functionality. Instead of looking at each platform, you’ve got all the metrics you need right in front of you.

It provides comprehensive insight into your social media strategy as a whole. Plus, it saves a ton of time. Who doesn’t love that?

Setup your own social media dashboard

Now that you understand the basic components of our cross-platform social media audit template, you can start making your own.

 

Step 1: Copy the tempkate

Before anything else, you’ll need to download the template. Click file, select “make a copy,” and save the sheet to your own Google Drive.

A screenshot of a cell phone Description automatically generated

This will be your base file. Once you save that Google Sheet, you can continuously make fresh copies of the template for each client. The idea is that you’ll start a new social media audit template every time you take on a new client or run a yearly audit.

 

Step 2: Install Supermetrics 

To run and access all the full features of your social media audit template, you’ll have to install and run an add-on called Supermetrics.

Trust me, it’s worth it. If you don’t have Supermetrics yet, you absolutely need it. Supermetrics is a game changer for social media analysts and marketers because it grabs data from all your apps and platforms.

A screenshot of a cell phone Description automatically generated

Yes, Supermetrics can pull data from all your social media platforms instantly – Facebook, Twitter, Instagram, YouTube, and so on – and place it into Google Sheets on an automated schedule. However, it also integrates with other platforms like your CRM software, ad platforms, analytics tools, email service providers, and more.

See? True game changer.

So go grab Supermetrics now (take advantage of that free trial).

Got it? Alright great. Let’s move on.

 

Step 3: Duplicate the Sheet for all social media platforms

You’ll now need to duplicate the social media audit template for Facebook and Instagram. Click “Add-ons” and select “Supermetrics” to launch it.

Great, now that you have Supermetrics officially running, you’ll tab over to “Supermetrics” again and open the menu to choose “Duplicate this file for another account.”

Here’s a picture to explain:

A screenshot of a social media post Description automatically generated

Once you do this, Supermetrics will give you a popup that allows you to select your Facebook Insights account. From here, you can pick all the social media accounts you want to analyze.

Supermetrics will give you the choice of picking Facebook, Twitter, Instagram, LinkedIn, YouTube, yada yada.

Keep in mind you’ll need to be logged in or have access to these social media accounts to pull the data. So go ahead and log into them now if you haven’t already.

For some platforms, your brand might not have accounts. That’s fine. Just click “Next” to skip the data source. When you do this, Supermetrics might give you a warning message but you can just keep clicking “Next” to move through the motions selecting your client’s active social media accounts.

You do, however, need at least one data source to run a Supermetrics report for your social media audit template.

Finally, you’ll click “Duplicate this file.”

I can hear you right now: “What’s going on? Why am I copying things so many times?”

When you click “Duplicate this file” after setting up your accounts, Supermetrics makes a new Google Sheet so you’ll ALSO have a master copy of the social media audit template on your Google Drive.

After you have all your client’s social media accounts connected AND you’ve duplicated the sheet, Supermetrics will refresh and process all the data in your base file. Grab a cup of coffee and get comfortable because this can take a while.

A screenshot of a cell phone Description automatically generated

Is it done? Awesome! Click “here” and it will take you to a new file. Feel free to rename the file and remove “copy of copy of” as you see fit.

Congratulations! You now have a full set of data from all your client’s social media accounts. Not so fast though – they’re all in separate tabs for each platform. To visualize and analyze your social media presence, you’ll need to merge the data.

See the sixth tab at the end called “Stacking Query?” That’s the good stuff where you’ll find all posts for all platforms when we finish Step 5.

 

Step 4: Copy the Data Studio Data source

Open your 2020 Cross Channel Social | Data Input sheet to access the Data Studio Data Source.

(Yeah, it’s a lot of data.)

A screenshot of a cell phone Description automatically generated

Click the two rectangles and select “Copy data source.”

A screenshot of a cell phone Description automatically generated

Rename your file:

Click “Edit Connection:”

A screenshot of a cell phone Description automatically generated

This part is critical: Click “Stacking Query” and choose the big blue “Reconnect” button.

A screenshot of a cell phone Description automatically generated

You may or may not get a popup like this one. Just click “Apply.”

A screenshot of a cell phone Description automatically generated

You’re almost there! Here’s your data:

A screenshot of a cell phone Description automatically generated

 

Step 5: Copy the Data Studio report template

Click your 2020 Cross-Channel Social | Data Input sheet and make a copy of it. (Yes, more copies!)

A screenshot of a social media post Description automatically generated

For a data source, pick the one you just made:

A screenshot of a social media post Description automatically generated

Choose “Create Report:”

A screenshot of a social media post Description automatically generated

CONGRATS! Your social media audit template is now filled with all the data you need to analyze your entire social presence!

A screenshot of a computer Description automatically generated

Bask in the Glory of Your Cross-Channel Social Media Audit Template and Data

The days of individually pulling social media analytics and engagement from every platform are no more.

You can now spend more time analyzing and less time grabbing data.

Keep in mind this is only a tiny fraction of what you can accomplish with Google Sheets + Supermetrics + Data Studio. Play around and get comfortable with the features to make your life a little easier.

Grab the ‘social media analysis’ workbook through the link below.

We’re not asking for your email address because I know that’s annoying. But if you do get value from this post, I’m hoping you will check out my YouTube Channel where I have even better tutorials.

Ready to build your pipeline?

My name is David, and I help companies automate their data analysis in BigQuery. I’m standing by to chat about how we can help you get more done.

FIND OUT MORE