Cart Abandonment Analysis: 4 Useful SQL Queries

Almost everyone shops online now, and I do too. If I am being honest, I have done something that most of us have done. I add an item to my cart, feel good about it, and then somewhere between that moment and the payment page, my mood or my mind changes. I close the tab or the application, and the item remains in the cart, never purchased.

For a long time, that was just a small, everyday habit. Then I started learning data analytics, and a question hit me. If millions of people do exactly this, how does a business even know? How do they know how many people added something to the cart, how far they got, and where they stopped? That question is what this cart abandonment analysis is about.

I found this topic intriguing because it is so relatable. You do not need a business background to understand it, because you have probably lived it yourself. That makes it a good way to see how data, SQL, and a dashboard come together to answer one real question. I am writing this for everyone who is curious about data and building their skills. I am still learning too, so let’s explore it together.

Quick summary

Cart abandonment analysis answers one question: where do interested shoppers stop, and what does that cost the business? Here is what this case study covers.

• The four-stage funnel, from product view to purchase, and how to measure the drop at each stage
• Four SQL queries, from the overall abandonment rate to revenue at risk
• A dashboard layout with two KPI cards, a trend line, and a guest versus logged-in view
• Three findings, five common mistakes, and interview questions to practice

This article uses a simulated dataset for teaching purposes, meaning that every number serves as an illustration rather than real company data. Want more material to practice on afterwards? Browse the BloomInData dataset library.

The business scenario

Imagine we are helping a mid-sized e-commerce store. The marketing lead comes to us with a familiar kind of worry. Traffic to the site has grown for two straight months, but revenue has stayed flat. She wants to know where that growth is leaking out.

This is the shape most cart abandonment analysis requests arrive in. Nobody starts by asking for a cart abandonment rate. They start with a business symptom, more visitors, and no more sales, and it becomes the analyst’s job to find where in the customer journey that symptom actually lives.

To answer it, we need a shared picture of the journey a shopper takes before buying anything. For this walkthrough we will use four stages.

1. Product view: the shopper looks at an item.
2. Add to cart: the shopper adds it to their cart.
3. Checkout starts; the shopper begins the checkout process.
4. Purchase: the shopper completes the order.

Cart abandonment occurs between stages two and four of the funnel. A shopper who showed real intent to buy decided not to. The primary objective of this case study is to determine the reasons and locations of cart abandonment.

What cart abandonment actually means

Before writing SQL, we should clarify what we are measuring, as the term is used loosely.

The most common definition is this formula.

Cart abandonment rate = 1 - {completed purchases}\{carts created}}

If 1,000 shoppers added something to their cart and 250 of them completed a purchase, the cart abandonment rate is 75 percent.

Here is where teams disagree and where it matters most. Some companies measure abandonment from the moment an item is added to the cart. Others measure it only from the moment checkout begins, which is really a checkout abandonment rate rather than a cart abandonment rate. The two numbers can look entirely different for the same store, since plenty of shoppers add something to a cart and browse for a while before ever starting checkout.

For this case study we will track both. We will track two metrics: “Cart to Purchase,” which uses “Add to Cart” as the starting point, and “Checkout to Purchase,” which uses “Checkout Start” as the starting point. Separating them becomes significant when we reach the SQL stage.

Why it matters to the business

A cart abandonment rate on its own is just a number. What makes it worth a marketing lead’s attention is what it implies about revenue and about where the team can actually act.

Go back to the scenario. If the store sees 4,000 carts created in a month and a 70 percent abandonment rate, that means 2,800 carts were abandoned. If the average cart is worth roughly 1,800 rupees, that works out to about 50 lakh rupees in orders that almost happened but fell through. That framing tends to attract a stakeholder’s attention faster than a percentage does.

More importantly, this metric points to where the business can intervene. A high drop-off between product view and add to cart usually points to a pricing, product page, or targeting issue. A high drop-off between adding to cart and starting checkout often points to hesitation or comparison shopping. A high drop-off between checkout start and purchase usually points to friction in the checkout flow itself, things like forced account creation, unexpected costs, or a clunky payment step.

The last issue is where the marketing lead’s question, “Why is revenue flat?” and the product team’s question, “What should we fix?” intersect. The rest of this case study focuses there, because it is usually the stage with the clearest fix and the fastest payback.

The dataset

To make this concrete, we will work with a simulated events table. It is not real transaction data from any company. It is a small dataset built to teach cart abandonment analysis, but the structure mirrors what a typical e-commerce event log looks like.

Each row is one event in one shopper’s session.

A small sample looks like this.

