How We Automated Our Social Media Analysis
- Home
- Automation
- How We Automated Our Social Media Analysis
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:
- Analyze the results of organic posts
- Make executive decisions on which content to promote
- Wisely juggle resources and budgets
- Tie your social media strategy to return on investment to please the boss
- Switch back and forth between multiple platforms to grab data and accomplish your tasks
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.
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!
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.
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:
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.
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:
- Conversation: Like comments on your post or replies to your stories
- Amplification: Free publicity like shares, retweets, and saves.
- Applause: Likes, loves, and reactions to your posts.
These metrics show improvement in brand recognition, authority, and content effectiveness. You can use them to make judgment calls and understand:
- Your audience: What kind of content they like and don’t like, both in terms of topics and types (like video, images, infographics, blogs, etc.)
- Your content: What sparks conversations, earns shares, and gets those conversions flowing
- Your overall strategy: Which channels deserve the bulk of your time and how your social media efforts improve over time
- Your impact compared to competitors: Since these metrics are public information, you can see how your strategy stacks up against the Other Guys and develop benchmarks
Setting your goals and baseline
The social media audit template also provides a space for median engagement over the past year:
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:
- What’s their median for post comments, likes, and shares?
- What does a viral or moderately successful post look like? (Those are your outliers.)
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.
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?
- What topics performed best?
- Which types of content did people share and click the most?
- How can you leverage this into your overall business strategy?
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.
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!
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?
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.
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.
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:
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.
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.)
Click the two rectangles and select “Copy data source.”
Rename your file:
Click “Edit Connection:”
This part is critical: Click “Stacking Query” and choose the big blue “Reconnect” button.
You may or may not get a popup like this one. Just click “Apply.”
You’re almost there! Here’s your data:
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!)
For a data source, pick the one you just made:
Choose “Create Report:”
CONGRATS! Your social media audit template is now filled with all the data you need to analyze your entire social presence!
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.
