Query for funnel data
WITH funnel_data AS (
SELECT
event_date,
user_pseudo_id,
event_name
FROM
df
WHERE
event_name IN ('session_start', 'view_item', 'add_to_cart',
'begin_checkout', 'purchase')
),
stage_counts AS (
SELECT
event_date,
event_name,
COUNT(DISTINCT user_pseudo_id) AS sess_count
FROM
funnel_data
GROUP BY
event_date,
event_name
)
SELECT
event_date,
SUM(CASE WHEN event_name = 'session_start' THEN sess_count ELSE 0 END)
AS homepage_views,
SUM(CASE WHEN event_name = 'view_item' THEN sess_count ELSE 0 END) AS
product_views,
SUM(CASE WHEN event_name = 'add_to_cart' THEN sess_count ELSE 0 END) AS
adds_to_cart,
SUM(CASE WHEN event_name = 'begin_checkout' THEN sess_count ELSE 0 END)
AS checkouts_started,
SUM(CASE WHEN event_name = 'purchase' THEN sess_count ELSE 0 END) AS
purchases
FROM
stage_counts
GROUP BY
event_date
ORDER BY
event_date;
All the values in the below table are # sessions. For ex: Product views column specifies number
of sessions with at least one product view.
Session with Cart to
Total Product Adds Checkouts Product view Product to Checkout Checkout to
Event Date Sessions Views to Cart Started Purchases (%) Cart (%) (%) Purchase (%)
2023-05-30 309 177 19 12 5 57% 11% 63% 42%
2023-05-31 490 249 29 22 8 51% 12% 76% 36%
2023-06-01 468 237 30 21 4 51% 13% 70% 19%
2023-06-02 430 241 26 23 5 56% 11% 88% 22%
2023-06-03 390 237 32 28 7 61% 14% 88% 25%
2023-06-04 420 238 26 23 9 57% 11% 88% 39%
2023-06-05 488 266 23 14 4 55% 9% 61% 29%
2023-06-06 451 227 30 19 6 50% 13% 63% 32%
2023-06-07 388 197 15 12 3 51% 8% 80% 25%
2023-06-08 333 219 17 14 5 66% 8% 82% 36%
2023-06-09 260 216 19 11 5 83% 9% 58% 45%
2023-06-10 255 227 22 17 9 89% 10% 77% 53%
2023-06-11 289 255 30 21 6 88% 12% 70% 29%
2023-06-12 303 256 20 15 9 84% 8% 75% 60%
**2023-06-
13 103 77 13 9 1 75% 17% 69% 11%
** 13th June data is incomplete, it is only available till 07:58 am
Funnel Data Analysis and Insights
Based on the provided funnel data, we can identify several key insights at each stage of the
funnel and potential reasons for drop-offs.
1. Homepage Views to Product Views
Observation:
● The conversion rate from homepage views to product views varies between ~50% to
~89%.
● Highest conversion: 2023-06-10 (89%), Lowest conversion: 2023-06-06 (50%).
Insight:
● The proportion of sessions via direct traffic steadily increased from 15% on 5th June to
33% on 12th June.
Possible Reasons:
● This increase is correlated with an increase in % session with at least one product view,
indicating that direct traffic is more engaged as compared to other mediums
2. Product Views to Adds to Cart
Observation:
● The progression from product views to add to cart is ranging from ~8% to ~13%.
● Highest conversion: 2023-06-06 (13%), Lowest conversion: 2023-06-07 (8%).
Insight:
● A significant number of users who view products do not add them to the cart, suggesting
issues at the product page level.
Possible Reasons:
● Lack of detailed product information, poor product images, or unclear pricing.
● Product descriptions may not be compelling enough.
● Lack of trust signals such as reviews, ratings, or testimonials.
3. Adds to Cart to Checkouts Started
Observation:
● The conversion rate from add to cart to checkouts started is relatively higher, ranging
from ~57% to ~88%.
● Highest conversion: 2023-06-04 (88%), Lowest conversion: 2023-06-09 (58%).
Insight:
● Users who add items to the cart are relatively more likely to start the checkout process
Possible Reasons:
● Complicated or lengthy checkout process.
● Lack of payment options or concerns about payment security.
4. Checkouts Started to Purchases
Observation:
● The conversion rate from checkout to purchases varies, ranging from ~19% to ~60%.
● Highest conversion: 2023-06-04 (60%), Lowest conversion: 2023-06-01 (19%).
Insight:
● There is a significant drop-off during the checkout process, which indicates potential
issues with the checkout experience.
Possible Reasons:
● Technical issues or errors during the checkout process.
● Complicated checkout forms requiring too much information.
● Lack of confidence in the security of the transaction.
