SQL Data-Driven Analysis

📊 A comprehensive analysis of website

traffic, marketing channels, user behavior,
and product performance using SQL.

Prepared by: Sriharshini Ankam​

Role: eCommerce Data Analyst​
Date: 4th March, 2025
Introduction to the Project..........................................................................................................3
Project Overview......................................................................................................................3
Project Scope & Objectives..................................................................................................... 3
Key Objectives:........................................................................................................................3
Methodology & Tools............................................................................................................... 4
Database Introduction: Maven Fuzzy Factory.......................................................................... 4
Overview..................................................................................................................................4
Database Structure..................................................................................................................5
1. website_sessions (Tracks user activity).........................................................................5
2. website_pageviews (Records page interactions)...........................................................5
3. products (Contains product details)............................................................................... 5
4. orders (Captures customer purchases)......................................................................... 6
5. order_items (Breaks down order details)....................................................................... 6
6. order_item_refunds (Tracks refunds and returns)......................................................... 6
1. Traffic Source Analysis........................................................................................................... 7
a. Traffic Source Trend Analysis.............................................................................................. 8
b. Traffic Source Bid Optimization........................................................................................... 8
c. Traffic Source Segment Trending........................................................................................ 9
2. Analyzing Website Performance.......................................................................................... 10
a. Analyzing Top Website Content......................................................................................... 10
I. Identifying the Most Viewed Pages...............................................................................10
ii. Analyzing Entry Pages................................................................................................. 11
b. Landing page performance & Testing................................................................................ 11
i. Landing Page vs Exit Page Conversion Rate............................................................... 12
ii. Bounce Rate Analysis.................................................................................................. 13
iii. Weekly Bounce Rate Trends....................................................................................... 13
c. Analyzing & Testing Conversion Funnels...........................................................................14
i. Understanding the Conversion Funnel..........................................................................15
ii. Key Takeaways & Opportunities for Improvement....................................................... 15
3. Channel Portfolio Analysis & Optimization.........................................................................16
Overview.......................................................................................................................... 18
a. Weekly Session Trend Analysis...................................................................................18
b. Mobile Traffic Distribution.............................................................................................18
c. Conversion Rate Analysis (Bid Optimization).............................................................. 19
d. Channel Portfolio Trends............................................................................................. 19
4. Product Analysis................................................................................................................... 20
Overview.......................................................................................................................... 20
a. Product Sales Trends.................................................................................................. 20
b. Product Launch Performance...................................................................................... 21
c. Product Pathing Analysis............................................................................................. 22
d. Product Conversion Funnel Analysis........................................................................... 22
 e. Cross selling Products................................................................................................. 23
f. Portfolio expansion analysis......................................................................................... 25
g. Product Refund Analysis..............................................................................................27
5. User Analysis......................................................................................................................... 29
Overview.......................................................................................................................... 29
1. User Sessions & Engagement..................................................................................... 30
2. Time Between Repeat Visits........................................................................................ 30
3. Traffic Sources: New vs. Repeat Visitors.....................................................................31
4. Conversion & Revenue: New vs. Repeat Users.......................................................... 31
Recommendations for Growth............................................................................................... 31
Introduction to the Project
Project Overview
This project focuses on analyzing and optimizing website traffic, marketing channels, and user
behavior for Maven Fuzzy Factory, an online retailer that has just launched its first product.
The goal is to leverage SQL and data-driven insights to improve business performance,
enhance conversion rates, and optimize marketing spend.

As an eCommerce Database Analyst, my role in this project was to work with the CEO, Head
of Marketing, and Website Manager to analyze marketing channels, measure website
conversion performance, and assess the impact of new product launches.

Project Scope & Objectives

The project was structured into multiple phases, covering key areas of traffic analysis, website
measurement, and user behavior insights. Each phase involved extracting, analyzing, and
interpreting data from the Maven Fuzzy Factory database to support business decisions.

Key Objectives:
✅ Traffic Analysis & Optimization – Identify top traffic sources, measure conversion rates,
✅ Website Measurement & Testing – Find the most-visited pages, track entry and exit
analyze trends, and segment data for bidding optimization.​

✅ Channel Analysis & Optimization – Compare marketing channels, evaluate performance,

points, calculate bounce rates, and analyze conversion funnels.​

✅ Product-Level & User-Level Analysis – Understand sales trends, assess the

and develop an optimized channel portfolio.​

effectiveness of cross-selling strategies, and analyze customer behavior patterns.

