Compare dealership lead sources using completed sales and vehicle gross profit. Build a matched lead cohort, calculate conversion in a spreadsheet, and reconcile the result to dealer exports.
Automotive marketing analytics connects dealership leads to completed sales and gross profit by store and source. Start with a fixed lead cohort, match its completed deals, and compare conversion alongside the money retained after vehicle cost and campaign spending. A source that brings more leads can still produce less gross profit after spending.
For this worked example, a fictional US dealer group compares leads created in July 2026, with completed sales observed through September 30, 2026. Both stores get the same outcome cutoff. This is a lead-cohort report; it will not equal a calendar-month sales report because some July leads buy later.
Agree which leads qualify. Exclude test entries and duplicate submissions under a written rule. Keep a customer returning for another vehicle as a separate buying opportunity when the business can distinguish it reliably. Do not merge records just because their names look similar.
Keep these export tabs:
| Tab | Grain | Required columns |
|---|---|---|
| Leads | One retained lead | Lead key, store, original source, creation date |
| Sales | One completed deal | Deal key, matched lead key, net vehicle revenue, vehicle cost, completion date |
| Costs | One store-source allocation | Store, source, cohort campaign cost |
Before applying formulas, filter Leads to the July cohort and Sales to completed deals through September 30, 2026, excluding cancellations. Keep the full filtered Sales file for reconciliation, including deals without a July lead match. Retain unmatched leads too. Match using an existing lead-to-deal reference where available; send ambiguous matches for review before assigning their gross profit.
Use a consistent net revenue definition. The SEC's financial statement guide explains net revenue after returns and allowances. A canceled deal must therefore leave the completed-sale result rather than continuing to support the source's performance.
On Sales, use columns A through E for deal key, lead key, net vehicle revenue, vehicle cost, and vehicle gross profit. In E2:
=C2-D2
This is vehicle gross profit, before marketing and dealership operating expenses. The basic sales-less-cost calculation follows the IRS explanation of gross profit. Finance must approve which vehicle costs the dealer export includes; do not silently add finance and insurance income to that measure.
On Leads, columns A through C contain lead key, store, and source. Add D for a sold-lead flag and E for matched gross profit:
=IF(A2="","",--(COUNTIF(Sales!$B$2:$B$10000,A2)>0))
=IF(A2="","",SUMIF(Sales!$B$2:$B$10000,A2,Sales!$E$2:$E$10000))
Each retained lead appears once. A lead connected to multiple completed deals still contributes only one sold lead to conversion; its gross profit includes those matched deals. The example below happens to have one deal per sold lead. Extend the ranges to cover the entire export.
Lead-to-sale conversion = Unique sold leads / Unique retained leads
Gross profit after source spending = Matched vehicle gross profit − Allocated campaign cost
For acquisition spending with a broader cost scope, use the separate customer acquisition cost method. Do not label source spending alone as fully loaded acquisition cost.
Create a Summary tab with store in A, source in B, leads in C, sold leads in D, gross profit in E, and campaign cost in F. The first source row uses these formulas:
=COUNTIFS(Leads!$B$2:$B$10000,$A2,Leads!$C$2:$C$10000,$B2)
=SUMIFS(Leads!$D$2:$D$10000,Leads!$B$2:$B$10000,$A2,Leads!$C$2:$C$10000,$B2)
=SUMIFS(Leads!$E$2:$E$10000,Leads!$B$2:$B$10000,$A2,Leads!$C$2:$C$10000,$B2)
=SUMIFS(Costs!$C$2:$C$10000,Costs!$A$2:$A$10000,$A2,Costs!$B$2:$B$10000,$B2)
Use =IF(C2=0,"",D2/C2) for conversion and =E2-F2 for gross profit after spending. The Google Sheets SUMIFS function sums values meeting multiple criteria, which keeps store and source boundaries explicit. Format percentages to one decimal place; dollars below are whole USD.
| Store | Source | Leads | Sold leads | Vehicle gross profit (USD) | Campaign cost (USD) | Conversion | Gross profit after spending (USD) |
|---|---|---|---|---|---|---|---|
| North | Search | 80 | 8 | 24,800 | 8,000 | 10.0% | 16,800 |
| North | Referral | 40 | 6 | 21,000 | 2,000 | 15.0% | 19,000 |
| South | Search | 100 | 5 | 14,500 | 9,000 | 5.0% | 5,500 |
| South | Referral | 30 | 6 | 20,400 | 1,500 | 20.0% | 18,900 |
| Total | 250 | 25 | 80,700 | 20,500 | 10.0% | 60,200 |
The group converts 25 / 250 = 10.0%. Matched gross profit less source spending is $80,700 − $20,500 = $60,200. This is not dealership net profit: rent, general payroll, and other operating costs remain outside the calculation.
South Search deserves investigation because it combines the largest lead count with the lowest conversion. Check vehicle availability, lead age, and follow-up before moving the budget. The table establishes a commercial difference, not its cause. For the return terminology, see marketing ROI versus ROAS.
The source rows must add to the retained lead register. Sold-lead flags must tie to distinct matched lead keys in the completed-deal file. Matched and unmatched deal gross profit together must equal the dealer export's gross profit for the same completed-deal scope.
Campaign allocations must sum to the cost file, including shared creative or agency costs if those are inside the agreed scope. Keep unallocated spending visible rather than spreading it differently at each store.
Give the sales manager ownership of lead disposition, marketing ownership of source mapping and spending, and finance ownership of the gross profit bridge. Keep the KPI definitions in the marketing KPI scorecard; account buying groups need the different matching approach in B2B marketing analytics.
Bring a sample of your own lead, completed-deal, and campaign cost files to Covirage. Covirage maps the columns, and its tools compute and check the comparisons; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, then set up your business and bring your exports.
See sales insights for the supporting business context. Covirage is not affiliated with Google Sheets.
Start with a lead export, a completed-deal export, and a campaign cost file. Keep stable lead and deal identifiers, store, source, dates, net vehicle revenue, and vehicle cost. Use business identifiers rather than copying customer contact details into the analysis workbook.
Use unique leads for lead-to-sale conversion. Appointment-to-sale conversion answers a different question and needs an appointment denominator. Report both when useful, but keep their labels and definitions separate so a change in appointment booking does not disguise a change in sales outcomes.
No. Matching a completed deal to a source shows an attributed outcome under your matching rule. It does not establish that the source caused the purchase. Existing customer demand, vehicle availability, salesperson follow-up, and overlapping advertising can also affect results.
Keep vehicle gross profit and finance and insurance results in separate columns. Include the latter only under an agreed definition, including any applicable reversals. Apply the same scope to every store and source before comparing their profitability.