Bianca Racine - Product & E-Commerce Manager

Case Study

Turning data into a fix - three brands, three fixes.

Serge Lutens · Shiseido SQL BigQuery Google Sheets

The problem

Overall sales looked healthy, but leadership suspected the holiday Gift Sets category was quietly losing mobile shoppers. Shopify's dashboard shows revenue by product, not by step of the buying journey - so there was no way to confirm it, let alone see where people were dropping off.

The data

I pulled six weeks of session data into a spreadsheet, broken down by device and journey step: how many people landed, added to cart, reached checkout, and completed their purchase.

D4 fx =ROUND(D4_checkouts / C4_carts * 100, 0)

	A	B	C	D	E
1	Segment	Sessions	Added to cart	Reached checkout	Completed purchase
2	Gift Sets · Mobile	4,180	1,940	34%	19%
3	Gift Sets · Desktop	2,760	1,510	63%	41%
4	Gift Sets · Mobile (after fix)	3,920	2,050	56%	33%

Mobile - reached checkout

34%

Desktop - reached checkout

63%

Mobile, after fix

56%

Before After fix

View the SQL behind this

WITH funnel AS (
  SELECT
    device_type,
    COUNT(DISTINCT CASE WHEN event_type = 'session_start' THEN session_id END) AS sessions,
    COUNT(DISTINCT CASE WHEN event_type = 'add_to_cart' THEN session_id END) AS add_to_cart,
    COUNT(DISTINCT CASE WHEN event_type = 'checkout_start' THEN session_id END) AS checkout_start,
    COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN session_id END) AS purchases
  FROM shopify_events
  WHERE product_category = 'Gift Sets'
    AND event_date BETWEEN '2025-11-01' AND '2025-12-24'
  GROUP BY 1
)
SELECT
  device_type, sessions,
  ROUND(100.0 * checkout_start / NULLIF(add_to_cart,0), 0) AS pct_reach_checkout,
  ROUND(100.0 * purchases / NULLIF(checkout_start,0), 0) AS pct_complete_purchase
FROM funnel
ORDER BY pct_reach_checkout ASC; -- this is what powers the spreadsheet above

The insight

Row 2 tells the story: on mobile, only 34% of shoppers who added a Gift Set to their cart made it to checkout, versus 63% on desktop. A quick look at real sessions showed why - shoppers couldn't see what was inside a bundle without leaving the cart, so most gave up rather than dig for detail on a small screen.

The decision & outcome

I wrote the user story and acceptance criteria for an in-cart bundle preview on mobile, prioritised it in the holiday roadmap, and briefed design and dev. We shipped it as an A/B test two weeks before Christmas - row 4 shows the result. The pattern was strong enough that we rolled it out to Bon Parfumeur and One Step right after.

+22 pts Mobile checkout reach, Gift Sets category

2 Sibling brands it was rolled out to

2 wks From spreadsheet to shipped A/B test

Bon Parfumeur SQL BigQuery Klaviyo

The problem

Bon Parfumeur's model runs on $19 Discovery Sets that introduce people to custom scent combinations - the real value comes when they return to buy a full-size bottle. Nobody could tell whether the post-purchase email flow was actually driving that return visit, or just landing in inboxes unread.

The data

I joined order history with our Klaviyo flow data in a spreadsheet, segmented by how each customer first found us, to see who opened the follow-up email, who clicked through, and who actually came back to buy full-size within 60 days.

E4 fx =ROUND(E4_repurchased / B4_buyers * 100, 0)

	A	B	C	D	E
1	Segment	Discovery buyers	Opened email	Clicked to shop	Repurchased (60d)
2	Paid Social · New	2,340	41%	9%	6%
3	Organic / Referral · New	1,120	68%	22%	19%
4	Paid Social · New (after fix)	2,480	41%	9%	13%

Paid Social - repurchased

Organic - repurchased

19%

Paid Social, after fix

13%

Before After fix

View the SQL behind this