Each of these components contributed to building a data-driven strategy for improving

website traffic, increasing conversions, and maximizing marketing ROI.

Methodology & Tools

🔹 SQL Queries: Used extensively to extract and manipulate data from the Maven Fuzzy
🔹 Data Visualization: Insights were derived from query results and presented in an
Factory database.​

easy-to-understand format.​
🔹 Trend Analysis: Evaluated weekly and monthly trends to identify key growth opportunities
🔹 Optimization Strategies: Provided actionable recommendations for improving traffic
and problem areas.​

quality, reducing bounce rates, and increasing conversions.

By applying data analytics to real-world eCommerce challenges, this project provided

practical business insights that can help Maven Fuzzy Factory make better marketing and
sales decisions.

Database Introduction: Maven Fuzzy Factory

Overview
The Maven Fuzzy Factory database is designed to model a real-world eCommerce platform.
This database contains structured information about website activity, customer sessions,
products, orders, and refunds. The goal of using this database is to analyze how users
interact with the website, measure key performance metrics, and optimize marketing efforts for
better conversions and revenue growth.

The database is implemented using MySQL, and the full SQL script used to create it is attached

📂
to this report with the filename:​
create_mavenfuzzyfactory_vApril2022.sql
Database Structure
The Maven Fuzzy Factory database consists of six related tables, each serving a distinct
purpose in analyzing website performance and sales trends. Below is a breakdown of these
tables and their functions:

1. website_sessions (Tracks user activity)

●​ Stores session-related data such as session ID, user ID, traffic source, campaign details,
and device type.
●​ Helps analyze how users arrive on the site and which marketing campaigns drive the
most traffic.

2. website_pageviews (Records page interactions)

●​ Logs each pageview within a session and links it to the corresponding session ID.
●​ Helps analyze landing page effectiveness, bounce rates, and user navigation patterns.

3. products (Contains product details)

●​ Stores product names, creation timestamps, and product IDs.
●​ Useful for tracking sales trends and product-level performance.
4. orders (Captures customer purchases)
●​ Includes order ID, session details, user ID, primary product ID, and total price.
●​ Helps track sales conversion rates and revenue generation.

5. order_items (Breaks down order details)

●​ Tracks individual products within each order, including item prices, quantities, and cost of
goods sold (COGS).
●​ Helps measure profit margins, upselling success, and cross-selling strategies.

6. order_item_refunds (Tracks refunds and returns)

●​ Captures refund amounts, order details, and timestamps.
●​ Helps businesses understand refund patterns and improve customer satisfaction.

Next Steps

●​ To explore this database further, you can execute the attached SQL script
(create_mavenfuzzyfactory_vApril2022.sql) in MySQL Workbench to generate the
schema.
●​ Once the database is set up, you can start running analytical queries to derive key
insights on website traffic, sales trends, and customer behavior.
 1.​Traffic Source Analysis

We use the UTM parameters stored in the database to identify paid website sessions.
From our session data, we can link to our order data to understand how much revenue our
paid campaigns are driving.

Traffic Analysis and Optimization Report

📅 Date Range: March 1, 2012 – June 9, 2012​
📊 Data Source: Google Search (Non-Brand Campaign)
For detailed code and SQL queries, please refer to the document "1. Traffic Analysis and
Optimization" in the repository.

a.​Traffic Source Trend Analysis

🔍 Key Findings:​
✔ Traffic is gradually declining from March to early May.​
✔ The highest number of sessions was recorded on April 1, 2012, with 1,152 sessions.​
✔ Post-April, there is a noticeable downward trend in traffic.

📉 Possible Causes:​
🔸 Seasonal fluctuations affecting user engagement.​
🔸 Increased competition from other advertisers.​
🔸 Changes in Google Ads rankings or bidding strategies.
💡 Actionable Recommendations:​
✅ Investigate potential seasonal trends in past years to confirm if this is a recurring pattern.​
✅ Re-evaluate ad creatives, keywords, and targeting to maintain traffic levels.​
✅ Test different bidding strategies to regain lost momentum.

b.​Traffic Source Bid Optimization

🔍 Key Findings:​
✔ Desktop has a significantly higher conversion rate compared to mobile.​
✔ Desktop Sessions: 3,911 | Conversion Rate: 3.73%​
✔ Mobile Sessions: 2,492 | Conversion Rate: 0.96%

