User Segmentation Analysis

This is a dataset of user transactions in a peer-to-peer money transfer service.

I'll develop a user segmentation method to group users for targeting through marketing and sales strategies.

Data Validation

First I'll make sure the data is complete and doesn't contain any null values.

import pandas as pd # The path to the CSV file file_path = "/work/data.csv" # Read data into a pandas dataframe df = pd.read_csv(file_path) # Display the data types for each column print(df.dtypes)

There are 8 fields, and they seem to be read in the correct format.

Next I'll check for missing values.

import pandas as pd # The path to the CSV file file_path = "/work/data.csv" # Read data into a pandas dataframe df = pd.read_csv(file_path) # Check for missing values in the dataset missing_values = df.isnull().sum() missing_values

Great, there are no missing values.

Next I'll check how many unique values are in each field.

import pandas as pd # The path to the CSV file file_path = "/work/data.csv" # Read data into a pandas dataframe df = pd.read_csv(file_path) # Check the number of unique values in categorical columns unique_values = df[['date','sender_currency', 'sender_country', 'recipient_currency', 'recipient_network']].nunique() unique_values

We're working with a dataset of people sending money in 2 currencies and from 7 countries to 3 currencies.

Exploratory Data Analysis

Next I'll do some exploratory analysis to better understand the data and see if there are any obvious trends.

import pandas as pd # The path to the CSV file file_path = "/work/data.csv" # Read data into a pandas dataframe df = pd.read_csv(file_path) # Get a statistical summary of numeric columns data_summary = df.describe() data_summary

The 2 numerical fields show there is 52,589 records. Since we're working with different currencies the summary statistics aren't very valuable to us.

I need to validate if the sender_amt is in the local currency or in a standardized currency such as USD. There are 2 currencies in this dataset: GBP and EUR.

import pandas as pd # The path to the CSV file file_path = "/work/data.csv" # Read data into a pandas dataframe df = pd.read_csv(file_path) # Group by sender_currency and calculate the min, max, average (mean), and median for sender_amt for each currency sender_amt_stats = df.groupby('sender_currency')['sender_amt'].agg(['min', 'max', 'mean', 'median']) sender_amt_stats

The values are in similar distribution patterns, and the ranges overlap significantly, but the FX rate from Euro to Pound is 1.17 to 1 so it's not a big difference. I'll assume the values are in 2 different currencies and create a new field that shows all values in Euro to keep it simple.

import pandas as pd # The path to the CSV file file_path = "/work/data.csv" # Read data into a pandas dataframe df = pd.read_csv(file_path) # Create a new column 'sender_amt_eur' # If the sender_currency is GBP, multiply the sender_amt by 1.17. Otherwise, keep the sender_amt value as is. df['sender_amt_eur'] = df.apply(lambda row: row['sender_amt'] * 1.17 if row['sender_currency'] == 'GBP' else row['sender_amt'], axis=1) # Display the first few rows of the updated dataset df.head()

Next we'll look at the total number of transactions over time.

import matplotlib.pyplot as plt import pandas as pd # The path to the CSV file file_path = "/work/data.csv" # Read data into a pandas dataframe df = pd.read_csv(file_path) # Convert the 'date' column to datetime format df['date'] = pd.to_datetime(df['date'], dayfirst=True) # Group by date and count the number of transactions for each date transactions_over_time = df.groupby('date').size() # Plotting the transactions over time plt.figure(figsize=(14, 6)) transactions_over_time.plot(linewidth=2) plt.title('Number of Transactions Over Time') plt.xlabel('Date') plt.ylabel('Number of Transactions') plt.grid(True, which='both', linestyle='--', linewidth=0.5) plt.tight_layout() plt.show()

We have data from 60 days, with a general upward trend in the number of transactions over time, suggesting growing popularity in the service.

There are some seasonality trends, which may require further investigation. There are also some spikes in demand, suggesting promotions or other circumstances that are engaging customers more.

Next lets look at top sender countries by amount sent.

