Visual analysis of my last 100 Facebook posts

The Background

Ever wondered why the number of people who respond to your Facebook posts varies based on the post? Have you ever gave it a thought that your friends’ list might run into thousands but the people who respond would most certainly be in hundreds. And, you wouldn’t have missed that there are a handful of people who respond to your posts regularly.

Same questions were running in my mind when I observed the number of friends who respond to my posts and my total friends count of 765.


Through this exercise, I wanted to analyse the response rate of my Facebook posts, reveal hidden facts and patterns. As this was a personal project, the final output was not planned at this stage.

The Challenge

I started by looking for settings on Facebook to download the response rate data for a given period of time. Unfortunately, Facebook doesn’t offer any. It became clear that it was going to be a daunting task to manually scan through each post and download their data.

Every post required a series of manual steps to be performed:

  1. Selecting and copying the friends who have responded for a post
  2. Pasting it to notepad to remove formatting
  3. Copying this data and pasting onto Excel
  4. Finding and removing any text other than friends’ name
  5. Sorting the names alphabetically
  6. Appending this list to Excel with date, month, post category and a short description to identify the post later.
These steps were repeated 100 times. Wish I knew how to write Macros!

Time to Sort Data and Filter

Once all the data from 100 posts were added to Excel, it was time to slice & dice the data. To better organise, I started grouping the data for each month.

While consolidating the monthly data, there was a duplication of names which were removed by using Remove Duplicates function in Excel. That resulted in a unique number of responses in a month. The number of posts in a month and their types were also consolidated.

When all the posts data were grouped as months, it was time to consolidate the entire data set. That exercise gave me fresh insights which were hidden till now. It also revealed that there were a few instances where friends had multiple Facebook profiles. Those duplications were corrected in the unique response list. Additionally, people who were not in my friend’s list and responded were also not counted in the total response count.

Fresh Insights Revealed

Here are a few hidden insights that were revealed when the entire data set was consolidated:

Looking at this formalised data, it was tempting to visualise it as a infographic. That would make the data more meaningful and intuitive.

Visualising Data

I started out with a few initial sketches and tried different visual means for representation.

Design Iterations

I preferred the idea of using Sankey diagram to visualise the data.

Final Design

I completed my final design by adding an introduction and few additional insights at the top.

Facebook visual analysis

What I Learnt

I conceived this project while wondering about how my friends were responding to my Facebook posts. Initially, I did not have a clue to design such an infographic. But, while working with the data, it started revealing lots of hidden insights. That’s when I realised the idea of translating this raw data into an graphical representation so that it speaks for itself without explanation.

In this project, I learnt a few functions in Excel that helps in sorting, cleaning and filtering data. In Adobe Illustrator, I learnt how to draw smooth curves that can be easily edited.

Overall, it was a fun personal project.

Prev Project

Plant trees remotely with smartphones using drones and LIDAR (Light Detection and Ranging)

Next Project

Hypothesis on making Google Maps more intuitive with road surface condition indicator