Operation Analytics and Investigating Metric Spike

Jipson o s

overview

• Project Description
• Approach
• Tech-stack used
• Case Study 1 (Job Data)
• Case Study 2 (Investigating metric spike)
• Insights

Operation Analytics and Investigating Metric Spike

2

Project Description

Operation Analytics is the analysis done for the complete end-to-end operations of a company. With the help of this, the company then finds the areas in which it must improve upon. Investigating metric spikes is also an important part of any company to calculate the company revenue, company growth etc... In this project, we are supposed to provide a detailed report for the operation analytics and investigate metric spikes asked by the different departments. This analysis will help them make a decision based on different metrics and insights.

Operation Analytics and Investigating Metric Spike

3

Approach

First, I convert the given dataset into SQL using the table-to-SQL convertor. Because the table data import wizard method is taking a long time. For case study-1, I added a few rows to the given dataset to calculate some questions like the 7-day rolling average, and the number of jobs reviewed per hour per day, and then I answered the questions given by the various departments. For case study-2, I convert the given dataset to SQL using the tableconvert.com website and I understand what this means I cracked these problems.

Operation Analytics and Investigating Metric Spike

4

Tech-stack used�

• MS Excel - To create table.
• Power point – To create presentation.
• MySQL workbench – To work on my tasks.
• Tableconvert.com – convert csv file into mysql.
• Pen and paper – To roughly draw the tables on my notebook.

Operation Analytics and Investigating Metric Spike

5

Case Study 1 (Job Data)�

Given dataset:- https://drive.google.com/file/d/1jnXY05sc-T44UDnsfn8ElMpS-H651QDT/view?usp=sharinghttps://drive.google.com/file/d/1jnXY05sc-T44UDnsfn8ElMpS-H651QDT/view?usp=sharing

Operation Analytics and Investigating Metric Spike

6

1. Number of jobs reviewed: Number of jobs reviewed per hour per day for November 2020

Query

SELECT ds,COUNT(job_id)*3600/SUM(time_spent) AS jobs_reviewed_hour_per_day FROM table_1

WHERE ds >= '2020/11/01' AND ds <= '2020/11/30’

GROUP BY ds;

​

​

https://drive.google.com/file/d/18VPTGMIR9eA6XFWSTkMBJ5JHaMktazUG/view?usp=share_link

Operation Analytics and Investigating Metric Spike

7

B. Throughput: It is the no. of events happening per second.

Let’s say the above metric is called throughput. Calculate the 7-day rolling average of throughput.

Query

SELECT ds, no_events_per_second,

AVG(no_events_per_second) OVER (ORDER BY ds ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_average

FROM (

SELECT ds,

COUNT(event)/SUM(time_spent) AS no_events_per_second

FROM table_1 GROUP BY ds) subq;

Ans:-

https://drive.google.com/file/d/1PDv6XObDxYT3VbUDDusK9EA8qsgJ-dsP/view?usp=share_link

Operation Analytics and Investigating Metric Spike

8

C. Percentage share of each language: The percentage share of each language in the last 30 days.

Query

WITH subq AS (

SELECT language, COUNT(*) AS count_language FROM table_1 GROUP BY language),

total as ( select count(language) as total_language from table_1)

SELECT language, (count_language * 100.0)/total_language AS percent_share

FROM subq cross join total

order by percent_share desc;

​

​

Operation Analytics and Investigating Metric Spike

9

D. Duplicate rows: Rows that have the same value present in them.

Query

SELECT actor_id,count(actor_id) as count from table_1

group by actor_id

having count(actor_id)>1;

​

​

​

​

I displayed the duplicates by actor_id because I think its unique compare to the job_id.

Operation Analytics and Investigating Metric Spike

10

Case Study 2 (Investigating metric spike)

1. User Engagement: The weekly user engagement.

Query

select extract(week from occurred_at) as week_num,

count(distinct user_id) as active_weekly_users

from table_2_events

where event_type='engagement’

group by 1 order by 1;

Operation Analytics and Investigating Metric Spike

11

Operation Analytics and Investigating Metric Spike

12

B. User Growth: Amount of users growing over time for a product.

Query

select extract(month from created_at) as `month`,

count(user_id) as monthly_users,

count(activated_at) as active_monthy_users

from table_1_users where activated_at is not null

group by `month` order by `month`;

​

Operation Analytics and Investigating Metric Spike

13

Operation Analytics and Investigating Metric Spike

14

C. Weekly Retention of users sign-up cohort: Users get retained weekly after signing up for a product.

Query

SELECT COUNT(user_id) as users,

SUM(CASE WHEN retention_week = 1 THEN 1 ELSE 0 END) AS week_1,

SUM(CASE WHEN retention_week = 2 THEN 1 ELSE 0 END) AS week_2,

SUM(CASE WHEN retention_week = 3 THEN 1 ELSE 0 END) AS week_3,

SUM(CASE WHEN retention_week = 4 THEN 1 ELSE 0 END) AS week_4,

SUM(CASE WHEN retention_week = 5 THEN 1 ELSE 0 END) AS week_5 FROM

(SELECT a.user_id, a.sign_up_week, b.engagement_week,

b.engagement_week - a.sign_up_week as retention_week

FROM ((select distinct user_id, extract(week from occurred_at) as sign_up_week

from table_2_events where event_type = 'signup_flow' and

event_name = 'complete_signup' and extract(week from occurred_at) = 18)

a left JOIN (

select distinct user_id,extract(week from occurred_at) as engagement_week

from table_2_events where event_type = 'engagement') b

on a.user_id = b.user_id) order by a.user_id ) a

Operation Analytics and Investigating Metric Spike

​

15

​

Ans-

​

​

​

Operation Analytics and Investigating Metric Spike

​

16

D. Weekly Engagement per device: To measure the activeness of a user. Measuring if the user finds quality in a product/service weekly.

Query

select extract(week from occurred_at) as`week`,

device, count(distinct user_id) as weekly_engagement_per_device from table_2_events

where event_type='engagement’

group by 1,2 order by 1,2;

ans:-

https://drive.google.com/file/d/1NDvRp1v0hVl0Uj_E4Jf4zLKBnbZjXN_o/view?usp=sharing

Operation Analytics and Investigating Metric Spike

​

17

E. Email Engagement: Users engaging with the email service.

Query

Select extract(WEEK FROM occurred_at) as week_num,

COUNT(CASE WHEN table_3_email_events.action = 'sent_weekly_digest' THEN user_id ELSE NULL END) AS weekly_emails,

COUNT(CASE WHEN table_3_email_events.action = 'sent_reengagement_email' THEN user_id ELSE NULL END) AS reengagement_emails,

COUNT(CASE WHEN table_3_email_events.action = 'email_open' THEN user_id ELSE NULL END) AS email_opens,

COUNT(CASE WHEN table_3_email_events.action = 'email_clickthrough' THEN user_id ELSE NULL END) AS email_clickthroughs

FROM table_3_email_events

GROUP BY 1 order by 1;

Operation Analytics and Investigating Metric Spike

18

Operation Analytics and Investigating Metric Spike

19

Insights

for case study-1

The most spoken language is Persian which is 37.5 per cent. We used the 7-day rolling average of throughput as it gives the average for all the days right from day 1 to day 7 whereas, the daily metric gives the average for only that particular day itself.

For case study-2

The weekly users and user growth per monthly are declining after week 30 and the month of august respectively. This means that some of the users do not find much quality in the product/service in the last months and weeks.

Operation Analytics and Investigating Metric Spike

20

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

Operation Analytics and Investigating Metric Spike

21