--- title: "Product Metrics Case Study Exercise" description: "Subscriptions are up 8 percent, but NPS dropped 12 points. Build a metrics tree, write SQL, design a dashboard, and decide what to fix. A 90 minute exercise." canonical_url: "https://builderscamp.com/guides/challenges/data-for-product-managers-subscription-metrics-case-study" date_published: "2026-09-16" date_modified: "2026-09-16" author: "Andre Albuquerque" publisher: "Builders Camp" guide_class: "challenges" --- # Product metrics case study exercise **TL;DR:** This exercise puts you in charge of Premium at a video learning platform where subscriptions grew 8 percent last quarter but NPS dropped 12 points and refunds are climbing. You build a metrics tree, write three SQL queries against a real schema, design a six panel dashboard, and choose between an acquisition push and a retention fix with a fixed budget. ## The scenario A video learning platform runs a free tier with limited course access alongside a $19 a month Premium subscription with unlimited access and certificates. Last quarter, the CEO celebrated 8 percent subscription growth at the company all-hands. This week, the head of data delivered a report showing that the number underneath that headline is not so simple: NPS dropped 12 points and refunds are climbing. As the PM for Premium, your job is to use four connected data skills, a metrics tree, SQL, a dashboard, and a decision, to figure out what is actually happening and what to do about it. Growth in subscriber count alone does not tell you whether the product is getting healthier or whether more people are signing up for something that is quietly disappointing a growing share of them once they are in. ## What you are asked to do The exercise moves through four connected steps: - Build a three level metrics tree starting from Premium revenue as the north star, breaking it into drivers and then inputs, with 8 to 12 total nodes, each one something you could actually query in a database rather than a vague concept. - Write three SQL queries against a given schema of users, subscriptions, sessions, courses, and enrollments tables: the 30 day retention rate for Premium users, a list of at-risk users with no session in the last 14 days, and a comparison of retention between users who completed at least one course and those who completed none. - Design a six panel dashboard the CEO could open every Monday morning, specifying for each panel what it shows, why it matters, and the exact chart type and axes, then name the single headline metric that should sit above all six. - Make a resource decision between two $60,000 options, an acquisition push to increase trial starts or a retention fix targeting onboarding completion, defended in three to four sentences using specific numbers from your own analysis, plus the biggest risk of being wrong. ## What a strong answer covers The exercise's own objectives are the bar a strong submission has to clear: - Does the metrics tree connect a real north star down through drivers and inputs that could each be pulled from the given schema, rather than including a vague concept like general satisfaction? - Do the three SQL queries actually answer the specific question the CEO raised, using the correct tables and joins, rather than a general query that happens to touch the same data? - Does every dashboard panel name a specific chart type and axes, with a stated warning sign, rather than a generic instruction like "a line chart of growth"? - Is the headline KPI something that captures Premium's health better than raw subscriber count, with a reason given for why? - Does the final resource decision cite specific numbers from the metrics tree or the SQL results, rather than a general preference for one option over the other? ## Skills this exercise practises Connecting a single headline number to the drivers underneath it before trusting what it says about the business. Writing SQL that answers a specific stakeholder question instead of a generic report. Designing a dashboard around what actually needs watching, not everything that could be measured. Making a resource trade-off under a real budget constraint, backed by the numbers you just built rather than intuition. These map onto the bootcamp's own curriculum on metrics that matter, funnel and retention analysis, and communicating insights. For the framework behind picking which metric matters most in the first place, see [north star metric examples](https://builderscamp.com/guides/other/north-star-metric-examples), and for prioritizing what to build once the diagnosis is done, the [RICE prioritization framework](https://builderscamp.com/guides/other/rice-prioritization-framework). If you want to practice a comparable numbers-first audit on a business case instead of a product dashboard, the [business case audit exercise](https://builderscamp.com/guides/challenges/business-for-product-managers-business-case-audit) from Business for Product Managers runs a similar discipline. ## Which bootcamp this comes from This exercise is the practical challenge from [Data for Product Managers](https://builderscamp.com/bootcamps/data-for-pm), a two week bootcamp on Builders Camp with four live sessions covering metrics that matter, funnel and activation analysis, retention and cohort analysis, and experimentation basics. Completing the practical challenge counts toward the bootcamp's completion requirement and its certificate, alongside the certification quiz. Builders Camp runs this bootcamp both live and self-paced, included with the Builders Camp Membership alongside every other bootcamp, track, and masterclass. ## Frequently asked questions ### What is the headline problem in this data exercise? A video learning platform's CEO celebrated 8 percent subscription growth last quarter. Underneath that number, NPS dropped 12 points and refunds are climbing, and your job is to build the metrics, SQL, and dashboard work needed to figure out what is actually happening. ### Do I need to write real SQL to complete this exercise? Yes, three real queries against a given schema: users, subscriptions, sessions, courses, and enrollments tables. You are asked to answer three specific questions a CEO raised, not to memorize syntax, and the schema is given to you. ### What is a metrics tree, and why does the exercise start there? A metrics tree connects a north star metric, in this case Premium revenue, down through the drivers and inputs that move it. The exercise has you build one with three levels and 8 to 12 nodes before touching any SQL, so you know which numbers actually matter before you go query them. ### What is the hard decision at the end of the exercise? Choosing between a $60,000 acquisition push or a retention fix for onboarding, using the same data you just analyzed. Both are plausible. The exercise scores whether your choice is backed by specific numbers from your own work, not which option you pick. ### How long does the exercise take? About 90 minutes, rated intermediate difficulty, inside a two week bootcamp with four live sessions. ### Does completing it count toward a certificate? Yes. Finishing the practical challenge counts toward completing the Data for Product Managers bootcamp on Builders Camp, alongside the certification quiz, and the bootcamp issues a certificate on completion. ## Sources - [Builders Camp: Data for Product Managers bootcamp page](https://builderscamp.com/bootcamps/data-for-pm) ## How this guide was made Researched from Builders Camp's bootcamp, track and masterclass material and the sources listed on this page, drafted with AI, and fact-checked against every source cited.