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.