---
id: educacao/conciliacao-crm-sistema-academico-financeiro
industry: Education
usage: widely used
effort: high
grain: Won deal, enrollment and contract
---

# Sales, enrollment and payment reconciled

Every CRM sale linked to the official enrollment and to what was paid, even when the person who negotiated, the person who pays and the person who studies are different people.

## Executive summary

**For:** Schools, colleges and education companies where the sale happens in the CRM, the enrollment in the academic system and the billing in the finance system.

**What it solves**

- Every CRM sale has its matching enrollment and payment, or an explicit reason why it does not.
- Enrollments with no CRM deal show up instead of disappearing from the funnel.
- The source of each enrollment is attributed by evidence, with the same person identity across all three systems.

**Why it matters:** In education, the lead, the financial guardian and the student are often different people, and each system stores a different one. Without reconciliation, the CRM says a sale was made, the academic system says something else and finance matches neither. This is the foundation for CAC per enrollment, salesperson commissions and any reliable answer about acquisition.

**Business questions it answers**

- How many enrollments this semester have no deal in the CRM?
- How many sales this month have not yet become enrollments in the academic system?
- Which campaign and channel did the August enrollments come from?
- Why does this customer's payment differ between the CRM and finance?
- How much was paid by financial guardians other than the student?

**What you need**

- CRM, academic system and finance connected to Nekt (the academic data can come from a prepared query or a spreadsheet)
- An academic key in the CRM, such as the RA (student ID number), filled in before the sale is marked as won
- The school's rule: one sale generates one enrollment, or several

**What you get**

- A table per won deal with the linked enrollment, link method and reconciliation status
- A person and customer dimension with a validated document and identifiers from all three systems
- An acquisition dashboard with enrollment source and pending reconciliation items
- An AI agent that explains the mismatch in a specific case

## Detailed

### The problem

The CRM records the deal with the guardian, the academic system records the student and finance records who paid. There is no common key, and the linking fields are filled in by hand. Matching by name or by any document attaches sales to the wrong customer; matching by person splits the contract in two when a parent pays for a child.

### Business rules

