Twitter Audit-01

The key to a successful Twitter marketing strategy involves frequently studying the data you have on hand to inform your strategy moving forward. It’s easy to guess what kind of content will perform well if you know what has worked in the past. One of the best ways to dig into the data you have is to perform your own Twitter audit.

Though the term audit may jog unpleasant thoughts of the IRS, you can leave your W2s in the shoebox under your bed. Instead, this article shows you how to pull, visualize and make sense of your Twitter data so that you can make informed decisions about your future strategy.

Step 1. Amass Your Twitter Data

Modern marketers are obsessed with data. In fact, the idea of “big data” has gotten so popular the trend line looks like one of the world’s most dangerous ski hills.

Although “big data” sounds like a complicated concept, it’s not as tough as you may think and we’ll be using our own big data for this Twitter audit. The first step in the audit process is to pull your data, and here’s how you can do that using Twitter or Sprout Social.

Pull From Twitter

Twitter has all of the data you’ll need to conduct an audit on your presence. Navigate to their analytics page while signed into the account you’d like to pull the data for, then follow these steps.

Twitter Analytics Audit
  1. Click the “Tweets” tab to get data on a Tweet-level.
  2. Choose the date range that you’d like to analyze (the more data you have the stronger your insights).
  3. Export the data as a CSV.

Pull From Sprout Social

The major advantage to using Sprout Social is the ability to aggregate and export data across several different handles simultaneously, which makes it easier to find the major trends in data that inform your decisions. If you’re not currently using Sprout Social you can quickly sign up for a free 30-day trial here.

If you use Sprout Social, follow the steps below to pull your raw data from our Sent Messages Report.

Sprout Twitter Audit 2
  1. Open your Sprout Social dashboard and navigate to the reports tab.
  2. Click on your Sent Messages report to see data on a Tweet level.
  3. Select the time frame (the larger the window the more data to analyze).
  4. Select the Twitter profile you’d like information on and Export CSV.

Step 2. Choose Your Key Performance Indicators

Set aside your data for the time being. Before we can start analyzing all of the information on hand you’ll need to decide what metrics–or KPIs–are most important to you. If you don’t know which metrics are important to your brand you won’t know what has succeeded in the past.

  • Favorites are a way for Twitter users to show they like your post. Some may refer to Favorites as a vanity metric, but Favorites are an indicator that someone enjoys the content that you’re sharing.
  • Retweets are similar to Favorites in that they show someone enjoys your content, except they are stronger signals that a person liked it. A Retweet means that a person enjoyed your post so much they they decided to share with their own following.
  • Replies are messages people send to you in response to one of your specific Tweets. These can be positive, negative and neutral, so make sure you’re looking at the quality of replies instead of just the quantity.
  • Clicks: When you share a link in one of your Tweets and someone clicks through to that site it’s considered a click. This is typically an important metrics for social media marketers, as a top goals is driving site traffic.
  • Engagement is a combination of everything listed above. Whether a Twitter user Favorites, Retweets, Replies to or clicks one your messages, they’re engaging with your brand on social. Engagement is a great umbrella KPI to look at.
  • Reach: As Twitter users share your messages to their own audiences, it will be seen by even more Twitter users. This leads to an increase in your overall reach, or the amount of people who have seen one of your messages. This is important for marketers focusing on branding and awareness.

Step 3. Export & Visualize Your Data

Navigate back to the data you pulled in step one. Sprout Social and Twitter provide you with a similar data format, so it’s just a matter of finding the best way to visualize the data. One of the easiest ways to slice and dice your data into actionable information is by creating a series of pivot tables. Here’s how to create your unique pivot tables using either Microsoft Excel or Google Sheets.

Microsoft Excel

Sprout Social and Twitter automatically export your data as a CSV document, which opens using Microsoft Excel if you have it. Before you create a PivotTable, you want to add one column to your document. Put “Day of the Week” at the top of the first open column and enter this formula in the second row =TEXT(A2,”dddd”). Drag this formula to fill in the rest of the column.

Now, highlight every single column with data in it and navigate to the Data tab. Choose PivotTable and click “OK.”


This opens a new sheet for your PivotTable, and you’ll be presented with a PivotTable Builder. Match your own builder with the example below, but swap out Clicks in the Values section for whichever KPI is your top priority.

Screen Shot 2015-12-03 at 11.27.40 AM

Google Sheets

