How to Analyze Your Social Media Activities With Excel

social media how toAre you looking for a way to analyze the impact of your content?

Do you need a simple system that works?

If you don’t have the budget for expensive software, spreadsheets and a little of your time can produce some useful data.

In this article I’ll show you how to analyze the engagement and impact of your social media posts using Excel.

Why Analyze?

No matter the size of your business, when it comes to social media you want to build two things: engagement and community.

To ensure that you deliver great content that resonates with your fans and followers, you need to track, test and measure the posts you put on your social networks.

It’s really that simple.

Here’s how:

#1: Build Your Spreadsheet

To begin, open Excel and create 14 columns that will contain the following categories to incorporate all of the information you need on your social media updates and posts:

  • Date
  • Network
  • Categories
  • Subcategories
  • Target
  • Calls to action
  • Meta-tags
  • Posts
  • Impressions
  • Comments (replies)
  • Likes (favorites)
  • Shares (retweets)
  • Clicks
  • Total engagement
categories-columns

An example spreadsheet (target, meta-tags, posts and impressions hidden).

The last four columns are where you store the engagement numbers for your content. Every network has three basic engagement actions: like, comment and share. Even on Instagram, which does not have a repost function built into it, there’s user behavior of reposting and tagging the original author.

Use this spreadsheet to store data and analyze your content.

#2: Identify Categories and Subcategories

You’ll need to decide on the information below and make a note of it before moving on to step #4 below where you fill in the spreadsheet.

Categories

Base your categories on the major topics of your posts and use them to identify major trends in content performance across each network. Examples of categories are:

Create as many categories as you like, but keep them manageable and general. Save the specifics for subcategories.

Subcategories

Break down categories into smaller subcategories. This enables you to identify microtrends, as well as which pieces of content work the best and the worst. Based on the categories above, examples could include:

  • Specific product names or features.
  • Specific names of holidays or campaigns you’re running for a particular season.
  • Specific website names from which you pull content or categories of topics for articles such as “humor,” “lifestyle” or “beauty.”

Subcategories can be as specific as you like, but keep them thematic. Each subcategory should have at least three posts; otherwise it may be too specific and better suited as a meta-tag (more on that later).

create-subcategories-istock-image

Keep your categories broad and use subcategories to drill down. Image Source: iStockPhoto.

#3: Outline Your Targets and Calls to Action

These two columns require the most forethought and a good understanding of your target market and business goals. You’ll need this information to fill out these columns on your spreadsheet.

Target

The target is whom you’re speaking to with your content. Your business may have multiple targets based on the people and decision-makers who engage with your content and purchase your products. For the sake of simplicity, we will use “male” and “female” as target examples.

However, I encourage you to spend time considering the different types of people in your audience and build context around those individuals to inform your content strategy.

Calls to Action

CTAs take many forms, depending on the individual goals of the post in each category. Just keep in mind that not all posts need CTAs and not all CTAs must be related to products.

Examples of CTAs include:

  • Subscribe
  • Visit website
  • Buy now
  • Share
calls-to-action-column

Enter your calls to action to help you monitor your success.

Meta-tags

For a much deeper analysis on your types of content, you should use additional meta-tags to describe the individual elements of the content itself.

Keywords, image descriptions (such as person, object, colors, size or orientation) and even tone descriptors for the copy (such as humorous or excited) can be used as meta-tags.

These tags help isolate trends in creative content so you can select more engaging images and write more dynamic copy for your posts.

#4: Collect Your Data

Time to fill in your spreadsheet! There are a number of ways to do this, depending on the networks you use.

Below is an example of a spreadsheet for a retail company using the above-defined categories, subcategories, targets and meta-tags.

example-spreadsheet

Populate your spreadsheet with post details.

There’s a lot of information to gather, but it’s all easy to find.

Twitter Analytics has an Export feature for your last 500 tweets, and Facebook Insights can be exported in 180-day chunks. Export regularly to make sure your data is current.

Use a tool like bit.ly to keep track of your link clicks. If you tend to post the same link multiple times, make sure you’re attributing your click to the right content by using tools like HootSuite‘s Click Report or BufferApp to track which specific posts generate the clicks. Each of these tools has free versions that allow you to measure posts on multiple networks.

