Retail demographics analysis: compare customer segments

Retail demographics analysis compares supplied aggregate customer segments on their share of customers, recorded sales and average order value. Keep unknown labels in the denominator and calculate each store on the same period and counting rules. The result describes the customers recorded in the file; it does not identify personal traits or predict individual purchases.

Define the customer population first

Ask the business owner for an aggregated export containing store, period, supplied segment, customer count, completed order count and net merchandise sales. Use labels the business already holds appropriately. Do not fill gaps by inferring age, income or other personal characteristics from names, locations or purchases.

State whether the count means purchasers during the period or all registered customers. Use purchasers here, with each customer assigned to one supplied adult age band within each store. Counts are deduplicated within a store. A customer buying at both stores can appear in both, so the combined count measures store-customer memberships rather than unique people across the chain.

Choose net sales after returns and discounts, excluding tax, and document how later returns are assigned to segments. Keep this rule consistent with the order denominator. Customer composition is a different question from retail customer churn analysis, which follows whether customers return over time.

Lay out the aggregate file

The following illustrative monthly export belongs to a US retailer. The labels are supplied adult age bands, with Unknown retained as its own row. Put the input fields in columns A through E, from Store to Net sales.

Before comparing anything, confirm that these rows partition the store's customers and orders. If a source allows several labels per customer, its segment counts cannot simply be added. Request an exclusive grouping or describe the result as overlapping memberships.

Calculate segment share and order value

In F2, divide customers by the customer total for the same store. In G2, divide net sales by orders:

=IF(SUMIFS($C$2:$C$7,$A$2:$A$7,A2)=0,"",C2/SUMIFS($C$2:$C$7,$A$2:$A$7,A2))
=IF(D2=0,"",E2/D2)

Fill both formulas down. Microsoft's SUMIFS documentation confirms that the function sums values meeting specified criteria; here the criterion is the store. Covirage is not affiliated with Microsoft.

Display percentages and dollars to two decimals, retaining full precision underneath. North's 18-44 share is 120 / 300 = 40.00%. South's Unknown order value is 8,550 / 130 = $65.77. Unknown customers account for a larger share at South, so a comparison of known labels alone describes different coverage.

The recorded 45+ group has higher order value at both stores. That is a descriptive result, not proof that age caused spending differences. Product mix, discounts and visit purpose can all differ. Use it to frame questions for the commercial team, rather than assuming the behavior of any individual customer.

Reconcile the store and company totals

The combined order value is 62,000 / 830 = $74.70, not an average of segment averages. In Excel, use =SUM(E2:E7)/SUM(D2:D7). Segment sales must reconcile to the sales report on the same period and return policy; orders must reconcile to the completed-order total.

This file cannot establish gross profit because it contains no cost figures. Retail industry financial analysis addresses the separate financial review.

Add catchment context carefully

A supplied catchment summary can add population context when its geography, age bands and reference period are documented. The Census Bureau describes the American Community Survey as an ongoing source of yearly information about the population. Population information is separate from a retailer's observed customer file.

Compare this hypothetical supplied North catchment with the known-label customer rows above:

The younger segment's catchment share is 2,000 / 5,000 = 40.00%, compared with 120 / 220 = 54.55% of known-label customers. Each column uses its own population denominator; percentages are rounded to two decimals.

Those customer shares exclude the 80 Unknown memberships; the main report does not. The comparison can motivate a coverage check, but it cannot measure market penetration or prove an underserved population. Visitors and online fulfillment may cross the assumed catchment boundary. Retail location analysis owns the wider location decision.

Where it goes wrong

• Dropping unknowns silently. Show all-customer shares first and label any known-only denominator.
• Summing overlapping people. Store totals and chain-unique customers require different counting rules.
• Averaging order values. Divide combined sales by combined orders, retaining unrounded inputs.
• Treating population as customers. Catchment residents, registered customers and purchasing customers are different populations.

Compare your aggregate file in Covirage

Bring your store-segment summary and its control totals to Covirage. Covirage maps the columns, its tools compute and check segment shares and order values, and the external AI model explains the comparisons 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 business workflow.