import pandas as pd import seaborn as sns import matplotlib.pyplot as plt # The path to the CSV file file_path = "/work/data.csv" # Read data into a pandas dataframe df = pd.read_csv(file_path) # Create a new column 'sender_amt_eur' # If the sender_currency is GBP, multiply the sender_amt by 1.17. Otherwise, keep the sender_amt value as is. df['sender_amt_eur'] = df.apply(lambda row: row['sender_amt'] * 1.17 if row['sender_currency'] == 'GBP' else row['sender_amt'], axis=1) # Group by sender country and sum send volume send_market_volume = df.groupby('sender_country')['sender_amt_eur'].sum().sort_values(ascending=False).reset_index() # Take the top 10 sender countries for visualization top_send_countries = send_market_volume.head(10) # Plot the chart plt.figure(figsize=(12, 8)) sns.barplot(x='sender_amt_eur', y='sender_country', data=top_send_countries, palette='viridis') plt.xlabel('Send Volume') plt.ylabel('Sender Country') plt.title('Top Sender Countries by Send Volume') plt.show()

The top sender country by far is Italy, followed by Spain.

Top Send Countries

Finally, lets look at top sender countries by send transaction volume.

import pandas as pd import seaborn as sns import matplotlib.pyplot as plt # The path to the CSV file file_path = "/work/data.csv" # Read data into a pandas dataframe df = pd.read_csv(file_path) # Group by sender country and count the number of transactions send_market_transaction_count = df['sender_country'].value_counts().reset_index() send_market_transaction_count.columns = ['sender_country', 'transaction_count'] # Take the top 10 sender countries for visualization top_send_countries_by_transactions = send_market_transaction_count.head(10) # Plot the chart plt.figure(figsize=(12, 8)) sns.barplot(x='transaction_count', y='sender_country', data=top_send_countries_by_transactions, palette='viridis') plt.xlabel('Number of Transactions') plt.ylabel('Sender Country') plt.title('Top Sender Countries by Quantity of Transactions') plt.show()

We see a similar trend as the send amount chart.

Top Receive Countries

Lets do the same analysis as above but for receive countries, starting with transaction volume.

Because we only have the recipient network, we have to do some transformation of the country name.

import pandas as pd import seaborn as sns import matplotlib.pyplot as plt # The path to the CSV file file_path = "/work/data.csv" # Read data into a pandas dataframe df = pd.read_csv(file_path) # Extract receiver country from recipient_network df['receiver_country'] = df['recipient_network'].str.split('-').str[-1] # Count the number of transactions for each receiver country receiver_country_transaction_count = df['receiver_country'].value_counts().reset_index() receiver_country_transaction_count.columns = ['receiver_country', 'transaction_count'] # Take the top receiver countries for visualization top_receiver_countries_by_transactions = receiver_country_transaction_count.head(10) # Plot the chart plt.figure(figsize=(12, 8)) sns.barplot(x='transaction_count', y='receiver_country', data=top_receiver_countries_by_transactions, palette='viridis') plt.xlabel('Number of Transactions') plt.ylabel('Receiver Country') plt.title('Top Receiver Countries by Quantity of Transactions') plt.show()

import pandas as pd # The path to the CSV file file_path = "/work/data.csv" # Read data into a pandas dataframe df = pd.read_csv(file_path) # Extract receiver country from recipient_network df['receiver_country'] = df['recipient_network'].str.split('-').str[-1] # Calculate the total number of transactions and the number of transactions sent to Cameroon total_transactions = len(df) transactions_to_cameroon = len(df[df['receiver_country'] == 'cameroon']) # Calculate the percentage of transactions sent to Cameroon percent_to_cameroon = (transactions_to_cameroon / total_transactions) * 100 percent_to_cameroon

Cameroon contributes 85% of total received transaction volume.

Customer Segmentation

Feature Selection

First we have to decide how we want to segment our customer base. What are the most important data points to segment these users?

Some options include by transaction volume or frequency (or both). Country? Do we want to segment based on sender or receiver attributes?

In order to determine the most important factors we have to define our goal of this segmentation.

Since these users are already customers we don't have to segment for user acquisition, so I'll define the goal as:

a. optimize for revenue, and

b. customer retention,

focusing on the sender's attributes, since they are paying the transaction costs and are our customers.

Assumptions:

a. higher send values are more valuable in terms of revenue for the company.

b. we can adjust the marketing campaign in each country by just adjusting language and don't have to consider any other factors such as cultural changes.

Revenue Segmentation

First we'll calculate the total revenue generated by each sender, then we can segment them into 3 bins:

High-revenue (top third of revenue generated)

Medium-revenue (middle third of revenue generated)

Low-revenue (bottom third of revenue generated)

