Project Description:-
Operation Analytics is the analysis done for the complete end to end operations of a company and investigating metric spikes is part of operation analytics. In this project, I have used the Operation_Analytics_and_Investigating_Metric_Spike database provided by trainity and drawn some conclusions in the form of case study 1 and 2. I have provided insights to topics serving different departments of the company. I have used Structured Query Language for data analytics.
Approach:-
Firstly, I have used SQL queries to process raw data and create a relational database with various tables on mysql workbench. Then, I have used different queries to draw insights and answer all the possible questions. Finally, I combined all the results into this report.
Tech-Stack Used:-
I have used “MySQL Server 8.0.32” for locally hosting the database and “MySQL Workbench 8.0.24” as an interface for SQL query editor. It was easy to work with databases and better visualization.
Github link:-
https://github.com/Arpan-shrma/Operation-Analytics-and-Investigating-Metric-Spike
SQL File:-
Project Insights:-
The database included tables namely: job_data for case study 1 and users, events, email_events for case study 2.
A) Case Study 1 (Job Data):
1. Number of jobs reviewed: The number of jobs reviewed per hour per day for November 2020.
Approach — I grouped the dataset according to dates of november 2020 while calculating daily throughput as no of jobs reviewed per hour for each day. In the query 3600 is used as a conversion factor for converting seconds into hours.
Conclusion :- The number of jobs reviewed per hour per day for November 2020 is shown in the result grid as daily_throughput.
2. Throughput: Calculate 7 day rolling average of throughput
Approach — I have used a as an alias to calculate no of job ids. Then used the window
function to calculate an average of 7 day rolling throughput for jobs.
Conclusion :-.7 day rolling average of throughput is shown in the result grid as weekly_throughput. I prefer the 7 day rolling average over daily throughput as it is more accurate due to more instances of data.
3. Percentage share of each language: Share of each language for different contents. Approach: I have used group by statement to group rows having the same language and aggregate function count to calculate the total number of jobs in alias a and Then, I have used the cross join statement to alias b for data of total no. of distinct jobs. Then, the simple percentage formula is used to calculate the percentage share for each language.
Conclusion :- Share of each language for different contents can be seen in the result grid as pct_share_jobs. Here the Persian language content is mostly used by different actors.
4. Duplicate rows: Rows that have the same value present in them.
Get Arpan Sharma’s stories in your inbox
Join Medium for free to get updates from this writer.
Approach: I have used row_number() windows function which returns the sequential number for each row within its partition in target table and then condition that the row number must be more than 1 which means if any duplicate is present then that row number with be more than one and the query will display it.
Conclusion: The query 1 produced no output as there are no duplicates with respect to all columns in the dataset. For clarity of the query, I have changed the partition to single columns and the query produced duplicated rows results in query no. 2,3,4. As seen in query 2 job_id 23 is duplicated in rows 3 times. In query 3, ds 28 nov 2020 and 30 nov 2020 is duplicated twice. In query 4, Persian language is duplicated in 3 rows.
B) Case Study 2 (Investigating metric spike):
1. User Engagement: Calculate the weekly user engagement
Approach: I have used extract function to extract week number of year from the date in the events table data. Then the aggregation function count is used to count active users and grouped according to week number. This data set is of year 2014
Conclusion: The number of active users increased from 663 users in week 17 to 1467 in week 30 of 2014 after that it dropped to only 104 active users in the next 5 weeks.
2. User Growth: Amount of users growing over time for a product.
Approach: I have used group by statement to group active users according to the week number of 2013 and 2014 using where statement to check if the user is active and extract function to extract year and month to target table a. Then used the window function to calculate the cumulative number of active users growing over time from the target table.
Conclusion: The total number of active users from the beginning of 2013 to the end of week 35 of 2014 is 9381 with 261 being maximum active users per week and 18 being the minimum active users per week. The average number of active users per week is 105 in the course of 89 weeks.
3. Weekly Retention: Users getting retained weekly after signing-up for a product.
Approach: I have used the datediff function to calculate the age of users on the platform from the latest date and then used aggregate function to make cohorts to segregate users and grouped the cohort by week.
Conclusion:- Results show that the users who are active are decreasing over time. If we look closely at week 24 the cohort of users 10+ weeks older started decreasing. Thus representing that the users who joined 10 weeks prior to may1 have a decreasing level of engagement overtime.
4. Weekly Engagement: To measure the activeness of a user. Measuring if the user finds quality in a product/service weekly. Calculate the weekly engagement per device.
Approach: I have used condition to check event_type as engagement which segregates the users retained after signup as they are using the platform. I counted the number of users per week per device and just showed the average active users per device per week for different devices.
Conclusion: The average number of active users per week using different devices can be seen in the result grid. Macbook pro is the most popular device with 260 average active users per week and samsung galaxy tablet is least popular with 10 active users per week on average.
5. Email Engagement: Calculate the email engagement metrics.
Approach: I have used the extract function to extract year and week of email engagement. Then, I used the aggregation function with different conditions to calculate the number of different parameters of email metrics and grouped those rows by week and year.
Conclusion: The average weekly digest emails sent to users is 3014 mails per week. The average reengagement emails sent to users is 192 per week. The average emails opened by users is 1077 emails per week and average number of click throughs by users in those emails is 474 times per week.
Result:-
I have answered all the questions asked by the management team in this project and explained the result grid and conclusion under the project insights part. While doing the project I applied my learning of SQL fundamentals and use of aggregate function, sorting functions, operators and join operations along with advanced SQL functions like window function including over, partition by, order by and date time function like extract.
