A dashboard to categorize all your social posts to know exactly what works and doesn’t7 min time well spent

7 min time well spent

You’re going to learn about a neat dashboard with which you categorize social media posts. With this dashboard you will have a direct overview of your posts and you can see what kind of content really resonates with your followers.

This goes beyond the typical ‘video vs image’ or ‘normal post vs link post’. This is tailored to what works best for your social media content.

Here’s an example of what that would look like:

This table is sorted on ‘Media reach’, which is 100% organic. Thanks to this dashboard, we found out that certain types of post consistently had twice the amount of organic reach. Furthermore, one of the most time consuming type of content we produced was always underperforming. We clearly had a lot to figure out after seeing these numbers. 

In this article you’ll find out exactly how I created this dashboard so you can do the same.

SocialTweede Divisie Instagram
ProductOfficial social media channels of sportscompetitions
ResultA clear overview of what differend kind of content is working on social media so you know where to invest in more or less time for better results.
ToolsGoogle Data Studio, Supermetrics for Google Sheets
SkillsSocial media, Google Data Studio, data analysis

Use Supermetrics and dive into Google Sheets

How to start? I used a tool called Supermetrics for Google Sheets to export all social media post metrics to …. Google Sheets (didn’t see that coming, did you?).

You’ll get something like this:

If you don’t want to pay €70 per month for Supermetrics, it’s important that you have the post and the views in the same row. That’s all you need to continue.

Categorize your posts

The next step is to add a column called ‘Category’. With this column you will indicate in which ‘bucket’ the post falls. The categories you will use are highly dependent on the content you are creating. In our case, we are posting content about the highest soccer amateur division of the Netherlands. Categories we were making were: 

  • Goal of the week
  • Bloopers
  • Aggressiveness
  • Single goal
  • Derby item
  • In-depth preview of game
  • Image of the standings

Here’s the sheet with the added column:

You’ll have to manually add the categories. To categorize 600 posts a full year I had to set apart a full morning, consuming ±5 coffees, doing the same repetitive thing over and over again. Sometimes you can spot the category in the posts via the text or the hashtag, other times  judge it with your own eyes.

Although it might seem a hussle at the start, the work will definitely be worth it eventually. Just prepare to do the boring work, so you can actually do the juicy analysis work.

After categorizing every post we can now go to Data Studio.

Get everything into Google Data Studio

Now go into Google Data Studio. Connect the sheet as a source. Get a new column in. The metrics should be the reach, engagement, views, etc. As dimension select ‘Category’. Voilà! There you go.

What to do with the results?

You shouldn’t relentlessly follow the data and cut all content categories which perform below average. What categories of content you past has also to do with the brand image you want to create.

Consider what has a good fit of your brand and has good performance. If something doesn’t link back to your brand or underperforms, kill it as soon as possible. 

Another element you should consider is the investment you made to create the piece of content. It can be that the angle/message of your content doesn’t resonate and/or it can be that the item is too labor intensive for it’s performance.

To keep in mind

When you are summarizing the rows in Datastudio always use the median instead of the average. The median give a better image of a result you’ll actually get, instead of a result skewed by posts that went virals.

To furthermore decrease the effect of viral posts, you can add a column name ‘Outlier’. Make a rule, e.g. “=IF(B2>50000, ‘Outlier’, ‘No outlier’)”. This way you can also filter out outliers to have a look if there’s a common denominator or category between the posts.

Adding customized dimensions

Adding a category is not the only thing you can do to differentiate the content. Is there another dimension you can add? Since we had a social media account representing a whole soccer competition, 99% of our posts included soccer clubs.

Adding an extra dimension depends 100% on your social content.

Here’s the table with ‘Team’ as a dimension:

Let me first tell you about how the method, afterwards the implications.

Adding this dimension was way more complicated to figure out. Why? Since one post can have multiple teams (most of the time two). After a lot of number crunching I figured out how to do it. 

  1. Start with a new sheet. 
  2. Duplicate every post which has multiple teams. Even though the post is now in the sheet twice, it’s attributed independent to the two different teams!

Now the results. What to do with it? This graph alone? Not much. The reason is that I’m not sure if the team they see in a post is really the reason people engage with a post. I think the angle (read: category), originality and emotion is something people interact with, not the team. Then why are there still such big differences between teams and media reach? Glad you asked!

Adding a second category dimension

Here, I cross-linked the teams to the categories. This way I could explain why teams were ranking high or low. 

For instance, on Instagram the category ‘Derby’ was a very bad performer. No wonder that the two teams with all the derby posts were also bad performers. On the other hand, aggressive content is a gold mine (people don’t show their best side on social media…). What a coincidence that the best performing team had the most posts in the category ‘aggressive’.

How did I do this? In the same sheet as where I linked teams to all the posts, I made a VLOOKUP formula to the other sheet to get the categories in. After doing this, I had to turn the categories into binary values. I added as many columns as I had categories. For the ease of the formula, let’s assume column E is where the category is in: “=IF(E2=’CategoryA’,1,0). You have to make this formula for every category. In Google Data Studio, use the settings to the right. Make sure the categories are summed.

Social media dependency

As a business, you don’t want to become dependent (read: >50% of revenue comes from social media channels). This is because you’re on someone else’s platform. Look at the constant decrease of organic reach on Facebook and Instagram.

There are industries like sports, events, fashion and entertainment, to name a few, that do have a so called “channel fit” with social media. Just keep this image in mind. 👇

Related image.

Need help? Want to dive deeper? Let me know by getting in touch.


Most recent

Close Menu