import pandas as pd import matplotlib.pyplot as plt # The path to the CSV file file_path = "/work/data.csv" # Read data into a pandas dataframe df = pd.read_csv(file_path) # Create a new column 'sender_amt_eur' # If the sender_currency is GBP, multiply the sender_amt by 1.17. Otherwise, keep the sender_amt value as is. df['sender_amt_eur'] = df.apply(lambda row: row['sender_amt'] * 1.17 if row['sender_currency'] == 'GBP' else row['sender_amt'], axis=1) # Calculate total revenue generated by each sender (assuming sender_amt as revenue) total_revenue_by_sender = df.groupby('user_id')['sender_amt_eur'].sum().reset_index() # Segment senders based on revenue generated total_revenue_by_sender['revenue_segment'] = pd.qcut(total_revenue_by_sender['sender_amt_eur'], q=[0, 0.33, 0.67, 1], labels=['low', 'medium', 'high']) # Display the first few rows of the segmented data total_revenue_by_sender.head()

Retention Segmentation

Next we'll look at how customers retention can be grouped. We'll use the distribution of transaction counts for segmentation.

import pandas as pd import matplotlib.pyplot as plt # The path to the CSV file file_path = "/work/data.csv" # Read data into a pandas dataframe df = pd.read_csv(file_path) # Calculate transaction frequency for each sender transaction_frequency = df.groupby('user_id').size().reset_index(name='transaction_count') # Display the distribution of transaction counts transaction_frequency['transaction_count'].describe()

Since the majority of users have made 1-2 transactions, it's quite skewed and won't result in a great segmentation. So we'll segment the users into the following bins:

Rare: 1 transaction

Occasional: 2 transactions

Frequent 3+ transactions

import pandas as pd import matplotlib.pyplot as plt # The path to the CSV file file_path = "/work/data.csv" # Read data into a pandas dataframe df = pd.read_csv(file_path) # Segment senders based on defined thresholds transaction_frequency['frequency_segment'] = 'frequent' transaction_frequency.loc[transaction_frequency['transaction_count'] == 1, 'frequency_segment'] = 'rare' transaction_frequency.loc[transaction_frequency['transaction_count'] == 2, 'frequency_segment'] = 'occasional' # Display the first few rows of the segmented data transaction_frequency.head()

Combined Segmentation

Now that we have the 2 distributions, we can combine them.

import pandas as pd import matplotlib.pyplot as plt import seaborn as sns from sklearn.preprocessing import StandardScaler # The path to the CSV file file_path = "/work/data.csv" # Read data into a pandas dataframe data = pd.read_csv(file_path) # Convert sender_amt based on sender_currency to sender_amt_eur data['sender_amt_eur'] = data.apply( lambda row: row['sender_amt'] * 1.17 if row['sender_currency'] == 'GBP' else (row['sender_amt'] if row['sender_currency'] == 'EUR' else None), axis=1 ) # Calculate total revenue generated by each sender in EUR total_revenue_by_sender_eur = data.groupby('user_id')['sender_amt_eur'].sum().reset_index() total_revenue_by_sender_eur['revenue_segment'] = pd.qcut(total_revenue_by_sender_eur['sender_amt_eur'], q=[0, 0.33, 0.67, 1], labels=['low', 'medium', 'high']) # Calculate transaction frequency for each sender transaction_frequency = data.groupby('user_id').size().reset_index(name='transaction_count') transaction_frequency['frequency_segment'] = 'frequent' transaction_frequency.loc[transaction_frequency['transaction_count'] == 1, 'frequency_segment'] = 'rare' transaction_frequency.loc[transaction_frequency['transaction_count'] == 2, 'frequency_segment'] = 'occasional' # Combine the segments combined_segments = pd.merge(total_revenue_by_sender_eur, transaction_frequency, on='user_id') combined_segments['combined_segment'] = combined_segments['revenue_segment'].astype(str) + "_" + combined_segments['frequency_segment'].astype(str) combined_segments

Next we can visualize the combined segmentation.

