INSTAGRAM USER ANALYTICS
BY – ADITYA PUNJABI
Project Description
The marketing team is looking to launch several campaigns on Instagram, including rewarding the most loyal users, reminding inactive users to start posting, declaring a contest winner, researching hashtags, and launching an ad campaign. The goal of these campaigns is to increase user engagement and reach on the platform.
To accomplish these goals, I have to find out 5 oldest users of Instagram, identifying users who have never posted a single photo, determining the winner of a contest based on the number of likes on a single photo, identifying the top 5 most commonly used hashtags on the platform, and determining the best day of the week to launch an ad campaign based on user registration data.
In addition, the investors of the company want to know if Instagram is performing well and not. To assess this, I am also tasked with providing data on user engagement, such as the average number of posts per user. The investors also want to know if the platform is crowded with fake and dummy accounts, so I am tasked with providing data on users who have liked every single photo on the site, as these accounts are likely to be bots or fake.
Approach
My approach towards this project would be to first make the database in MySQL from the file. Then, I would use various data analysis techniques to extract insights and information from the data to answer the questions of marketing team and investors. For example, I would use sorting and filtering methods to find the 5 oldest users and users who have never posted a photo, and use statistical methods to calculate the average number of posts per user.
Once the data analysis is done, I would use my findings to make recommendations to the marketing team and investors. For example, I would find top 5 most commonly used hashtags , provide the best day of the week to launch an ad campaign and the data on users (bots) who have liked every single photo on the site.
Tech-Stack Used
I am using MySQL Workbench version 8.0 CE for this Project
Insights
1. Marketing
1.1 Rewarding Most Loyal Users :-
Code - Output -
SELECT username,
created_at
FROM users
ORDER BY created_at
LIMIT 5;
Suggestion – This are the 5 users marketing team has to give rewards for being the most oldest user by giving them stickers or by giving shoutout by mentioning them in story.
1.2 Remind Inactive Users To Start Posting :-
Code - SELECT u.id, Output -
u.username
FROM users u
LEFT JOIN photos p
ON u.id = p.user_id
WHERE user_id IS NULL;
Suggestion – This are the users marketing team has to send the warm messages to remind that to post photo or suggesting content or accounts that may be of interest to them.
1.3 Declaring Contest Winner :-
Code - SELECT username, Output -
p.user_id,
photo_id,
Count(l.user_id) AS likes
FROM likes l
INNER JOIN photos p
ON l.photo_id = p.id
INNER JOIN users u
ON p.user_id = u.id
GROUP BY photo_id
ORDER BY likes DESC
LIMIT 1;
Suggestion – Marketing team to reward this user of high likes on their posts could include offering a prize or special promotion to the user with the most likes on their post or featuring the post on the brand's official Instagram account.
1.4 Hashtag Researching (Top 5 Hashtags) :-
Code - SELECT tag_name, Output -
tag_id,
Count(tag_id) AS times_used
FROM photo_tags pt
INNER JOIN tags t
ON pt.tag_id = t.id
GROUP BY tag_id
ORDER BY times_used DESC
LIMIT 5;
Suggestion – Marketing team to host a contest or giveaway that encourages users to use the hashtags in their own posts.
1.5 Launch Ad Compaign (Best day to launch compaign) :-
Code - SELECT Dayname(created_at) AS day, Output -
Count(*) AS registered_accounts_count
FROM users
GROUP BY Dayname(created_at)
ORDER BY registered_accounts_count DESC;
Suggestion – The best days to launch Ad campaign are Thursday and Sunday. But marketing team wants to focus more on weekends to capture the audience.
2. Investor Metrics
2.1 User Engagement (Average User Posts and Total Users) :-
2.1.1 Average User Posts
Code - SELECT Round(Avg(count)) AS Output -
Average_user_post_count
FROM (SELECT user_id,
Count(id) AS count
FROM photos
GROUP BY user_id
ORDER BY count DESC)a;
2.1.2 Total Users
Code - SELECT Count(username) AS total_users Output -
FROM users;
2.2 Bots And Fake Accounts :-
Code - SELECT user_id, Output -
username AS fake_accounts,
Count(user_id) AS total_likes
FROM users u
INNER JOIN likes l
ON u.id = l.user_id
GROUP BY user_id
HAVING total_likes = (SELECT Count(*)
FROM photos);
Result
While making this project, I have achieved the following –
THANK YOU