💡 Actionable Recommendations:​
✅ Increase bids for desktop marketing campaigns to maximize conversions.​
✅ Reallocate budget from mobile to desktop for better efficiency.​
✅ Analyze mobile user behavior to understand drop-offs and improve mobile experience.
🚀 Impact of This Strategy:​
✔ Higher ROI on marketing spend by targeting users who convert better.​
✔ Improved ad visibility on desktop searches, leading to more quality sessions.
 c.​Traffic Source Segment Trending
🔍 Key Findings:​
✔ Desktop traffic is consistently higher than mobile.​
✔ The highest desktop sessions were recorded in the week of May 20, 2012 (661 sessions).​
✔ Mobile traffic peaked earlier, on May 6, 2012 (282 sessions), and then started declining.

💡 Actionable Recommendations:​
✅ Continue prioritizing desktop ads, as desktop users have a higher engagement and
✅ Improve mobile site experience to prevent further decline in mobile sessions.​
conversion rate.​

✅ Test different ad placements and creatives for mobile to improve performance.

🚀 Expected Benefits:​
✔ Better cost efficiency by investing more in high-converting desktop users.​
✔ Balanced traffic distribution by enhancing mobile experience and optimizing mobile ads.​
✔ Higher overall conversions and revenue by focusing on high-performing channels.

Final Takeaways

🔹 Desktop remains the best-performing traffic source – invest more in desktop ads.​
🔹 Mobile traffic is declining – investigate and optimize the mobile experience.​
🔹 Traffic is decreasing after April 1 – revisit bidding strategies to counteract this trend.
🚀
By implementing these changes, we can increase conversions, improve ad efficiency, and
ensure better return on marketing investments.

For a deeper dive into the data and SQL queries, please refer to "1. Traffic Analysis and
Optimization" in the repository.
 2.​Analyzing Website Performance

a.​Analyzing Top Website Content

Finding Top Pages

●​ We can analyze our pageviews data and GROUP BY URL to see which pages are
viewed most.
●​ To find top entry pages, we will limit to just the first page a user sees during a given
session, using a temporary table.

📅 Date Range: Before June 12, 2012​

📊 Data Source: Website Pageviews & Sessions
For detailed code and SQL queries, please refer to the document "2. Website Performance
Analysis" in the repository.

I. Identifying the Most Viewed Pages

🔍 Key Findings:​
✔ The Home Page is the most frequently visited page, making it a key entry point.​
✔ The Products Page ranks second, indicating strong user interest in browsing product
offerings.​
✔ The Cart and Thank You pages have significantly fewer views, suggesting that users drop off
before completing purchases.​
✔ Lower-ranked pages may need improvements in navigation or marketing efforts to attract
more visitors.
💡 Actionable Recommendations:​
✅ Optimize the Home Page with engaging content, clear navigation, and better CTAs to guide
✅ Improve the visibility of the Cart Page by making the checkout process more seamless.​
users toward purchases.​

✅ Consider A/B testing alternative landing pages to see if different layouts improve user
engagement.

ii. Analyzing Entry Pages

🔍 Key Findings:​
✔ The Home Page is the top entry page, meaning most visitors start their journey here.​
✔ Other entry points, such as product pages, have significantly fewer sessions.​
✔ High drop-off rates from non-home entry pages indicate that users may not be engaging
enough after landing.

💡 Actionable Recommendations:​
✅ Improve first impressions on non-home entry pages by making them more engaging,
✅ Ensure all entry pages have a clear path guiding users towards checkout or product
informative, and conversion-focused.​

✅ Consider driving traffic directly to high-converting product pages through targeted ads or
exploration.​

SEO strategies.

Business Impact & Next Steps

📊 Why This Matters?

●​ Understanding which pages users visit the most helps prioritize improvements.
●​ Optimizing entry pages reduces bounce rates and improves conversions.
●​ Enhancing the checkout process can increase completed orders and revenue.

Landing page performance & Testing

 ●​ To analyze landing page performance and compare multiple pages, we will again use
temporary tables and write a multi-step "data program".
●​ We will find the first pageview for relevant sessions, associate that pageview with the
URL seen, then analyze whether that session had additional pageviews.

Website Performance & Bounce Rate Analysis Report

📅 Date Range: January 2014 – August 2012 (Historical Data for Trend Analysis)​
📊 Data Source: Website Sessions & Pageviews
For detailed code and SQL queries, please refer to the document "2. Website Performance
Analysis" in the repository.

i. Landing Page vs Exit Page Conversion Rate

