Operation Analytics involves analyzing the entire operational process of a company to identify areas for improvement. By closely collaborating with the operations, support, and marketing teams, data is collected, analyzed and insights are derived to help the company enhance its overall performance.
As a crucial aspect of a company, this type of analysis is also utilized to forecast the overall success or failure of a company.
It facilitates improved automation, better coordination among cross-functional teams, and more efficient workflows.
4 of 25
Approach
I first examined the queries and requirements of the ops team, support team, marketing team etc., and then evaluated the available data to identify the most relevant information that would help them achieve their goals.
After that, I approached each problem in a methodical manner and formulated the reasoning behind each query to obtain the intended responses.
Lastly, I optimized each query to improve the efficiency and maintainability of the code.
5 of 25
Software and Tools Used
MySQL Workbench 8.0:
MySQL is a popular open-source relational database management system that is commonly used for web applications and other data-driven projects.
Some common features of MySQL include support for multiple storage engines, the ability to handle large amounts of data, and strong security features.
MySQL is relatively easy to use and learn, which makes it a popular choice for developers of all skill levels.
MySQL has a large and active community of developers who contribute to its development, offer support, and share their knowledge and expertise with others.
6 of 25
Analysis
Number of jobs reviewed: Amount of jobs reviewed over time.�Task: Calculate the number of jobs reviewed per hour per day for November 2020?
Insights:
The SQL query revealed that the max number of jobs were reviewed was on 28th and 30th.
The time given in the data was in seconds was converted to hours
7 of 25
8 of 25
Throughput: It is the no. of events happening per second.�Task: Let’s say the above metric is called throughput. Calculate 7 day rolling average of throughput? For throughput, do you prefer daily metric or 7-day rolling and why?
Insights:
Regarding the preference for daily metric or 7-day rolling average, it depends on the context and the purpose of the analysis.
If the goal is to understand the daily trends and patterns of events, then a daily metric would be more appropriate.
However, if the goal is to smooth out the short-term fluctuations and focus on the long-term trend, then a rolling average would be more suitable.
A 7-day rolling average provides a good balance between capturing the short-term and long-term trends.
9 of 25
10 of 25
Percentage share of each language: Share of each language for different contents.�Task: Calculate the percentage share of each language in the last 30 days?
Insights:
It can be inferred from the findings that Persian language has the largest proportion.
11 of 25
12 of 25
Duplicate rows: Rows that have the same value present in them.�Task: Let’s say you see some duplicate rows in the data. How will you display duplicates from the table?
Insights:
Based on the results, it appears that there were no replicated or identical rows present in the data.
13 of 25
14 of 25
Case Study 2 (Investigating metric spike)
User Engagement: To measure the activeness of a user. Measuring if the user finds quality in a product/service.�Task: Calculate the weekly user engagement?
Insights:
In this query a ‘YEARWEEK’ function is used that will change the timestamp into year and the week number.
15 of 25
16 of 25
User Growth: Amount of users growing over time for a product.�Task: Calculate the user growth for product?
Insights:
New user acquisition showed a consistent increase up until the seventh month, but in the eighth month, there were no new users added.
17 of 25
18 of 25
Weekly Retention: Users getting retained weekly after signing-up for a product.�Task: Calculate the weekly retention of users-sign up cohort?
19 of 25
20 of 25
Weekly Engagement: To measure the activeness of a user. Measuring if the user finds quality in a product/service weekly.�Task: Calculate the weekly engagement per device?
21 of 25
22 of 25
Email Engagement: Users engaging with the email service.�Task: Calculate the email engagement metrics?
23 of 25
24 of 25
Conclusion
While working on this project, I was able to improve my logical thinking and learn how to apply techniques to real-world problems.
The project also gave me exposure to business problems and taught me how to handle and sort datasets using SQL.
This project provided a comprehensive understanding of operations analytics and its functioning.
Overall, this project helped me enhance skills and knowledge in data analysis and problem-solving, which can be applied to future projects and work opportunities.