Marketing analytics
Marketing Channel Performance
- Company
Robinhood- Job positions
- Data AnalystBusiness AnalystProduct Analyst
- Topics
- Marketing metricsJoins and aggregationCAC and ROASpandas or SQLBudget planning
The scenario
Robinhood: Compare six months of ad spend against signups and paying customers, then recommend where the next budget should go.
Robinhood lets people open a free account, and some of them upgrade to Robinhood Gold, a paid subscription. You're a marketing analyst on the growth team. Over the first half of 2025 the team spent money on search ads, social ads, display ads and finance blogs. The CMO is setting the budget for the second half and wants to know which channels are actually worth the money.
Your task
Measure how each paid channel and campaign performed, compare them with unpaid signups, and recommend how to split next half's budget.
Instructions
- 1Load both files and check that they line up: make sure channel and campaign names match between them and that the spend data has no repeated rows.
- 2For each campaign, calculate total spend, impressions, clicks, click-through rate and cost per click.
- 3Connect spend to outcomes: signups, paying subscribers, cost per signup, cost per paying subscriber and return on ad spend using first-90-day revenue.
- 4Compare paid campaigns with organic and referral signups on how often signups turn into paying subscribers.
- 5Look at how performance changed over the six months, and note any campaign that stopped running.
- 6Explain which metric you would use to judge channels, and why that choice changes the ranking.
- 7Recommend a budget split for the next six months and describe the risks of your plan.
Datasets
The data is synthetic and does not come from Robinhood, but it's modeled on how real companies record it, including the mess. All files come in one download.
ad_spend.csv
Daily results for each paid campaign, January to June 2025.
1,046 rows · 6 columns · 53 KB
| Column | Type | Description |
|---|---|---|
| date | date | Day the results were recorded. |
| channel | text | Marketing channel the campaign ran on. |
| campaign | text | Campaign name. |
| impressions | integer | Times the ad was shown. |
| clicks | integer | Clicks on the ad. |
| spend | decimal | Amount spent that day, in USD. |
Preview the first 5 rowsHide preview
| date | channel | campaign | impressions | clicks | spend |
|---|---|---|---|---|---|
| 2025-01-01 | Paid Search | brand_search | 1357 | 73 | 55.5 |
| 2025-01-01 | Paid Search | generic_search | 4767 | 112 | 186.15 |
| 2025-01-01 | Paid Social | prospecting_video | 37197 | 447 | 222.72 |
| 2025-01-01 | Paid Social | retargeting | 5456 | 85 | 70.38 |
| 2025-01-01 | Display | display_network | 63070 | 68 | 148.4 |
signups.csv
One row per new user who signed up between January and June 2025.
18,840 rows · 7 columns · 872 KB
| Column | Type | Description |
|---|---|---|
| user_id | integer | Unique ID of the user. |
| signup_date | date | Date the user created a free account. |
| channel | text | Channel the signup is credited to. |
| campaign | text | Campaign the signup is credited to. Empty for unpaid channels. |
| subscription_start_date | date | Date the user started paying. Empty if they never did. |
| plan | text | Paid plan chosen: monthly or annual. Empty if they never paid. |
| revenue_first_90_days | decimal | Revenue from the user in the 90 days after signup, in USD. |
Preview the first 5 rowsHide preview
| user_id | signup_date | channel | campaign | subscription_start_date | plan | revenue_first_90_days |
|---|---|---|---|---|---|---|
| 500001 | 2025-01-01 | paid_social | prospecting_video | empty | empty | 0 |
| 500002 | 2025-01-01 | organic | empty | empty | empty | 0 |
| 500003 | 2025-01-01 | affiliate | finance_blogs | empty | empty | 0 |
| 500004 | 2025-01-01 | paid_social | prospecting_video | empty | empty | 0 |
| 500005 | 2025-01-01 | referral | empty | empty | empty | 0 |
Hint
The two files don't write channel names the same way. Build a shared key from each before you join them.
ad_spend["channel_key"] = ad_spend["channel"].str.lower().str.replace(" ", "_")Deliverable
A public GitHub repo with your analysis (a notebook or scripts), a table of channel and campaign metrics, and a README with your budget recommendation.
When you're done, post your repo in the Solutions tab to share it with other learners.
What grading checks
Use this checklist to review your own work before you post and share it.
- Submitted GitHub repo is public and reachable.
- Repo contains at least one notebook or script file.
- Repo has a README with a written recommendation.
- Channel names are matched across the two files and repeated spend rows are removed before totals are calculated.
- Cost per signup, cost per paying subscriber and return on ad spend are calculated for each campaign.
- Analysis points out that the cheapest campaign per signup is not the cheapest per paying subscriber.
- Budget recommendation follows from the metrics and names at least one risk.