By performing in-depth customer segmentation and marketing channel analysis, I identified Facebook as the most effective marketing channel and uncovered significant customer behavior patterns through RFM analysis, providing actionable insights to optimize strategic marketing efforts.

Project Summary

This project aims to provide insights into customer behavior and marketing channel effectiveness by analyzing two datasets:

1. Attribution Data: This dataset captures customer interactions with different marketing channels, including conversions and their associated values.
2. Superstore Orders Data: This dataset contains sales and customer information for a superstore, enabling customer segmentation using RFM analysis.

A mission focused on understanding how customer behavior and marketing channels generate commercial momentum through segmentation, attribution analysis, and conversion efficiency modeling.

Methodology:

Attribution Analysis

• attribution_analysis.sql: This script analyzes the attribution data to:
• Calculate total interactions and conversions per channel.
• Determine conversion rates for each channel.
• Calculate total and average conversion values.
• Analyze customer session and conversion behavior.
• Create summary tables for marketing channel performance and customer behavior.

RFM Analysis

• rfm_analysis.sql: This script performs RFM (Recency Frequency Monetary) analysis on the superstore orders data to segment customers. It includes steps to:
• Calculate RFM values for each customer.
• Assign RFM scores.
• Create a final table with RFM scores and customer data.
• Integrate RFM scores with superstore order detail.

Insights Summary

1. Facebook: The Most Effective Channel

• Conversions: 1 996 | Total Conversion Value: 12.51K | Interactions: 67K Facebook delivers the highest number of conversions and interactions across all channels indicating that it is the most effective platform for driving user actions such as purchases or sign-ups. Its performance is consistently strong across both engagement and conversion metrics.

2. Paid Search: A Strong Contender

• Conversions: 1 747 | Total Conversion Value: 10.82K | Interactions: 58K Paid Search ranks second demonstrating its strength in capturing high-intent traffic. The balance between conversions and interactions suggests this channel is highly effective for performance-driven campaigns.

3. Online Video: Solid Mid-Level Performance

• Conversions: 1 301 | Total Conversion Value: 8.15K | Interactions: 43K Video content remains a valuable channel for engagement and mid-funnel conversion. While it doesn't outperform Facebook or Paid Search it contributes significantly to awareness and action.

4. Instagram and Online Display: Lowest Performance

• Instagram: 868 conversions | 5.38K Conversion Value | 29K Interactions
• Online Display: 816 conversions | 4.99K Conversion Value | 28K Interactions These channels show the lowest conversion and engagement rates. This may be due to a mismatch in ad creative audience targeting or the inherently passive nature of the platforms.

Strategic Recommendations

1. Prioritize High-Performing Channels

• Maintain and further optimize campaigns on Facebook and Paid Search as they provide the highest return on investment (ROI) in terms of conversions and engagement.

2. Conduct Cost-Effectiveness Analysis

• Perform a cost-per-conversion and conversion-per-interaction analysis to ensure continued efficiency across channels.

3. Leverage Online Video for Mid-Funnel Strategy

• Develop and test video content focused on brand awareness and nurturing interest especially for audiences not yet ready to convert.

4. Reevaluate Instagram and Online Display Strategies

• Explore improvements through:
• A/B testing new ad creatives and messaging
• Revisiting audience segmentation Optimizing