🔍 Key Findings:​
✔ Users who land on the home page tend to stay longer and navigate further into the site
compared to other landing pages.​
✔ Lander-3 has the highest bounce rate (61.57%), indicating poor engagement or content
issues.​
✔ The checkout funnel experiences a drop-off before the shipping page, affecting
conversion rates.​
✔ The home page conversion rate to the shipping page is the highest (3.298%), while
Lander-3 performs the worst (2.00%).

💡 Actionable Recommendations:​
✅ Improve Lander-3 by optimizing content, visuals, and navigation to increase
✅ Reduce checkout friction by making the process easier and adding trust-building
engagement.​

✅ Conduct A/B testing on different landing page layouts to improve conversion rates.
elements (security badges, progress indicators, etc.).​

ii. Bounce Rate Analysis

🔍 Key Findings:​
✔ Overall bounce rate: 59.18% (6,538 sessions bounced out of 11,048 total sessions).​
✔ A bounce rate above 50% suggests content, navigation, or targeting issues.​
✔ The home page has a slightly worse bounce rate (58.34%) than ‘/lander-1’ (53.24%),
indicating that visitors may not be finding what they expect.

💡 Actionable Recommendations:​
✅ Enhance landing pages with clear calls-to-action, engaging content, and intuitive navigation.​
✅ Test different headlines, images, and layouts to see what keeps users engaged.​
✅ Improve site load speed and ensure mobile optimization to reduce immediate exits.

iii. Weekly Bounce Rate Trends

🔍 Key Findings:​
✔ The home page started with a bounce rate above 60% in June but improved over time.​
✔ Between July 29 - August 26, traffic to the home page dropped significantly, indicating a
possible shift in marketing strategy.​
✔ The bounce rate for '/lander-1' increased in August, suggesting ineffective engagement
strategies or user targeting issues.

💡 Actionable Recommendations:​
✅ Analyze traffic sources and marketing campaigns to understand the reason behind shifts in
✅ Implement behavioral analysis tools (such as heatmaps) to see where users drop off and
home page traffic.​

✅ Ensure consistency in ad messaging and landing page content to align with user
why.​

expectations.
 Business Impact

📊 Why This Matters?

●​ A high bounce rate means lost potential customers – reducing it can increase
conversions and sales.
●​ Landing pages play a crucial role in user engagement – optimizing them can improve ad
performance and reduce wasted marketing spend.
●​ Tracking and improving conversion funnel performance can help drive more users
toward checkout completion.

b.​Analyzing & Testing Conversion Funnels

●​ We will create temporary tables using pageview data to build our multi-step funnels.
●​ We will first identify the sessions we care about, then bring in the relevant pageviews,
flag each session as having reached specific funnel steps, and finally perform a
summary analysis.
Website Conversion Funnel Analysis Report

📅 Date Range: June 19, 2012 – July 28, 2012​

📊 Data Source: Website Sessions & Pageviews
For detailed code and SQL queries, please refer to the document "2. Website Performance
Analysis" in the repository.

i. Understanding the Conversion Funnel

The goal of this analysis is to track how users navigate through the website, engage with key
pages, and complete purchases. By identifying drop-off points, we can optimize the funnel to
improve conversions.

Key Clickthrough Rates:

✔ 29.42% of users from the home page clicked through to "The Original Mr. Fuzzy" product
page – a strong indicator of product interest.​
✔ 24.74% of users who viewed the product proceeded to the cart page, meaning a significant
portion of interested users don’t add to cart.​
✔ 100% of users who reach the billing page continue – suggesting once users reach billing,
they’re committed to purchasing.​
✔ 75% of users on the billing page complete their purchase and reach the Thank You page,
meaning a quarter of users abandon at the last step.​
✔ Users coming from "Lander-1" engage slightly better, with 34.38% clicking through to the
product page.​
✔ Lander-1 users also have a higher checkout completion rate, with 91.26% making it to the
Thank You page.

ii. Key Takeaways & Opportunities for Improvement

🔹 Cart Page Drop-Off (24.74% Clickthrough Rate)
●​ Problem: Many users engage with the product page but don’t add it to the cart.
●​ Solution: Optimize the cart experience by reducing friction, adding trust signals (free
shipping, secure payment badges), and including limited-time discounts to encourage
conversion.

🔹 Checkout Funnel Optimization (75% Completion Rate)

 ●​ Problem: 25% of users abandon at the final step, meaning some hesitation remains
