Case Study #6 - CliqueBait.

In this post, we’ll tackle the second case study using MySQL from 8weeksqlchallenge.com. We’ll Explore the Pizza Runner case study with us, delving into the data to optimize operations and unveil customer insights using SQL analytics. Join the journey where ’80s Retro Styling and Pizza is the future drive the success of this innovative pizza delivery venture!
MySQL
SQL
Author

Chirag Sharma

Published

January 7, 2024

Case Study #6 - CliqueBait

Image1

Introduction

Clique Bait is not like your regular online seafood store - the founder and CEO Danny, was also a part of a digital data analytics team and wanted to expand his knowledge into the seafood industry!

In this case study - you are required to support Danny’s vision and analyse his dataset and come up with creative solutions to calculate funnel fallout rates for the Clique Bait online store.

Available Data

For this case study there is a total of 5 datasets which you will need to combine to solve all of the questions.

Users

Customers who visit the Clique Bait website are tagged via their cookie_id.

user_id cookie_id start_date
397 3759ff 2020-03-30 00:00:00
215 863329 2020-01-26 00:00:00
191 eefca9 2020-03-15 00:00:00
89 764796 2020-01-07 00:00:00
127 17ccc5 2020-01-22 00:00:00
81 b0b666 2020-03-01 00:00:00
260 a4f236 2020-01-08 00:00:00
203 d1182f 2020-04-18 00:00:00
23 12dbc8 2020-01-18 00:00:00
375 f61d69 2020-01-03 00:00:00

Events

Customer visits are logged in this events table at a cookie_id level and the event_type and page_id values can be used to join onto relevant satellite tables to obtain further information about each event.

The sequence_number is used to order the events within each visit.

visit_id cookie_id page_id event_type sequence_number event_time
719fd3 3d83d3 5 1 4 2020-03-02 00:29:09.975502
fb1eb1 c5ff25 5 2 8 2020-01-22 07:59:16.761931
23fe81 1e8c2d 10 1 9 2020-03-21 13:14:11.745667
ad91aa 648115 6 1 3 2020-04-27 16:28:09.824606
5576d7 ac418c 6 1 4 2020-01-18 04:55:10.149236
48308b c686c1 8 1 5 2020-01-29 06:10:38.702163
46b17d 78f9b3 7 1 12 2020-02-16 09:45:31.926407
9fd196 ccf057 4 1 5 2020-02-14 08:29:12.922164
edf853 f85454 1 1 1 2020-02-22 12:59:07.652207
3c6716 02e74f 3 2 5 2020-01-31 17:56:20.777383

Event Identifier

The event_identifier table shows the types of events which are captured by Clique Bait’s digital data systems.

event_type event_name
1 Page View
2 Add to Cart
3 Purchase
4 Ad Impression
5 Ad Click

Campaign Identifier

This table shows information for the 3 campaigns that Clique Bait has ran on their website so far in 2020.

campaign_id products campaign_name start_date end_date
1 1-3 BOGOF - Fishing For Compliments 2020-01-01 00:00:00 2020-01-14 00:00:00
2 4-5 25% Off - Living The Lux Life 2020-01-15 00:00:00 2020-01-28 00:00:00
3 6-8 Half Off - Treat Your Shellf(ish) 2020-02-01 00:00:00 2020-03-31 00:00:00

Page Hierarchy

This table lists all of the pages on the Clique Bait website which are tagged and have data passing through from user interaction events.

page_id page_name product_category product_id
1 Home Page null null
2 All Products null null
3 Salmon Fish 1
4 Kingfish Fish 2
5 Tuna Fish 3
6 Russian Caviar Luxury 4
7 Black Truffle Luxury 5
8 Abalone Shellfish 6
9 Lobster Shellfish 7
10 Crab Shellfish 8
11 Oyster Shellfish 9
12 Checkout null null
13 Confirmation null null

Interactive SQL Instance

The Dataset for this case study can be accessed from here. I will be using MySQL to solve this case study. In order to solve yourself this case study, simply go to the above link and choose MySQL Dialect (version > 8, if using MySQL version higher than 8 locally), copy & paste the Database schema into MySQL.

Here is the snapshot of it.

CREATE SCHEMA clique_bait;

CREATE TABLE clique_bait.event_identifier (
  `event_type` INTEGER,
  `event_name` VARCHAR(13)
);

INSERT INTO clique_bait.event_identifier
  (`event_type`, `event_name`)
VALUES
  ('1', 'Page View'),
  ('2', 'Add to Cart'),
  ('3', 'Purchase'),
  ('4', 'Ad Impression'),
  ('5', 'Ad Click');

