Digital Marketers: do you know how to set up your Social Media Analytics in Excel spreadsheets?

I spend a lot of time talking to analysts and data scientists about how they work, what their challenges are, and what makes their job easier. While there are thousands of tools and applications that enable social media analytics in one wayor another, it seems to be that almost all of them continue to turn to spreadsheets for their analysis and data manipulation because it is a language and tool that they’re accustomed to.

Companies spend thousands of dollars a month generating content, speaking with customers, and building creative ideas on new ways to interact with users. When it comes to analyzing that data, however, it seems that some of them rely on outdated spreadsheets or really old models. While there thousands of complex models that we can share, this article should be used to help you get started on how to set up your social media analytics in spreadsheets. In doing so, I will show you how to analyze the engagement and social media posts using Excel.

Set Up Your Social Media Analytics Spreadsheet

There are two ways to build your social media analytics spreadsheet. The first one is to use a ready-made template from Factivate (www.factivate.com), add your social media account logins and then have a dynamic spreadsheet that will display data in real time. This takes a few minutes and can be found here: https://factivate.com/automated-spreadsheet-template-marketplace/

The second one is to do it the old manual way in Excel. Note that you will be required to update your spreadsheet continuously in order to keep your data fresh.

Why do I need a Social Media Analytics Spreadsheet?

Regardless of the size of your business, when it comes to social media analytics we should always measure two things (to begin with): engagement and community.

To ensure that you continue to deliver interesting and relatable content that resonates with your followers, you need to continuously track, test, and measure the reactions to your own posts. That’s what this spreadsheet helps you do.

Step 1: Build Your Spreadsheet

To begin, open Excel and create 14 columns that will contain the following categories:

• Date
• Network
• Categories
• Subcategories
• Target
• Calls to Action
• Meta-tags
• Posts
• Impressions
• Comments (replies)
• Likes (favorites)
• Shares (retweets)
• Clicks
• Total Engagement

An example of a social media analytics spreadsheet template

The last four columns are where you store the engagement numbers for your content. Remember that you can also add weights to measure content based on importance (ie., I only want my clicks to weigh about 30% of a Comment) so that you can refine your total Engagement numbers based on what you have learned.

Since every network has three basic engagement actions: like, comment, and share, you should be measuring that user’s behavior or reposting and tagging the author.

Step 2: Identify Categories and Subcategories

Base your categories on the major topics of your posts and your overall content strategy. Once you have that list, you can use them to identify the major trends in content performance across the different channels and networks. In our example, we have used some of the Categories that Factivate has used in the past but typical categories should include:

• Product: Posts about major product categories
• Holidays/Seasonal: Posts with seasonal themes. These are really helpful if your business has any sort of ecommerce component.
• Third-party content: Posts with content or references from other websites such as blog posts, white papers, presentations, news, and articles.

You can create as many categories as necessary but be sure to keep them general.

Once you have the list of categories, break them down into subcategories. This will enable you to identify microtrends as well as learn which pieces of content work best within your particular segments. Based on the categories above, examples can include:

• Specific product names or features
• Specific holiday names or seasonal campaign names
• Specific website names (i.e. HuffingtonPost) or topics for articles such as “analytics”, “humor”, etc…

Subcategories can be as specific as necessary but make sure they align within your overall marketing campaign and that they are thematic. Each subcategory should have at least three posts per your campaign (we use 7 as a rule of thumb); otherwise, it may be too specific and should instead be a meta-tag.

Step 3: Outline your Target Demographics and Calls To Action

These two columns require a really good understanding of your target market and overall goals for the marketing campaigns. Be sure to sit down with your entire team if you have not taken the time to consider these two columns.

Target Demographic

Your target demographic should be your intended audience for your content. In general, the more specific you can get, the more impact your content will have. For the sake of this example, we have used job functions inside marketing organizations for our target demographics. If you have not already done so, be sure to spend time with your team considering all of the different types of personas in your audience and build content/context around those individual personas you wish will read your content.

Calls to Action (CTA)

CTAs can take many forms and it depends on the individual goals of the post in each category. Note that not all posts need CTAs and not all CTAs should be related to products since audiences today tend to shy away from the constant advertising pushed to them. Instead think about what you can do to get them excited to talk about your brand (think Ice Bucket Challenge).

