DIY: Analytics Driven Social Media (Facebook)

With this blog, we will try to see how you can drive your social media strategies based on your own analysis. The whole blog is divided into two parts: Data Preparation and Data Analysis.

Data Preparation:

1. Go to Facebook insights
2. Download Post level data for last six months
3. Get rid of unnecessary columns
   1. If you own a local business, you can delete columns like Country and Language
   2. If you are looking into what’s working rather than what’s not, you can delete columns like Lifetime Negative Feedback
   3. If you haven’t run any ads, you can delete all columns related to paid promotions
   4. You can also choose to delete Permalink column
4. After removing unnecessary columns, add self configured columns that you’ll need for analysis
   1. If you’d want to know which day of the week the post was published on- add a new column with formula- =TEXT(POSTED_CELL,”dddd”)
   2. Round up the time using – =ROUND(E3/(1/24),0)*(1/24)
   3. Separate Date and Time using- Data > TextToColumns > Fixed Width > insert a column break between the two
      1. You may need to format cells and select Date and Time respectively.
      2. Copy all these new columns (Day, Date, Time) and select paste special as values to free it from reference (of Column Posted).
      3. You can delete the Posted column now.
5. If you wish to drive your social media through insights, make sure you have a theme, topic and purpose well defined for each post. (and add columns for the same)
   1. For example, you can have few themes like Contest, Donation or Discussion
   2. Purposes like Engagement, Branding, Product selling or Survey can be defined
   3. Similarly, you can also have topics of posts
6. You can also add Character column if you want to see what length of the posts works best for your audience using- =Len(Post Message_Cell)
7. Add one final and important column Engagement Rate  by calculating it using- =(Lifetime Post Total Impressions/ Lifetime Engaged Users)

Once you have your data prepared, you can start the analysis!

Data Analysis:

1. Go to Insert>Pivot Table
2. Select columns you want to do analysis for
3. Figure out what metrics is most useful to you thinking what is that you want to get out of data
   1. For example, I would want to increase Engagement Rate for my brand. For that I will look into (select/drag-drop) columns- Theme (theme that gets me good engagement rate), Day (best day for specific posts), Type (what works better- photos, links, videos, questions) and of course the Engagement Rate column. 
4. Here’s what you’ll see after getting your selected data columns on PivotChart
5. Play around with the dataset and filters, select columns you’d like to analyze with a view to increase the engagement rate  for your Facebook Page.

You can also target some other KPI than Engagement Rate, sometimes you’d like to see how did your paid posts do- in that case, you will have to focus on columns related to paid posts.

This blog is based on Courtney Livingston’s presentation Social Content Analysis.

About these ads