top of page

Instagram User Analytics

Project Description:

User analysis is the process by which we track how users engage and interact with our digital product (software or mobile application) to derive business insights for marketing, product & development teams.

Teams across the business then use these insights to launch a new marketing campaign, decide on features to build for an app, and track the success of the app by measuring user engagement and improving the experience altogether while helping the business grow.


Here, I am working with the product team of Instagram and the product manager has asked to provide insights on the questions asked by the management team.

  1. Find the 5 oldest users of Instagram from the database provided.

  2. Find the users who have never posted a single photo on Instagram.

  3. Identify the winner of the contest (most likes on a single photo) and provide their details to the team.

  4. Identify and suggest the top 5 most commonly used hashtags on the platform.

  5. What day of the week do most users register on? Provide insights on when to schedule an ad campaign.

  6. Provide how many times does average user posts on Instagram. Also, provide the total number of photos on Instagram and/or the total number of users.

  7. Provide data on users (fake and dummy accounts/bots) who have liked every single photo on the site (since any normal user would not be able to do this).


Approach:

All the questions asked can be answered through SQL. I used various operators and functions to get the solutions.

First, I created the new database and loaded all the files (SQL tables) present. Then, I performed an analysis of the Database to get the answers to all the questions asked.


Tech-Stack Used:

MySQL Workbench 8.0.30 build 2054668 CE


Dataset:


Insights:

All the questions asked can be answered through SQL. I used various operators and functions to get the solutions

1. 5 Oldest Users of Instagram

table users;
select * from users order by created_at limit 5;







2. Users who have never posted a single photo:

table photos;
select u.id,u.username,p.id as pic_id from users u left join photos p on u.id=p.user_id where p.id is null

























3. Winner of the contest:

table likes;
select user_id,photo_id,count(photo_id) as Num from likes group by photo_id order by Num desc;

Harley_Lind18 (user id: 3, photo id: 145, likes: 48)


4. Top 5 most commonly used hashtags:

table tags;
table photo_tags;
select tag_id,count(tag_id) as Num from photo_tags group by tag_id order by Num desc
select t.tag_name,pt.tag_id,count(pt.tag_id) as Num from photo_tags pt left join tags t on pt.tag_id=t.id group by tag_id order by Num desc;










5. Day of the week with the most registrations:

table users;
select date(created_at),dayofweek(created_at) as Day1,count(dayofweek(created_at)) as Day_Num from users group by Day1 order by Day_Num desc;

Sunday and Thursday (with 16 registrations each) (1st and 5th day of the week)


6. Average posts per user:

table users;
table photos;

select u.id,u.username,count(p.id) as num from users u left join photos p on u.id=p.user_id group by u.id order by num desc;

2.57; Total users on the platform: 100; Total photos: 257


7. Fake accounts

table users;
table likes;
select user_id,count(user_id) from likes group by user_id having count(user_id) = 257;

















Result:

After doing all the analyses, I propose the following:

The top 5 oldest people who have been using the platform should be rewarded considering their loyalty.

We should send promotional emails to post 1st photo to all the users who haven’t done so.

The contest winner should be declared at the earliest.

For scheduling the ad campaign, we should have it on either Sunday or Thursday, we’ll get better outreach.

We should let the brand partner know about the top hashtags so that they can reach more people.


We should let the investors know that the platform is working in regular condition and have a good online audience.

We should ban the dummy and fake accounts/bots to protect the authenticity of the platform.

Comments

bottom of page