Ecommerce funnel analysis: locate purchase drop-offs

Ecommerce funnel analysis shows which shopping stages, devices, and acquisition sources account for purchase drop-offs. Calculate conversion from comparable exported session counts, then separate changes within segments from changes in traffic mix. The result is a focused business investigation, with the cause still to be established.

Prepare a comparison that can answer the question

Use a prepared export with one row per period, device, and source. Each row needs product-view sessions, cart sessions, checkout sessions, and purchasing sessions. Count distinct sessions progressing through the stated sequence within the same completion boundary.

Do not substitute raw action totals. Google Analytics describes separate ecommerce events for product views, cart additions, checkout starts, and purchases; a repeated action is not another shopping session. The ecommerce conversion funnel explains how to define the stage counts before this diagnostic comparison.

Use a single acquisition definition. Google Analytics distinguishes session-level channels from first-user and event-level channels. Document the scope supplied in the export and keep it unchanged across devices, periods, and stages.

Filter out internal visits under an agreed rule. Keep unknown devices and unassigned sources visible so the totals reconcile. Check that the date range has the same number of completed reporting days and that each stage uses the same time zone.

Calculate stage rates inside each segment

A US kitchenware retailer exports the following September summary. Every checkout session previously added an item after viewing a product; every purchasing session completed that sequence. Percentages are rounded to two decimal places, with calculations made before rounding.

Place device in column A, source in B, and the four counts in C through F. In G2 through K2, enter:

=IF(C2=0,"",D2/C2)
=IF(D2=0,"",E2/D2)
=IF(E2=0,"",F2/E2)
=IF(C2=0,"",F2/C2)
=E2-F2

These return view-to-cart, cart-to-checkout, checkout-to-purchase, overall conversion, and checkout loss. Fill them down through the segment rows.

Mobile paid search stands out at both handoffs after cart addition. It loses 1,600 minus 800 = 800 sessions before checkout, then another 480 before purchase. The evidence supports investigating that segment first; it does not identify shipping charges, payment failures, or campaign targeting as the cause.

Reconcile before explaining

Total conversion is 1,544 / 20,000 = 7.72%, not the average of the segment percentages. The four checkout losses sum to 996, which also equals 2,540 minus 1,544.

For a device rollup, enter Mobile in M2 and calculate its view and purchasing counts:

=SUMIFS(C2:C5,A2:A5,M2)
=SUMIFS(F2:F5,A2:A5,M2)

Microsoft's SUMIFS documentation explains summing rows that satisfy criteria. Divide the resulting purchase count by the view count. These formulas also work in Google Sheets.

Mobile has 656 purchasing sessions from 12,000 views, or 5.47%; desktop has 888 from 8,000, or 11.10%. Those totals reconcile to 1,544 and 20,000. Inspect the source rows before using the device gap as a diagnosis: paid-search traffic accounts for much more of mobile's starting population.

Check whether traffic mix explains the movement

Suppose the prior comparison period had these counts. Each segment's overall conversion is unchanged from September.

Prior conversion was 1,632 / 20,000 = 8.16%. September's 7.72% is lower by 0.44 percentage points, despite unchanged segment rates. The shift produces 168 additional mobile-organic purchases, 336 fewer desktop-paid purchases, and 80 additional mobile-paid purchases: a net decrease of 88.

To reproduce a constant-rate comparison, multiply each September view count by its prior segment rate and sum the results. If views are in D2:D5 and prior rates in F2:F5:

=SUMPRODUCT(D2:D5,F2:F5)

The result is 1,544. In this example, mix fully explains the overall decline. For returning-customer differences within each segment, use ecommerce cohort analysis.

Assign an investigation with a measurable next step

The ecommerce owner should inspect mobile paid-search checkout records with marketing and customer operations. Ask whether promoted products were available, delivery promises matched the landing page, and payment or address issues clustered in the affected period. Record supporting and contradicting evidence.

A scenario can size the question. Applying desktop paid search's 60.00% checkout completion to mobile paid search's 800 checkouts gives 480 purchasing sessions, or 160 above the recorded 320. That is a comparison scenario, not promised incremental sales or proof that the desktop rate is attainable.

Use marketing funnel analysis for earlier acquisition stages and ecommerce ROI analysis when weighing the cost and commercial return of a proposed change. Qualified deal progression belongs in sales funnel analysis.

Where it goes wrong

• Device averages hide source mix. Compare device within source before assigning responsibility.
• Small groups drive loud percentages. Inspect counts alongside rates before prioritizing a segment.
• Period or stage definitions change. Reconcile the filters before interpreting a movement.
• Purchasing sessions become net revenue. Purchases, cancellations, refunds, and collected cash need separate definitions and records.
• An observed gap becomes a causal claim. Investigate the explanation and assess a proposed change with a controlled comparison where practical.

Covirage is not affiliated with Google or Microsoft.

Investigate your own ecommerce export in Covirage

Bring a file or Google Sheet containing your prepared stage counts by period, device, and source. Covirage maps the columns, its tools compute and check the comparisons, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, which takes about a minute, and bring a sample of your own export.

See sales insights for the supporting business context.