user_id,session_id,event_time,funnel_stage,order_id,cart_value,is_logged_in,device_type
u1001,s5001,2026-09-01 10:02:00,product_view,,,false,mobile
u1001,s5001,2026-09-01 10:04:00,add_to_cart,,1450,false,mobile
u1001,s5001,2026-09-01 10:06:00,checkout_start,,1450,false,mobile
u1002,s5002,2026-09-01 11:15:00,product_view,,,true,desktop
u1002,s5002,2026-09-01 11:18:00,add_to_cart,,3200,true,desktop
u1002,s5002,2026-09-01 11:22:00,checkout_start,,3200,true,desktop
u1002,s5002,2026-09-01 11:26:00,purchase,ORD9001,3200,true,desktop

User u1001 stops at checkout and never reaches purchase. That row is our abandoned cart. User u1002 completes the full journey. Everything we calculate from here comes from rows shaped like this.

Writing the SQL for cart abandonment analysis

With the data in place, we can start answering the business question in layers, starting broad and getting more specific.

Query 1, the overall cart abandonment rate

What question are we answering: of everyone who added something to their cart, what share never completed a purchase?

SELECT
  COUNT(DISTINCT CASE WHEN funnel_stage = 'add_to_cart' THEN session_id END) AS sessions_with_cart,
  COUNT(DISTINCT CASE WHEN funnel_stage = 'purchase' THEN session_id END) AS sessions_with_purchase,
  ROUND(
    1.0 - COUNT(DISTINCT CASE WHEN funnel_stage = 'purchase' THEN session_id END) * 1.0
        / COUNT(DISTINCT CASE WHEN funnel_stage = 'add_to_cart' THEN session_id END),
    3
  ) AS cart_abandonment_rate
FROM ecommerce_events;

What it calculates: the count of sessions that reached “add to cart,” the count that reached purchase, and the share of the first group that never converted.

Why it matters: this metric is the headline number a stakeholder will ask for first.

What decision it supports: whether cart abandonment is worth investigating at all. What counts as normal depends on the business, so compare the rate with your own history and with a current, reliable industry benchmark rather than a fixed number.

Query 2, the stage-by-stage funnel

What question are we answering: at which specific stage are we losing the most people?

SELECT
  funnel_stage,
  COUNT(DISTINCT session_id) AS sessions
FROM ecommerce_events
GROUP BY funnel_stage
ORDER BY
  CASE funnel_stage
    WHEN 'product_view' THEN 1
    WHEN 'add_to_cart' THEN 2
    WHEN 'checkout_start' THEN 3
    WHEN 'purchase' THEN 4
  END;

What it calculates: a session count at each of the four stages, so we can see the shape of the drop-off rather than just one final rate.

Why it matters: an overall abandonment rate hides where the leak actually is. A funnel view shows it.

What decision does it support: which team owns the solution? A drop between product view and add to cart is a merchandising or pricing conversation. A drop between checkout start and purchase is a checkout design conversation.

Running the analysis on our sample data produces a funnel like this.

The largest single drop happens before checkout even starts, but the checkout stage still loses about 44 percent of the shoppers who reach it. We will come back to why in a moment.

Query 3, breaking checkout drop-off down by authentication status

What question are we answering: does forcing an account matter here? Do guests abandon at checkout more often than logged-in shoppers?

SELECT
  is_logged_in,
  COUNT(DISTINCT CASE WHEN funnel_stage = 'checkout_start' THEN session_id END) AS checkout_starts,
  COUNT(DISTINCT CASE WHEN funnel_stage = 'purchase' THEN session_id END) AS purchases,
  ROUND(
    1.0 - COUNT(DISTINCT CASE WHEN funnel_stage = 'purchase' THEN session_id END) * 1.0
        / NULLIF(COUNT(DISTINCT CASE WHEN funnel_stage = 'checkout_start' THEN session_id END), 0),
    3
  ) AS checkout_abandonment_rate
FROM ecommerce_events
GROUP BY is_logged_in;

What it calculates: the checkout abandonment rate separately for guest sessions and logged-in sessions.

Why it matters: checkout stage drop-off is rarely uniform across a shopper base. Segmenting by something like authentication status often explains a large share of the overall number.

What decision does it support: whether offering guest checkout or streamlining account creation is worth testing?

On our sample data, the result is roughly what comes back.

That gap is large enough to be the headline finding of this whole analysis. We will come back to it in the next section.

Query 4, revenue at risk

What question are we addressing: which abandoned carts are most significant in terms of revenue, rather than just in count?

SELECT
  device_type,
  COUNT(DISTINCT session_id) AS abandoned_sessions,
  ROUND(SUM(cart_value), 2) AS revenue_at_risk,
  ROUND(AVG(cart_value), 2) AS avg_cart_value
