Тестовое задание
На продуктового аналитика
Apalon

Текст задания и данные приведены в таблице excel (вкладка Task 1)

The data below represents usage statistics of some application.
Please, analyze the data and answer the following questions:

1. Describe how the app* metrics were developing during the last year. What trends do you observe? Provide your explanation of the trends and mention all details that may matter.

2. Can you say from the data below what caused average session growth at the end of December 2015 and its drop in the beginning of June 2016?

3. What other useful metrics can be calculated from the data presented in the table?

4. Present the findings of your analysis in a form that will be understandable to TOP management. Use any tools you consider to be the best ones for carrying out the analyisis and visualization your findings.

*app - short form of "application"

Текст задания и данные приведены в таблице excel (вкладка Task 2)


You have 3 types of data below:

- #1 shows what % of users returns to the application after the month of install (month 0). Example: if 100 users installed the app in January then just 24 users opened the app again in March.

- #2 represents the number of monthly active users (who used the app at least once during the month)

- #3 shows how much revenue this free app earns from advertising monthly (showing advertisments in the app while a person uses it).

1. Please, forecast what % of users will return to the app in month 13, 14, 15, … and so on. When will the last user leave the app?

2. Calculate lifetime value (LTV) of an average user - how much money an average user will bring to a company during the whole time it will use the app.

Текст задания и данные приведены в таблице excel (вкладка Task 3)

The data below represents sales statistics of some paid application.

You need to analyze the data and advice a marketing manager:

1. At what price does application maximize its margin (Revenue-Marketing spend)?

2. What factors have influence on total insatlls of the application? What factor influences app downloads more? Evaluate their influence and statistical significance using regression analysis.

3. Try to forecast Total application installs at price $19.99. How will app margin change? Is it worth increasing the app price to this level?

4. Show the findings of your analysis in two forms:

____a) save all intermediate calculations that you've done to demonstate the logic and steps of your analysis to a senior data analyst;

____b) summarize your conclusions in a short and clear form to present them to a marketing manager.

Текст задания и данные приведены в таблице excel (вкладка Task 4)

Please write SQL queries which will answer the following questions:

1. Please find the absolute difference in revenue (price multiplied by quantity) between the last purchase in the status Regular and the first purchase in the status 'Loyal' for each userid;

2. Please calculate the number of unique daily purchases in the status regular and loyal for each userid. The output should have a format - userid, number of regular purchases, number of loyal purchases;

3. Find the difference in days between the first regular purchase and the second loyal purchase for each userid;

Текст задания и данные приведены в таблице excel (вкладка Task 4, 65 cтрока)

Please write SQL query which will answer the following questions:

Please find distinct names of users who bought books but not clothes.