TidyRecon Open the app

How to reconcile CRM customers with paid subscriptions

Export CRM customers and paid subscriptions, drop both into TidyRecon, and the tp-08 recipe matches them on email. Next month you rerun it.

Open in app Download sample and the paid subscriptions
1 of 2 matchedtp-08 recipe on these samples
Matched1
Only in CRM customers1
CRM customerstp-08-left.csv · 2 rows
email
a@example.com
b@example.com
Paid subscriptionstp-08-right.csv · 1 rows
customer_email
a@example.com

The files you need

CRM customers
Columns email
Paid subscriptions
Columns customer_email

Other columns can stay in the file. Only the ones above are checked, so an extra column never stops a run.

How the rows are matched

A row matches when CRM customers “email” equals paid subscriptions “customer_email”.

Every row ends in one class: Matched, Values differ, Only in the CRM customers file, Only in the paid subscriptions file, Ambiguous, or Duplicate in file.

Steps

  1. Export both files for the same period, as CSV or Excel.
  2. Open the tp-08 template in the app and drop the CRM customers on the left, the paid subscriptions on the right.
  3. Clean anything the preview shows: stray spaces, mixed date formats, a renamed header. Each fix becomes a step.
  4. Save the recipe and press Run. Read the rows that are not Matched first.
  5. Next month, drop the new exports and press Run again. Last month's open rows come back with their age.

Common problems

  • A key has spaces or a different case. “SO-1003” and “ SO-1003” are different strings. Add a Trim step, and an Upper-case step if one system lowers its codes.
  • The export renamed a column. The run stops and names the missing column. Rename it back in the export, or add a Rename step.
  • A row appears twice in one file. It is marked Duplicate in file rather than matched twice.

Transcript

The short video for this guide is being recorded. Its full transcript will be published here with it.

Next guide: How to compare data before and after a migration

A painted scene of Jakob Fugger standing beside his seated bookkeeper, who writes in an open ledger in front of drawers labelled with cities.
Jakob Fugger and his bookkeeper, Augsburg, about 1517. Each drawer holds the accounts of one branch.
A handwritten merchant's ledger page from September 1833, with customer names on the left and columns of amounts on the right.
A merchant's ledger, Pittsboro, North Carolina, 1833. Each line was once ticked against another book by hand.