WITH cohort AS (
  SELECT
    acquisition_channel,
    COUNT(DISTINCT CASE WHEN order_type = 'discovery_set' THEN customer_id END) AS discovery_buyers,
    COUNT(DISTINCT CASE WHEN email_event = 'opened' AND flow_name = 'discovery_followup' THEN customer_id END) AS opened_email,
    COUNT(DISTINCT CASE WHEN email_event = 'clicked' AND flow_name = 'discovery_followup' THEN customer_id END) AS clicked_email,
    COUNT(DISTINCT CASE WHEN order_type = 'full_size' AND days_since_discovery <= 60 THEN customer_id END) AS repurchased
  FROM customer_journey
  WHERE first_order_date BETWEEN '2025-07-01' AND '2025-09-30'
  GROUP BY 1
)
SELECT
  acquisition_channel, discovery_buyers,
  ROUND(100.0 * opened_email / NULLIF(discovery_buyers,0), 0) AS pct_opened,
  ROUND(100.0 * clicked_email / NULLIF(discovery_buyers,0), 0) AS pct_clicked,
  ROUND(100.0 * repurchased / NULLIF(discovery_buyers,0), 0) AS pct_repurchased
FROM cohort
ORDER BY pct_repurchased ASC; -- this is what powers the spreadsheet above

The insight

Paid-social customers opened the follow-up email at the same rate as organic ones - the problem wasn't the email, it was the timing. It went out 10 days after delivery, by which point interest in the scent had cooled. Organic and referral customers already trusted the brand enough to come back anyway; paid-social customers needed a nudge while the scent was still fresh.

The decision & outcome

I rebuilt the flow to trigger 3 days after delivery instead of 10, personalised to the exact scent combination the customer bought. Shipped as an A/B test to the paid-social segment - row 4 shows the result.

+7 pts Repurchase rate, Paid Social segment

3 days New email trigger, down from 10

4 wks From spreadsheet to shipped flow

One Step · IKKS SQL BigQuery Google Sheets

The problem

One Step's return rate was sitting above the category benchmark, but nobody could say whether it was quality, fit, or something else - the return report only showed totals, not a breakdown by product category or reason.

The data

I pulled three months of orders and returns into a spreadsheet, split by category and return reason, to see exactly where the rate was highest and why.

C2 fx =ROUND(C2_returned / B2_orders * 100, 0)

	A	B	C	D	E
1	Category	Orders	Returned	Reason: size	Reason: other
2	Kids Outerwear	1,860	27%	19%	8%
3	Tops	2,940	11%	5%	6%
4	Kids Outerwear (after fix)	1,640	16%	8%	8%

Kids Outerwear - return rate

27%

Tops - return rate

11%

Kids Outerwear, after fix

16%

Before After fix

View the SQL behind this

WITH returns AS (
  SELECT
    o.product_category,
    COUNT(DISTINCT o.order_id) AS orders,
    COUNT(DISTINCT CASE WHEN r.order_id IS NOT NULL THEN o.order_id END) AS returned,
    COUNT(DISTINCT CASE WHEN r.return_reason = 'size' THEN o.order_id END) AS returned_size
  FROM orders o
  LEFT JOIN returns r USING (order_id)
  WHERE o.order_date BETWEEN '2025-09-01' AND '2025-11-30'
  GROUP BY 1
)
SELECT
  product_category, orders,
  ROUND(100.0 * returned / NULLIF(orders,0), 0) AS pct_returned,
  ROUND(100.0 * returned_size / NULLIF(orders,0), 0) AS pct_returned_size
FROM returns
ORDER BY pct_returned DESC; -- this is what powers the spreadsheet above

The insight

Kids Outerwear had by far the highest size-related return rate of any category. The size guide it linked to used adult logic - chest and waist measurements - for a category parents actually shop by their child's age and height, so people were guessing.

The decision & outcome

I briefed a new size guide built around age and height with a simple fit visual, and had it placed directly on the product page instead of buried in a linked help article. Row 4 shows the result after the first six weeks.

-11 pts Return rate, Kids Outerwear

2 Other kids categories it was rolled out to

5 wks From spreadsheet to live PDP change

Building digital experiences that connect with people.

Strategy, turned into execution.

Where I've made things happen.

What I bring to the table.

Product Management

E-Commerce

Growth & Marketing

Turning data into a fix - three brands, three fixes.

The problem

The data

The insight

The decision & outcome

The problem

The data

The insight

The decision & outcome

The problem

The data

The insight

The decision & outcome

Six languages, two of them fluent.

Trained across three continents.

ESSEC Business School

ESSEC Asia-Pacific

Kobe University

Building something cool? Let's grab a coffee. ☕