OPERATION ANALYTICS AND INVESTIGATING METRIC SPIKE�
BY – ADITYA PUNJABI
Project Description
This project is about analyzing and evaluating two data sets, one for job data and the other for user engagement and email engagement. The project aims to find important insights and metrics through data analysis to evaluate the performance and growth of different aspects of the datasets. The job data set will be analyzed to calculate the number of jobs reviewed per hour per day for November 2020, 7-day rolling average of throughput, percentage share of each language, and duplicates in the data.
On the other hand, the user engagement and email engagement data sets will be analyzed to find out weekly user engagement, user growth, weekly retention of users-sign up cohort, weekly engagement per device, and email engagement metrics. To handle these analyses, the project will be conducted using SQL.
Approach
My approach towards this project would be to utilize SQL for data analysis. I would start by creating the database and reviewing the data. I would execute the required calculations and analysis using SQL commands such as SELECT, WHERE, GROUP BY, and aggregate functions like COUNT, SUM, AVG, etc.
For the first case study, I would calculate the throughput, language share, and identify duplicate rows. For the second case study, I would calculate the weekly user engagement, growth, retention, engagement per device, and email engagement metrics. The results of the analysis would then be presented in tabular format. This project would require a strong knowledge of SQL to effectively execute and achieve the desired results.
Tech-Stack Used
I am using MySQL Workbench version 8.0 CE for this Project
Insights
Case study 1
A. Number of jobs reviewed :-
Code - SELECT ds, Output -
Count(job_id) AS reviewed_perDay,
Count(job_id) / 24 AS reviewed_perHour
FROM job_data
WHERE ds >= "2020-11-01"
AND ds <= "2020-11-30"
GROUP BY ds;
On the date 28 and 30 November the double the jobs reviewed as per other days in the month.
B. 7 day rolling average of throughput :-
Code - SELECT ds, Output -
Avg(Count(job_id)) over
(ORDER BY ds rows 6 preceding)
AS 7_days_rolling_average
FROM job_data
GROUP BY ds
ORDER BY ds;
For the observation of the overall trend and smooth out fluctuations, a 7-day rolling average would be a better choice. A 7-day rolling average can provide a better representation of the long-term trend, as it reduces the impact of short-term fluctuations.
C. Percentage share of each language :-
Code - SELECT language, Output -
Concat(( Count(language) / (SELECT Count(*)
FROM job_data) ) * 100, "%") AS lang_share
FROM job_data
GROUP BY language;
Persian language has the highest share 37.5 % and other language has the similar share throughout.
D. Duplicate rows :-
Code - SELECT ds, Output -
job_id, actor_id, event,
language, time_spent,
org, Count(*)
FROM job_data
GROUP BY ds, job_id, actor_id,
event, language,
time_spent, org
HAVING Count(*) > 1;
There are no duplicated rows in the data.
Case study 2
A. User Engagement (Weekly) :-
Code - SELECT Week(occured_at) AS weekly, Output -
Count(DISTINCT user_id) AS active_users
FROM events
WHERE event_type = "engagement"
GROUP BY weekly, event_type;
The highest engagement of users in the week 30 that is 1467 and all the other weeks are in the range 1100 to 1400.
B. User Growth For The Product :-
Code - WITH monthly_act_users Output -
AS (SELECT Year(activated_at) AS year,
Month(activated_at) AS month,
Count(user_id) AS active_users
FROM users
WHERE state = "active"
GROUP BY month, year)
SELECT *,
( active_users - Lag(active_users)
OVER(ORDER BY year, month) ) AS monthly_growth
FROM monthly_act_users;
The growth is increasing month by month after 4th month of 2013 and lowest in the 2nd month of 2014.
C. Weekly Retention:-
Code - SELECT *, Output -
Concat(( ( weekly_users / Lag(weekly_users)
OVER( ORDER BY year, week) ) * 100 ), "%") AS
"retention based on previous week"
FROM (SELECT Year(occured_at) AS year,
Week(occured_at) AS week,
Count(DISTINCT user_id) AS weekly_users
FROM events
WHERE event_name = "login"
GROUP BY year, week) a;
User retention is highest in 18th week of 2014 and lowest in 31st week of 2014.
D. Weekly Engagement :-
Code - SELECT Week(occured_at) AS week, Output -
device,
Concat(Count(DISTINCT user_id), "users")
AS engagements
FROM events
WHERE event_type = "engagement"
GROUP BY week,
device;
Weekly Engagement is highest in the all Apple company devices and further decrease in other devices.
E. Email Engagement :-
Code - SELECT concat(round((COUNT(CASE
WHEN action = "email_open" THEN 1 END) /
COUNT(CASE WHEN action IN ('sent_weekly_digest’,
'sent_reengagement_email') THEN 1 END)) * 100), " %") AS "email opened rate",
concat(round((COUNT(CASE WHEN action = "email_clickthrough" THEN 1 END) /
COUNT(CASE WHEN action IN ('sent_weekly_digest’,
'sent_reengagement_email') THEN 1 END)) * 100)," %") AS "email clickthrough rate"
FROM email_events;
Output -
Email opened rate is 34 % and Clickthrough rate is 15% from Total sent emails .
Result
Results have achieved in the project are –
THANK YOU