CREATE TABLE clique_bait.campaign_identifier (
  `campaign_id` INTEGER,
  `products` VARCHAR(3),
  `campaign_name` VARCHAR(33),
  `start_date` TIMESTAMP,
  `end_date` TIMESTAMP
);

INSERT INTO clique_bait.campaign_identifier
  (`campaign_id`, `products`, `campaign_name`, `start_date`, `end_date`)
VALUES
  ('1', '1-3', 'BOGOF - Fishing For Compliments', '2020-01-01', '2020-01-14'),
  ('2', '4-5', '25% Off - Living The Lux Life', '2020-01-15', '2020-01-28'),
  ('3', '6-8', 'Half Off - Treat Your Shellf(ish)', '2020-02-01', '2020-03-31');

CREATE TABLE clique_bait.page_hierarchy (
  `page_id` INTEGER,
  `page_name` VARCHAR(14),
  `product_category` VARCHAR(9),
  `product_id` INTEGER
);

INSERT INTO clique_bait.page_hierarchy
  (`page_id`, `page_name`, `product_category`, `product_id`)
VALUES
  ('1', 'Home Page', null, null),
  ('2', 'All Products', null, null),
  ('3', 'Salmon', 'Fish', '1'),
  ('4', 'Kingfish', 'Fish', '2'),
  ('5', 'Tuna', 'Fish', '3'),
  ('6', 'Russian Caviar', 'Luxury', '4'),
  ('7', 'Black Truffle', 'Luxury', '5'),
  ('8', 'Abalone', 'Shellfish', '6'),
  ('9', 'Lobster', 'Shellfish', '7'),
  ('10', 'Crab', 'Shellfish', '8'),
  ('11', 'Oyster', 'Shellfish', '9'),
  ('12', 'Checkout', null, null),
  ('13', 'Confirmation', null, null);

CREATE TABLE clique_bait.users (
  `user_id` INTEGER,
  `cookie_id` VARCHAR(6),
  `start_date` TIMESTAMP
);

INSERT INTO clique_bait.users
  (`user_id`, `cookie_id`, `start_date`)
VALUES
  ('1', 'c4ca42', '2020-02-04'),
  ('2', 'c81e72', '2020-01-18'),
  ('3', 'eccbc8', '2020-02-21'),
  ('4', 'a87ff6', '2020-02-22'),
  ('5', 'e4da3b', '2020-02-01'),
  ('6', '167909', '2020-01-25'),
  ('7', '8f14e4', '2020-02-09'),
  ('8', 'c9f0f8', '2020-02-12'),
  ('9', '45c48c', '2020-02-07'),
  ('10', 'd3d944', '2020-01-23'),
  ('11', '6512bd', '2020-01-17'),
  ('12', 'c20ad4', '2020-02-06'),
  ('13', 'c51ce4', '2020-02-12'),
  ('14', 'aab323', '2020-01-12'),
  ('15', '9bf31c', '2020-01-28'),
  ('16', 'c74d97', '2020-01-06'),
  ('17', '70efdf', '2020-02-17'),
  ('18', '6f4922', '2020-02-29'),
  ('19', '1f0e3d', '2020-02-11'),
  ('20', '98f137', '2020-02-12'),
  ('21', '3c59dc', '2020-02-14'),
  ('22', 'b6d767', '2020-02-08'),
  ('23', '37693c', '2020-01-16')
  
CREATE TABLE clique_bait.events (
  `visit_id` VARCHAR(6),
  `cookie_id` VARCHAR(6),
  `page_id` INTEGER,
  `event_type` INTEGER,
  `sequence_number` INTEGER,
  `event_time` TIMESTAMP
);

INSERT INTO clique_bait.events
  (`visit_id`, `cookie_id`, `page_id`, `event_type`, `sequence_number`, `event_time`)