before purchase.
●​ Solution: Improve checkout flow with fewer form fields, guest checkout options, and
additional payment methods to reduce drop-offs.

🔹 Lander-1 Outperforms Home Page in Engagement

●​ Problem: The home page gets more visitors, but Lander-1 converts users better.
●​ Solution: Consider using Lander-1 in targeted ad campaigns to drive higher-intent traffic
to product pages.

🔹 Biggest Drop-Off is from Cart to Billing

●​ Problem: Only 24.74% of users make it past the cart, which is the biggest bottleneck.
●​ Solution: Experiment with cart abandonment emails, exit-intent popups, and progress
indicators to reassure users and improve conversion rates.

3. Channel Portfolio Analysis &

Optimization
Here’s a non-technical report based on the Channel Portfolio Analysis & Optimization
findings.
 Overview
The goal of this analysis was to assess the performance of two paid search channels,
Gsearch and Bsearch, across mobile and desktop traffic. By examining weekly trends, mobile
traffic distribution, conversion rates, and overall channel effectiveness, we identified key insights
to optimize bid strategy and marketing spend.

📅 Date Range Analyzed:

●​ August 22, 2012 – December 22, 2012

📊 Data Source:
●​ Website Sessions Table (website_sessions)
●​ Orders Table (orders)

For detailed SQL queries and code implementation, please refer to the document "3.
Channel Portfolio Analysis & Optimization" in the repository.

a.​ Weekly Session Trend Analysis

We analyzed weekly traffic from August 22, 2012 – November 29, 2012 to understand session
trends across Gsearch and Bsearch.

Key Findings:

✔ Gsearch consistently outperforms Bsearch in driving website sessions.​

✔ A significant traffic peak was observed in mid-November for Gsearch, indicating an effective
campaign push.​
✔ Bsearch remains stable but significantly lower than Gsearch, suggesting a weaker impact on
website traffic.

📌 Actionable Insight:​
Since Gsearch attracts a higher number of sessions, marketing budgets should prioritize
Gsearch while re-evaluating Bsearch's role in campaigns.

b.​ Mobile Traffic Distribution

We compared how mobile users contribute to total sessions in both search channels.

Key Findings:
✔ Gsearch has a stronger mobile presence, with 24.52% of its sessions from mobile users.​
✔ Bsearch mobile traffic is very low (only 8.62%), indicating that most of its audience uses
desktops.

📌 Actionable Insight:​
Bsearch campaigns may need a stronger mobile strategy or a shift in budget towards
Gsearch’s mobile ads, as mobile engagement is higher in Gsearch.

c. Conversion Rate Analysis (Bid Optimization)

We evaluated conversion rates for both mobile and desktop traffic across Gsearch and
Bsearch.

Key Findings:

✔ Desktop conversion rates are significantly higher than mobile for both channels.​
✔ Gsearch Desktop CVR: 4.52%, while Bsearch Desktop CVR: 3.73% (indicating Gsearch
performs better).​
✔ Mobile conversion rates are poor overall (Gsearch: 1.28%, Bsearch: 0.76%).

📌 Actionable Insight:
●​ Since Gsearch has the highest desktop conversion rate, increasing bids for desktop
traffic on Gsearch could yield higher returns.
●​ Bsearch’s mobile conversion rate is weak, so investing in mobile-friendly ad formats or
reducing spend on Bsearch mobile ads might be a better strategy.

d. Channel Portfolio Trends

We examined how desktop and mobile sessions compare across both channels over time.

Key Findings:

✔ Bsearch’s desktop traffic is consistently 40% of Gsearch’s traffic, meaning it has some value
but is secondary.​
✔ Bsearch’s mobile traffic is consistently weak (8-12% of Gsearch’s mobile sessions).​
✔ Gsearch dominates both desktop and mobile, making it the stronger paid search platform
overall.

📌 Actionable Insight:
 ●​ Increase ad spend on Gsearch desktop as it delivers the highest conversion rates and
strong traffic numbers.
●​ Reduce reliance on Bsearch mobile ads, as they bring low traffic and poor conversions.
●​ Consider reallocating some Bsearch budget to Gsearch mobile, which has a larger
mobile audience.

4. Product Analysis

Product-Level Sales and Conversion Analysis

Overview

This report provides insights into product sales trends, revenue generation, product pathing, and
conversion funnel performance. The data is derived from transactional records between April
2012 and April 2013, capturing key performance metrics before and after new product
launches.