Examples of CTAs include:

• Download
• Visit website
• Subscribe
• Buy Now
• Share

Meta-tags

If subcategories had subcategories, these would be our meta-tags. For a much deeper analysis on your content type, you should use meta-tags to describe the individual elements of the content. These can include keywords, image descriptions, and even tone descriptors for the copy (such as humorous or excited).

Meta-tags will help isolate trends in your creative content so you can build more dynamic context for your posts. (i.e. #lovethyspreadsheet came from our meta-tag of passion for data). For the sake of this example, we have removed all meta tags and posts, and impressions but feel free to add to our comments section if you would like further explanation).

Step 4: Collecting your data

Here is where you will start importing information from different sources into the spreadsheet. Note that about 88% of spreadsheets have errors associated with these importing and manual processes so be careful that you do this correctly. If you want this process to be done for you automatically, be sure to sign up for Factivate. Once you’re a Factivate user, this process will be done in real time for you.

In the example below, we’ve imported information from Twitter Analytics, Facebook Insights, and Linkedin. Be sure to devote at least 2 hours every time you want to import from these sources into Excel since you will need to format the imports and check that no human errors have occurred.

You will need to export regularly to make sure your data is up to date.

If you tend to post the same link multiple times, make sure that you’re attributing your click to the right content by using tools like HootSuite, Buffer App, Pardot, or Marketo to help you track which specific posts generate the clicks. Some of these tools are offered for free and will allow you to measure posts on multiple networks.

Step 5: Formatting, and Sorting your Data

In order to identify your best-performing content across all of your marketing channels, sort the Total Engagement column in descending order. Once your data is sorted, the best-performing posts will show up at the top of the column. If you want to be really efficient, you should be measuring this information consistently (we do it every 3 days) and then refining your content on the best performing post categories, subcategories and meta data.

If you wish to find the best performing posts for specific social networks, like Twitter or Facebook, you can also filter by Network and then by total engagement.

Step 6: Analyze Categories

The next step is to manually filter for the categories and subcategories you’re interested in reviewing and begin to analyze trends. Once you have that information, see if you can compare it to your historical data trends to see if there is anything that pops out to you.

In order to manually filter in Excel, you should make sure that you include more than one category or subcategory assigned to content. You will need to use the “contains” input in the Excel filter feature rather than selecting the check boxes that appear when you start the filter process.

Once you identify the top content per channel, per category, it’s time to analyze them for your post ROI. Compare it to historical data to see if you can notice any emerging trends or downward trends. Ask yourself what can help you optimize content for each strategy.

From the data below, what does the analysis show you? One conclusion could be that one of the image categories did extremely well and that we should be iterating on that basis to increase user engagement in Twitter. Based on our historical data, this is an apt conclusion.

Conclusion

You don’t need to be a data scientist to build your first social media analytics portal. With a little elbow grease and basic understanding, you can find which content performs best. Remember to keep iterating and trying different things continuously to keep your content fresh and relatable. Be sure to use your spreadsheet to reveal important insights about your post engagement and continue to update your spreadsheet to keep your data as up to date as possible. You can also use Factivate to keep your data up-to-date to make the best-informed decisions about your content strategy.

There are many variables that can be used to measure your social media impact. Hopefully this post will help you get started in discovering what these are. If you don’t want to continue updating your spreadsheet manually and would rather have a prebuilt spreadsheet that automatically updates with your social media KPIs, you can use one of our social media KPI spreadsheet templates available by signing up for Factivate by clicking on the following link:

Automated Spreadsheet Template Marketplace

About Factivate:

Factivate is a software company part of the Google for Entrepreneurs community in Durham, NC. Factivate provides business users with a cloud-based data analytics platform that can sync with different online tools to create automated, real-time spreadsheet reports and web dashboards with just-in-time business reactions. The result of our intelligent spreadsheet is accurate data analysis that drives better business decisions while reducing manual spreadsheet report time and errors by a factor of 150+ hours/employee/year. Factivate requires no learning curve or programming knowledge. In sharp contrast to the complex and expensive business intelligence tools on the market today, if you know spreadsheets, then you know 95% of what you need to use Factivate–we’ve got the rest covered.