VALUES
  ('ccf365', 'c4ca42', '1', '1', '1', '2020-02-04 19:16:09.182546'),
  ('ccf365', 'c4ca42', '2', '1', '2', '2020-02-04 19:16:17.358191'),
  ('ccf365', 'c4ca42', '6', '1', '3', '2020-02-04 19:16:58.454669'),
  ('ccf365', 'c4ca42', '9', '1', '4', '2020-02-04 19:16:58.609142'),
  ('ccf365', 'c4ca42', '9', '2', '5', '2020-02-04 19:17:51.72942'),
  ('ccf365', 'c4ca42', '10', '1', '6', '2020-02-04 19:18:11.605815'),
  ('ccf365', 'c4ca42', '10', '2', '7', '2020-02-04 19:19:10.570786'),
  ('ccf365', 'c4ca42', '11', '1', '8', '2020-02-04 19:19:46.911728'),
  ('ccf365', 'c4ca42', '11', '2', '9', '2020-02-04 19:20:45.27469'),
  ('ccf365', 'c4ca42', '12', '1', '10', '2020-02-04 19:20:52.307244'),
  ('ccf365', 'c4ca42', '13', '3', '11', '2020-02-04 19:21:26.242563'),
  ('d58cbd', 'c81e72', '1', '1', '1', '2020-01-18 23:40:54.761906'),
  ('d58cbd', 'c81e72', '2', '1', '2', '2020-01-18 23:41:06.391027'),
  ('d58cbd', 'c81e72', '4', '1', '3', '2020-01-18 23:42:02.213001'),
  ('d58cbd', 'c81e72', '4', '2', '4', '2020-01-18 23:42:02.370046'),
  ('d58cbd', 'c81e72', '5', '1', '5', '2020-01-18 23:42:44.717024'),
  ('d58cbd', 'c81e72', '5', '2', '6', '2020-01-18 23:43:11.121855'),
  ('d58cbd', 'c81e72', '7', '1', '7', '2020-01-18 23:43:25.806239'),
  ('d58cbd', 'c81e72', '8', '1', '8', '2020-01-18 23:43:40.537995'),
  ('d58cbd', 'c81e72', '8', '2', '9', '2020-01-18 23:44:14.026393'),
  ('d58cbd', 'c81e72', '10', '1', '10', '2020-01-18 23:44:22.103768'),
  ('d58cbd', 'c81e72', '10', '2', '11', '2020-01-18 23:45:00.004781'),
  ('d58cbd', 'c81e72', '12', '1', '12', '2020-01-18 23:45:38.186554'),
  ('9a2f24', 'eccbc8', '1', '1', '1', '2020-02-21 03:19:10.032455'),
  ('9a2f24', 'eccbc8', '4', '1', '2', '2020-02-21 03:19:24.677901'),
  ('9a2f24', 'eccbc8', '4', '2', '3', '2020-02-21 03:19:48.146489'),
  ('9a2f24', 'eccbc8', '7', '1', '4', '2020-02-21 03:20:13.39183'),
  ('9a2f24', 'eccbc8', '7', '2', '5', '2020-02-21 03:20:13.869733'),
  ('9a2f24', 'eccbc8', '10', '1', '6', '2020-02-21 03:20:45.854556'),
  ('9a2f24', 'eccbc8', '11', '1', '7', '2020-02-21 03:21:20.335104'),
  ('9a2f24', 'eccbc8', '12', '1', '8', '2020-02-21 03:21:43.262109'),
  ('9a2f24', 'eccbc8', '13', '3', '9', '2020-02-21 03:22:22.501245'),
  ('7caba5', 'a87ff6', '1', '1', '1', '2020-02-22 17:49:37.646174'),
  ('7caba5', 'a87ff6', '4', '1', '2', '2020-02-22 17:50:23.736729'),
  ('7caba5', 'a87ff6', '5', '1', '3', '2020-02-22 17:50:26.878153')

Case Study Questions

1. Enterprise Relationship Diagram

Using the following DDL schema details to create an ERD for all the Clique Bait datasets. Click_Here to access the DB Diagram tool to create the ERD.

Image1

2. Digital Analysis

Using the available datasets - answer the following questions using a single query for each one:

  1. How many users are there?
  2. How many cookies does each user have on average?
  3. What is the unique number of visits by all users per month?
  4. What is the number of events for each event type?
  5. What is the percentage of visits which have a purchase event?
  6. What is the percentage of visits which view the checkout page but do not have a purchase event?
  7. What are the top 3 pages by number of views?
  8. What is the number of views and cart adds for each product category?
  9. What are the top 3 products by purchases?

3. Product Funnel Analysis

Using a single SQL query - create a new output table which has the following details:

  • How many times was each product viewed?
  • How many times was each product added to cart?
  • How many times was each product added to a cart but not purchased (abandoned)?
  • How many times was each product purchased?

Additionally, create another table which further aggregates the data for the above points but this time for each product category instead of individual products.

Use your 2 new output tables - answer the following questions:

  1. Which product had the most views, cart adds and purchases?
  2. Which product was most likely to be abandoned?
  3. Which product had the highest view to purchase percentage?
  4. What is the average conversion rate from view to cart add?
  5. What is the average conversion rate from cart add to purchase?

4. Campaigns Analysis

Generate a table that has 1 single row for every unique visit_id record and has the following columns:

  • user_id
  • visit_id
  • visit_start_time: the earliest event_time for each visit
  • page_views: count of page views for each visit
  • cart_adds: count of product cart add events for each visit
  • purchase: 1/0 flag if a purchase event exists for each visit
  • campaign_name: map the visit to a campaign if the visit_start_time falls between the start_date and end_date
  • impression: count of ad impressions for each visit
  • click: count of ad clicks for each visit
  • (Optional column) cart_products: a comma separated text value with products added to the cart sorted by the order they were added to the cart (hint: use the sequence_number)

