Imagine that you have already started your job as a Data Analyst at Semrush, you have completed basic onboarding, and started receiving your first tasks from colleagues.
The situations in the examples below are exaggerated in some places and simplified in others, but generally, they look like real problems that come to analysts.
The first two are quite simple and have the «correct» answers, but with the last example we would like to see your thinking process, how deep you dig into data, how you present the result, and visualize the data. Good luck!
Задание #1
Colleagues conducted an A/B test for the new design of the website’s home page for completely new users. The target metric in the experiment was the conversion to a paid subscription. The following results were obtained.
Colleagues ask you to confirm them and give a final conclusion on the experiment.
Option A (control) - 100047501 visitors, 1003 payments.
Option B (test) - 100001055 visitors, 1099 payments
What recommendations would you make based on this data?
Задание #2
You have a «payments» table with columns «payment_id«, «payment_date«, «amount«.
The table has data for the last several years, the number of payments is quite large (millions), payment amount also varies greatly. Your colleagues would like to better understand our biggest customers and asked you to prepare a list of the largest payments for each month since the beginning of 2021. They are interested in the top 5% payments and their amount.
Write an SQL query to the payments table that will display for each month since the beginning of 2021 the 5% of the largest payments in this month, and in the additional column month_sum will show the total revenue for all payments in this month.
The resulting table should contain the columns «month», «payment_id», «payment_date», «amount», «month_sum», the results should be sorted by month and payment amount (both columns are in descending order). The result of this task is a complete SQL query.
Задание #3
You have been provided with the data: synthetical_payments.csv It’s an extract with payment data over the last few years.
The context: users are paying for a subscription to the service at different levels (product: PRO -> GURU -> BUSINESS). The company is interested in users to pay as long as possible. Users can change the subscription level over time. You can pay every month, or you can pay for the year at once and get a small discount.
Other fields in the data:
price is the price of the product per month
amount is the actual amount paid during the subscription period
period is the duration of the subscription that a user paid for (# of months)
transactionTime is the transaction timestamp in the Unix time format (seconds)
Based on this data, tell the stakeholders how the business is doing, how well it retains users, and whether there are problems in user segments.
The result of completing a task is a visual representation of what is happening in the business. The form can be anything: from text with pictures to an online Tableau workbook. Please also attach the code you wrote to process the data and get the output.