How to analyze social media metrics, DIY-style

(If you attended ONA and want to compare your numbers to what we shared, here’s how to do that.)

This set of instructions will walk you through how I run basic data analysis on social media metrics to reveal long-term trends. Essentially, we’re going to build a big, unwieldy database containing all your Facebook posts, and then turn around and slice it right back up again.

What’s happening to Facebook reach on my Page? What content type is performing best? How often should we post? Which strategies lead to the most audience engagement? Which strategies lead to the most referral traffic? These are all questions we can answer with a different slice of our giant database.

When you want to observe a trend, one of the most important elements is time. Chronology is also an incredibly powerful way to slice a database, and it will be the backbone of this analysis.

Now, we’re going to use Terminal on Mac as well as Excel formulas. Trust me, you don’t need a technical background in spreadsheets or math to analyze data straight from the source — I’ll approach these instructions like you have neither (I certainly have neither!) — but you do need an attention to detail and to check your work often. Misleading or inaccurate analysis is worse than no analysis.

Before we dive in, you’ll need to decide where you want to get your data from: Facebook Insights (FB) or CrowdTangle Historical Data (CT).

For me, the decision comes down to FB reach: there’s only one definitive way to gauge how FB’s News Feed algorithm is treating your content over time, and that is by measuring average FB reach on a consistent body of FB posts over time. Now, if you just want a general sense of your engagement (comments, shares, reactions) over time, use CrowdTangle’s Historical Data option. If you do choose to use CrowdTangle, you can skip ahead to the “Slice the data” step.

Getting a complete dataset from FB Insights can be tricky, so that’s where I’ll start. This is what you’ll need to get going with FB Insights analysis:

  • Microsoft Excel or Google Sheets: If your FB Page posts more than 25 times a day, you’ll be better off using Excel. You likely encounter internet bandwidth issues when trying to analyze a full year of Post-level Insights in Google Sheets.
  • A desired timeframe: How far back do you want to go with your analysis?
  • A general idea of how often your Page posts
  • A FB admin role on the Page of Analyst or higher
  • A steely resolve!

FYI: I also did a video tutorial for this workflow, you can find that here.

  1. Get yourself to the Insights tab for your desired Facebook Page, click the prompt to “Export Data”
  2. Download Post data in the .CSV file format for your desired timeframe, the standard layout for “All Page Post Data” will include reach. Be sure to get every single post from your desired timeframe, and remember to delete duplicate rows if any of your .CSV spreadsheets overlap.

Pitfalls you should be aware of:

  • Each Page Post will be a row in this .CSV spreadsheet. You can only export 500 rows at a time. For a Page that posts 25 times a day, that means selecting a timeframe of no more than 20 days for each download.
  • If you select a timeframe that contains more than 500 posts, Facebook will arbitrarily drop posts at the beginning of your selected timeframe from your spreadsheet.
  1. Create a folder on your desktop, name that folder “folder”, drag all the newly downloaded .CSVs from your desired timeframe into this folder.
  2. Select all the .CSVs in your folder, right-click and select “Rename…”
  3. Choose the default naming convention “file1.csv”, make a note of how many .CSV files are in your folder.
  4. Open Terminal (Applications > Utilities)
  5. Tell Terminal to navigate to your desktop by typing “cd desktop” and pressing ENTER, then navigate to your newly created folder by typing “cd folder” then press ENTER
  6. Next we’ll paste the actual concatenate command. Alter the following starter command to correspond with the number of .CSVs in your desktop folder: cat file1.csv file2.csv file3.csv file4.csv file5.csv file6.csv file7.csv file8.csv file9.csv file10.csv file11.csv file12.csv file13.csv file14.csv file15.csv file16.csv file17.csv file18.csv file19.csv file20.csv file21.csv file22.csv file23.csv file24.csv file25.csv file26.csv file27.csv file28.csv file29.csv file30.csv > merged.csv
  7. Paste the altered “cat” command into Terminal, press ENTER
  8. You should now see a new .CSV in the desktop folder called “merge.csv”
  9. Open “merged.csv” in Excel
  10. Under the “View” tab, select the option to Freeze Top Row
  11. Select your entire spreadsheet, sort by “Posted” column, Oldest to Newest
  12. Scroll to the bottom (or COMMAND + DOWN ARROW) and delete all duplicate header rows

*I haven’t had a chance to investigate how to merge .CSV files on a PC. If you know how to do this, it’d be great to add that workflow to this document — please reach out in the comments or you can find me on Twitter.

  1. Select Column H, insert four new columns to the left
  2. Label the new Column H “Date”, paste the following formula into cell H2: =INT(G2)
  3. Apply formula to all of Column H, format entire column as a Date
  4. Label the new Column I “Time”, paste the following formula into cell I2: =G2-INT(G2)
  5. Apply formula to entire Column I, format entire column as a Time
  6. Label the new Column J “Month #”, paste the following formula into cell J2: =MONTH(G2)
  7. Apply formula to entire Column I, format entire column as General
  8. Label the new Column K “Week #”, paste the following formula into cell K2: =WEEKNUM(G2)
  9. Apply formula to entire Column I, format entire column as General
  • This process of splitting up the date field can be tweaked for any dataset with a date, including CrowdTangle Historical Data exports from any type of social account. (You’ll use slightly different formulas, which I’d be happy to share.)
  1. Select your entire spreadsheet, under the “Insert” tab, click the option for PivotTable and click “OK” on the follow-up prompt
  2. Use the PivotTable Fields section of your new sheet to take advantage of the different chronological slices of data you just created.

Typically my next move is selecting “Week #” and dragging that to the Row box, selecting “Lifetime Post Total Reach” and dragging that to the Value box, and clicking the “i” icon to switch from sum of reach to average reach. Excel will probably add another item to your Values section, drag that away to delete. You should end up with a weekly running figure for average reach per post. Create a line graph out of this data, add a trendline and you should be able to visually see whether your Facebook reach is trending positively or negatively over your desired timeframe.

Here’s an example of what your DIY reach line graph + trendline will look like.
Here’s what the exact same timeframe looks like in the reach section of Facebook Insights: it’s hard to tell what’s actually going on, thanks to the 20-month timeframe and that large spike. Breaking your data up into weekly chunks might make it easier to see trends.

If you attended ONA and want to compare your Facebook numbers to our data set of local news Pages, you’ll want to pull monthly average reach.

Thanks for reading, and I hope this is helpful. I sincerely welcome your feedback. I’d love to hear if this works for you, if you run into issues, and also to hear about how you analyze social metrics. There are many ways you can go about seeing long-term average reach on your Page, this is simply the method that works for me.

Good luck!

Social media editor @McClatchy | Also: @BestBuddiesMD, cooking enthusiast, amateur adult, INFP, often replies to promoted tweets when inebriated, She/Her