Project Summary

This project involved a comprehensive analysis of user event data to derive actionable insights into customer behavior and marketing channel effectiveness. By leveraging SQL for data cleaning and enrichment, and preparing the dataset for visualization in Tableau, we've identified key trends in user engagement across various funnel stages and evaluated the performance of different traffic sources. Our findings highlight the crucial role of email and paid search in driving conversions, pinpoint areas for optimizing social media and organic strategies, and provide a clear understanding of user retention patterns through cohort analysis.

Digital acquisition systems operate under diminishing conversion efficiency, where increasing traffic volume does not always produce proportional revenue growth. Understanding user behavior across the funnel allows businesses to optimize acquisition channels, reduce conversion leakage, and improve customer lifetime value efficiency.

Methodology:

Our approach encompassed the following key steps:

1. Data Preprocessing & Enrichment: The user_events dataset ("Assignment Datasets Funnel Cohort - Events") underwent thorough cleaning. Key temporal dimensions (created_year created_month created_day) and a cohort_month identifier were added to facilitate granular analysis and cohort tracking.
2. SQL Scripting: Custom SQL scripts (developed by Q-rocket Lab) were utilized for data transformation and the creation of analytical views necessary for funnel and cohort analysis.
3. Funnel Analysis: We analyzed user progression through key events (home product cart purchase) to identify drop-off points and understand conversion efficiencies.
4. Traffic Source Analysis: Performance of various traffic sources (Email Adwords YouTube Facebook Organic) was evaluated based on unique user acquisition and their contribution across different event types.
5. Cohort Analysis: A monthly cohort analysis was performed to track user retention over time revealing patterns of long-term engagement and potential churn.
6. Visualization Readiness: The processed and enriched dataset was prepared for dynamic visualization in Tableau enabling interactive exploration of insights.

Datasets

The core of this analysis was built upon the user_events dataset identified as "Assignment Datasets Funnel Cohort - Events." This dataset contained detailed records of user interactions and marketing attribution data enabling a holistic view of the customer journey.

• Dataset Name: user_events (referred to as "Assignment Datasets Funnel Cohort - Events")
• Key Data Points: User IDs event types (e.g. home product cart purchase) timestamps and associated traffic sources.

Analysis & Key Insights

1. Funnel Analysis: Understanding User Journey Stages

• Event Type Insights:
• The "cart" and "product" events show the highest unique user engagement indicating strong user interest in products and adding items to the cart.
• A natural decline in unique users is observed from "cart" to "purchase " highlighting the importance of optimizing the checkout process.
• The "home" event had the lowest unique user count suggesting direct navigation to product/category pages is common.
• Event Type Comparisons:
• Product vs. Cart: The gap between product views and cart additions can indicate product appeal or issues with product information/pricing.
• Cart vs. Purchase: The conversion rate from cart to purchase is a critical metric revealing potential friction points in the checkout flow (e.g. shipping costs payment options).

2. Traffic Source Analysis: Channel Performance Evaluation

• Traffic Source Insights:
• Email is the leading traffic source by a significant margin underscoring its effectiveness in attracting and re-engaging users.
• Adwords consistently ranks as the second-largest source demonstrating strong performance for paid acquisition.
• YouTube and Facebook contribute moderately suggesting their role in awareness and initial engagement.
• Organic traffic is currently the lowest indicating an opportunity for SEO enhancement.
• Traffic Source Comparisons:
• Email vs. Adwords: The stark difference suggests either superior email retention strategies or more cost-effective email acquisition.
• YouTube & Facebook: While contributing similar user volumes further analysis of post-click behavior can reveal distinct user characteristics.
• Organic Potential: Low organic traffic presents a clear opportunity for SEO investment to build sustainable high-quality traffic.

3. Event Type VS Traffic Source: Granular Channel Effectiveness

• Key Findings:
• Email consistently proves to be the most effective channel across all funnel stages (home product cart purchase) making it crucial for driving final conversions.
• Adwords is highly effective in bringing users with purchase intent showing significant contributions to "cart" and "purchase" events.
• YouTube and Facebook primarily support awareness and initial engagement (home visits product views) with lower contributions to direct conversions (cart purchase).
• Organic traffic shows a modest contribution especially at the product viewing stage.
• Overall Conclusion: Email is a powerhouse for conversion and Adwords is a strong contender for acquiring high-intent users. Social media plays a vital role in early-funnel engagement and optimizing organic reach remains a growth opportunity.

4. Cohort Analysis: User Retention & Lifetime Value##

• Cohort Insights:
• Initial Drop-Off: A significant decline in active users occurs after the first month across most cohorts highlighting the importance of early user experience.
• Cohort Variation: Retention rates vary between different acquisition cohorts suggesting potential influences from acquisition strategies user quality or product changes.
• Long-Term Decline: A natural gradual decrease in retention is observed as cohorts age illustrating the typical user lifecycle.
• Retention Opportunity: The drop-off after the first month presents a prime opportunity to implement strategies to improve initial user engagement and reduce early churn.
• Conclusion: Cohort analysis provides critical insights into user loyalty and helps identify key periods for intervention to improve retention and maximize customer lifetime value. Monitoring the performance of newer cohorts is essential for adapting retention strategies.