Use the subsequent dataset to generate at least 5 insights for the Clique Bait team - bonus: prepare a single A4 infographic that the team can use for their management reporting sessions, be sure to emphasise the most important points from your findings.

Some ideas you might want to investigate further include:

  • Identifying users who have received impressions during each campaign period and comparing each metric with other users who did not have an impression event
  • Does clicking on an impression lead to higher purchase rates?
  • What is the uplift in purchase rate when comparing users who click on a campaign impression versus users who do not receive an impression? What if we compare them with users who just an impression but do not click?
  • What metrics can you use to quantify the success or failure of each campaign compared to each other?

2. Digital Analysis

1. How many users are there?

SELECT
    COUNT(DISTINCT user_id) AS number_of_unique_users
FROM users;

Output:

number_of_unique_users
500

Analysis of Number of Unique Users

  1. Insight:

    • The dataset contains information about 500 unique users who have visited the Clique Bait website.

2. How many cookies does each user have on average?

WITH cookie AS (
SELECT user_id,
        COUNT(cookie_id) AS cookie_count
FROM users GROUP BY user_id
)
SELECT ROUND(AVG(cookie_count),0) AS average_cookie_per_user
FROM cookie;

Output:

average_cookie_per_user
4

Analysis of Average Number of Cookies per User

  1. Insight:

    • On average, each user has approximately 4 cookies associated with their user account.

3. What is the unique number of visits by all users per month?

SELECT
    MONTH(event_time) AS 'month',
    COUNT(DISTINCT visit_id) AS customer_count
FROM events
GROUP BY MONTH(event_time);

Output:

month customer_count
1 876
2 1488
3 916
4 248
5 36

Analysis of Unique Number of Visits per Month

  1. Insights:

    • Seasonal Trends: The data shows fluctuations in customer visits across different months, indicating potential seasonal patterns or shifts in user behavior.
    • Peak Months: February witnessed the highest number of unique visits, suggesting increased activity or interest during that period.
    • Monthly Variability: There is variability in customer engagement across months, with some months experiencing higher or lower visit counts compared to others.

4. What is the number of events for each event type?

SELECT EI.event_name,
    COUNT(E.event_time) AS number_of_events
FROM events AS E
JOIN event_identifier AS EI ON E.event_type = EI.event_type
GROUP BY EI.event_name
ORDER BY number_of_events DESC;

Output:

event_name number_of_events
Page View 20928
Add to Cart 8451
Purchase 1777
Ad Impression 876
Ad Click 702

Analysis of Number of Events by Event Type

  1. Insights:

    • Event Distribution: Page views constitute the majority of events, indicating high user engagement with various pages on the website.
    • Conversion Actions: While page views are common, fewer users proceed to add items to their cart or make purchases, as evidenced by the lower counts for “Add to Cart” and “Purchase” events.
    • Marketing Engagement: The number of ad impressions and ad clicks suggests user interaction with advertising content, which can provide insights into the effectiveness of marketing campaigns.

5. What is the percentage of visits which have a purchase event?

SELECT
    ROUND(100.0 * COUNT(DISTINCT E.visit_id) / (SELECT COUNT(DISTINCT visit_id) FROM events),2)
    AS vists_percentage
FROM events AS E
JOIN event_identifier AS EI ON E.event_type = EI.event_type
WHERE EI.event_name = 'Purchase';

Output:

visits_percentage
49.86

Analysis of Percentage of Visits with Purchase Events

  1. Insights:

    • Purchase Rate: Approximately 49.86% of visits result in a purchase event, indicating a moderate conversion rate.
    • Conversion Performance: Understanding the proportion of visits that lead to purchases provides insights into the effectiveness of the website in driving sales.
    • Potential Growth Opportunities: Identifying areas for improvement in the conversion funnel to increase the purchase rate and enhance overall revenue generation.

6. What is the percentage of visits which view the checkout page but do not have a purchase event?

WITH view_purchase AS (
SELECT
    COUNT(E.visit_id) AS visit_count
FROM events AS E JOIN event_identifier AS EI ON E.event_type = EI.event_type
JOIN page_hierarchy AS PH ON E.page_id = PH.page_id
WHERE PH.page_name = 'Checkout' and EI.event_name = 'Page View')
SELECT
    ROUND(100 - (100.0 * COUNT(DISTINCT E.visit_id) /
        (SELECT visit_count FROM view_purchase)),2) AS pct_of_checkout_visits_not_purchased
FROM events AS E JOIN event_identifier AS EI ON E.event_type = EI.event_type
WHERE EI.event_name = 'Purchase';