For detailed SQL queries and implementation, please refer to the document "4.1. Product
Sales Analysis" in the repository.

a.​ Product Sales Trends

Date Range: March 2012 - January 2013​
Data Source: Orders Table

Key Insights:

●​ Steady Growth in Sales: Monthly sales increased from 60 orders in March 2012 to a
peak of 618 orders in November 2012.
●​ Revenue and Margins Increased:
○​ March 2012 Revenue: $2,999.40
○​ November 2012 Revenue: $30,893.82
○​ Gross Margin Growth: $1,830 in March to $18,849 in November.
●​ Post-December Drop: Sales significantly declined in January 2013 to 42 orders,
suggesting a seasonal impact.

b.​ Product Launch Performance

Date Range: April 2012 - April 2013​

Data Source: Website Sessions & Orders Table

Key Insights:

●​ Conversion Rate Growth: Improved from 2.65% in April 2012 to 5.02% in December
2012, indicating better site engagement and purchase intent.
●​ Revenue per Session Increased:
○​ April 2012: $1.32 per session
○​ December 2012: $2.51 per session
●​ New Product Performance (Product 2):
○​ Gained 162 orders by February 2013, outperforming expectations.
○​ Shift in sales trends after launch, impacting existing product (Product 1).
 c.​ Product Pathing Analysis

Date Range: October 2012 - April 2013​

Data Source: Website Pageviews Table

Key Insights:

●​ Before New Product (Pre-January 2013):

○​ 72.29% of users navigated from the "Products" page to "The Original Mr. Fuzzy."
●​ After New Product (Post-January 2013):
○​ 61.3% of users continued to "The Original Mr. Fuzzy."
○​ 14.44% started visiting "The Forever Love Bear," showing initial traction.
●​ Conclusion: Product 2’s introduction led to traffic distribution between both products.

d.​ Product Conversion Funnel Analysis

Date Range: January 2013 - April 2013​

Data Source: Website Pageviews Table

Key Insights:

●​ Cart Clickthrough Rates:

○​ "Forever Love Bear": 54.85%
○​ "Original Mr. Fuzzy": 43.49%
 ●​ Checkout Funnel Performance:
○​ Shipping Page Clickthrough: ~69%
○​ Billing Page Clickthrough: ~81%
○​ Final Purchase Clickthrough: 63.6% (Mr. Fuzzy), 61.68% (Forever Love Bear)
●​ Conclusion:
○​ The Forever Love Bear had a higher add-to-cart rate, making it the more
engaging product.
○​ Both products had similar checkout performance, with minor drop-offs at
billing.

Final Recommendations

1.​ Optimize Seasonal Strategy: Address post-December sales decline by offering

promotions in Q1.
2.​ Enhance Product Visibility: Continue marketing Product 2, as it is attracting traffic.
3.​ Improve Checkout Process: Reduce friction at billing to increase purchase completions.
4.​ Focus on Cart Optimization: Since Forever Love Bear has a higher cart clickthrough, test
promotions for "Mr. Fuzzy" to balance engagement.

e. Cross selling Products

Cross-Selling Strategy Performance Analysis

Overview

This report evaluates the impact of implementing a cross-selling strategy on customer

purchasing behavior. The analysis compares key performance indicators (KPIs) before and after
the introduction of cross-selling to determine its effectiveness in increasing sales and revenue.

To view the detailed SQL code used for this analysis, please refer to the document 4.2 Product
Cross selling Analysis in the repository.

●​ Data Source: Website session and order data from the mavenfuzzyfactory database,
specifically from the website_pageviews and orders tables.
●​ Pre Cross-Selling Period: August 25, 2013 – September 25, 2013
●​ Post Cross-Selling Period: September 25, 2013 – October 25, 2013

Key Insights

1. Cart Clickthrough Rate (CTR)

●​ Before Cross-Selling: 67.16%

●​ After Cross-Selling: 68.41%
●​ Impact: A 1.25% increase in CTR, indicating that more customers proceeded to the next
step after viewing their cart. This suggests that the cross-selling strategy encouraged
customers to continue shopping.

2. Products Per Order

●​ Before Cross-Selling: 1.00 products per order

●​ After Cross-Selling: 1.04 products per order
●​ Impact: A slight increase, showing that customers are purchasing additional items after
cross-selling was introduced.

3. Average Order Value (AOV)

●​ Before Cross-Selling: $51.42