#5: Sort Your Data

To identify your best-performing content across all of your networks, sort the Total Engagement column in descending order.

engagement-sorted-spreadsheet

Sort by Total Engagement.

The best-performing posts in terms of engagement show at the top of the column.

post-sorted-spreadsheet

After sorting by Total Engagement, your most successful posts show at the top of the spreadsheet.

To find the best-performing posts for specific social networks like Twitter or Instagram, filter first by Network, then by Total Engagement.

twitter-sorted-spreadsheet

Filter your spreadsheet to find the posts with the highest engagement on each network.

#6: Analyze Categories

The next step is to manually filter for the categories and subcategories you’re interested in reviewing.

filtered-spreadsheet

This spreadsheet is filtered to show only Product category posts.

Excel is very literal, so if you include more than one category or subcategory assigned to content, you’ll need to use the “contains” input in the Excel Filter feature, rather than selecting the check boxes. Use this setting to see the rows in the column that have multiple subcategories assigned, and those with just one.

multiple-filtered-spreadsheet

Use the “contains” filter to show Product category posts that cross into multiple subcategories.

Once you identify the top posts in your categories, it’s time to analyze them for post copy, sentence structure and images (if applicable). What trends can help you optimize content for each network?

Here are examples of three posts that promoted the same link.

sweater-posts-comparison

A comparison of three posts promoting the same link shows that engagement was highest with men.

In this case, analysis shows that the shorter post lengths received more engagement. Among these similar posts about sweaters, the post using two different images generated a higher click-through rate with males than with females.

Conclusion

You don’t have to rely on intuition to find out which posts perform best for you. Use Excel to reveal important insights about post engagement in nearly real time and make more informed decisions about your content strategy.

What do you think? How are you currently measuring the impact of your content? Do you have other strategies or tools that you use? Please leave your questions and comments in the box below.

Images from iStockPhoto.

Tags: , , , , , , , ,

About the Author, Merlin U Ward

Merlin is an author, marketer, strategist, and beer geek! His book “You Get What You Give” explores five distinct social media marketing strategies that strengthen the customer and brand relationship. Other posts by »




