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.
You are working with the product team of Instagram and the product manager has asked me to provide insights on the questions asked by the management team.
For this project, I have used My SQL Knowledge to connect with SQL Server using My SQL Workbench and extracted the required data from the given database using the Join function, subqueries, Aggregation, where Clause, Group by, Distinct and other functions are required.
keeping the Primary key and foreign key in consideration provided all the reports asked by the marketing department and Investor metrics department.
I have used Google sheets and documents for making this presentation as it contains required Elements, Graphs, Images which made this project more attractive.
Click here to know the commands used for Analysis
1) Rewarding the Most Loyal Users:
Here I am going to find the 5 oldest users who have been using this platform for the longest time.So it will be helpful to the Marketing team to reward the most loyal users.
Keywords in Query : Select( For Selecting the columns ) , order by ( To Sort the Data ) , Limit ( To display the top number of rows ).
Click here to see Loyal Users
2)Remind Inactive Users to Start Posting:
Here I am going to Identify users who have never posted a single photo on Instagram.So it will help the Marketing team to encourage inactive users to start posting by sending them promotional emails.
Keywords in Query : Where (Clause is used to filter the data based on condition) ,not in (include rows where a condition is not true)
Click here to see Inactive Users
3) Declaring Content Winner:
Here I am going to Identify the maximum number of likes for the Photo and respective information of the user. So it will help the Marketing team to declare the Winner of that Contest.
Keywords in Query : inner join (this join is used to get the rows from the both columns which are having same data based applied on condition) ,group by (which is used to group the data based on column name).
Click here to see details of Winner
4) Hashtag Researching:
Here I am going to Identify the top 5 Most used Hashtags by users. So it will help the partner brands to know the most popular hashtags to use in their posts to reach the most people.
Keywords in Query : as (this command is used to rename a column or table with an alias. An alias only exists for the duration of the query).
Click here to see top 5 hashtags
5) Launcg AD Campaign:
Here I Identified the Day Of the Week are Thursday and Sunday Which help the Marketing team to know the best day to launch ads.
(1 - Sunday to 7 - Saturday)
Click here to see the user regestrations Vs Day of the week
1) User Engagement:
Here I am going to calculate the average number of posts per user on Instagram. So It will help the Investors to know if users are still active and posting on Instagram or if they are making fewer posts.
Keywords in Query : left join (this join is used to get the all rows from the left table and matching rows from the right table based applied on condition)
Quantitative Metrics:
Also, I would like to provide details like the total number of photos on Instagram (257) & total number of users (100),Active users (74). So the Average is 2.57
Based on the results, there are –
so the average will be 257/74 = 3.47, Based on the data. we can say that an average user posts 3-4 times.
Click here to see the Average number of posts per user
2)Bots & Fake Accounts:
Here I am going Identify users (potential bots) who have liked every single photo on the site, as this is not typically possible for a normal user.So it will help the Investors to know if the platform is crowded with fake and dummy accounts. By this metric we have identified a potential impact for the investors.
Keywords in Query : count() (This function is used to count the number of rows.
Click here to see the Duplicate Accounts
From this project, I got an idea about how as a business or data analyst we work on real-time data to take and data-driven decisions. One thing I infer about this project is the dataset provided was very limited and small in respect of Rows and columns, but still, it was a very good experience working on such a project. It helped me a lot to understand the analysis process well, and to provide insights for the best decision possible.