Chapter 5: Product Analytics. Part 1

Welcome to the chapter on Product Analytics!

You’re almost there, this is the last chapter required to unlock a SQL Habit Certificate.

After we’ve covered Marketing Analytics in Chapter 3 and Unit Economics in Chapter 4, the only thing left is to talk about Product Analytics.

The instruments we’re going to use here should sound familiar by now – we’ll look at timelines, we’ll look at cohorts of users and we’ll build a lot of funnels.

The funnel from the 3rd chapter was very simple – people click on ads, created Bindle (a fiction company behind the SQL Habit course) accounts and purchased subscriptions. Also our goal with Marketing Analytics is straightforward – we need to measure campaign profitability to make bidding decisions.

Product Analytics takes our funnel game further – we’ll try to answer more general questions like “How people use our product?”, “What’s the biggest bottleneck in our product?”, “What feature should we build next?”, etc.

Under the hood, though, they’re still the same good old funnels – our goal is to understand how the real-world process is modeled with data in our data warehouse; join necessary tables together and count users at every step of the funnel.

Of course, there’re many flavors to that technique – we’ll build funnels with absolute numbers, we’ll build measure percentage of users churned at every step, we’ll break it down to multiple user cohorts, etc.

SQL theory

You’ll also learn how to work with JSON data. Querying JSON data is a must in modern Data Analysis. All web and mobile analytics frameworks rely on JSON to track specific context features. For example, in Bindle we’ll track “reading” or “listening” event, but a book’s name, page number, time on page will all be stored in a JSON column.

This chapter will fully complete your SQL for Data Analysis checklist by covering window functions. IMO window functions is the most complex part of SQL, but once you’ve mastered it you can answer any question with a query.

SQL Habit is called SQL Habit because I want you to be at the level where you don’t even think about syntax. After this chapter you’ll get very close to it, at least from the SQL theory point of view.

After you finish this chapter, I highly recommend start solving Practice exercises. They’re based on different datasets and they’re more complex than exercises in the course.

Practical skills

A big chunk of Product Analytics is based on funnels. In turn, product funnels are based on web or mobile analytics data. Someone land on a page – we track an event, someone clicked on a button – we track an event, etc.

Eventually, somewhere in the data warehouse there’s a table with all these events (often 2 tables – for web and mobile events respectively).

Having product events in one table gives us unlimited opportunities for analysis. There’re so many insights hidden in these 2 event tables, now it’s a matter of asking great questions and writing SQL queries!

We’ll warm up and calculate CTR (Click Through Rate) for a signup button. I’m sure you’ve heard about an AB-test in Booking or Amazon where they tested 2 versions of the same button – one was slightly bigger. So was CTR.

Then we’ll move to building a web onboarding funnel. It’s going to be a big query with lots of joins. Sounds a bit scary, but on the flip side it’ll give you a template to build any funnel.

To see window functions in action we’ll look back at a mission-critical use case – user attribution (often referred to as “marketing attribution”). Attribution is a process of figuring out where users came from (from a referral link, from a marketing campaign, etc). This information is vitally important for product as well (for cohort analysis).

All righty, you’re 38 lessons and exercises from unlocking a SQL Habit Certificate! Buckle up and let’s go!