How to Analyze Instagram User Data with SQL Fundamentals | Trainity Project

4 min readJun 4, 2023

Finding business insights that can be used by teams across the business to launch a new marketing campaign, decide on features to build for an app, track the success of the app by measuring user engagement and improve the experience altogether while helping the business grow.

YouTube video link Presentation: https://youtu.be/SoNvCad2jhM

AGENDA

Project Description
Approach
Tech-Stack Used
Insights
Result

Project Description

Approach

Database creation: Created and inserted the values in the database using the DDL & DML SQL queries provided by the product manager (as per project) in the MySQL database using MySQL workbench.

Extraction of insights: After creating the database required insights are generated from the database tables by running SQL queries in MySQL workbench.

Tech-Stack Used

Used MySQL Community Server — GPL Version 8.0.29 and Connector Version C++ 8.0.29 for creating my project as MySQL Community Server — GPL is a free and open-source relational database management system that uses SQL.

Insights: Marketing

# 1.Rewarding Most Loyal Users: People who have been using the platform for the longest time.

SELECT id,
       username,
       created_at
FROM   users
ORDER  BY created_at
LIMIT  5;

The 5 oldest users of the Instagram from the database are:

# 2.Remind Inactive Users to Start Posting: By sending them promotional emails to post their 1st photo.

SELECT u.id,
       u.username,
       Count(p.user_id) AS 'no._of_posts'
FROM   users u
       LEFT JOIN photos p
              ON u.id = p.user_id
GROUP  BY u.id
HAVING Count(p.user_id) = 0;

The users who have never posted a single photo on Instagram

# 3.Declaring Contest Winner: The team started a contest and the user who gets the most likes on a single photo will win the contest now they wish to declare the winner.

SELECT id,
       username
FROM   users
WHERE  id = (SELECT user_id
             FROM   photos
             WHERE  id = (SELECT photo_id
                          FROM   likes
                          GROUP  BY photo_id
                          ORDER  BY Count(photo_id) DESC
                          LIMIT  1));

Details of the winner of the contest are:

# 4.Hashtag Researching: A partner brand wants to know, which hashtags to use in the post to reach the most people on the platform.

SELECT t.tag_name,
       Count(t.tag_name) AS "tags count"
FROM   tags t
       INNER JOIN photo_tags ph
               ON t.id = ph.tag_id
GROUP  BY t.tag_name
ORDER  BY Count(t.tag_name) DESC
LIMIT  5;

The top 5 most commonly used hashtags on the platform are

# 5.Launch AD Campaign: The team wants to know, which day would be the best day to launch ADs.

SELECT Dayname(created_at) "day of week",
Count(Dayname(created_at)) "count of users registered"
FROM users
GROUP BY Dayname(created_at)
ORDER BY Count(Dayname(created_at)) DESC
LIMIT 2;

Day of the week do most users register on :

Insights: investor metrics

# 1. User Engagement: Are users still as active and post on Instagram or they are making fewer posts.

SELECT (SELECT Count(id)
        FROM   photos) / (SELECT Count(DISTINCT user_id)
                          FROM   photos) AS Average_posts_per_User,
       (SELECT Count(id)
        FROM   photos) / (SELECT Count(id)
                          FROM   users)  AS Ratio_of_Total_Posts_to_Total_Users;

Average user posts and ratio of total posts to total users in Instagram are:

# 2.Bots & Fake Accounts: The investors want to know if the platform is crowded with fake and dummy accounts

SELECT id,
       username
FROM   users
WHERE  id IN (SELECT user_id
              FROM   likes
              GROUP  BY user_id
              HAVING Count(user_id) = (SELECT Count(id)
                                       FROM   photos));

Data of users (bots) who have liked every single photo on the site (since any normal user would not be able to do this) are:

Results

Data analysis using SQL queries to extract insights from database by which we track how users engage and interact with our digital product (software or mobile application) in an attempt to derive business insights for marketing, product & development teams.

Conclusions from above analysis:

Marketing team can reward the most loyal customers, send promotional emails to inactive users, use popular hashtags and most active day for brand promotions.
User engagement can be very useful success metric for the growth of the company.
Company can remove the bots and fake accounts from the platform to enhance the user experience.

Thank you for your time.

GitHub link for all the data sources: https://github.com/sudhansuku/Instagram-User-Analytics

LinkedIn: linkedin.com/in/sudhansu810