🚀 Data Analyst Project Series – Part 10

Social Media Analytics Project (Beginner to Intermediate Guide)

🎯 Project Goal

The goal of this project is to analyze social media performance and discover insights related to:

• Audience engagement

• Content performance

• Reach & impressions

• Follower growth

• Hashtag performance

• Platform trends

Social Media Analytics is one of the most in-demand analytics fields because businesses rely heavily on digital platforms for growth.

This project is widely used in:

• Digital marketing agencies

• Influencer marketing

• E-commerce brands

• Media companies

• Startups

🛠 STEP 1: Choose the Dataset

Recommended Dataset Types

Search on Kaggle:

• Instagram Analytics Dataset

• Social Media Engagement Dataset

• YouTube Analytics Dataset

• Twitter/X Analytics Dataset

You can also export your own:

• Instagram Insights

• YouTube Studio Analytics

• LinkedIn Analytics

📂 STEP 2: Understand the Dataset

Common Columns

Column Name : Meaning

Post ID : Unique post identifier

Platform : Instagram/YouTube/etc

Post Date : Content upload date

Likes : Total likes

Comments : Total comments

Shares : Number of shares

Reach : Total people reached

Impressions : Total views

Followers Gained : New followers

Hashtags : Tags used

Engagement Rate : Interaction percentage

🧹 STEP 3: Data Cleaning

Social media datasets often contain:

• Duplicate posts

• Missing engagement values

• Inconsistent hashtags

• Incorrect date formats

✔ Cleaning Tasks

Remove Duplicate Posts

Check:

• Duplicate Post IDs

Handle Missing Values

Common missing fields:

• Reach

• Shares

• Comments

Methods:

• Replace missing values

• Remove incomplete rows

Standardize Platform Names

Example:

• “Insta”

• “Instagram”

• “IG”

Convert into:

• “Instagram”

Correct Numeric Formats

Examples:

• Reach → Integer

• Engagement Rate → Percentage

📊 STEP 4: Define Social Media KPIs

Essential KPIs

✔ Total Posts

COUNT(Post_ID)

✔ Total Engagement

SUM(Likes + Comments + Shares)

✔ Engagement Rate

Purpose:

Measures audience interaction quality.

✔ Follower Growth Rate

Purpose:

Measures audience growth performance.

✔ Average Reach Per Post

AVG(Reach)

🗄 STEP 5: Analyze Social Media Data Using SQL

📌 SQL Query Examples

1. Top Performing Posts

SELECT Post_ID,

SUM(Likes + Comments + Shares) AS Total_Engagement

FROM Social_Media_Data

GROUP BY Post_ID

ORDER BY Total_Engagement DESC

LIMIT 10;

2. Platform-wise Engagement

SELECT Platform,

AVG(Engagement_Rate) AS Avg_Engagement

FROM Social_Media_Data

GROUP BY Platform

ORDER BY Avg_Engagement DESC;

3. Monthly Follower Growth

SELECT MONTH(Post_Date) AS Month,

SUM(Followers_Gained) AS Followers

FROM Social_Media_Data

GROUP BY MONTH(Post_Date)

ORDER BY Month;

4. Most Used Hashtags

SELECT Hashtags,

COUNT(*) AS Usage_Count

FROM Social_Media_Data

GROUP BY Hashtags

ORDER BY Usage_Count DESC

LIMIT 10;