More Info
  • http://indispensablemarketing.com/ Patrick McFadden

    Awesome Merlin! I really appreciate this article. We all should want to make more informed decisions about our content strategy. And you have shown us that we can use Excel to reveal these important insights about post engagement in nearly real time.

    You Rock!

  • Kathie Higginson

    This is so useful, thank you! …Now write a post on how to find the time to actually make this incredible spreadsheet! :)

  • http://about.me/vvanspengen/ Valerie Van S

    Wow, very in-depth. I use iFTTT to make a recipe to capture all my tweets in an spreadsheet located on Dropbox. This is very handy. I will start on a spreadsheet as you suggested to get a better look at what is going on. I find SocialBro has lots of good stats that I utilize as well. Again, great post with actionable items.

  • http://MerlinUWard.com/ Merlin U Ward

    I’m glad you enjoyed it! You can grab your content information from any free tool or native network insights (i.e. FB insights). Love the idea of iFTTT for the spreadsheet!

  • Big Like

    Great article, Merlin!
    Something we’ve never considered is using a spread sheet to track engagement. It’s something everyone can do, thanks for this info!

  • http://MerlinUWard.com/ Merlin U Ward

    :-) Time is always the trick, isn’t it? The spreadsheet actually won’t take you more than 30 minutes to devise. Its deciding on your categories that may be the challenge. Trib21 (above comment) had a great idea about using iFTTT for capturing the data automatically.

  • http://www.EntrepreneurOnFire.com/ John Lee Dumas

    Great post Merlin! Excel has hundreds of tools we can utilize, but there are still so many people who aren’t using it to track their social media activities (I love doing this by the way!) I’m glad you explained here that it’s really not hard to do. Thanks Merlin!

  • SarahHowe

    Great post! I also use IFTTT to pull in twitter and instagram mentions to provide invaluable product feedback. However, I will definitely implement your plan too. Thanks for sharing :-)

  • http://www.infusionsoft.com/ Heather Dopson

    Wow! What an excellent & useful post Merlin! It’s all about the data & this is a great way to organize & analyze that data. Thanks taking the time to write it!

  • Aimée Tañón

    Gracias Merlin!! Got a lot of information here, yep… looks like I may need to add an extra latte or green drink while I set this spreadsheet up. Thanks again!! :)

  • http://unbounce.com Tia Kelly

    I never thought to include the specific CTA wording in a column, that’s a great tip!

    However, in the end example results I think it’s worth noting that the measure of success in regards to “engagement” may differ depending on your goals.

    For this example it’s likely you wanted click throughs to the product page, so clicks would have been your highest weighted measure.

    If it was a post that was designed to generate discussion instead, I’d say that even though the total “interactions” was highest on the post aimed at men, it wouldn’t win in this case, as the highest comment level came from the “Ladies, the cold is coming…” post.

    Was just a thought I had while reading. I love digging into data like this to see what people are responding to! Thanks for the clear explanations in your post, Merlin.

  • http://MerlinUWard.com/ Merlin U Ward

    Great points, Tia! You’re absolutely correct. Success is definitely defined by the goals of the posts (and even the channel). You may only be looking to drive discussion, in which case comments is the column you’d care about most. You can even take it a step further and add a “conversions” column to amend sales or subscriber data to the sheet to show which posts were most effective.

  • http://expertnicheacademy.com/ Carol Mortarotti

    The information you have shared here is fantastic Merlin! I love how you breakdown the categories and get specific and include CTAs. This is going to be extremely helpful going forward in order to focus on what’s really working.

    You rock!

  • http://www.postplanner.com/ Scott Ayres

    That made my head hurt… Keep in mind not everyone has Excel. You could use a spreadsheet in Google Drive.. But why not just look at the insights given to you from Facebook as they are? Seems a bit overkill to me.

  • Jessica Brown

    I read Social Media Examiner often and have learned so many tips and tricks to helping my always ongoing education towards social media. But the one thing that can be hard is my company is in the remodeling/design-build industry and we don’t really have a “product”. And our service isn’t something like getting your hair cut. We also rarely have “deals” or “offers” because it’s hard to offer “10% off a remodel” for liking us on Facebook.

    While this might not be the right article to ask for, any suggestions for people in this type of industry with similar issues? Might make a good article someday.

  • http://MerlinUWard.com/ Merlin U Ward

    Scott, it may seem complex, but the process does give you insights you can’t get from the native platforms. This process is designed to help you identify trends over times. FB insights gives you nice overview on items like post reach and change in Page Likes, but doesn’t give you a great overview of your content. You can look at specific posts and get an idea of how that single post performed, but it is challenging to look at a group of post.

    Secondly, you are left only looking at Facebook, while your other networks’ data are in different places. That is where this organization and categorization process can help you monitor key user behaviors over any network and any category of content that you prescribe over any given time period.

    To optimize your content and focus your strategy on the actions you want, it takes a more analysis than what the networks give you on the backend.

  • Earl

    Great article. It carries both substance and weight. Thank you.

  • http://MerlinUWard.com/ Merlin U Ward

    Thank you, Earl!

  • http://MerlinUWard.com/ Merlin U Ward

    Someone just removed their comment about concerns not having “product” or discount posts because of their industry – remodeling/construction. Here’s my reply, just in case they come back or someone else has a similar concern.

    ==

    You bring up a few great concerns that I’m sure many others have. I would, however, argue that you do have product posts. Do you post examples of your past work? Also, you could created a category named “tips,” where you post your company’s tips/tricks on how to choose materials, styles, or vendors.

    Not every brand needs to offer discounts, so don’t worry about that. Focus on categorizing the content you currently have, and see if there are any holes to fill in your content strategy.

  • http://MerlinUWard.com/ Merlin U Ward

    De nada!

  • Jessica Bunker

    What a fantastic tool Merlin! I’m lucky enough to have access to HubSpot at work but I’m going to use this tool with some of the nonprofits I help!!

  • http://MerlinUWard.com/ Merlin U Ward

    Thank you, John! Loved your thoughts on Content Warfare today.

  • Pingback: Marketing Day: January 30, 2014()

  • http://www.antoniocalero.com/ Antonio Calero

    Excellent post Merlin. I would simply suggest to timeframe your analysis. E.g.: are you going to populate that spreadsheet with info from the past day, week, month? Whatever you choose, should be consistent across all channels…

  • http://MerlinUWard.com/ Merlin U Ward

    Absolutely, Antonio! It is intended that this sheet be used on an ongoing basis. Using the filter options you can change the timeframe at your leisure on the date column.

  • http://www.antoniocalero.com/ Antonio Calero

    I was actually referring to the timeframe you are considering for the data. In my case I usually check this information a week after the content has been published. I also have an extra column to specify if the content was promoted in some way or not.

  • http://MerlinUWard.com/ Merlin U Ward

    Yes and great point. The time of export may have an impact on the records. FB has moved to push content days after it was first published with their new timeline updates. An early export mean read lower numbers. Great point on the promoted posts – you certainly don’t want the exception to become the rule!

  • http://about.me/roufull abdiman

    I never imagine this way. Thanks Merlin.

  • Marco Colussi

    So interesting the use of iFTTT. Would you mind to share the recipe? Thanks

  • treb072410

    Great share Merlin!.. It is very useful.. Thanks for sharing your article…

  • http://about.me/vvanspengen/ Valerie Van S
  • Philipp Grant

    I was searching for this kind of a tool and want to say thanks for a great explanation it is useful!

  • Dave Barnett

    Outstanding Article. Excellent!

  • Chris

    Hi Merlin. A very interesting read and I have set up the spreadsheet. One question I do have is whether you would log customer tweets which you re-tweets? Would you do this in the same way as a tweet which originates from you?

  • Amanda

    This is so fantastic! Thank you for sharing such a great resource. I have a few questions: how often do you recommend updating something like this and when should you start a new document ie. 2013’s data vs 2014s data because it could of course get very large quite quickly.

  • http://MerlinUWard.com/ Merlin U Ward

    Hi Amanda, sorry to get back to you with such a delay. To your question below, you would only log retweets of customers if they play a crucial role in your content marketing. If you do it for the sake of promotion, it is likely not necessary – especially if they don’t garner much engagement or clicks. To answer you most recent comment, for the sake of ease-of-use, do annual spreadsheets. You can keep a full year of data in one spreadsheet as long as you computer can handle the file. That one file can be sliced and diced anyway you want using pivot tables and the filtering options. Mainly, do what makes the most sense for your business and in chunks of time that get you the best insights. Hope the process is working out for you!

  • Pingback: 7 Strategic Social Media Steps | Social Media Marketing 24/7()

  • http://www.h2ocommunications.com.au/ Sandra van der Lingen

    Thanks Merlin for your tips! Very useful.

  • AMS

    Isn’t there a spreadsheet somewhere with all this already on it that we can just download?

  • http://MerlinUWard.com/ Merlin U Ward

    Hi AMS, I apologize for the late reply. There is not a downloadable spreadsheet, but it only requires a few columns to get started. All the data, of course, will need to come from your social networks or tracking tools.

  • http://www.buzzintown.com/ buzzintown

    Hi,

    Very nice post. I downloaded the said spreadsheet but it shows lots of other info like total reach, organic reach, clicks, other clicks, total impressions, organic impressions, engaged users, post consumers, Lifetime Post reach by people who like your Page,
    Lifetime People who have liked your Page and engaged with your post, etc.

    Which clicks to consider?
    Why we are not recording Negative feedback per post? I think that’s an imp. insight too…

    The post level spreadsheet does not give info about likes, comments and shares.

    Do i need to open each post level link & record the desired info? Or is there an easy way to do it?

  • Brunelan

    Thank You very much for a great article! Much appreciated.









Pinterest
Join our Social Media Marketing Networking Club
Get Your FREE Copy of the 2014 Social Media Marketing Industry Report
Wondering how your peers are using social media? Get this free report (50 pages, 80+ charts) and never miss another great article from Social Media Examiner.
Check out the Social Media Marketing Podcast!