FROM (
  SELECT DISTINCT session_id, device_type, cart_value
  FROM ecommerce_events
  WHERE funnel_stage = 'add_to_cart'
    AND session_id NOT IN (
      SELECT session_id FROM ecommerce_events WHERE funnel_stage = 'purchase'
    )
) abandoned_carts
GROUP BY device_type;

What it calculates: for each device type, the number of abandoned sessions, the total value sitting in those abandoned carts, and the average cart value.

Why it matters: not all abandoned carts are worth the same. A channel with a lower abandonment rate but a much higher average order value can carry more revenue at risk than a channel with a higher rate but small carts.

What decision does it support: where to prioritize a fix first, based on money at stake rather than raw volume of abandoned sessions?

From SQL to dashboard

A marketing lead will not run these queries herself, and she should not have to. The output of the SQL needs to become something she can glance at and act on. That is what a dashboard is for.

A useful cart abandonment dashboard needs four things, not just a funnel and a single rate.

The dashboard should include a funnel visual that matches the shape of the diagram above, allowing anyone to quickly identify where the largest drop occurs.

Two KPI cards up top, one for the headline cart abandonment rate and one for total revenue at risk. Volume and value both need a home, since query four showed they do not always point the same direction.

A line chart of abandonment rate over time, daily or weekly. A single static rate has no context. A line chart lets a stakeholder tell whether today’s number is normal, slowly drifting, or an emergency.

A spike like week 7 usually means something broke; a payment gateway outage is a common cause. A slow drift over a month points to something softer, a competitor offering free shipping, or a checkout page that got slower after a recent change.

A bar chart segmented by authentication status, guest versus logged in, rather than by device or traffic source alone. The chart visually represents the findings from query three, making the recommendation for testing a guest checkout option almost self-evident.

A stakeholder looking at this dashboard for the first time does not need to read a single query. She can see the shape of the funnel, whether this week’s rate is normal or alarming, and which segment to address first, all in one glance.

What the cart abandonment analysis reveals

Numbers on their own do not answer the marketing lead’s question. Interpreting them does. Three findings stand out from the queries above.

The first is the guest checkout gap. Guests abandon at checkout about eight times more often than logged-in shoppers, 64 percent against 8 percent. That gap is too significant to attribute solely to guests being less serious buyers. It points to friction in the checkout process itself, most likely forced account creation, and the natural next step is to test a guest checkout option rather than redesigning the whole flow blind.

The second point is that rate and revenue can diverge. Query four would likely show mobile with a higher abandonment rate but a lower average cart value, while desktop shows a lower abandonment rate but a much higher average order value. If desktop shoppers carry more revenue at risk even though fewer of them abandon, a solution aimed at desktop checkout may pay back faster than one aimed only at mobile, even though mobile looks like the bigger problem on a rate chart alone. A competent analyst checks both before recommending where to focus.

The third point is a hypothesis worth testing, specifically regarding hidden costs, rather than a proven fact. If session data showed drop-off clustering right around the moment a shipping or tax calculation loads, that points to sticker shock at the final step rather than a checkout design problem. In that case the recommendation is not to redesign the checkout; it is to test a free shipping threshold or show the shipping cost earlier in the journey.

Notice that none of these three findings came from the overall abandonment rate in query one. Analyzing the abandonment rate by segment and stage yielded these insights, which are the essence of this type of analysis.

Common mistakes in cart abandonment analysis

1. Confusing correlation with cause. A high abandonment rate on mobile does not by itself prove mobile checkout is broken; it could just as easily reflect who tends to browse on mobile in the first place.
2. Using an inconsistent denominator. Comparing a cart to purchase rate from one month against a checkout to purchase rate from another month produces numbers that look comparable and are not.
3. Ignoring segment differences. A single blended abandonment rate can hide a problem as large as the guest checkout gap in this case study entirely.
4. Presenting a number without a recommendation is not an analysis; it is merely a fact. Telling a stakeholder the rate is 70 percent is not an analysis; it is a fact. Providing her with information about where the performance is worst and what to test next constitutes the analysis.
5. A short attribution window is often treated as the truth. Some shoppers treat a cart as a bookmarking tool, especially on mobile, and come back to buy a few days later on a different device. A cart marked abandoned after a strict one-day window might just be a delayed purchase. Before locking in a definition of abandoned, it is worth checking the average time to purchase for shoppers who do eventually convert. Sending an aggressive discount email to someone who was already going to buy in three days is not a save; it is an unnecessary discount.

What to learn next

This case study answered where and roughly why shoppers abandon. Two logical next steps involve further investigating the subject.

