Задание #1
Context: You ran a test with a campaign manager to optimise the performance of an acquisition channel. Test information below.
1. Which discount performed best?
2. What is the minimum av. loyalty of Group C to remain profitable?
3. What if one offer was 2*$20? What additional information would you need to draw a conclusion?
4. What data are you missing to give a certain recommendation to the campaign manager?
Задание #2
Profit Drop. Context: The Growth Manager ask you to better understand a drop in profit (€) since the beginning of the year:
To answer the question, you pull some data and build the following graphs:
Additional information:
Loyalty is defined as the number of boxes ordered by a customer while their Hellofresh subscription is active.
On the charts, loyalty and profits are analyzed by group of customers (or cohorts) who subscribed to HelloFresh in the same month.
Questions:
How can you explain the drop in total Profit?
Please summarize your findings in an email to send to the Growth Manager.
Add some initiatives you would take to bring profits back up
Задание #3
SQL. Questions
Table structures: boxes_shipped (box_id: int, delivery_date: int, customer_id: int, fk_product_id: int, revenue_eur: float, country: string, delivery_week: string)
products (product_id: int, country: string, product_name: string, product_family:string)
Important information
All tables might have additional columns, which are not necessary in this example
All tables are clean and have no duplicate records
A given customer, in a given country, may have between 0 and 1 delivered boxes and products for a given week
The combination of customer and country is unique
Dates are stored in an INT field, with format as YYYYMMDD
boxes_shipped.delivery_week follows the format YYYY-W00 (e.g: 2020-W01 = first week of 2020)
boxes_shipped data is stored in a partitioned table, with country and delivery_week as partition columns.
Based on the below table structure, please answer the following questions, creating a SQL query to retrieve the data for each one of those:
1. The total amount of boxes delivered along with the amount of customers delivered to, per week, country, for the year of 2020
2. What were all the products and their respective product families sold in each country in the first 10 weeks of 2020.