Reconcile CRM Amounts and Dates After Migration
Prepared with AI assistance by Alactic Editorial. Examples identified as hypothetical are illustrative, not customer results.
A CRM migration can preserve every record while changing the values that make those records useful. An amount may become a hundred times larger, a currency conversion may overwrite the original value, or an April date may become a March date. Matching record counts will not reveal these failures.
To reconcile amounts and dates, compare a fixed source extract with a matching target extract using stable record identifiers. Check amounts separately by currency, compare dates according to their original format and meaning, and inspect boundary cases where parsing or timezone changes are most likely to alter a value. Use totals to locate problems, then record-level comparisons to prove what changed.
The filled control sheet below shows how to do this with a small fictional dataset. The same structure can be expanded to a full migration without relying on a particular CRM interface.
1. Define what must be preserved before calculating differences
Start with a field-level preservation agreement. A comparison is only useful when the team agrees on what the target value should mean.
- Original amount: Preserve the numeric value and its currency code. Treat a converted reporting amount as a separate field, not a replacement for the original.
- Decimal precision: Document the permitted precision for each field. Do not silently round every amount to two decimal places merely because the comparison spreadsheet defaults to that display.
- Date-only value: Preserve the calendar date. A renewal date with no time component should not move to the previous day because it passed through a timestamp conversion.
- Timestamp: Preserve the instant. Different timezone displays can represent the same instant, so compare a common representation such as UTC.
- Unspecified local time: Do not assume a timezone without evidence. Flag it for a documented interpretation rather than presenting a guess as a faithful migration.
- Missing value: Keep missing, zero, and invalid values distinct. A blank amount is not automatically zero, and a blank date is not a default calendar date.
This agreement concerns preservation, not accounting policy or the design of future reporting metrics. If a transformation is intentional, name the rule and retain enough evidence to distinguish it from corruption.
2. Build a comparison extract that cannot hide the original
Keep an unchanged source extract and create a separate comparison copy. Preserve the raw amount and date strings alongside parsed values. Otherwise, a comparison tool can make the same parsing mistake as the migration and falsely report agreement.
Useful comparison columns include source record ID, target record ID, field name, raw source value, source currency, source format, expected normalized value, actual target value, difference, and exception reason. Add the migration batch and the relevant mapping version so a failed group can be traced to the transformation that produced it.
Use a fixed cut-off or pause relevant edits while taking the extracts. If source and target remain live, capture changes after the cut-off separately. Comparing different moments creates discrepancies that are real changes, not migration defects.
Verify that each source ID maps to exactly one expected target record before comparing values. Matching total counts can still conceal a missing record offset by a duplicate. Also check that the extract includes the stored value and currency, rather than only a rounded or localized display.
For amounts, use decimal-safe comparison tools where available. A displayed match is not sufficient if hidden precision differs. Conversely, do not classify a formatting difference, such as a grouping separator, as a numeric change.
3. Use this filled hypothetical control sheet
Fictional worked example: The following sheet uses six invented CRM records. All values, dates, identifiers, differences, and results are hypothetical. Each record has an original amount and one date field; some date fields are date-only values and others are timestamps.
The amount comparison preserves original currency. The date comparison uses a declared source format for each group, not the workstation's default locale. Source and target each contain six uniquely matched records, yet several values fail reconciliation.
CONTROL SHEET — HYPOTHETICAL FILLED EXAMPLE Scope: six records, M01–M06; one fixed migration batch Join check: 6 source IDs / 6 target IDs / 6 unique matches Difference convention: actual target minus expected source AMOUNT DETAIL ID Currency Raw source Expected numeric Actual target M01 EUR 1.234,56 1234.56 123456.00 M02 EUR 2.000,44 2000.44 2000.44 M03 USD 100.00 100.00 90.00 M04 USD 250.00 250.00 225.00 M05 JPY 12000 12000 12000 M06 JPY 8000 8000 8000 AMOUNT CONTROLS BY ORIGINAL CURRENCY Group Count S/T Expected sum Target sum Difference Result EUR 2/2 3235.00 125456.44 +122221.44 FAIL USD 2/2 350.00 315.00 -35.00 FAIL JPY 2/2 20000 20000 0 PASS Amount matches: 3/6 EUR diagnosis: M01 decimal separator lost during parsing. USD diagnosis: target values equal source × 0.90, while currency labels remain USD. Hypothesis: unintended conversion. JPY result: both record-level amounts match. DATE DETAIL M01 | DMY date-only | raw 03/04/2025 Expected: 2025-04-03 | Actual: 2025-03-04 | FAIL M02 | DMY date-only | raw 13/04/2025 Expected: 2025-04-13 | Actual: 2025-04-13 | PASS M03 | MDY date-only | raw 04/03/2025 Expected: 2025-04-03 | Actual: 2025-04-03 | PASS M04 | ISO date-only | raw 2025-01-01 Expected: 2025-01-01 | Actual: 2024-12-31 | FAIL M05 | Offset timestamp | raw 2025-04-01T00:30:00+02:00 Expected: 2025-03-31T22:30:00Z Actual: 2025-04-01T00:30:00Z | FAIL: +7200 seconds M06 | Offset timestamp | raw 2025-03-31T23:30:00-04:00 Expected: 2025-04-01T03:30:00Z Actual: 2025-04-01T03:30:00Z | PASS DATE CONTROLS BY SOURCE FORMAT Group Count S/T Value mismatches Result DMY date-only 2/2 1 FAIL MDY date-only 1/1 0 PASS ISO date-only 1/1 1 FAIL Offset timestamp 2/2 1 FAIL Date matches: 3/6 Disposition: do not accept this batch as value-reconciled. Next owner: migration operator to inspect parsing and mapping. Retest scope: all six records after correction, plus boundary set.
The sheet deliberately separates observations from explanations. The USD ratio is evidence of a possible conversion, not proof of the exact setting responsible. Inspect the transformation and rerun it against the source values before closing that exception.
4. Read amount controls without letting totals cancel errors
Never add different currencies into one preservation total. A combined EUR, USD, and JPY number has no useful meaning for checking original amounts. Compare each currency separately, and include a distinct group for missing or unrecognized currency codes.
Within each currency, collect more than the sum:
- Record count and count of populated amounts.
- Sum, minimum, and maximum.
- Counts of zero, positive, negative, and missing values.
- Count of record-level numeric mismatches.
- Count of unexpected currency changes.
These controls answer different questions. A sum can reveal a misplaced decimal, while minimum and maximum can expose an implausibly large value. A rise in zero values can reveal failed parsing that was replaced with a default.
Record-level comparison remains necessary because errors can cancel. In a separate hypothetical case, increasing one amount by 50 and decreasing another by 50 leaves the total unchanged. The group total passes even though both records are wrong.
Split failed currency groups further by source file, amount format, import batch, or mapping rule. In the worked example, only one EUR record fails. That pattern suggests a narrower parsing problem than a currency-wide scaling rule. The two USD records share a ratio, so inspecting a conversion or derived-field mapping is a more useful first step.
Do not adopt a broad percentage tolerance to make a migration pass. Where an approved transformation genuinely requires rounding, define the expected result per record and compare against that result. Original-value preservation should not need unexplained financial variance.
5. Compare dates by format and meaning, not appearance
A date can look valid while representing the wrong day. Grouping by source format makes these defects visible: day-month-year, month-day-year, unambiguous year-month-day, and timestamps with or without timezone information should not share one guessed parser.
In the example, 03/04/2025 is ambiguous in isolation. The declared DMY source format makes April 3 the expected value. A successful parse into March 4 is still a failure. By contrast, 13/04/2025 cannot be interpreted as month-day-year; a parser may reject it or use a fallback. This is why some correct dates do not validate the whole parsing rule.
For date-only fields, compare calendar dates directly. M04 loses a day, consistent with a date passing through a timezone-sensitive timestamp path. Confirm that cause in the transformation rather than assuming every one-day discrepancy has the same explanation.
For timestamps, normalize both sides to UTC before comparing. M06 crosses into the next UTC day correctly and should pass. M05 fails because its two-hour offset was effectively discarded, changing the instant by 7,200 seconds.
Add grouped distributions by month or day where operationally relevant, but use them as signals rather than final proof. If timestamps are grouped into days, use the same declared timezone on both sides. Comparing a source local day with a target UTC day creates false exceptions.
6. Add boundary checks that ordinary records will not exercise
A representative sample is useful for exploration, but preservation checks should cover all records where feasible. Supplement those checks with a deliberately difficult boundary set.
Hypothetical amount boundary examples include a comma decimal such as 1234,56, a grouped decimal such as 1.234,56, a negative amount, a true zero, a blank, and a value with more precision than the target field permits. Include the actual negative and grouping conventions found in the source, rather than assuming every parser supports them.
Hypothetical date boundary examples include:
05/06/2025, where either day-month interpretation produces a valid date.13/06/2025, which tests whether the declared DMY rule is followed.2024-02-29, which exercises leap-day handling.- Dates at month-end and year-end, where a one-day shift changes the reporting period.
- Timestamps immediately before and after local midnight, with explicit offsets.
- Local timestamps near a daylight-saving transition, if the source uses a named timezone with such transitions.
If a local time is ambiguous or nonexistent under its declared timezone rules, preserve the raw value and raise an exception. Do not silently choose an interpretation just to produce a populated target field.
7. Correct failed values without overwriting legitimate changes
When a group fails, retain the failed extract and pause acceptance for the affected scope. Separate the cause into parsing, mapping, conversion, precision loss, missing data, or post-migration edits. Each category calls for a different repair.
- Reproduce the defect: Run a small failed group through the transformation using the retained raw values.
- Correct the rule: Declare decimal separators and date formats explicitly; separate original amounts from converted fields; preserve date-only semantics.
- Prepare a controlled correction: List the exact record IDs, fields, current values, and proposed replacements. Retain before-images and obtain authorization for the update.
- Check intervening edits: If users have changed a target value since migration, resolve that conflict instead of blindly replacing it.
- Retest broadly: Compare all records affected by the changed rule, including those that previously passed. Repeat the boundary set.
A full reload may be simpler before users begin working in the target. A targeted repair may be safer after legitimate edits exist, but requires stronger ID matching and conflict handling. Neither approach is complete until fresh target extracts reproduce the expected controls.
8. Make acceptance measurable
For the next migration rehearsal, produce one control sheet for each migrated amount and date field. Record matched IDs, amount totals by currency, date mismatch counts by source format, and the outcome of boundary checks.
Use explicit acceptance conditions: every in-scope record is matched uniquely; original amounts and currencies match their expected values; date-only fields preserve their calendar dates; timestamps preserve their instants at the agreed precision; and unresolved exceptions are listed with an owner and disposition.
Your next action is to select one important amount field and one important date field, freeze a comparison scope, and populate this sheet from both extracts. Report the number of unexplained value mismatches alongside the record count. That turns “all records arrived” into a testable statement about whether their meaning survived.
Put the next conversation to work.
Explore Alactic Communications for WhatsApp conversations, campaigns and AI-assisted workflows. Choose the product and plan that fit your team.
Explore Communications