Trial Conversions Across Devices

Apple: Measure how many free trials turn into paid subscriptions and how many of those renew.

Assignment

The Services analytics team is preparing the quarterly review for a media subscription that offers a 30-day free trial. They have exported the billing events for every account that started a trial in February or March and asked for a quick analysis of conversion and renewal before the numbers go to leadership.

Deliverables

• The share of trials that converted to a paid subscription, for each plan.
• The share of paid subscriptions that renewed after their first paid period, by the month in which the first payment was made.
• A short closing report of your findings for the Services analytics team, including anything they should double-check before relying on the numbers.

Data Description

apple_subscription_events.csv has one row per billing event:

• event_id
• account_id
• device_id - the device the event was recorded from
• event_type - trial_start, paid_start, renewal or cancel
• plan - individual, family or student
• event_time_utc - when the event was recorded, in UTC
• local_offset_hours - the account's local time zone, in hours ahead of (positive) or behind (negative) UTC

Practicalities

You can solve this in Python, SQL, or a mix of both — StrataScratch notebooks support both languages in the same notebook.

Assume this data comes directly from the billing system and hasn't been cleaned, so review it carefully before relying on the numbers. When something looks off, work out what it is and decide how to handle it; when a definition or a final decision is genuinely ambiguous, choose a reasonable option and explain why. There's often more than one valid way to compute a result, so explain the approach you take and the reasoning behind your choices.

What's assessed: your results, and how clearly you explain what you see in the data and justify the choices you make.