Chapter 7: Product Analytics. Part 2
The first chapter on Product Analytics was focused a lot on learning SQL fundamentals required for all Data Analysis – different applications of SQL JOIN-s, aggregate and window functions.
Consider this chapter a practice for everything that you’ve already learned in a business context.
Namely, you’ll focus on analyzing the remaining of the AARRR startup metrics – Activation and Retention.
Activation analysis
Activation analysis is trying to answer the question “What percentage of new users actually started using our product?”.
When it comes to activation, we can identify multiple activation steps – a user finished onboarding (soft activation), used a certain product features (hard activation) and everything in between.
Remember, AARRR (Acquisition, Activate, Retention, Revenue, Referral) framework is a basically a funnel analysis that could be applied to any product. As with any funnel analysis, we need to know what funnel step has the biggest churn rate, so we can fix it.
Retention analysis
Retention is probably the most straightforward AARRR funnel step. We need to answer the question “How many users who signed up on a certain day used the app on day 1, day 7, day 30, day X”.
Basically, retention rate is a number. You may’ve heard about retention curves – it’s when we plot retention rate for every day on a chart and have these beautiful pictures.
With this being said, retention rate analysis is probably the most tricky one SQL wise, so roll up your sleaves – we’re going to work, let’s go!
-
1. Calculating release date of the app from dataStart
-
2. Extracting release dates from dataStart
-
3. Counting app releases per monthStart
-
4. Introduction to mobile analyticsStart
-
5. How to measure activation rateStart
-
6. Calculating soft activation rateStart
-
7. Activation rate per countryStart
-
8. Measuring activation rate without analytics events. Part 1Start
-
9. Measuring activation rate without analytics events. Part 2Start
-
10. Retention rate basicsStart
-
11. Calculating D7 retention rate for a daily cohortStart
-
12. Aggregating analytics events into daily activityStart
-
13. Calculating D1-D30 retention curve. Part 1Start
-
14. Calculating D1-D30 retention curve. Part 2Start
-
15. Calculating D1-D30 retention curve. Part 3Start
-
16. Using helper table for date rangesStart
-
17. Product Analytics. Part 2 recapStart
Lessons and exercises
![Anatoli Makarevich, author of SQL Habit]()
About SQL Habit
About SQL Habit
Hi, it’s Anatoli, the author of SQL Habit. 
SQL Habit is a course (or, as some of the students say, “business simulator”). It’s based on a story of a fictional startup called Bindle. You’ll play a role of their Data Analyst 
and solve real-life challenges from Business, Marketing, and Product Management.
SQL Habit course is made of 13 chapters (you’re looking at one atm) that contain 273 bite-sized lessons and exercises. All of them have a real-life setting and detailed explanations. You can immediately apply everything you’ve learned at work. 
The 2nd part of the course is called Practice. It’s made of standalone exercises based on multiple datasets – E-commerce, Finance and Meditation app a-la Headspace or Calm. Practice exercises are harder than in the main course. They’ll get you ready for any challenge at work or an interview. 