If you don’t have Microsoft Excel, Google Sheets is a free alternative. Once you export your Twitter Data as a CSV, dump that information into a Google Sheet. You need to make sure to add the Day of the Week column from above as well. Highlight every column, click on the Data tab and create a Pivot table.


Similar to Excel, set your Values section to your chosen KPI and the rows section to your messages.

report editor example

Step 4. Ask Yourself Key Questions

Now that we have our unique data and can manipulate it as we see fit, we’re ready to start auditing our Twitter performance. There are so many data points we can look at with this information. Here, we’ll stick to the questions about what has performed well so we can use the information to guide future Tweets.

What Type of Posts Should You Share on Social?

Click into the first value box in your Pivot Table and sort it in descending order so that your top posts are at the top. This will help you look for trends to see which of your post types perform the best.

  • Photos
    If you see that most of your top performing Tweets include a link with pic.twitter, then it’s clear your Tweets with photos perform the best. Tr to add more unique images to your social posts. If you need help creating images, check out these free image creation tools.
  • Videos
    As networks like Twitter embrace video with features such as autoplay, the amount of videos shared to the network continues to increase. If you see that your top Tweets include shared videos then you should try and create more. If you need some help use a tool like Animoto which makes video creation easy.
  • Hashtags
    Including hashtags in your social media posts helps increase the overall reach by showing your messages to users looking up that topic. Hashtags can work along with your other post types like images and videos, so if you see hashtags help increase your KPIs make sure to include them.
  • Mentions
    Do you see an increase in your KPIs when you mention other Twitter handles? It’s possible, since the users that you mention may be more inclined to share that message to their audiences.
  • Links
    Links are some of the most popular post types on Twitter since marketers want to drive traffic to their websites. See which of your links perform best on Twitter and try to share content similar to that contained in the link.

What Kind of Copy Resonates With Your Audience?

This portion is all about studying the actual copy you use in your successful Tweets to see if there are any trends you can capitalize on. For instance, are your followers more likely to click on your link if you include a call-to-action phrase like “click here”?

A fantastic way to look for trends in copy is to drop the body copy of your top performing Tweets into a word cloud generator.

Screen Shot 2015-12-04 at 9.47.06 AM

Based on the word cloud generated above, it’s pretty clear that the most successful posts for this author revolve around social marketing, so when sharing links and Tweets it would be wise to include those words.

What Day of the Week Should You Post to Social?

Click on your PivotTable to pull up the builder and swap your Row Labels and Values to match the one below–remember to choose your top KPI for the value.

Screen Shot 2015-12-04 at 10.07.35 AM

This shows you which days of the week your messages perform the best on average.

average of clicks on twitter example

Save the content that you feel will perform best for those days, and try to post more frequently then. You should also try to avoid sharing posts you feel confident about on days notoriously devoid of success.

Step 5. Dig Deeper With Sprout Social

If you have a Sprout Social account–or if you start a free trial–you can look through our social media reports to continue learning about your Twitter performance. Here are some of the reports to consider when performing a Twitter audit:

Demographic Reports

Figure out who your Twitter audience is so you can create content you know will resonate with them. Females from the ages of 18 to 20 aren’t the same as males between the ages of 45 and 54, and they shouldn’t be marketed to as such.

Screen Shot 2015-12-04 at 10.19.40 AM

Engagement Reports

Engaging with your customers is one of the most important things you can do on Twitter. Audit your engagement statistics and make sure your response rate and time are as solid as you’d like them to be. The Sprout Social index has engagement benchmarks you can compare yourself to.

Screen Shot 2015-12-04 at 11.20.33 AM

Hashtag Reporting

Our Trends Report will show you which topics and hashtags your brand is frequently mentioned with. Study this data to learn what topics resonate with your fans and what hashtags you could use in your Twitter posts.

Screen Shot 2015-12-04 at 10.27.39 AM

Competitive Analysis

Running a good Twitter audit requires more than just looking at your own page: you should be studying your competition as well. See how your influence and engagement stacks up to your competitors and use that information to dictate what you focus on.

Screen Shot 2015-12-04 at 11.29.49 AM

Keep Learning

The point of a Twitter audit is to study your data to learn what kind of posting and engagement strategy improves your KPIs. These are just a few examples of reports you could run, and if you know how to pull and visualize the data there is no limit to what you could find out. Keep playing around with the information you have, and continue to learn about what works for your brand!