Data Preparation

import plotly.express as px import plotly.graph_objects as go import pandas as pd from scipy.stats import zscore excel_data = pd.ExcelFile("Senior Marketing Data Analyst - Data.xlsx") funnel = pd.read_excel(excel_data, sheet_name="P1 - Funnel") spend = pd.read_excel(excel_data, sheet_name="P1 - Spend") cohort = pd.read_excel(excel_data, sheet_name="P1 - Cohort")

funnel.sample(5)

spend.sample(5)

cohort.sample(5)

## Remove observations without any signup; ## Drop columns that are already in the cohort table; ## Aggregate the numbers to monthly/country level; ## Standardize country names funnel_clean = (funnel[funnel['signups'] > 0] .groupby(['month', 'country_group']) .sum() .reset_index() .rename(columns={ 'net_maus': 'net_mau', 'month': 'date', 'country_group': 'market'}) .sort_values('date', ascending=False)) funnel_clean['market'] = funnel_clean['market'].str.upper() funnel_clean.sample(5)

## Remove observations with zero amount; ## Drop columns that only has 1 value ## Adjust names and data types ## Aggregate the numbers to monthly/country level; ## Standardize country names spend_clean = ((spend[spend['Amount'] != 0].drop('Units', axis=1) .rename(columns={ 'Market': 'market', 'month': 'date', 'Year': 'year', 'Month': 'month', 'Amount': 'spend'})) .astype({'year': 'str', 'month': 'str', 'date': 'datetime64[ns]'}) .groupby(['market', 'year', 'month', 'date']).sum(numeric_only=True) .reset_index() .sort_values('date', ascending=False)) spend_clean['market'] = spend_clean['market'].str.upper() spend_clean.sample(5)

cohort_clean = cohort.drop('country_tnc_legal', axis=1) cohort_clean