●​ After Cross-Selling: $54.25
●​ Impact: AOV increased by nearly $3, suggesting that customers are either buying
higher-priced items or additional products due to cross-selling.

4. Revenue Per Cart Session

●​ Before Cross-Selling: $18.31

●​ After Cross-Selling: $18.43
●​ Impact: A small but stable increase, indicating that revenue per cart session remained
consistent, even as customers engaged more with additional products.
Analysis & Recommendations

The cross-selling strategy successfully improved customer engagement and sales performance
with a higher cart clickthrough rate, increased products per order, and a rise in average
order value.

Recommendations for Further Improvement:

●​ Refine Product Recommendations: Optimize suggested items based on customer

preferences and purchase history to further boost conversions.
●​ Test Different Upsell Strategies: Experiment with limited-time offers or bundled deals
to increase revenue per cart session.
●​ Enhance the User Experience: Make cross-selling suggestions more visually appealing
and seamlessly integrated into the shopping experience.
●​ Monitor Customer Feedback: Analyze customer reactions to cross-selling efforts to
ensure the strategy is well-received and not disrupting the shopping flow.

By continuously refining the cross-selling approach, there is potential for even greater
improvements in sales, customer satisfaction, and overall business performance.

f. Portfolio expansion analysis

Overview

This report analyzes the impact of the Birthday Bear event on website performance by
comparing key metrics before and after the event. The goal is to understand changes in
customer behavior, conversion rates, and revenue generation.

For technical details and SQL code, please refer to document 4.3 Product Portfolio
Expansion Analysis in the repository.

Date Range & Data Source

●​ Date Range: November 12, 2013 – January 12, 2014

○​ Pre-Birthday Bear: Before December 12, 2013
○​ Post-Birthday Bear: December 12, 2013, onward
●​ Data Source: Maven Fuzzy Factory database (website_sessions & orders tables)

Key Findings
1. Website Traffic & Orders

●​ Before the event: 17,343 website sessions, 1,055 orders.

●​ After the event: 13,383 website sessions, 940 orders.
●​ Insight: Fewer visitors came to the site after the event, leading to a decline in total
orders.

2. Conversion Rate (CR)

●​ Before the event: 6.08%

●​ After the event: 7.02%
●​ Insight: Despite lower traffic, a higher percentage of visitors made a purchase after the
event. This suggests that post-event visitors were more engaged and likely to buy.

3. Total Revenue

●​ Before the event: $57,208.96

●​ After the event: $53,515.44
●​ Insight: Although revenue slightly declined due to fewer orders, it remained strong, likely
due to higher spending per customer.

4. Average Order Value (AOV)

●​ Before the event: $54.23

●​ After the event: $56.93
●​ Insight: Customers spent more per order post-event, helping to offset the decrease in
total orders.

5. Products Per Order

●​ Before the event: 1.05 items per order

●​ After the event: 1.12 items per order
●​ Insight: Shoppers bought more items in each purchase after the event, which
contributed to maintaining revenue levels despite the lower number of orders.

6. Revenue Per Session (RPS)

●​ Before the event: $3.30 per visitor

●​ After the event: $3.99 per visitor
●​ Insight: Each website visitor generated more revenue after the event, indicating
improved customer value and purchase behavior.

Key Takeaways & Recommendations

✔ Higher Conversion & Order Value Post-Event

●​ The event led to more engaged visitors and increased spending per purchase.

✔ Decline in Website Traffic

●​ Fewer visitors came to the site after the event. This might be due to marketing
adjustments or reduced promotional efforts.

✔ Sustaining Revenue Despite Lower Traffic

●​ The rise in conversion rates and spending per customer compensated for lower website
traffic. Future marketing efforts should focus on attracting more visitors while
maintaining high engagement levels.

g. Product Refund Analysis

Product Refund Rates Analysis Report

Overview

This report provides an analysis of product orders and refund trends over time. The goal is to
identify patterns in customer refunds and assess potential areas for improvement in product
quality or customer satisfaction.
For technical details and SQL code, please refer to document 4.4 Product Refund Rates
Analysis in the repository.

Key Findings

1. Order Trends

●​ Product 1 had the highest number of orders, maintaining consistent demand.

●​ Product 2 started gaining traction in early 2013 and continued growing.
●​ Products 3 and 4 saw a notable increase in orders from late 2013, indicating new
product expansion.

2. Refund Trends