Output:

pct_of_checkout_visits_not_purchased
15.50

Analysis of Percentage of Visits Viewing Checkout Page but Not Making a Purchase

  1. Insights:

    • Checkout Abandonment: Approximately 15.50% of visits proceed to the checkout page but do not culminate in a purchase, indicating a significant dropout rate.
    • Potential Revenue Loss: Identifying and addressing factors contributing to checkout abandonment is crucial to mitigate potential revenue loss and maximize conversion rates.
    • User Experience Evaluation: Analyzing the user experience at the checkout stage, including ease of navigation, payment options, and shipping information, can provide insights into areas for improvement.

7. What are the top 3 pages by number of views?

WITH top_3_pages AS
(SELECT
    page_id, COUNT(DISTINCT visit_id) AS number_of_views
FROM events
WHERE event_type = '1'
GROUP BY page_id
ORDER BY number_of_views DESC
LIMIT 3
)
SELECT
    page_name, number_of_views
FROM top_3_pages
JOIN page_hierarchy ON top_3_pages.page_id = page_hierarchy.page_id;

Output:

page_name number_of_views
Home Page 1782
All Products 3174
Checkout 2103

Analysis of Top 3 Pages by Number of Views

  1. Insights:

    • User Engagement: The Home Page, All Products, and Checkout pages are pivotal in user navigation, as evidenced by their high view counts.
    • Browsing Behavior: Users frequently visit the All Products page, indicating a strong interest in exploring available products or services.
    • Checkout Process: The significant number of views on the Checkout page underscores its importance in the conversion journey, suggesting a substantial portion of users progress towards completing transactions.

8. What is the number of views and cart adds for each product category?

SELECT PH.product_category,
       SUM(CASE WHEN E.event_type = '1' THEN 1 ELSE 0 END) AS number_of_views,
       SUM(CASE WHEN E.event_type = '2' THEN 1 ELSE 0 END) AS number_of_cart_adds
FROM events as E
JOIN page_hierarchy AS PH ON E.page_id = PH.page_id
WHERE PH.product_category IS NOT NULL
GROUP BY PH.product_category
ORDER BY PH.product_category;

Output:

product_category number_of_views number_of_cart_adds
Fish 4633 2789
Luxury 3032 1870
Shellfish 6204 3792

Analysis of Product Category Views and Cart Adds

  1. Insights:

    • Popular Categories: Shellfish has the highest number of views and cart additions, followed by Fish and Luxury categories.
    • Engagement Discrepancy: Despite Fish having fewer views compared to Shellfish, it has a relatively higher cart addition rate, indicating stronger user intent or interest in purchasing Fish products.
    • Conversion Opportunities: Analyzing user behavior within each category can help identify opportunities to optimize product pages, pricing strategies, or promotional efforts to drive conversions.

9. What are the top 3 products by purchases?

SELECT
    PH.product_id,
    PH.page_name,
    PH.product_category,
    COUNT(PH.product_id) AS product_count
FROM events AS E
JOIN event_identifier AS EI ON E.event_type = EI.event_type
JOIN page_hierarchy AS PH ON PH.page_id = E.page_id
WHERE EI.event_name = 'Add to Cart' AND E.visit_id IN
    (SELECT E.visit_id FROM events as E
     JOIN event_identifier AS EI ON E.event_type = EI.event_type WHERE EI.event_name = 'Purchase')
GROUP BY PH.product_id, PH.page_name, PH.product_category
ORDER BY product_count DESC
LIMIT 3;

Output:

product_id product_name product_category product_count
7 Lobster Shellfish 754
9 Oyster Shellfish 726
8 Crab Shellfish 719

Analysis of Top 3 Products by Purchases

  1. Insights:

    • Shellfish Dominance: All top 3 products belong to the Shellfish category, indicating its popularity among customers.
    • High Demand Items: Lobster, Oyster, and Crab are evidently high-demand items within the Shellfish category, likely due to factors such as taste, availability, and pricing.
    • Cross-Promotion Opportunities: Identifying complementary products or bundle offers with these top-selling items can help increase average order value and enhance customer satisfaction.

3. Product Funnel Analysis

Using a single SQL query - create a new output table which has the following details:

  • How many times was each product viewed?
  • How many times was each product added to cart?
  • How many times was each product added to a cart but not purchased (abandoned)?
  • How many times was each product purchased?

Additionally, create another table which further aggregates the data for the above points but this time for each product category instead of individual products.

PART 1

Creating a Temporary table view_add_to_cart

