Marketing Campaign Analysis¶
This analysis seeks to measure the impact of a marketing campaign when it comes to customer acquisition and retention. The data in this analysis is 100% synthetic, and it was generated to resemble real-world use cases that where I have successfully applied these analytical steps with success.¶
________________________________________________________________________________________¶
In [1]:
import pandas as pd
import numpy as np
import datetime as datetime
import openpyxl
Importing files¶
In [3]:
pre_campaign = pd.read_excel("bi_cust_01-01-2020_08-06-2023.xlsx")
campaign = pd.read_excel("bi_cust_08-09-2020_08-16-2023-ECOM.xlsx")
post_campaign_2023 = pd.read_excel("bi_cust_09-01-2023_12-31-2023.xlsx")
post_campaign_2024_2025 = pd.read_excel("bi_cust_01-01-2024_12-31-2025.xlsx")
In [4]:
pre_campaign.info()
<class 'pandas.core.frame.DataFrame'> RangeIndex: 120800 entries, 0 to 120799 Data columns (total 5 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 Customer name 120795 non-null object 1 Customer email 120797 non-null object 2 Store Name 111650 non-null object 3 Orders 120651 non-null float64 4 Net sales 118030 non-null float64 dtypes: float64(2), object(3) memory usage: 4.6+ MB
Cleaning dataframes¶
In [5]:
def clean_dataframe(df):
"""Clean and standardize dataframe columns"""
# Strip whitespace from column names
df.columns = df.columns.str.strip()
# Strip whitespace from string columns
df['Customer name'] = df['Customer name'].str.strip()
df['Customer email'] = df['Customer email'].str.strip().str.lower()
# Convert numeric columns
df['Orders'] = pd.to_numeric(df['Orders'], errors='coerce')
df['Net sales'] = pd.to_numeric(df['Net sales'], errors='coerce')
# Handle missing values
df = df.dropna(subset=['Customer email'])
return df
In [6]:
pre_campaign = clean_dataframe(pre_campaign)
campaign = clean_dataframe(campaign)
post_campaign_2023 = clean_dataframe(post_campaign_2023)
post_campaign_2024_2025 = clean_dataframe(post_campaign_2024_2025)
Identifying customer groups¶
In [7]:
# Get unique customer emails from each period
emails_pre_campaign = set(pre_campaign['Customer email'].unique())
emails_campaign = set(campaign['Customer email'].unique())
emails_post_campaign_2023 = set(post_campaign_2023['Customer email'].unique())
emails_post_campaign_2024_2025 = set(post_campaign_2024_2025['Customer email'].unique())
# Combine all post-campaign emails
emails_post_campaign_all = emails_post_campaign_2023.union(emails_post_campaign_2024_2025)
print(f"Unique customers in pre-campaign period: {len(emails_pre_campaign)}")
print(f"Unique customers in campaign: {len(emails_campaign)}")
print(f"Unique customers in post-campaign 2023: {len(emails_post_campaign_2023)}")
print(f"Unique customers in post-campaign 2024-2025: {len(emails_post_campaign_2024_2025)}")
print(f"Total unique customers in all post-campaign periods: {len(emails_post_campaign_all)}")
Unique customers in pre-campaign period: 116151 Unique customers in campaign: 3279 Unique customers in post-campaign 2023: 14216 Unique customers in post-campaign 2024-2025: 90805 Total unique customers in all post-campaign periods: 103638
In [8]:
# Check if campaign week customers were existing customers
emails_existing_customers = emails_campaign.intersection(emails_pre_campaign)
emails_new_customers = emails_campaign - emails_pre_campaign
print(f"\ncampaign Customer Breakdown:")
print(
f" - Existing customers (appeared pre-campaign): {len(emails_existing_customers)} ({len(emails_existing_customers) / len(emails_campaign) * 100:.1f}%)")
print(
f" - New customers (did NOT appear pre-campaign): {len(emails_new_customers)} ({len(emails_new_customers) / len(emails_campaign) * 100:.1f}%)")
campaign Customer Breakdown: - Existing customers (appeared pre-campaign): 750 (22.9%) - New customers (did NOT appear pre-campaign): 2529 (77.1%)
In [9]:
# New customers who made post-campaign purchases
new_customers_returned = emails_new_customers.intersection(emails_post_campaign_all)
new_customers_not_returned = emails_new_customers - emails_post_campaign_all
print(f"\nNew Customer Retention:")
print(
f" - New customers who made post-campaign purchases: {len(new_customers_returned)} ({len(new_customers_returned) / len(emails_new_customers) * 100:.1f}%)")
print(
f" - New customers who did NOT return: {len(new_customers_not_returned)} ({len(new_customers_not_returned) / len(emails_new_customers) * 100:.1f}%)")
New Customer Retention: - New customers who made post-campaign purchases: 227 (9.0%) - New customers who did NOT return: 2302 (91.0%)
Aggregating sales data¶
In [10]:
# Combine all post-campaign dataframes
df_post_campaign_combined = pd.concat([post_campaign_2023, post_campaign_2024_2025], ignore_index=True)
# Filter for new customers only
df_new_customers_post = df_post_campaign_combined[df_post_campaign_combined['Customer email'].isin(new_customers_returned)]
# Aggregate by customer
new_customer_summary = df_new_customers_post.groupby(['Customer name', 'Customer email']).agg({
'Orders': 'sum',
'Net sales': 'sum'
}).reset_index()
# Add period breakdown
new_customer_summary['Purchased in 2023 (Sep-Dec)'] = new_customer_summary['Customer email'].isin(emails_post_campaign_2023)
new_customer_summary['Purchased in 2024-2025'] = new_customer_summary['Customer email'].isin(emails_post_campaign_2024_2025)
# Get campaign data for these customers
df_campaign_new = campaign[campaign['Customer email'].isin(emails_new_customers)]
customer_summary = df_campaign_new.groupby('Customer email').agg({
'Orders': 'sum',
'Net sales': 'sum'
}).reset_index()
customer_summary.columns = ['Customer email', 'Marketing Push Orders',
'Marketing Push Net Sales']
# Merge with campaign data
new_customer_summary = new_customer_summary.merge(customer_summary, on='Customer email', how='left')
# Reorder columns
column_order = [
'Customer name',
'Customer email',
'Marketing Push Orders',
'Marketing Push Net Sales',
'Orders',
'Net sales',
'Purchased in 2023 (Sep-Dec)',
'Purchased in 2024-2025'
]
new_customer_summary = new_customer_summary[column_order]
# Rename post-campaign columns for clarity
new_customer_summary.rename(columns={
'Orders': 'Post-Push Orders',
'Net sales': 'Post-Push Net Sales'
}, inplace=True)
# Calculate lifetime value
new_customer_summary['Lifetime Orders'] = (
new_customer_summary['Marketing Push Orders'].fillna(0) +
new_customer_summary['Post-Push Orders'].fillna(0)
)
new_customer_summary['Lifetime Net Sales'] = (
new_customer_summary['Marketing Push Net Sales'].fillna(0) +
new_customer_summary['Post-Push Net Sales'].fillna(0)
)
print(f"Created summary for {len(new_customer_summary)} new customers with post-campaign activity")
Created summary for 233 new customers with post-campaign activity
Metrics¶
In [12]:
# Overall metrics
total_marketing_customers = len(emails_campaign)
total_new_customers = len(emails_new_customers)
total_returning_new_customers = len(new_customers_returned)
print(f"\n1. Customer Acquisition:")
print(f" • Total customers during campaign: {total_marketing_customers}")
print(
f" • New customers acquired: {total_new_customers} ({total_new_customers / total_marketing_customers * 100:.1f}%)")
print(
f" • Existing customers: {len(emails_existing_customers)} ({len(emails_existing_customers) / total_marketing_customers * 100:.1f}%)")
print(f"\n2. New Customer Retention:")
print(
f" • New customers who returned: {total_returning_new_customers} ({total_returning_new_customers / total_new_customers * 100:.1f}%)")
print(
f" • New customers who did not return: {len(new_customers_not_returned)} ({len(new_customers_not_returned) / total_new_customers * 100:.1f}%)")
# Financial metrics
campaign_total = df_campaign_new['Net sales'].sum()
post_campaign_total = new_customer_summary['Post-Push Net Sales'].sum()
lifetime_total = new_customer_summary['Lifetime Net Sales'].sum()
print(f"\n3. Revenue from New Customers:")
print(f" • Revenue during campaign: ${campaign_total:,.2f}")
print(f" • Revenue in post-campaign periods: ${post_campaign_total:,.2f}")
print(f" • Total lifetime revenue: ${lifetime_total:,.2f}")
print(f" • Average revenue per new customer (lifetime): ${lifetime_total / total_new_customers:,.2f}")
# Order metrics
campaign_orders = df_campaign_new['Orders'].sum()
post_campaign_orders = new_customer_summary['Post-Push Orders'].sum()
print(f"\n4. Order Volume from New Customers:")
print(f" • Orders during campaign: {int(campaign_orders)}")
print(f" • Orders in post-campaign periods: {int(post_campaign_orders)}")
print(
f" • Average orders per new customer (lifetime): {(campaign_orders + post_campaign_orders) / total_new_customers:.2f}")
# Period breakdown for returning customers
returned_2023 = len([e for e in new_customers_returned if e in emails_post_campaign_2023])
returned_2024_2025 = len([e for e in new_customers_returned if e in emails_post_campaign_2024_2025])
returned_both = len(
[e for e in new_customers_returned if e in emails_post_campaign_2023 and e in emails_post_campaign_2024_2025])
print(f"\n5. Return Period Breakdown:")
print(f" • Returned in 2023 (Sep-Dec): {returned_2023}")
print(f" • Returned in 2024-2025: {returned_2024_2025}")
print(f" • Returned in both periods: {returned_both}")
1. Customer Acquisition: • Total customers during campaign: 3279 • New customers acquired: 2529 (77.1%) • Existing customers: 750 (22.9%) 2. New Customer Retention: • New customers who returned: 227 (9.0%) • New customers who did not return: 2302 (91.0%) 3. Revenue from New Customers: • Revenue during campaign: $1,251,564.56 • Revenue in post-campaign periods: $216,084.61 • Total lifetime revenue: $395,128.86 • Average revenue per new customer (lifetime): $156.24 4. Order Volume from New Customers: • Orders during campaign: 6104 • Orders in post-campaign periods: 724 • Average orders per new customer (lifetime): 2.70 5. Return Period Breakdown: • Returned in 2023 (Sep-Dec): 72 • Returned in 2024-2025: 171 • Returned in both periods: 16
Export¶
In [13]:
# Sort by lifetime total sales (descending)
new_customer_summary_sorted = new_customer_summary.sort_values('Lifetime Net Sales', ascending=False)
# Export to CSV
output_filename = f'campaign_new_customers_analysis.csv'
new_customer_summary_sorted.to_csv(output_filename, index=False)
print(f"\nAnalysis complete. Results exported to: {output_filename}")
Analysis complete. Results exported to: campaign_new_customers_analysis.csv
In [ ]: