Blog · Finance metrics and formulas
Measure retail customer acquisition cost across stores and online channels. Build a deduplicated new-shopper denominator, allocate spending consistently, and compare the result with observed customer contribution.
Customer acquisition cost in retail divides acquisition spending by distinct new shoppers across the channels being measured. The hard part is establishing who is new when the same person buys in a store, enrolls in loyalty, and later orders online. A loyalty enrollment or a new order is not enough.
Use a new-to-retailer definition: the shopper's earliest qualifying purchase in the combined history falls in the acquisition period. Keep the general CAC formula as the common definition; this method deals with retail identities and channel ownership.
The distinction matters even in a straightforward purchase file. Shopify's explanation of CAC explicitly separates first-time customers from returning or retained customers. An existing store shopper who creates an online account should not enter your new-shopper count again.
Specify which purchases qualify. Exclude canceled transactions and apply a documented rule for fully returned first purchases. Include historical orders before the reporting period. If history starts recently, describe customers as new to the available history rather than claiming they have never shopped with you.
Bring the order or point-of-sale export, loyalty customer file, existing identity crosswalk, and acquisition spending ledger. The order file needs purchase timestamp, order ID, customer key, channel, store, net sales, and variable costs. The spending file needs posting date, campaign, channel or store, amount, and acquisition classification.
Use your supported customer crosswalk to connect online and store IDs. Do not assume a shared surname establishes one customer, or that every blank customer ID is the same person. Put anonymous purchases and unresolved matches in separate control totals.
Assign each identified new shopper to the channel of their earliest qualifying purchase. This is a stated reporting convention, not proof that the last channel caused the purchase. Use a stable order-ID sort to resolve simultaneous purchases consistently.
On Orders, put the mapped customer key in A, qualifying purchase timestamp in B, order ID in C, and channel in D. Include the complete available qualifying history, sorted by timestamp and order ID. In G2, find the first timestamp for that key:
=IF(A2="","",MINIFS($B$2:$B$10000,$A$2:$A$10000,A2))
Microsoft documents MINIFS as returning the minimum value meeting specified conditions. Dates must be actual spreadsheet date values. In H2, flag only one first-purchase row per customer, including tied timestamps:
=IF(OR(A2="",B2<>G2),0,--(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=1))
Fill both formulas down through the data. For a summary with channel names in column A, the January 2026 new-shopper count is:
=COUNTIFS(Orders!$D$2:$D$10000,A2,Orders!$H$2:$H$10000,1,
Orders!$G$2:$G$10000,">="&DATE(2026,1,1),
Orders!$G$2:$G$10000,"<"&DATE(2026,2,1))
This counts the first row, rather than every order placed by a newly acquired shopper. Expand the ranges if the export is larger. Covirage is not affiliated with Microsoft or Shopify.
Consider an illustrative US retailer's January cohort. The spending totals below come from acquisition-classified advertising and allocated acquisition labor. Shared labor follows a documented time allocation; routine store operations and retention campaigns are excluded. The same classification applies to both channels.
Contribution is recorded net sales less merchandise cost, payment fees, fulfillment, and other included variable costs during each shopper's first 90 days. Discounts and returns reduce net sales. Every shopper has completed that observation window, and later purchases remain assigned to their original acquisition channel.
| Acquisition channel | Spending (USD) | New shoppers | CAC (USD) | Observed contribution (USD) | After acquisition spending (USD) |
|---|---|---|---|---|---|
| Store-first | 12,000 | 240 | 50.00 | 16,800 | 4,800 |
| Online-first | 14,400 | 360 | 40.00 | 16,200 | 1,800 |
| Total | 26,400 | 600 | 44.00 | 33,000 | 6,600 |
With these columns in A:F, enter the following in D2 and F2, then fill down:
=IF(C2=0,NA(),B2/C2)
=E2-B2
The total CAC is =SUM(B2:B3)/SUM(C2:C3): 26,400 / 600 = $44.00. Display dollars to two decimal places and retain full precision underneath. The store-first channel costs more per shopper but produces more contribution after acquisition spending over this observation window. That finding needs the contribution comparison; CAC alone cannot establish it.
The channel counts must sum to the unique new-customer list: 240 + 360 = 600. Each customer must appear in exactly one channel. Tie channel spending to the acquisition ledger, including shared allocations, and tie cohort contribution to the underlying orders after returns.
The spending check is =SUMPRODUCT(C2:C3,D2:D3)-SUM(B2:B3). It returns zero using unrounded CAC values. For broader comparisons, CAC by industry explains why consistent definitions come before outside benchmarks. Restaurant acquisition costs need a different identity boundary around identified diners and delivery orders.
Upload your order history, customer identity crosswalk, and acquisition spending file or Google Sheet. Covirage maps the columns; its tools compute and check acquisition costs and observed contribution, 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 data after setting up your business.
See customer profitability for the wider comparison of customer earnings and costs.
Only when the member's first qualifying purchase is also their first recorded purchase across the retailer's linked channels. An existing shopper who joins a loyalty program is a new member, but counting that enrollment as customer acquisition would inflate the denominator.
Use a consistent customer key supported by your identity records, then find that customer's earliest qualifying purchase across both channels. Keep uncertain matches separate. A person who buys online after shopping in a store remains the same acquired customer.
Choose a cost boundary that answers the decision. A campaign measure can exclude routine occupancy, while a broader acquisition measure can include an approved allocation. Document the treatment and use it consistently across stores, channels, and periods.
Report those transactions separately. They establish sales and orders, but cannot establish whether a shopper is new. Label a calculation based on identifiable customers accordingly, and avoid comparing it with a channel that can identify nearly every purchaser without qualification.