●​ Product 1 had the highest refunds, in line with its high sales volume.
●​ Product 2 experienced fluctuating refund trends, with occasional spikes.
●​ Products 3 and 4 had lower refunds initially, but as sales increased, their refund
numbers also grew.

3. Refund Rates (Percentage of Orders Refunded)

●​ Product 1: Refund rate ranged from 2.3% to 9.0% across different months.
●​ Product 2: Initially low but showed occasional spikes, indicating possible quality issues.
●​ Product 3 & 4: Refund rates increased as sales grew, reaching 7-8% in later months,
suggesting post-launch challenges.

Key Insights

✔ Steady demand for Product 1, but its refund rate remains moderate and should be
monitored.​
✔ Product 2’s fluctuations in refunds could indicate quality or fulfillment inconsistencies.​
✔ Product 3 & 4 refunds increased with sales growth, requiring further analysis to identify
customer pain points.​
✔ Overall refund rates are within an acceptable range, but certain months had spikes that
need investigation.

Recommendations

✅ Investigate refund spikes for Products 2, 3, and 4 to identify underlying issues.​

✅ Monitor Product 1 closely as it drives the most sales and refunds.​
✅ Review customer complaints and return reasons to improve product experience.​
✅ Enhance quality control and after-sales support for newly launched products.
By addressing these insights, we can improve customer satisfaction, reduce refunds, and
enhance product reliability.

📌 For detailed SQL analysis, please refer to document 4.4 Product Refund Rates Analysis in
the repository.

5. User Analysis

Overview

This report analyzes user behavior, focusing on repeat customers, session patterns, and
retention trends. The goal is to identify opportunities for increasing user engagement, improving
conversion rates, and maximizing revenue from returning customers.

For technical details and SQL code, please refer to document 5. User Analysis in the
repository.
Date Range & Data Source

●​ Date Range: January 1, 2014 – November 1, 2014

○​ This period was selected to analyze user behavior and repeat session trends
before the assignment deadline.
●​ Data Source: Maven Fuzzy Factory database
○​ The analysis is based on data from the website_sessions and orders tables,
tracking user visits, conversion rates, and revenue metrics.

Key Findings

1. User Sessions & Engagement

●​ 128,424 users had only one session, meaning most visitors interacted with the site just
once.
●​ Only a small percentage of users (14,391) returned for a second session, with even
fewer engaging beyond that.
●​ Opportunity: Increase user retention by improving engagement strategies, such as
personalized email marketing and remarketing campaigns.

2. Time Between Repeat Visits

●​ Minimum time between first and second session: 1 day

●​ Maximum time between first and second session: 69 days
●​ Average time for a repeat visit: 33 days
 ●​ Insight: The average revisit time is quite long, meaning users take over a month to
return. This suggests a need for proactive re-engagement strategies, such as retargeting
ads or loyalty incentives.

3. Traffic Sources: New vs. Repeat Visitors

●​ Paid Nonbrand Ads: Attracted the highest number of new visitors (119,950 sessions)
but had zero repeat sessions, indicating low retention.
●​ Organic Search & Direct Traffic: Drove more repeat visitors than paid channels,
showing stronger long-term engagement.
●​ Paid Social Ads: Generated some new users but failed to bring them back for future
sessions.
●​ Action Plan:
○​ Focus on organic and direct traffic sources to build a loyal user base.
○​ Improve retargeting and nurture campaigns for paid nonbrand users to
encourage repeat visits.

4. Conversion & Revenue: New vs. Repeat Users

●​ New users converted at a rate of 6.8%, while repeat users converted at 8.11%—a
noticeable improvement.
●​ Revenue per session was higher for repeat users ($5.16) compared to new users
($4.34).
●​ Key Takeaway:
○​ Returning visitors are more likely to convert and spend more per visit.
○​ Recommendation: Invest in customer retention efforts such as loyalty programs,
follow-up emails, and personalized offers to drive repeat visits.

Recommendations for Growth

✅ Retarget first-time visitors through email campaigns, push notifications, and remarketing
✅ Improve paid campaign strategies by increasing focus on organic and direct traffic
ads.​

✅ Reduce the revisit gap by offering incentives (discounts, rewards) within the first 30 days
sources, which show better long-term engagement.​

✅ Capitalize on repeat visitors by enhancing loyalty programs and personalized promotions,

of a user’s first visit.​

as they drive higher conversion rates and revenue.

By focusing on retention and engagement strategies, the business can increase

conversions, reduce acquisition costs, and maximize revenue per visitor.