Analytics Case Study: MXN to USD Currency Route
Tribe: Regional Expansion
Role: Senior Product Data Analyst
Candidate: Matias Scherer
Date: May 2026
Task: Evaluating the launch
Let’s assume the route has been launched worldwide and we have some initial data on its usage (csv sent includes only transfers from MXN to USD). Please analyse this data and provide your key insights. We’d like to know:
a. Which metric/s would you use to assess the performance of the
launch?
b. If the performance is on track? Are there any noticeable trends?
c. What findings would be most valuable to share with the product
team? Are there any anomalies that would require their attention?
Main Takeaways
"Stable" Facts:
New customers have significantly worse created → funded conversion, for all countries and platforms
Trends:
Conversion rates are stable within segments, variation is all due to Mix shifts (Simpson’s Paradox)
Demand is growing
Mix is shifting towards new mexican users
Product Highlights/Anomalies:
MEX - New – Android segment pay-in is broken!
US funded → transferred conversion rate is stuck at an allarmingly low rate of ~45%
Analysis
Dataframe: MXN to USD events
Definitions
event_name: name of the transfer flow step event the user has
been able to successfully complete
experience: Customer experience type when performing the transfer -> New: When the customer is new to wise.
Existing: When the customer has already
performed transfers in the past.
Data manipulation
1. Renamed columns and dataframe to increase readability
2. Described table (table shape, distinct values count, min and max values, null values etc.)
select
user_id,
date_trunc('day', dt) as event_date,
event_name,
source_currency,
target_currency,
user_country,
platform,
experience
from 'wise_funnel_events_regional_v2-20260603-225732.csv'
order by 1, 2
# Using the existing dataframe `funnel_events` loaded from SQL
import pandas as pd
# Basic info
num_rows = len(funnel_events)
num_cols = funnel_events.shape[1]
columns = funnel_events.columns.tolist()
dtypes = funnel_events.dtypes.astype(str).to_dict()
# Min/max for numeric and datetime-like columns
min_max = {}
for col in funnel_events.columns:
s = funnel_events[col]
if pd.api.types.is_numeric_dtype(s):
min_max[col] = {
'min': float(s.min()) if pd.notnull(s.min()) else None,
'max': float(s.max()) if pd.notnull(s.max()) else None,
}
elif pd.api.types.is_datetime64_any_dtype(s):
min_max[col] = {
'min': s.min(),
'max': s.max(),
}
# Missing values by column
missing_counts = funnel_events.isna().sum().to_dict()
missing_pct = (funnel_events.isna().mean() * 100).round(2).to_dict()
# Unique counts per column
unique_counts = {col: funnel_events[col].nunique(dropna=True) for col in funnel_events.columns}
# Duplicate rows (exact duplicates across all columns)
duplicate_rows = int(funnel_events.duplicated().sum())
# Unique registries per row (check if user_id+event_date+event_name appears multiple times)
key_cols = ['user_id', 'event_date', 'event_name']
if all(k in funnel_events.columns for k in key_cols):
key_dups = int(funnel_events.duplicated(subset=key_cols).sum())
unique_key_rows = num_rows - key_dups
else:
key_dups = None
unique_key_rows = None
# Build a compact summary dataframe
summary_rows = []
summary_rows.append({'metric': 'rows', 'value': num_rows})
summary_rows.append({'metric': 'columns', 'value': num_cols})
summary_rows.append({'metric': 'duplicate_rows_all_cols', 'value': duplicate_rows})
if unique_key_rows is not None:
summary_rows.append({'metric': 'unique_rows_by_key(user_id,event_date,event_name)', 'value': unique_key_rows})
summary_rows.append({'metric': 'duplicate_rows_by_key(user_id,event_date,event_name)', 'value': key_dups})
summary_df = pd.DataFrame(summary_rows)
# Prepare a details dataframe with per-column stats
details = []
for col in columns:
col_min = min_max.get(col, {}).get('min', None)
col_max = min_max.get(col, {}).get('max', None)
details.append({
'column': col,
'dtype': dtypes[col],
'unique_values': unique_counts[col],
'missing_count': int(missing_counts[col]),
'missing_pct': float(missing_pct[col]),
'min': col_min,
'max': col_max,
})
description = pd.DataFrame(details)
summary_df, description
1. Metrics to assess the performance of the launch?
1.1. North Star: weekly completed transfers (and WoW growth)
-- weekly completed transfers (north star proxy) + wow growth
with weekly as (
select
date_trunc('week', event_date) as week_start,
sum(case when event_name = 'Transfer Created' then 1 else 0 end) as created,
sum(case when event_name = 'Transfer Transferred' then 1 else 0 end) as completed
from funnel_events
where 1=1
group by 1
)
select
week_start,
created,
completed,
round(100.0 * (completed / nullif(lag(completed) over (order by week_start), 0) - 1), 1) as completed_wow_pct
from weekly
where
1=1
-- filtering out the incomplete week
and week_start < '2024-02-26'
order by week_start
1.2. Overall Weekly Conversion Rates Evolution
-- weekly funnel with conversions only, unpivoted to long format
with counts as (
select
date_trunc('week', event_date) as week_start,
sum(case when event_name = 'Transfer Created' then 1 else 0 end) as created,
sum(case when event_name = 'Transfer Funded' then 1 else 0 end) as funded,
sum(case when event_name = 'Transfer Transferred' then 1 else 0 end) as transferred
from funnel_events
group by 1
),
conversions as (
select
week_start,
round(funded * 100.0 / nullif(created, 0), 1) as created_to_funded_conversion,
round(transferred * 100.0 / nullif(funded, 0), 1) as funded_to_transferred_conversion,
round(transferred * 100.0 / nullif(created, 0), 1) as created_to_transferred_conversion
from counts
)
select
week_start,
conversion_name,
conversion_pct
from conversions
unpivot(
conversion_pct for conversion_name in (
created_to_funded_conversion,
funded_to_transferred_conversion,
created_to_transferred_conversion
)
)
order by week_start, conversion_name;
1.3.1. All Combinations Segmented Conversion Rates
-- segmented funnel: country x experience x platform (drop dimensions as needed)
select
user_country || ' - ' || platform || ' - ' || experience as segment,
user_country,
platform,
experience,
count_if(event_name = 'Transfer Created') as created,
count_if(event_name = 'Transfer Funded') as funded,
count_if(event_name = 'Transfer Transferred') as transferred,
round(funded * 100 / nullif(created, 0), 1) as created_to_funded_conversion,
round(transferred * 100 / nullif(funded, 0), 1) as funded_to_transferred_conversion,
sum(created) over () as total_created,
round(created * 100 / total_created, 1) as segment_share_pct
from funnel_events
group by 1, 2, 3, 4
order by 1
MEX - Android - New Segment Drill-Down
This segment presented an alarmingly low created -> funded conversion since the route launch, and when it started to represent a bigger share of the total transfer creation events (~2% until 22-jan week -> 8% in 29-jan and ~12% from 05-fev onwards), affected negatively the overall created -> funded conversion rate.
-- segmented funnel: country x experience x platform (drop dimensions as needed)
select
date_trunc('week', event_date) as week_start,
count_if(event_name = 'Transfer Created') as created,
count_if(event_name = 'Transfer Funded') as funded,
count_if(event_name = 'Transfer Transferred') as transferred,
round(funded * 100 / nullif(created, 0), 1) as created_to_funded_conversion,
round(transferred * 100 / nullif(funded, 0), 1) as funded_to_transferred_conversion
from funnel_events
where
1=1
and user_country = 'MEX'
and platform = 'Android'
and experience = 'New'
group by 1
order by 1
-- Weekly share evolution of MEX - Android - New segment on total 'Transfer Created' events
with weekly_total as (
select
date_trunc('week', event_date) as week_start,
count_if(event_name = 'Transfer Created') as total_created
from funnel_events
group by 1
),
weekly_segment as (
select
date_trunc('week', event_date) as week_start,
count_if(event_name = 'Transfer Created') as segment_created
from funnel_events
where user_country = 'MEX' and platform = 'Android' and experience = 'New'
group by 1
)
select
t.week_start,
s.segment_created,
t.total_created,
round(100.0 * s.segment_created / nullif(t.total_created, 0), 1) as segment_share_pct
from weekly_total t
left join weekly_segment s using (week_start)
order by t.week_start;
1.3.2. Weekly Country Segmented Conversion Rates
-- segmented funnel: country
select
user_country,
date_trunc('week', event_date) as week_start,
count_if(event_name = 'Transfer Created') as created,
count_if(event_name = 'Transfer Funded') as funded,
count_if(event_name = 'Transfer Transferred') as transferred,
round(funded * 100 / nullif(created, 0), 1) as created_to_funded_conversion,
round(transferred * 100 / nullif(funded, 0), 1) as funded_to_transferred_conversion,
sum(created) over (partition by date_trunc('week', event_date)) as total_created_week,
round(created * 100 / total_created_week, 1) as segment_share_week_pct
from funnel_events
group by 1, 2
order by 1, 2
1.3.4. Weekly Experience Segmented Conversion Rates
-- segmented funnel: experience
select
experience,
date_trunc('week', event_date) as week_start,
count_if(event_name = 'Transfer Created') as created,
count_if(event_name = 'Transfer Funded') as funded,
count_if(event_name = 'Transfer Transferred') as transferred,
round(funded * 100.0 / nullif(created, 0), 1) as created_to_funded_conversion,
round(transferred * 100.0 / nullif(funded, 0), 1) as funded_to_transferred_conversion
from funnel_events
group by experience, week_start
order by experience, week_start;
2. Is performance on track? Are there any noticeable trends?
3. What findings would be most valuable to share with the product team? Are there any anomalies that would require their attention?
3.1. New Clients are increasing their share on total clinics creating transactions
select
date_trunc('week', event_date) as week_start,
experience,
count_if(event_name = 'Transfer Created') as created,
sum(created) over (partition by week_start) as total_created_week,
round(created * 100.0 / nullif(total_created_week, 0), 1) as segment_share_week_pct
from funnel_events
where
1=1
group by 1, 2
order by 1, 2
3.2. The majority of them, coming from Mexico (increasing share on Total New Clinics creating transactions)
select
date_trunc('week', event_date) as week_start,
user_country,
count_if(event_name = 'Transfer Created') as created,
sum(created) over (partition by week_start) as total_created_week,
round(created * 100.0 / nullif(total_created_week, 0), 1) as segment_share_week_pct
from funnel_events
where
1=1
and experience = 'New'
group by 1, 2
order by 1, 2
3.3. Which has a large (%) of Android users share among the platforms (stable over time and in a for New and Existing)
General
select
-- date_trunc('week', event_date) as week_start,
user_country,
platform,
count_if(event_name = 'Transfer Created') as created,
sum(created) over (partition by user_country) as total_created_country,
round(created * 100.0 / nullif(total_created_country, 0), 1) as segment_share_pct
from funnel_events
group by 1, 2
order by 1, 2