import pandas as pd import matplotlib.pyplot as plt import seaborn as sns # The path to the CSV file file_path = "/work/data.csv" # Read data into a pandas dataframe data = pd.read_csv(file_path) # Convert sender_amt based on sender_currency to sender_amt_eur data['sender_amt_eur'] = data.apply( lambda row: row['sender_amt'] * 1.17 if row['sender_currency'] == 'GBP' else (row['sender_amt'] if row['sender_currency'] == 'EUR' else None), axis=1 ) # Calculate total revenue generated by each sender in EUR total_revenue_by_sender_eur = data.groupby('user_id')['sender_amt_eur'].sum().reset_index() total_revenue_by_sender_eur['revenue_segment'] = pd.qcut(total_revenue_by_sender_eur['sender_amt_eur'], q=[0, 0.33, 0.67, 1], labels=['low', 'medium', 'high']) # Calculate transaction frequency for each sender transaction_frequency = data.groupby('user_id').size().reset_index(name='transaction_count') transaction_frequency['frequency_segment'] = 'frequent' transaction_frequency.loc[transaction_frequency['transaction_count'] == 1, 'frequency_segment'] = 'rare' transaction_frequency.loc[transaction_frequency['transaction_count'] == 2, 'frequency_segment'] = 'occasional' # Combine the segments combined_segments = pd.merge(total_revenue_by_sender_eur, transaction_frequency, on='user_id') combined_segments['combined_segment'] = combined_segments['revenue_segment'].astype(str) + "_" + combined_segments['frequency_segment'].astype(str) # Plotting each segment by number of users segment_counts = combined_segments['combined_segment'].value_counts() plt.figure(figsize=(12, 8)) sns.barplot(y=segment_counts.index, x=segment_counts.values, palette="viridis") plt.xlabel('Number of Users') plt.ylabel('Customer Segments') plt.title('Distribution of Users Across Combined Segments') plt.show()

As we see above, the largest segment is low-revenue, rare transacting customers.

Here we can see the segments by total sender amount, showing low rare is also sending the most money, due to the quantity of users, not because each person is sending a lot of money.

import pandas as pd import matplotlib.pyplot as plt import seaborn as sns # The path to the CSV file file_path = "/work/data.csv" # Read data into a pandas dataframe df = pd.read_csv(file_path) # Create a new column 'sender_amt_eur' # If the sender_currency is GBP, multiply the sender_amt by 1.17. Otherwise, keep the sender_amt value as is. df['sender_amt_eur'] = df.apply(lambda row: row['sender_amt'] * 1.17 if row['sender_currency'] == 'GBP' else row['sender_amt'], axis=1) # Calculate total revenue generated by each sender (assuming sender_amt as revenue) total_revenue_by_sender = df.groupby('user_id')['sender_amt_eur'].sum().reset_index() # Calculate the total number of users using the data dataframe total_users = df['user_id'].nunique() # Plot the segmentation with percentages plt.figure(figsize=(12, 8)) ax = sns.barplot(y=segment_counts.index, x=segment_counts.values, palette="viridis") plt.xlabel('sender_amt_eur') plt.ylabel('Customer Segments') plt.title('Distribution of Users Across Combined Segments') # Annotate each bar with the percentage for p in ax.patches: width = p.get_width() ax.text(width + 5, # set the text at 5 unit right of the bar p.get_y() + p.get_height()/2, # get Y coordinate + X coordinate / 2 '{:1.2f}%'.format(100 * width/total_users), ha="center", va="center") plt.show()

With this data, we now know where to focus our attention.

Part 2: SQL Queries

Average Transaction Size

SELECT sender_country, round(avg(sender_amt)) as avg_transaction_size FROM '/work/data.csv' GROUP BY sender_country ORDER BY avg_transaction_size desc;

Monthly Growth in Gabon

WITH MonthlyTransactions AS ( -- Calculate monthly transaction counts for Gabon SELECT date_trunc('month',date) AS month, -- Use the appropriate date formatting function for your SQL environment COUNT(*) as transaction_count FROM '/work/data.csv' WHERE RIGHT(recipient_network, LENGTH(recipient_network) - POSITION('-' IN recipient_network)) = 'gabon' GROUP BY date_trunc('month',date) ) SELECT month, transaction_count, LAG(transaction_count) OVER (ORDER BY month) as prev_month_transaction_count, CASE WHEN LAG(transaction_count) OVER (ORDER BY month) = 0 THEN 0 ELSE ((transaction_count - LAG(transaction_count) OVER (ORDER BY month)) / LAG(transaction_count) OVER (ORDER BY month)) * 100 END as month_on_month_growth_percent FROM MonthlyTransactions ORDER BY month;