CREATE TEMPORARY TABLE view_add_to_cart AS
SELECT
    PH.product_id,
    PH.page_name AS product_name,
    PH.product_category,
    SUM(CASE WHEN EI.event_name = 'Page View' THEN 1 ELSE 0 END) AS view_counts,
    SUM(CASE WHEN EI.event_name = 'Add to Cart' THEN 1 ELSE 0 END) AS add_to_cart_counts
FROM
    events AS E
    JOIN event_identifier AS EI ON E.event_type = EI.event_type
    JOIN page_hierarchy AS PH ON E.page_id = PH.page_id
WHERE
    PH.product_category IS NOT NULL
GROUP BY
    PH.product_id, PH.page_name, PH.product_category;

Creating a Temporary table products_abandoned

CREATE TEMPORARY TABLE products_abandoned AS
SELECT
    PH.product_id,
    PH.page_name AS product_name,
    PH.product_category,
    COUNT(*) AS abandoned
FROM
    events AS E
    JOIN event_identifier AS EI ON E.event_type = EI.event_type
    JOIN page_hierarchy AS PH ON E.page_id = PH.page_id
WHERE
    EI.event_name = 'Add to Cart'
    AND E.visit_id NOT IN (
        SELECT E.visit_id
        FROM events AS E
        JOIN event_identifier AS EI ON E.event_type = EI.event_type
        WHERE EI.event_name = 'Purchase'
    )
GROUP BY
    PH.product_id, PH.page_name, PH.product_category;

Creating a Temporary table products_purchased

CREATE TEMPORARY TABLE products_purchased AS
SELECT
    PH.product_id,
    PH.page_name AS product_name,
    PH.product_category,
    COUNT(*) AS purchased
FROM events AS E
JOIN event_identifier AS EI ON E.event_type = EI.event_type
JOIN page_hierarchy AS PH ON E.page_id = PH.page_id
WHERE EI.event_name = 'Add to Cart' AND E.visit_id IN (
        SELECT E.visit_id
        FROM events AS E
        JOIN event_identifier AS EI ON E.event_type = EI.event_type
        WHERE EI.event_name = 'Purchase')
GROUP BY
PH.product_id, PH.page_name, PH.product_category;

Creating a Temporary table product_information that combines all the above tables created above.

CREATE TEMPORARY TABLE product_information AS
SELECT
    VATC.*,
    AB.abandoned,
    PP.purchased
FROM
view_add_to_cart AS VATC
JOIN products_abandoned AS AB ON VATC.product_id = AB.product_id
JOIN products_purchased AS PP ON VATC.product_id = PP.product_id;

Dropping the created temporary tables, since they are not required anymore.

DROP TEMPORARY TABLE IF EXISTS view_add_to_cart, products_abandoned, products_purchased;

Displaying the Final resulting table product_information records..

SELECT * FROM product_information
ORDER BY product_id;

Output:

product_id product_name product_category view_counts add_to_cart_counts abandoned purchased
1 Salmon Fish 1559 938 227 711
2 Kingfish Fish 1559 920 213 707
3 Tuna Fish 1515 931 234 697
4 Russian Caviar Luxury 1563 946 249 697
5 Black Truffle Luxury 1469 924 217 707
6 Abalone Shellfish 1525 932 233 699
7 Lobster Shellfish 1547 968 214 754
8 Crab Shellfish 1564 949 230 719
9 Oyster Shellfish 1568 943 217 726

PART 2

Creating a Temporary table category_view_add_to_cart

CREATE TEMPORARY TABLE category_view_add_to_cart AS
SELECT
    PH.product_category,
    SUM(CASE WHEN EI.event_name = 'Page View' THEN 1 ELSE 0 END) AS view_counts,
    SUM(CASE WHEN EI.event_name = 'Add to Cart' THEN 1 ELSE 0 END) AS add_to_cart_counts
FROM events AS E
    JOIN event_identifier AS EI ON E.event_type = EI.event_type
    JOIN page_hierarchy AS PH ON E.page_id = PH.page_id
WHERE PH.product_category IS NOT NULL
GROUP BY PH.product_category;

Creating a Temporary table category_products_abandoned

CREATE TEMPORARY TABLE category_products_abandoned AS
SELECT
    PH.product_category,
    COUNT(*) AS abandoned
    FROM events AS E
JOIN event_identifier AS EI ON E.event_type = EI.event_type
JOIN page_hierarchy AS PH ON E.page_id = PH.page_id
WHERE EI.event_name = 'Add to Cart' AND E.visit_id NOT IN (
        SELECT E.visit_id
        FROM events AS E
        JOIN event_identifier AS EI ON E.event_type = EI.event_type
        WHERE EI.event_name = 'Purchase')
GROUP BY PH.product_category;

Creating a Temporary table category_products_purchased

CREATE TEMPORARY TABLE category_products_purchased AS
SELECT
    PH.product_category,
    COUNT(*) AS purchased