WITH base AS ( SELECT YEAR(date) AS year , market , SUM(signups) AS signups , SUM(kyc_completed) AS kyc_completed , SUM(first_time_mau) AS first_time_mau , SUM(net_mau) AS net_mau , SUM(lapsed_mau) AS lapsed_mau , SUM(reactivated_mau) AS reactivated_mau , SUM(churned_mau) AS churned_mau , SUM(spend) AS spend FROM funnel_clean LEFT JOIN spend_clean USING (market, date) GROUP BY 1, 2 ) SELECT year::string AS year , market , signups , kyc_completed/signups *100 AS onboarding_rate , first_time_mau/signups *100 AS activation_rate , net_mau/signups *100 AS retention_rate , AVG(signups) OVER (PARTITION BY market ORDER BY year ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS rolling_3y_signups , kyc_completed , AVG(kyc_completed) OVER (PARTITION BY market ORDER BY year ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS rolling_3y_kycc , first_time_mau , AVG(first_time_mau) OVER (PARTITION BY market ORDER BY year ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS rolling_3y_ftmau , net_mau , AVG(net_mau) OVER (PARTITION BY market ORDER BY year ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS rolling_3y_net_mau , lapsed_mau , AVG(lapsed_mau) OVER (PARTITION BY market ORDER BY year ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS rolling_3y_lapsed_mau , reactivated_mau , AVG(reactivated_mau) OVER (PARTITION BY market ORDER BY year ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS rolling_3y_react_mau , churned_mau , AVG(churned_mau) OVER (PARTITION BY market ORDER BY year ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS rolling_3y_churn_mau , spend , AVG(spend) OVER (PARTITION BY market ORDER BY year ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS rolling_3y_spend FROM base WHERE year between 2018 and 2023 ORDER BY year asc

SELECT YEAR(funnel_clean.date)::int AS year , MONTH(funnel_clean.date)::int AS month , date , market , signups , kyc_completed/signups *100 AS onboarding_rate , first_time_mau/signups *100 AS activation_rate , net_mau/signups *100 AS retention_rate , AVG(signups) OVER (PARTITION BY market ORDER BY date ROWS BETWEEN 12 PRECEDING AND CURRENT ROW) AS rolling_12m_signups , kyc_completed , AVG(kyc_completed) OVER (PARTITION BY market ORDER BY date ROWS BETWEEN 12 PRECEDING AND CURRENT ROW) AS rolling_12m_kycc , first_time_mau , AVG(first_time_mau) OVER (PARTITION BY market ORDER BY date ROWS BETWEEN 12 PRECEDING AND CURRENT ROW) AS rolling_12m_ftmau , net_mau , AVG(net_mau) OVER (PARTITION BY market ORDER BY date ROWS BETWEEN 12 PRECEDING AND CURRENT ROW) AS rolling_12m_net_mau , lapsed_mau , AVG(lapsed_mau) OVER (PARTITION BY market ORDER BY date ROWS BETWEEN 12 PRECEDING AND CURRENT ROW) AS rolling_12m_lapsed_mau , reactivated_mau , AVG(reactivated_mau) OVER (PARTITION BY market ORDER BY date ROWS BETWEEN 12 PRECEDING AND CURRENT ROW) AS rolling_12m_react_mau , churned_mau , AVG(churned_mau) OVER (PARTITION BY market ORDER BY date ROWS BETWEEN 12 PRECEDING AND CURRENT ROW) AS rolling_12m_churn_mau , spend , AVG(spend) OVER (PARTITION BY market ORDER BY date ROWS BETWEEN 12 PRECEDING AND CURRENT ROW) AS rolling_12m_spend FROM funnel_clean LEFT JOIN spend_clean USING (market, date) WHERE funnel_clean.date between '2018-01-01' and '2023-12-31' ORDER BY date asc

market_colors = { 'AUT': '#a2a2e1', 'DEU': '#f6d682', 'ESP': '#e1a4b6', 'FRA': '#28C9AA', 'GRE': '#a1dbf8', 'ITA': '#227c9d', 'NEURO': '#9fc3d0' }

Data Analysis

General questions: - How do different countries perform compared to each other? - How do seasonality or monthly trends affect custor's lifecycle stages? - How do external factors (COVID, Laws, BREXIT, Wars, Imigration, change of govermental policies) affect N26 results? - How do internal factors (Market expansion/contraction, new media channels, change in strategy) affect N26 results?

Awareness

To effectively measure awareness, it’s essential to analyze the reach versus interaction levels across different marketing campaigns. This assessment provides insights into how widely the content is seen (reach) and the extent to which it drives user actions (interaction). The main metric for this is Click-Through Rate (CTR), that eveals the percentage of users who engage with the content after viewing it. The dataset provided does not contain enough information to enable the CTR calculation.

Consideration

## Monthly evolution os signup numbers fig = px.bar(month_metrics.sort_values(['year', 'month'], ascending=True), x='month', y='signups', color='market', animation_frame='year', # category_orders= color_discrete_map=(market_colors), labels={'signups': '', 'month': 'Month'}, title="Evolution of Monthly Sign-ups per country") fig.update_layout(yaxis_range=[0, 700000]) fig.show()

## Rolling average signups by market import pandas as pd import plotly.express as px fig = px.line(month_metrics, y="rolling_12m_signups", x='date', color='market', color_discrete_map=(market_colors), labels={"year": "Year", "signups": "Number of Sign-ups"}, title="Signups by country comparision") fig.show()

## Market saturation population_data = { 'AUT': 8955000, # Austria 'DEU': 83784000, # Germany 'ESP': 47352000, # Spain 'FRA': 67564000, # France 'GRE': 10343000, # Greece 'ITA': 59554023 # Italy } year_metrics['population'] = year_metrics['market'].map(population_data)

SELECT market , ROUND(AVG(signups)/population * 100, 2) AS signups_penetration , ROUND(population) AS country_population , ROUND(AVG(signups)) AS average_year_signup , CASE WHEN signups_penetration > 2 THEN 'High' WHEN signups_penetration BETWEEN 1.25 AND 2 THEN 'Medium' ELSE 'Low' END AS signups_penetration_group FROM year_metrics GROUP BY market, population ORDER BY signups_penetration desc

WITH sums AS ( SELECT year , month , date , round(SUM(signups)) AS month_signups FROM month_metrics WHERE year between 2018 and 2023 GROUP BY 1,2,3 ) SELECT year::int AS year , month::int AS month , date , month_signups , round(AVG(month_signups) OVER (PARTITION BY year)) AS year_avg_signups FROM sums ORDER BY date asc

import matplotlib.pyplot as plt import numpy as np years = month_signup_variation['year'].unique() num_years = len(years) fig, axes = plt.subplots(num_years, 1, figsize=(10, num_years * 5), sharex=True) fig.subplots_adjust(hspace=0.5) months = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"] for i, year in enumerate(years): data_year = month_signup_variation[month_signup_variation['year'] == year] monthly_signups = data_year['month_signups'].values year_avg_signups = data_year['year_avg_signups'].values[0] # Plot each year in a separate subplot ax = axes[i] if num_years > 1 else axes ax.bar(months, monthly_signups, color="skyblue") ax.axhline(y=year_avg_signups, color="orange", linestyle="--", label=f"Average ({year_avg_signups:.1f})") # Set titles and labels for each subplot ax.set_title(f"Monthly Signups vs Year average ({year})") ax.set_ylabel("Signups") ax.legend() # Set the shared x-axis label plt.xlabel("Month") plt.show()

Conversions:

SELECT year , market , kyc_completed/signups * 100 AS onboarding_rate , first_time_mau/kyc_completed * 100 AS activation_rate , net_mau/first_time_mau * 100 AS retention_rate , rolling_3y_signups AS signups , rolling_3y_kycc AS kycc , rolling_3y_ftmau AS ftmau , rolling_3y_net_mau as net_mau FROM year_metrics WHERE year >= 2020

fig = go.Figure(go.Funnel( y = ["signups", "kycc", "ftmau", "net_mau"], x = [round(year_market_conversion['signups'].mean()) , round(year_market_conversion['kycc'].mean()) , round(year_market_conversion['ftmau'].mean()) , round(year_market_conversion['net_mau'].mean())], textposition = "inside", textinfo = "value+percent initial", connector = {"line": {"color": "beige","width": 3}}) ) fig.update_layout(title="Average funnel steps conversions from 2020 to 2023") fig.show()

fig = px.bar(year_metrics.groupby(['year']).sum().reset_index(), x='year', y=['signups', 'kyc_completed', 'first_time_mau'], # color_discrete_map=(market_colors), barmode='group', labels={"year": "Year", "value": "Amount"}, title="Funnel steps conversions over time", template="plotly_white") fig.update_layout(paper_bgcolor='#FFEBCE', plot_bgcolor='#FFEBCE') fig.show()

SELECT market , SUM(kycc)/SUM(signups)* 100 AS onboarding_rate , SUM(ftmau)/SUM(kycc)* 100 AS activation_rate , SUM(net_mau)/SUM(ftmau)* 100 AS retention_rate FROM year_market_conversion GROUP BY 1

SELECT market, date, rolling_12m_kycc/rolling_12m_signups AS onboarding_rate , rolling_12m_ftmau/rolling_12m_kycc AS activation_rate , rolling_12m_net_mau/rolling_12m_ftmau AS retention_rate FROM month_metrics WHERE market != 'NEURO' AND NOT (market = 'ITA' AND date > '2022-01-01')

Onboarding

Activation

market_conversion['zscores_onboarding'] = zscore(market_conversion['onboarding_rate']) onboarding_avg = round(market_conversion['onboarding_rate'].mean(), 2) onboarding_std = round(market_conversion['onboarding_rate'].std(), 2) print(market_conversion[['market', 'onboarding_rate', 'zscores_onboarding']], end='\n\n') print(f"onboarding_avg: {onboarding_avg}") print(f"onboarding_std: {onboarding_std}")

market_conversion['zscores_activation'] = zscore(market_conversion['activation_rate']) activation_avg = round(market_conversion['activation_rate'].mean(), 2) activation_std = round(market_conversion['activation_rate'].std(), 2) print(market_conversion[['market', 'activation_rate', 'zscores_activation']], end='\n\n') print(f"activation_avg: {activation_avg}") print(f"activation_std: {activation_std}")

fig = px.line(rates, x='date', y='onboarding_rate', color='market', # facet_col=variables, color_discrete_map=(market_colors), labels={"year": "Year", "value": "Amount"}, title="Rolling average onboarding rate by country") fig.update_layout(paper_bgcolor='#FFEBCE', plot_bgcolor='#FFEBCE') fig.show()

fig = px.line(rates, x='date', y='activation_rate', color='market', # facet_col=variables, color_discrete_map=(market_colors), labels={"year": "Year", "value": "Amount"}, title="Rollong average activation rate by country") fig.show()

fig = px.line(month_metrics, x='date', y='rolling_12m_signups', color='market', # facet_col=variables, color_discrete_map=(market_colors), labels={"year": "Year", "value": "Amount"}, title="Rolling average signups by country") # fig.update_layout(paper_bgcolor='#FFEBCE', plot_bgcolor='#FFEBCE') fig.show()

activation = (month_metrics[month_metrics['market'] != 'NEURO'])[['date','market', 'activation_rate']] fig = px.box( activation, y='activation_rate', points="all", color_discrete_map=(market_colors), color='market', # Corrected argument labels={'value':'Conversion Rate (%)', 'variable':'Metric', 'market':'Market'}, title='Average activation rate by Market') fig.update_yaxes(range=(0, 100)) fig.update_layout(title_x=0.5, height=800) fig.show()

onboarding = (month_metrics[month_metrics['market'] != 'NEURO'])[['date','market', 'onboarding_rate']] fig = px.box( onboarding, y='onboarding_rate', points="all", color='market', color_discrete_map=(market_colors), labels={'value':'Conversion Rate (%)', 'variable':'Metric', 'market':'Market'}, title='Average onboarding rate by Market') fig.update_yaxes(range=(0, 100)) fig.update_layout(title_x=0.5, height=800, paper_bgcolor='#F1FFF4', plot_bgcolor='#F1FFF4') fig.show()

User Retention (Cohort Analysis)

SELECT *, YEAR(signup_cohort) AS year, MONTH(signup_cohort) AS month FROM cohort_clean WHERE signup_cohort between '2020-01-01' and '2023-12-01'

rate = pd.DataFrame(cohort_trimmed.groupby(['relative_period', 'year'])['mau_retained'].mean()) rate.pivot_table(columns='relative_period', index='year', values='mau_retained')

fig = px.bar( cohort_trimmed[cohort_trimmed['year']>2022], x='signup_cohort', y='relative_period', color='net_mau', # Corrected argument labels={'value':'Conversion Rate (%)', 'variable':'Metric', 'market':'Market'}, title='Average retention rate by Market') # fig.update_yaxes(range=(0.3, 1)) # fig.update_xaxes(range=(0, 50)) # fig.update_layout(title_x=0.5, height=800) fig.show()

Spend

Cost per Acquisition (CPA) - Average cost to acquire an active user Calculations: • Acquisition spend / Net MAU • Compare CPA by country and analyze trends over time Importance: CPA indicates the efficiency of user acquisition efforts, helping optimize spending. Lower CPA suggests a more cost-effective growth strategy and indicates higher return on marketing investments.

SELECT year(date) AS year , market , ROUND(AVG(spend/signups),2) AS cost_per_signup , ROUND(AVG((spend/first_time_mau)),2) AS cost_per_activeted FROM month_metrics WHERE spend > 0 AND market != 'NEURO' AND NOT (market = 'ITA' AND year(date) = 2022) GROUP BY 1,2

fig = px.bar(cost_user, y="cost_per_signup", x='year', barmode="group", # facet_col="market", color='market', color_discrete_map=(market_colors), labels={"date_reference": "", "spend_per_signup": ""}, title="Cost per signup", ) fig.update_layout(title_x=0.5, paper_bgcolor='#F1FFF4', plot_bgcolor='#F1FFF4') fig.show()

import plotly.express as px # Removed the color_discrete_map parameter because plotly.colors.qualitative.Pastel is a list, not a dict. # Replaced it with a sample: 'RdBu'. Please replace 'RdBu' with another color map if desired. fig = px.treemap(year_metrics, names='market', values='signups', path=['year', 'market'], color='market', color_discrete_sequence=px.colors.qualitative.Pastel, labels={"year": "Year", "spend": "Signups over time"}, title="Signups value over time") fig.show()

General questions: - What is the average unique open rate for the campaigns? Where is there opportunity for improvement? - With only the data provided, which campaign would you say had the strongest body creative/copy? Why? - What’s an audience that you would prioritise for optimisation? Why? Outline an experiment you’d propose to improve performance. - If you could add any column to the dataset, which one would you add and why? ------------------ - How do different countries perform compared to each other? - How do different segments react to the campaigns? Do they behave similarly?

campaign_data = pd.read_excel(excel_data, sheet_name="P2 - Campaign Data") campaign_data

WITH base as ( SELECT name, count(date) as cnt FROM campaign_data GROUP BY 1 ) SELECT cnt, count(name) as same_email FROM base GROUP BY 1

Unique Open Rates - Initial Engagement Calculation: • Moving Average of (unique open rate/emails sent) calculated monthly per campaign - emails don't have a time series that can be used Questions: • Are there any specific email types or topics that consistently stand out in open rates? • Which customer segments show higher open rates, and why? • What improvements could boost open rates (e.g., subject line optimization, send time)? Importance: Unique open rates provide insight into the initial effectiveness of the email’s appeal, indicating how well the subject line, timing, and targeting resonate with recipients. A higher open rate reflects successful targeting and email relevance.

campaign_data['open_rate'] = campaign_data['opens'] / campaign_data['sends'] * 100 average_open_rate = campaign_data['open_rate'].mean() average_open_rate

strongest_campaign = campaign_data.loc[campaign_data['open_rate'].idxmax()] strongest_campaign

The campaign "Email 12" targeted at the "MAU" segment had the strongest body creative/copy, with an exceptionally high open rate of approximately 604.58%. This suggests that the content was highly engaging or relevant to the audience.

Campaign Engagement - Click Rate Analysis for Campaign Effectiveness Calculation: • Running average of click-through rates, calculated per campaign. Questions: • Which segments show the highest or lowest engagement in terms of clicks? • Do higher click-through rates correlate with lower unsubscribe rates, indicating more effective targeting and content relevance? Importance: Click-through rates offer deeper insight into how engaging and relevant the content is after the email is opened. High engagement suggests that the message aligns with the audience’s interests, while low engagement may signal a need for content adjustments.

campaign_data['engagement'] = campaign_data['clicks'] / campaign_data['opens'] * 100 campaign_data

strongest_campaign = campaign_data.loc[campaign_data['engagement'].idxmax()] strongest_campaign

Segment Engagement - Evaluating Engagement Across User Segments Calculation: • Running average of click rates by segment, calculated as per segment. Questions: • Which segments are most and least responsive to the content? • Is there a relationship between high engagement rates and lower unsubscribe rates, suggesting better alignment with user expectations? Importance: Segment-specific engagement analysis reveals which groups are most receptive to the content, guiding more personalized and effective messaging strategies for each user group.

# Analyze audience segments to prioritize for optimization segment_performance = campaign_data.groupby('segment').agg({'open_rate': 'mean', 'clicks': 'sum', 'unsubscribe': 'sum'}) segment_performance['click_rate'] = segment_performance['clicks'] / campaign_data.groupby('segment')['sends'].sum() * 100 segment_performance['unsubscribe_rate'] = segment_performance['unsubscribe'] / campaign_data.groupby('segment')['sends'].sum() * 100 segment_performance

The "Never Active" segment shows a relatively low open rate and click rate compared to the "MAU" segment, indicating potential for optimization. To improve performance for the "Never Active" segment, an experiment could be designed to test different subject lines and email content tailored to re-engage this audience. This could involve A/B testing various messaging strategies to identify which approach resonates best with this segment.

campaign_data['click_rate'] = campaign_data['clicks'] / campaign_data['sends'] * 100 campaign_data['unsubscribe_rate'] = campaign_data['unsubscribe'] / campaign_data['sends'] * 100 country_performance = campaign_data.groupby('name').agg({'open_rate': 'mean', 'click_rate': 'mean', 'unsubscribe_rate': 'mean'}) country_performance

The analysis shows that "Email 12" had the highest open rate, click rate, and unsubscribe rate, indicating strong engagement but also a higher likelihood of unsubscribes. Adding a column for "device_type" (e.g., mobile, desktop) could provide insights into how users engage with campaigns across different devices, helping to tailor content for better performance. In terms of country and segment performance, the data does not directly provide country-specific insights, but segment analysis shows that the "Never Active" segment has room for improvement.

.css-15w88e5{color:var(--chakra-colors-fg-neutral-primary);font-weight:inherit;letter-spacing:-0.09px;}Data Preparation

Data Analysis

.css-119j68r{color:#9042CC;font-weight:var(--chakra-fontWeights-bold);letter-spacing:-0.09px;-webkit-text-decoration:underline;text-decoration:underline;}Awareness

.css-1vy8eyo{color:#F5B218;font-weight:var(--chakra-fontWeights-bold);letter-spacing:-0.09px;-webkit-text-decoration:underline;text-decoration:underline;}Consideration