Instagram User Analytics(SQL)

Click here for Excel Data Set

The objective of this report is to analyze user engagement, identify trends, and extract actionable insights from Instagram’s user database to support two primary stakeholders: marketing teams and investors. For marketing, the report focuses on rewarding loyal users, re-engaging inactive users, optimizing ad campaigns, and providing hashtag research for improved reach. For investors, the report evaluates user activity, average posts per user, and detects potential bots or fake accounts to ensure platform integrity and reliable analytics.

This analysis aims to enhance user experience, strengthen marketing strategies, and support data-driven decision-making for platform optimization and stakeholder satisfaction.

Project Description

The Instagram User Analytics project aims to extract valuable insights from a database containing tables for users, photos, photo_tags, tags, likes, follows, and comments. Broadly, there were mainly two categories this project aims to assist – Marketing and Investors. The primary goals are to understand user engagement, explore trends, and identify patterns within the Instagram network. Through this comprehensive approach, the project aims to provide actionable insights for stakeholders, to optimize the platform, understand user engagement, and enhance marketing and to reward loyal users and help root out potential bots and fake accounts. These insights could potentially influence the future development of one of the world's most popular social media platforms.

Approach

The first step was understanding the nature and scale of the project. Because the project is on Instagram analytics, it was crucial to use My SQL as it can handle large datasets. The second step involved employing queries to build a database from the provided raw data. Once done, it was important to have a look at all the tables and columns to understand the type of data included. Following that, queries for sorting and extracting data were implemented to acquire the desired results and gain insights.

Tech-Stack Used

MySQL workbench version 8.0.36 was chosen for this project - Instagram User Analytics, due to its open-source nature, ease of use, and high performance. MySQL is scalable, allowing it to handle both small and large datasets. This is crucial for a platform like Instagram, which deals with a vast amount of user-generated content and interactions.

A) Marketing Analysis:

1. Loyal User Reward: The marketing team wants to reward the most loyal users, i.e., those who have been using the platform for the longest time. 

Task: Identify the five oldest users on Instagram from the provided database.

Insights

The insights gained from this project are the names of the 5 oldest users of instagram. And also that the oldest users are from the 6th of May, 2016. Another insight is that the

id of the users are not in a numerical order.

Result

The outcome reveals the five individuals who have been using Instagram for the longest duration. This helps us identify the early adopters of instagram who could be rewarded for their loyalty or could be reached out to help try out new products by instagram. Since they are long time users, their input could be valuable to understand what they have liked or disliked about the company so far.

2. Inactive User Engagement: The team wants to encourage inactive users to start posting by

sending them promotional emails.

Task: Identify users who have never posted a single photo on Instagram.

Insights

The insights gained from this project are the names and ids of the users who haven’t posted a single photo on instagram.

Result

The outcome reveals the individuals who never posted a single photo on instagram. The marketing team could use this information to send out promotion emails highlighting the benefits of using instagram. They could be reached out to try and understand the reason why they have not been active, whether they use a competitor’s photo application or if they find the interface of instagram difficult to navigate.

3. Contest Winner Declaration: The team has organized a contest where the user with the most likes on a single photo wins.

Task: Determine the winner of the contest and provide their details to the team.

Insights

The insight gained from this project is that we can now identify the user with most likes on his photo on instagram. We also gain an insight on the total number of likes he received and the type of photography that has been most popular.

Result

The outcome reveals the individual who has received the maximum number of likes on his photograph and this information can be used to understand the reason for its popularity.

4. Hashtag Research: A partner brand wants to know the most popular hashtags to use in their posts to reach the most people.

Task: Identify and suggest the top five most commonly used hashtags on the platform.

Insights

The insight gained from this project is that we can identify the most popular tags and the total number of times they were used by users. They are – smile, beach, party, fun and food.

Result

The 5 most used tags can now be provided to the partner brand so they can use these hastags to reach the maximum people.

5. Ad Campaign Launch: The team wants to know the best day of the week to launch ads.

Task: Determine the day of the week when most users register on Instagram. Provide insights on when to schedule an ad campaign.

Insights

There were two days a week when most users register on instagram. They are Thursday and Sunday with 16 users in each.

Result

The marketing team can now launch the ads on either Thursday or Sunday as the data reports that they are the two days a week with most user registrations.

B) Investors Metrics:

1. User Engagement: Investors want to know if users are still active and posting on Instagram or if they are making fewer posts.

Task: Calculate the average number of posts per user on Instagram. Also, provide the total number of photos on Instagram divided by the total number of users.

Insights

The insight shows that the average post per user who are active are 3.82 while the average post of the total users are 2.57.

Result

The results show that on an average each active user is posting 3.82 photos. The average on the total number of users including the inactive users is 2.57. So the inactive users must be encouraged to post more photos to bring the total average higher.

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

Task: Identify users (potential bots) who have liked every single photo on the site, as this is not typically possible for a normal user.

Insights

The insight gained from this project is that we can identify user ids and usernames of

the bots present in instagram.

Result

These identified bots and dummy accounts must be weeded out and the investors must

be informed about it. This is also help the data remain correct and truthful for future analytics

work.

During the project, I learned essential MYSQL terms, improved my proficiency in SQL, and became acquainted with SQL Workbench. I look forward to applying this knowledge in future projects. I confidently provided accurate solutions to all questions, effectively addressing each query to the best of my understanding