INSTAGRAM USER ANALYTICS

Jipson o s

overview

• Introduction
• Project Description
• Approach & Tech-stack used
• Marketing
• Investors metrics
• Insights

Instagram user analytics

2

Introduction

User analysis is the process 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.

These insights are then 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.

Instagram user analytics

3

Project description

This project is about collecting data from the given database “Instagram” to provide business insights to the management team. The main goal of the project is to

• find the 5 oldest users of the Instagram
• find the users who have never posted a single photo on Instagram
• identify the most liked photo on the Instagram
• find the top 5 trending hashtags on the platform
• what day of the week do most users register on?
• user engagement
• bot & fake accounts

Instagram user analytics

4

Approach & Tech-Stack used

First, I just installed the software required for doing this project which is installing MySQL on my system and watched the MySQL workbench tutorial to get familiar with the software. Then, I run the commands given from the dataset to create the database and do my task on the MySQL workbench which is filtering the data to get the result. I also briefly write down the tables in my notebook to get the connection between the columns from the different tables to write queries. I also visualise how can I connect these tables. I also used MS excel to import tables from the MySQL bench.

Instagram user analytics

5

Marketing

1. Rewarding Most Loyal Users: 5 oldest users of Instagram from the database provided are

SQL query -

select username,created_at from users

order by created_at limit 5;

Instagram user analytics

6

2. Remind Inactive Users to Start Posting: users who have never posted a single photo on Instagram.

SQL query:

select users.id,username,created_dat

from users left join photos

on users.id=photos.user_id

where created_dat is null;

Instagram user analytics

7

3. Declaring Contest Winner: most likes on a single photo and their user details.

SQL query

select username,photos.id,photos.image_url,

count(*) as most_likes from photos

inner join likes

on likes.photo_id=photos.id

inner join users

on photos.user_id=users.id

group by photos.id

order by most_likes desc

limit 1;

Instagram user analytics

8

4. top 5 trending hashtags on the platform.

SQL query

select tags.tag_name,count(*) as total

from photo_tags

inner join tags on tags.id=photo_tags.tag_id

group by tags.tag_name

order by total desc

limit 5;

Instagram user analytics

9

5. what day of the week do most users register on?

SQL query

select dayname(created_at) as day_of_the_week,

count(*) as total from users

group by day_of_the_week

order by total desc

limit 2;

Instagram user analytics

10

Investors metrics

1. User engagement:- Average number of photos per users

SQL query

select ((select count(*) from photos)/(select count(*) from users))

as avg_no_of_photos_per_user;

Instagram user analytics

11

2. Bots & fake accounts:- Provide data on users (bots) who have liked every single photo on Instagram.

SQL query

select username,count(*) as num_likes

from users inner join likes

on users.id=likes.user_id

group by likes.user_id

having

num_likes=(select count(*) from photos);

​

Instagram user analytics

12

Insights

One of the main insight I gained from the project is that most users register on Thursday and Sunday. So the marketing team might have a confusion which day is better out of these two. So I prefer the ad campaign should be done on Sunday than Thursday because Sunday is a holiday so the chances of users register on Instagram are higher. Instagram needs to remove the accounts who likes every post on Instagram

Instagram user analytics

13

Thank you��Jipson o s�jipson00716@gmail.com

Instagram user analytics

14