FROM events AS E
JOIN event_identifier AS EI ON E.event_type = EI.event_type
JOIN page_hierarchy AS PH ON E.page_id = PH.page_id
WHERE EI.event_name = 'Add to Cart'
AND E.visit_id IN (SELECT E.visit_id
    FROM events AS E
    JOIN event_identifier AS EI ON E.event_type = EI.event_type
    WHERE EI.event_name = 'Purchase')
GROUP BY PH.product_category;

Creating a Temporary table category_product_information that combines all the above tables created above.

CREATE TEMPORARY TABLE category_product_information AS
SELECT
    VATC.*, AB.abandoned, PP.purchased
FROM category_view_add_to_cart AS VATC
JOIN category_products_abandoned AS AB ON VATC.product_category = AB.product_category
JOIN category_products_purchased AS PP ON VATC.product_category = PP.product_category;

Drop the temporary tables, since they are not needed anymore

DROP TEMPORARY TABLE IF EXISTS category_view_add_to_cart, category_products_abandoned, category_products_purchased;

Displaying the final resulting category_product_information table records

SELECT *
FROM category_product_information
ORDER BY product_category;

Output:

product_category view_counts add_to_cart_counts abandoned purchased
Luxury 3032 1870 466 1404
Fish 4633 2789 674 2115
Shellfish 6204 3792 894 2898

1. Which product had the most views, cart adds and purchases?

SELECT *
FROM product_information
ORDER BY view_counts DESC
LIMIT 1;

Output:

product_id product_name product_category view_counts add_to_cart_counts abandoned purchased
9 Oyster Shellfish 1568 943 217 726 
SELECT *
FROM product_information
ORDER BY add_to_cart_counts DESC
LIMIT 1;

Output:

product_id product_name product_category view_counts add_to_cart_counts abandoned purchased
7 Lobster Shellfish 1547 
SELECT *
FROM product_information
ORDER BY purchased DESC
LIMIT 1;

Output:

product_id product_name product_category view_counts add_to_cart_counts abandoned purchased
7 Lobster Shellfish 1547 968 214 754

Analysis of Product Performance Summary

  1. Insights:

    • Shellfish Dominance: Both the product with the most views and the product with the most cart adds and purchases belong to the Shellfish category, indicating its popularity among customers.
    • Lobster Dominance: The product with the most cart adds and purchases is Lobster, suggesting that it is not only popular but also highly sought-after for purchase among customers.
    • Oyster Engagement: Although Oyster has the most views, it has a relatively lower number of cart adds and purchases compared to Lobster, indicating potential areas for improvement in conversion rate or marketing strategies.

2. Which product was most likely to be abandoned?

SELECT * FROM product_information
ORDER BY abandoned DESC
LIMIT 1;

Output:

product_id product_name product_category view_counts add_to_cart_counts abandoned purchased
4 Russian Caviar Luxury 1563 946 249 697

Analysis of Product Most Likely to Be Abandoned

  1. Insights:

    • Luxury Product: Russian Caviar falls under the Luxury category, which may imply a higher price point or more exclusive nature compared to other products.
    • High Abandonment Rate: The relatively high number of abandonments suggests that customers might have shown interest in the product but ultimately decided not to proceed with the purchase.
    • Potential Improvements: Analyzing the reasons behind abandonment, such as pricing concerns, shipping costs, or checkout process issues, could provide insights into areas for improvement to reduce abandonment rates and increase conversions. Additionally, targeted marketing or promotional strategies could be employed to encourage customers to complete their purchase of Russian Caviar.

3. Which product had the highest view to purchase percentage?

SELECT product_name,
    ROUND(100.0 * (purchased/view_counts),2) AS purchase_to_view_pct
FROM product_information
ORDER BY purchase_to_view_pct DESC
LIMIT 1;

Output:

product_name purchase_to_view_pct
Lobster 48.74

Analysis of Product with Highest View-to-Purchase Percentage

  1. Insights:

    • High Conversion Rate: The high purchase-to-view percentage indicates that a significant portion of customers who viewed the Lobster product ultimately made a purchase.
    • Appealing Product: Lobster seems to be particularly appealing to customers, leading to a relatively high conversion rate compared to other products.
    • Market Demand: The high conversion rate may suggest strong market demand for Lobster, potentially due to factors such as its taste, quality, or perceived value.

4. What is the average conversion rate from view to cart add?

SELECT
    ROUND(AVG(100.0 * (add_to_cart_counts/view_counts)),2) AS avg_conversion_rate
FROM product_information;

Output:

avg_conversion_rate
60.95

Analysis of Average Conversion Rate from View to Cart Add

  1. Insights:

    • Conversion Funnel Efficiency: The high average conversion rate indicates that a significant proportion of customers who view products proceed to add them to their cart.