1. **Each system answers one question.** CRM = sales process (deal, salesperson, stage). Academic system = official enrollment, student, class, contract and status. Finance = receivable, payer and payment. Marketing = source and campaign. One does not prove the other: a won deal does not prove an enrollment, and there can be enrollments with no CRM deal. (Avoid: Counting enrollments from won deals in the CRM.)
2. **Anchor on the contract, not the person.** Link accounts receivable by contract or sale id, never by person. The payer (father, mother, company) is often different from the account holder and the student; joining by person and contract splits the same contract into two rows. Expose the payer as a column. (Avoid: Joining finance and sales by the person's CPF (individual tax ID).)
3. **Academic key only when it is unique.** If the CRM stores the student's RA in a deal field, link the academic data only when the RA is unique in both sources. RA is text: keep leading zeros and never convert it to a number. A repeated RA stays unlinked, with a reason. (Avoid: Converting the RA to a number and joining even when it repeats.)
4. **Key cascade with a recorded method.** Try in order: the deal id stored in finance (for example, `numero_documento` in Omie), then a validated CPF or CNPJ (company tax ID) with a compatible product, then normalized email and phone. Record on each row which method matched. (Avoid: A single join by name or by email.)
5. **Documents only with a valid length.** Strip everything that is not a digit. Only a CPF with 11 digits or a CNPJ with 14 becomes a key. Any other length is a typo: treat it as no document and do not try to match, so the sale does not stick to the wrong customer. (Avoid: Matching on the raw document, with dots, dashes or missing digits.)
6. **One contact standardization for everyone.** Email: lowercase, no spaces. Phone: digits only, and remove the 55 country code only when more than 11 digits remain. Use a single phone reference table per person; two different logics have already cut off the area code thinking it was the country code. (Avoid: Each query with its own phone cleanup.)
7. **Identity is not sale reconciliation.** Knowing it is the same person does not mean it is the same sale: a student can have several enrollments and a guardian several children. Keep one row per won deal and separate the identity link (it is the same person) from the reconciliation (it is this enrollment, from this sale). (Avoid: Joining all of a person's enrollments to each of their sales.)
8. **Explicit reconciliation status.** Classify each case: reconciled, CRM only, academic only, finance only or ambiguous. An enrollment with no deal is not automatically an error (re-enrollment, walk-in, corporate); it is a category for the team to review. (Avoid: Discarding what did not match.)
9. **Source by evidence, single source.** The enrollment source comes from the strongest evidence for the same person: marketing UTM, then the support tag and channel, then the CRM source field. Recover UTMs embedded in `traffic_source` and in encoded payloads. When the source only stores the entry source, the model is single-touch, not multi-touch: state that. (Avoid: Using only the hand-filled source field in the CRM.)
10. **Standardize source and channel with references.** Source has dozens of spellings for the same event. Use a mapping table from raw value to standardized source and another from source to expected channel, which audits the channel chosen by hand. Fields that mix several kinds of information stay out of attribution. (Avoid: Grouping source by the raw text.)

### Which system answers what

And which key links each one.

- **Marketing** → Lead source, campaign and UTM (`normalized email and phone`)
- **Support (WhatsApp)** → Conversation tags and channel (`phone without country code`)
- **CRM** → Deal, salesperson, stage and amount sold (`deal id`)
- **Academic system** → Official enrollment, student, class and status (`RA (text, leading zeros)`)
- **Finance** → Receivable, payer and payment (`contract id or numero_documento`)
- **Person or customer dimension** → One validated document and the ids from all three systems (`CPF 11 or CNPJ 14 digits`)

### Definitions

| Metric | Definition |
|---|---|
| CRM sale | distinct deal with won status, on the close date |
| Official enrollment | academic system record with an active enrollment status in the academic term |
| Reconciliation rate | sales in the period with reconciled status / sales in the period |
| Enrollment with no deal | official enrollment in the period with no won deal linked by any method |
| Sale with no enrollment | won deal with no linked enrollment by the end of the academic term |
| Payer other than the account holder | contract whose main payer in finance has a document different from the account holder's or the student's |
| Enrollment source | channel and campaign from the person's strongest evidence, with the attribution method in a column |

### Pitfalls

- **Mistyped document.** Hundreds of deals had the CNPJ field filled in with the wrong length and matched the wrong customer. How to avoid: Only accept 11 or 14 digits after cleanup; everything else becomes no document.
- **Contract split in two.** When finance was joined by person, thousands of contracts where the guardian paid for the student became two rows and revenue doubled. How to avoid: Link accounts receivable by contract id only.
- **Payment with no linking id.** Some finance receivables had no contract number, and matching by document alone picked the wrong installment. How to avoid: Match on document + payment date + issue date + amount, and exclude documented manual outliers before the trusted layer.
- **Gateway outside Nekt.** Customers billed through an unconnected gateway showed up as unpaid and triggered false alerts. How to avoid: Check the deal's payment platform field before treating a missing payment as a problem.
- **Different email at capture and at purchase.** People who used one email on the form and another at purchase showed up as sales outside the funnel, understating the return on lead capture. How to avoid: Keep a dictionary of identifiers per person (emails, CRM ids and sales platform ids) and state the limit.
- **Campaign with no cost.** Hundreds of campaigns existed only in lead UTMs, with no spend recorded on any media platform. How to avoid: Combine platform campaigns with UTMs in a dimension with a synthetic key and flag which ones have no cost.

### Acceptance test

- [ ] Every won deal in the period appears exactly once, and the sum of reconciliation statuses equals total sales.
- [ ] Every official enrollment in the period has a status: linked to a sale or enrollment with no deal.
- [ ] No link uses a document with a length other than 11 or 14 digits.
- [ ] The finance total by contract equals the finance total for the same slice, including contracts where the payer differs from the account holder.
- [ ] Every RA keeps the same number of characters as in the academic source, with leading zeros.

### Context document

```text
Sales, enrollment and payment reconciliation
- CRM = sales process. Academic system = official enrollment, student, class and status. Finance = receivable, payer and payment. Marketing = source and campaign.
- A won deal does not prove an enrollment. There can be enrollments with no CRM deal.
- Lead, financial guardian and student can be different people. Do not call a CRM contact a student.
- Finance links to the sale by contract id, never by person. The payer goes in its own column.
- RA (student ID number) is text: keep leading zeros. Academic link only with an RA that is unique in both sources.
- Link order: deal id in finance, validated document with compatible product, normalized email and phone. Record metodo_match.
- Valid document: CPF (individual tax ID) with 11 digits or CNPJ (company tax ID) with 14 after cleanup. Other lengths = no document.
- Phone: digits only; remove the 55 country code only when there are more than 11 digits. Email lowercase, no spaces.
- Reconciliation status: reconciled, CRM only, academic only, finance only, ambiguous.
- Enrollment source: marketing UTM, then support tag, then CRM source field. Single-touch model, not multi-touch.
Per-client exceptions:
- <client>: the RA is stored in the deal field <field> before the sale is marked as won.
- <client>: the deal id is typed into the finance field <field>.
- <client>: one sale generates <n> enrollments (one per class or course unit).
```

### Variations

- **Digital products:** Sales platform instead of the academic system: person = normalized email, with a dictionary of emails and platform and CRM ids.
- **Study abroad and travel:** Contract with several items (course, lodging, insurance, flights): count distinct contracts and link finance by contract.
- **B2B and corporate:** Customer = validated CNPJ, canonical name from finance, then from billing, then from the CRM.