• Cohort analysis would show whether abandonment behavior differs by when a shopper first arrived; a shopper acquired through a discount-heavy campaign may behave very differently at checkout than one who arrived through search.

• A/B testing on the checkout flow would let the guest checkout hypothesis from this article move from an educated guess to a measured result, comparing conversion between shoppers shown a guest checkout option and shoppers who are not.

Building the dashboard described in this article in Power BI, with real DAX measures for abandonment rate and revenue at risk, is also a natural next project if you want the portfolio piece to accompany this analysis.

From my own GitHub: funnel projects and what I learnt

This article is a teaching case study, but funnels are something I have been practicing on my own GitHub for a while. I have built more than five projects around funnels, conversion and customer behavior, and I want to share what building them taught me. Here are the two I would point you to first.

AI Search and Zero Click Impact on Ecommerce Performance

This is the project from when I started building end to end data analysis projects. It asks a simple question: does AI powered search actually improve revenue, and how much value is lost when users leave through zero click behavior without ever entering the purchase funnel? I cleaned and validated the data in Python, loaded it into PostgreSQL for the funnel and conversion analysis in SQL, and built the dashboard in Tableau. You can see the whole project in the GitHub repository.

In that project the largest drop happened between click and add to cart, and roughly 18 percent of sessions were zero click. In this article’s case study, the sharpest problem came from a different place, guests at checkout. That was the first thing I learnt: funnels do not leak in the same place for every business or every segment, so you find the leak with data instead of assuming it.

Here is what else that project taught me.

• Start with hypotheses. Before opening the data I wrote down four hypotheses, for example that AI search sessions convert better than classic search sessions. It gave the analysis a direction instead of exploring blindly.
• Check SQL results in a second tool. I validated my SQL numbers in Python before building the dashboard, so I could trust what stakeholders would see.
• Define every KPI clearly. Conversion rate, add to cart rate and zero click percentage each needed a clear formula before I could compare anything.
• End with a decision, not just a chart. I finished with short term and long term recommendations, because a dashboard alone does not tell a stakeholder what to do next.

Marketing analytics with MySQL and Power BI

Another project looks at acquisition, campaign performance, funnel conversion, ROAS and ROI across seven marketing channels using MySQL and Power BI. You can find it in the marketing analytics repository.

The rest of my projects are on my GitHub profile.

Interview questions on this topic

1. How would you calculate cart abandonment rate, and what assumptions would you need to state alongside the number? A strong answer names the formula, then flags the denominator question from earlier in this article, cart-based versus checkout-based, as something to clarify before trusting any number.
2. How would you figure out why abandonment increased month over month? A strong answer does not guess; it lays out a plan and checks whether the mix of segments shifted first, such as more guest traffic, before assuming the checkout itself got worse.
3. What would you do differently with limited data, say only order completions and no event-level funnel data? A strong answer acknowledges the limits; you could still estimate a rough cart-to-purchase rate from cart creation and order tables alone, but you would lose the ability to localize where in the funnel people drop off.
4. How would you decide whether to address mobile or desktop checkout first? A strong answer brings up revenue at risk, not just the abandonment rate, echoing query four from this article.
5. A stakeholder wants to email every abandoned cart a discount code immediately. What would you push back on? A strong answer raises the wishlist behavior problem, some of those shoppers were already going to buy, and suggests checking typical time to purchase before triggering automated discounts.

Try it yourself

Reading queries only takes you so far, so here are four small challenges on the same events table. Try them before looking anything up.

1. Calculate the checkout abandonment rate by device type instead of by login status. Which device loses more shoppers at checkout?
2. Identify the sessions that added something to the cart but never started checkout. What share of all cart sessions are they?
3. Compare the average cart value of purchased sessions with unpurchased sessions. What might a gap tell a marketing team?
4. Add a date to the analysis and calculate the abandonment rate per week. Which week would you investigate first, and why?

Write the query, run it, and then ask what decision the result could support. That last step is what turns SQL practice into analysis.

Frequently asked questions

Keep exploring with BloomInData

If this case study made you curious, here are the best places to continue.

• Practice with datasets. Browse the BloomInData dataset library and pick a dataset to practice on.
• Watch and learn. Follow the journey on YouTube.
• See the code and projects. Find my work on GitHub.
• Connect and discuss. Say hello on LinkedIn.
• Read more. Follow my writing on Medium.

Now I would love to hear from you.

About the author
I'm Priyanka Lakra, a self-taught data learner and  I explore the data ecosystem one concept, dataset and project at a time, and I share what I learn as I go, including the mistakes.. I share what I learn on BloomInData. Come explore it with me.