5. What is the average conversion rate from cart add to purchase?

SELECT
    ROUND(AVG(100.0 * (purchased/add_to_cart_counts)),2) AS avg_conversion_rate
FROM product_information;

Output:

avg_conversion_rate
75.93

Analysis of Average Conversion Rate from Cart Add to Purchase

  1. Insights:

    • Conversion Funnel Efficiency: This high average conversion rate indicates that a significant proportion of customers who add products to their cart ultimately proceed to make a purchase.

Generate a table that has 1 single row for every unique visit_id record and has the following columns:

  • user_id
  • visit_id
  • visit_start_time: the earliest event_time for each visit
  • page_views: count of page views for each visit
  • cart_adds: count of product cart add events for each visit
  • purchase: 1/0 flag if a purchase event exists for each visit
  • campaign_name: map the visit to a campaign if the visit_start_time falls between the start_date and end_date
  • impression: count of ad impressions for each visit
  • click: count of ad clicks for each visit

(Optional column) cart_products: a comma separated text value with products added to the cart sorted by the order they were added to the cart (hint: use the sequence_number).

SELECT
    U.user_id, E.visit_id, MIN(E.event_time) AS visit_start_date, C.campaign_name,
    SUM(CASE WHEN EI.event_name = 'Page View' THEN 1 ELSE 0 END) AS page_view_counts,
    SUM(CASE WHEN EI.event_name = 'Add to Cart' THEN 1 ELSE 0 END) AS add_to_cart_counts,
    SUM(CASE WHEN EI.event_name = 'Purchase' THEN 1 ELSE 0 END) AS purchased_counts,
    SUM(CASE WHEN EI.event_name = 'Ad Impression' THEN 1 ELSE 0 END) AS impression_counts,
    SUM(CASE WHEN EI.event_name = 'Ad Click' THEN 1 ELSE 0 END) AS click_counts
FROM users AS U
JOIN events AS E ON U.cookie_id = E.cookie_id
JOIN event_identifier AS EI ON E.event_type = EI.event_type
JOIN campaign_identifier AS C ON E.event_time BETWEEN C.start_date AND C.end_date
GROUP BY U.user_id, E.visit_id, C.campaign_name;

Output:

First 20 records

user_id visit_id visit_start_date campaign_name page_view_counts add_to_cart_counts purchased impression_counts click_counts
1 ccf365 2020-02-04 19:16:09 Half Off - Treat Your Shellf(ish) 7 3 1 0 0
2 d58cbd 2020-01-18 23:40:55 25% Off - Living The Lux Life 8 4 0 0 0
3 9a2f24 2020-02-21 03:19:10 Half Off - Treat Your Shellf(ish) 6 2 1 0 0
4 7caba5 2020-02-22 17:49:38 Half Off - Treat Your Shellf(ish) 5 2 0 0 0
5 f61ed7 2020-02-01 06:30:40 Half Off - Treat Your Shellf(ish) 8 2 1 0 0
6 e0ce49 2020-01-25 22:43:21 25% Off - Living The Lux Life 9 3 1 0 0
7 8479c1 2020-02-09 17:27:59 Half Off - Treat Your Shellf(ish) 5 1 1 0 0
8 a6c424 2020-02-12 11:23:55 Half Off - Treat Your Shellf(ish) 7 2 0 0 0
9 5ef346 2020-02-07 17:32:45 Half Off - Treat Your Shellf(ish) 7 0 0 0 0
10 d39d35 2020-01-23 21:47:04 25% Off - Living The Lux Life 7 3 1 0 0
11 9c2633 2020-01-17 04:59:43 25% Off - Living The Lux Life 8 2 0 0 0
12 d69e73 2020-02-06 09:09:06 Half Off - Treat Your Shellf(ish) 5 1 0 0 0
13 c70085 2020-02-12 08:26:14 Half Off - Treat Your Shellf(ish) 6 1 0 0 0
14 6a20a3 2020-01-12 02:49:32 BOGOF - Fishing For Compliments 8 1 0 0 0
16 69440b 2020-01-06 21:45:51 BOGOF - Fishing For Compliments 4 1 1 0 0
17 e70fd5 2020-02-17 10:05:51 Half Off - Treat Your Shellf(ish) 7 2 0 0 0
18 48810d 2020-02-29 15:26:41 Half Off - Treat Your Shellf(ish) 4 0 0 0 0
19 fdf383 2020-02-11 13:52:24 Half Off - Treat Your Shellf(ish) 7 1 1 0 0
20 378a75 2020-02-12 23:33:51 Half Off - Treat Your Shellf(ish) 4 0 0 0 0