Reconcile ad spend, leads, sales and refunds in one acquisition cohort

Build a bridge, not a blended total

Choose one acquisition basis, such as the date a new enquiry was first accepted into the business. Create a cohort ledger with aggregate spend, raw enquiries, deduplicated enquiries, qualified enquiries, sales, net receipts and unresolved matches. Keep order and refund dates separately. An order paid this month can belong to a lead acquired last month, so cash-period totals and acquisition-cohort economics must be labelled as different views.

Educational example, not a customer case: a cohort costs 1,200 units, creates 60 raw enquiries and contains 10 duplicates. Of 50 unique enquiries, 20 qualify and 5 produce paid orders worth 2,000 in total. A subsequent refund of 400 reduces net receipts to 1,600. Raw CPL is 20, unique CPL 24, qualified CPL 60 and acquisition cost per paid order 240. None of those ratios alone measures profit because delivery costs and other variable costs are still missing.

Join records through a permitted stable identifier rather than a convenient name. A lead may create several orders; an order may have several payment events. Define the counting unit before joining, or a many-to-many join can multiply both lead and revenue counts. Keep a separate exception table for missing IDs, duplicates, conflicting campaign assignments and sales not matched to a known advertising lead. Do not assign unmatched revenue proportionally merely to make totals balance.

What a verified lead snapshot still cannot tell you

A second own-account snapshot, TRM Express in Meta, contains USD 197.98 of spend and 196 results labeled lead in stored rows dated September 28–October 3, 2026, in America/Chicago. These are six observed dates within the requested September 27–October 3 reporting window; the row aggregate alone does not establish why the first date has no row. The rows were fetched on October 4 at 08:01 UTC and verified in a read-only snapshot later that day. Attribution is labeled meta_account_default. The owner authorized publication.

USD 197.98 / 196 gives USD 1.01 per reported lead in those observed rows. It does not show how many people paid, their margin or repeat inquiries, and it must not silently represent seven fully observed dates. Revenue reconciliation needs confirmed CRM outcomes, coverage checks and a consistent join rule before profitability can be estimated. The honest memo is 'lead cost observed for the stored rows; sales profitability unknown.' The figure can be revised as attribution matures and is neither an AdAce performance improvement nor a benchmark for another business.

Where the reconciliation creates value

• A bridge exposes whether deterioration appears in acquisition, qualification, closing or repayment. The remedy can belong to sales or fulfilment rather than advertising.
• Separate the operational owner of each stage. Advertising owns spend lineage, sales owns status definitions, and finance verifies receipts and refunds.
• Preserve revision history. A later refund changes cohort economics without retroactively making an earlier snapshot a fabricated report, provided both snapshots are clearly dated.

Close a cohort without hiding exceptions

1. Agree on new-enquiry, qualified-enquiry, paid-order and refund definitions. State how repeat customers, tests and reopened enquiries are treated.
2. Select the cohort window and observation cutoff. Export only permitted fields needed for matching; aggregate or pseudonymous identifiers are preferable in working reports.
3. Deduplicate leads first, then join orders and payment events using explicit one-to-many rules. Reconcile counts and monetary totals at each join.
4. Compare matched and unmatched groups. Identify whether missing matches cluster around a campaign, a handoff change or a tracking interruption; log exceptions rather than hiding them.
5. Publish the cohort bridge beside a separate cash-period view. Use AdAce Ads for accessible stored advertising records; do not imply that an automatic CRM or payment integration exists unless it has been verified for your setup.

Limits of matching and revenue

Sources and further reading

• AdAce Ads: repeated lead clicks
• AdAce Ads: GA4 campaign outcomes