Customer behavior analysis from sales data

Customer behavior analysis describes what customers do in your sales records: how often they order, which products they buy, and how long they leave between purchases. Start with a consistent order export and calculate those measures for each customer. Use the results to ask better account questions, without treating recorded purchases as proof of intent or satisfaction.

Set the population and the purchasing window

Choose a completed reporting period and decide who belongs in the review. A sales-only export contains buyers, so a customer missing from it might be inactive, newly assigned, or absent because of a mapping error. Bring the approved customer list if the question includes customers with no orders.

For this example, a US commercial supplier reviews January 1 through March 31, 2026. It includes every completed order for the customers shown. Values are net of discounts, there are no returns in these rows, and every amount is USD.

The objective is descriptive: compare purchasing frequency, category mix, and observed order gaps. Profitability requires cost information as well; the separate cost-to-serve calculation handles that next question.

Prepare the order rows before calculating

The source needs customer ID, order ID, order date, product category, and net sales. Keep stable customer IDs even if account names change. Confirm the date represents the business event under review; do not mix order dates and invoice dates across customers.

An export often has one row per order line. Sum line values by order before counting order frequency. For product mix, retain a separate category summary when an order spans categories. Otherwise one mixed order can become several apparent purchases.

The example below has one category per order, so the same rows support both calculations. Put them on a sheet named Orders, with customer, date, category, and net sales in columns A through D. These are the complete example records, sorted by customer and date.

Before proceeding, compare the total with the same completed-order population in the approved export. A ledger reconciliation may require a bridge for timing differences between ordering and invoicing; do not silently treat them as the same event.

Calculate order frequency and category mix

Create a summary with customer IDs A, B, and C in H2:H4. Use I for orders, J for net sales, K for average order value, and L for supplies share. In Google Sheets, enter these formulas in row 2 and fill down:

=COUNTIFS(Orders!$A$2:$A$7,H2)
=SUMIFS(Orders!$D$2:$D$7,Orders!$A$2:$A$7,H2)
=IF(I2=0,"",J2/I2)
=IF(J2=0,"",SUMIFS(Orders!$D$2:$D$7,Orders!$A$2:$A$7,H2,Orders!$C$2:$C$7,"Supplies")/J2)

Google's COUNTIFS documentation describes counting rows that meet criteria. Here rows equal orders only because the export has already been reduced to one row per order. Google's SUMIFS documentation covers summing values by customer and category criteria.

The overall average is 9,500 / 6 = $1,583.33, not the average of customer averages. Supplies share is 4,500 / 9,500 = 47.4%. Dollar averages are rounded to cents and percentages to one decimal place; calculations retain full precision.

Customer B bought only supplies in this window. That is evidence of observed product mix, not proof that B has no equipment need. Use the cross-sell matrix method when you need a broader category comparison rather than extending this finding into an assumption.

Measure the gaps between completed orders

On Orders, add gap days in E. In E2, enter the formula below and fill down. The customer sort prevents a gap from crossing account boundaries.

=IF(A2=A1,B2-B1,"")

In the customer summary, calculate average observed gap in M2:

=IF(I2<2,"",SUMIFS(Orders!$E$2:$E$7,Orders!$A$2:$A$7,H2)/(I2-1))

Customer A's average is (15 + 30) / 2 = 22.5 days. Customer C has no completed interval, so its average is unavailable. A blank is more accurate than zero, which would imply immediate repeat purchasing.

This measure covers intervals with both orders inside the window. It excludes the unfinished interval after the last order and any earlier order outside the export. Extend the history when the account's normal buying cycle requires it, and keep that history length consistent across comparisons.

Covirage is not affiliated with Google.

Turn the pattern into an account question

Assign the sales operations analyst ownership of the calculations and the account owner responsibility for investigating changes. A category shift may justify checking a contract or discussing an upcoming project. A longer order gap may justify reviewing expected purchasing dates. Neither finding establishes a reason without additional evidence.

Repeat the measures over comparable completed periods using unchanged definitions. For a revenue-focused follow-up, use customer revenue analysis. For a customer with a missing expected purchase, the lost customer review template separates the transaction timeline from the explanation supplied later.

Where it goes wrong

• Counting lines as orders. A large order with several items looks artificially frequent.
• Mixing periods. A partial period cannot support the same frequency interpretation as a completed one.
• Treating single-order gaps as zero. No completed interval means the measure is unavailable.
• Ignoring credits or category changes. Returns and remapped products can change mix without a new purchasing preference.
• Claiming motives. A discount purchase or missing order establishes an action, not its cause.

Analyze your own sales export in Covirage

Upload your approved sales file or Google Sheet to Covirage. Covirage maps the columns; its tools compute and check purchasing measures, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.

See sales insights for the supporting account analysis context.