Customer analytics data sources: fields and matching checks

Customer analytics data sources are useful when their account identifiers, dates, and record definitions support the question you want to answer. Check those fields before combining customer, transaction, CRM, and service exports; otherwise a clean-looking total can hide missing accounts or duplicated revenue.

The job is to establish what each file proves. A service request shows recorded support activity, while an invoice shows a billed sale under the finance team's reporting policy. Matching the customer does not make those events equivalent.

Choose the smallest set of sources that answers the question

Write the decision first: compare billed revenue by segment, review activity before renewal, or measure support demand by account. Then choose the exports and ask each owner for the reporting definitions.

For context on which commercial questions these files answer together, see sales intelligence from data you already hold. Keep the present exercise focused on whether the sources can be matched reliably.

Preserve identifiers and define the reporting period

Keep account IDs as text so leading zeros survive. Preserve the original source ID alongside any approved replacement. If departments use different identifiers, request a mapping owned by those departments rather than guessing from names.

Use replacing client names with IDs when names are unnecessary for the analysis. An account mapping should distinguish a parent company from its billing entities; otherwise separate customers can collapse into one.

For the worked check below, use posted transactions from July 1 through September 30, 2026. Put their actual spreadsheet dates in column C. Test the period in H2 and fill down:

=AND(ISNUMBER(C2),C2>=DATE(2026,7,1),C2<DATE(2026,10,1))

Every included row must return TRUE. A text value that looks like a date needs correction before inclusion. Also record the export cutoff: a later credit can change the quarter's financial comparison even if the original sales file is unchanged.

Test each transaction against the customer master

Suppose a US supplier has these customer-master rows in a worksheet called Master, cells A2:B4:

Its transaction export has five rows. Columns A through D hold the original fields; E and F are the checks you add.

The dashes display empty ID or check cells. T03 is a credit and keeps its negative sign. In E2, count master matches, then classify the result in F2:

=IF(B2="","",COUNTIFS(Master!$A$2:$A$4,B2))

=IF(B2="","Missing ID",IF(E2=1,"Matched",IF(E2=0,"Unknown ID","Duplicate master ID")))

Fill both formulas down through row 6. Microsoft confirms that COUNTIFS counts records meeting specified criteria. Counting matches before retrieving attributes prevents an ambiguous master ID from silently receiving whichever segment appears first.

Also test each transaction key in G2 with =COUNTIFS($A$2:$A$6,A2). Every key here returns one. For invoice-line exports, use the true line key, since repeating an invoice number across legitimate product lines is expected.

Reconcile exceptions as well as matched revenue

Summarize F2:F6 by status. The matched revenue formula is:

=SUMIFS($D$2:$D$6,$F$2:$F$6,"Matched")

Microsoft's guide to adding worksheet values covers conditional sums for this kind of grouped total. Covirage is not affiliated with Microsoft.

Three matched rows divided by five source rows gives a 60.0% row match rate, rounded to one decimal place. The monetary reconciliation is $1,900 plus $300 plus $200 equals $2,400. Both checks matter: a high row match rate could still leave the largest invoice unmatched.

C03 has no transactions in this extract. Retain it when the question concerns all eligible customers, rather than treating absence as evidence that it never buys. Once matching is sound, customer analytics explains how to compare purchase behavior.

Where it goes wrong

• Matching two detailed files directly. An account with several invoices and several tickets can multiply the invoice rows. Summarize each source separately at the agreed account-period level before comparing measures.
• Using today's segment for an old period. Decide whether the question needs the segment at the time or the current sales territory, and label it accordingly.
• Dropping exceptions. Unmatched records must remain in the reconciliation even when they cannot enter a segment comparison.
• Mixing currencies or date meanings. An opportunity amount and posted USD revenue are different measures; keep their units and event dates explicit.

Check your own sources in Covirage

Bring a sample of your own customer and transaction exports with the identifiers and dates needed for the question; 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 file.

See Excel analysis for the supporting workflow.