Ecommerce customer experience analysis: friction and cost

Ecommerce customer experience analysis compares delivery and service problems with the money an order leaves behind and whether its customer buys again. Start with matched order records, rather than an overall satisfaction average. Keep the later purchase window equal for every customer.

Start with an order cohort you can reconcile

A US homewares seller selects orders delivered September 1–7, 2026. Each customer has one selected order, and every order has a complete 30-day follow-up window. The analysis closes after October 7, 2026. These dates define the example, not a recommended buying cycle.

Export order ID, customer ID, promised delivery date, actual delivery date, net revenue, item cost, and fulfillment cost. Separately export refunds and customer-initiated contacts. Reduce those records to one summary per order before attaching them. Otherwise, an order with several contacts repeats its revenue several times.

Keep a customer-level subsequent-purchase flag separate from index-order revenue. For customers with multiple eligible orders, select one index order under a written rule; otherwise the same later purchase can appear as several successes. Use ecommerce customer segmentation when purchase history is the grouping question.

Define the flags before comparing groups

The first table occupies A1:E9 in Google Sheets. Late means actual delivery after the promised date. Returned means the order was fully returned within follow-up. Repeat contact means at least two customer contacts about that order. Later purchase means another paid order within 30 days of delivery.

For an actual delivery date in P2 and promised date in O2, use =IF(OR(P2="",O2=""),"",--(P2>O2)). A blank stays unknown and goes on an exception list, rather than becoming on time.

Calculate the late-order count, late-order return rate, and late-order repeat-contact rate:

=COUNTIFS(B2:B9,1)
=COUNTIFS(B2:B9,1,C2:C9,1)/COUNTIFS(B2:B9,1)
=COUNTIFS(B2:B9,1,D2:D9,">=2")/COUNTIFS(B2:B9,1)

Google documents COUNTIFS as a count across multiple criteria, with matching range sizes. Change the late criterion to zero for the on-time group. Replace the return condition with E2:E9,1 for the later-purchase rate. Report a blank or an explicit unavailable label when a group has no eligible orders.

Attach the cost of the experience

Put these financial columns in F:J beside the corresponding orders. Net revenue is after refunds and excludes sales tax. Returned goods are restored to inventory in this example, so returned-order item cost is zero; fulfillment includes the outbound and return handling already incurred.

Service cost assumes $6 per contact. It is an illustrative allocation rate, not an industry benchmark. In I2 enter =D2*6; in J2 enter =F2-G2-H2-I2, then fill down. For actual driver rates and their limits, use cost to serve per customer.

Sum late-order contribution and divide by its order count:

=SUMIFS(J2:J9,B2:B9,1)
=SUMIFS(J2:J9,B2:B9,1)/COUNTIFS(B2:B9,1)

SUMIFS adds values meeting the stated criteria. Use the same ranges and eligibility rules for every measure. Covirage is not affiliated with Google.

Read the comparison and prove the totals

Rates are rounded to one decimal; per-order dollars to two. The late return rate is 1 / 3, its repeat-contact rate is 3 / 3, and its later-purchase rate is 1 / 3. The on-time counts are respectively 1 / 5, 1 / 5, and 3 / 5.

The proof is 3 + 5 = 8 orders and $31 + $143 = $174 contribution. Independently, $650 − $330 − $80 − $66 = $174. Reconcile these amounts to the selected order, refund, and cost records before publishing the comparison.

The late group merits a delivery and support review. Its lower contribution does not establish that lateness caused the gap: order values and returns also differ. Read the underlying records with operations, choose a specific change, and compare equivalent future cohorts. Use the lost customer review timeline for customer-level investigation and a customer experience dashboard for ongoing presentation.

Where it goes wrong

• Contact rows multiply sales. Summarize contacts before matching them to financial records.
• Recent orders look worse. Exclude incomplete follow-up windows from purchase comparisons.
• Refunds remove all costs. Keep handling, damaged inventory, and service costs that were actually incurred.
• Different customers masquerade as an effect. Check product and purchase-history differences before assigning a cause.

Analyze your own order file in Covirage

Bring an order export with related return, contact, and cost summaries. Covirage maps the columns; its tools compute and check the comparisons, and the external AI model explains them with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.

See customer profitability for the contribution perspective behind the review.