Cadence Radio is an internet radio service that listeners reach on many kinds of devices: the web, smartphones, tablets and e-readers, cars, home entertainment devices and others. The CTO wants to understand how each platform has grown over the last two years, how well each one keeps listeners coming back day to day, and where engagement is improving.
The data are simulated and aggregated by platform; there are no listener-level records.
The data
All four tables have one column per platform: tablet_e_reader, other, web, in_vehicle, smartphones, ce_home (consumer-electronics devices in the home).
monthly_uniques— one row per month (month, January 2012 to December 2014): unique listeners who used the platform that month.all_time_uniques— one row per month: unique listeners who had ever used the platform by the end of that month (a cumulative count).monthly_hours— one row per month: total listener hours on the platform that month.daily_uniques— one row per day (date, January 1, 2013 to December 31, 2014): unique listeners who used the platform that day.
There is no service-wide total, and a listener may use more than one platform, so don’t assume the platform columns add up to the number of people using the service.
Your task
- Compare monthly uniques on each platform from January 2013 to December 2014. How has the platform mix changed, and which trends deserve the CTO’s attention?
- Devise a single metric that measures how successful each platform was at retaining listeners who use the service on a daily basis during December 2014 — one number per platform.
- Explain why this is the best measure of retention you can build from these tables, what alternatives you considered, and what each would get wrong.
- Rank the platforms by your metric. How much does the ranking depend on the choice of metric?
- Compare December 2013 with December 2014: how did average listener hours per monthly unique change on each platform, and which platforms grew fastest? Is that growth driven by listeners listening more, or by changes in who the platform’s listeners are (for example, a wave of new, lighter users)? How much would you trust a single-month comparison?
- Based on all of this, where would you focus engineering and product investment next year, and what listener-level data or experiments would you want to confirm the story these aggregates tell?
What to deliver
A short deck (5–8 slides) for the CTO. The fit and finish of the slides matters: clear charts, one message per slide. Include an appendix with the definitions behind each metric and the queries or code that produced every number.
All company names in this question are fictional.