---
id: agencias/receita-carteira-agencia
industry: Marketing agencies
usage: specific
effort: medium
grain: Client x month, and client x service line x month
---

# Agency revenue and portfolio

How many paying clients the agency has, how much each one pays per month and what came in, grew, shrank and left, with contracts matching the service invoices.

## Executive summary

**For:** Partners and finance teams at agencies that sell monthly retainers, one-off projects and media.

**What it solves**

- One answer to how many active clients the agency has and what the MRR is.
- Every MRR change has a name: new, expansion, contraction, churn or reactivation.
- The agreed contract matches what was invoiced, and whatever does not match shows up on a list.

**Why it matters:** At an agency, the client is both the unit of revenue and the unit of delivery. Without a written active-client rule, each team counts a different number, MRR includes pass-through media and one-off projects, and churn vanishes from history when the registry changes. It is the basis for profitability per client and for cash forecasting.

**Business questions it answers**

- How many active paying clients do we have today, and what is the MRR?
- How much MRR did we lose to churn this quarter, and how much was just scope reduction?
- What is the NRR for the last 12 months?
- Which service line (media, content, CRM, BI) concentrates recurring revenue?
- Which clients were invoiced without a registered contract, and which contracts never became an invoice?
- How many clients are in their first quarter of contract?

**What you need**

- The contract control connected to Nekt (spreadsheet, portal database or ERP)
- The service invoices from the finance ERP
- The active-client and contract-type rule agreed with the partners

**What you get**

- A client x month table with recurring MRR, one-off equivalent, phase and movement
- Active portfolio, NRR and GRR by month, quarter and 12 months
- A list of unmatched items between contracts and invoices
- An AI agent that answers about the portfolio with the right rule

## Detailed

### The problem

The contract control stores each client's current value and overwrites it when the scope changes. The invoice goes out under the legal name, which does not match the contract name. Whoever sums the whole registry mixes retainers with one-off projects, exempt clients and the agency itself; whoever sums the invoices mixes fees with pass-through media. The result is a different MRR at every meeting and a churn that erases the client from every past month.

### Business rules

1. **Active client is a written rule.** Active paying client = active status and a recurring contract type (retainer), excluding one-off projects, exempt clients and the agency's own account. The current portfolio is broader: active, onboarding and paused. The question 'how many clients do we have' gets the strict definition; the broad one always comes with its name. Late payment does not change client status: that belongs to finance. (Avoid: Counting every row in the registry or every task in the client list.)
2. **Recurring, one-off and pass-through in separate columns.** MRR is monthly recurring retainer only. One-off projects, setup and training stay out; when they need comparing, they go in as a 12-month moving sum divided by 12, in a separate column, never added to MRR. Pass-through media budget and third-party production billed on the client's behalf are not agency revenue; media commission is. (Avoid: Summing the whole invoice, with pass-through budget and one-off projects, as MRR.)
3. **What adds up is the month's installment.** Sum the installment amount of each accrual month, never the total contract value or the header value repeated on each row. Semiannual or annual contracts are divided by the months in the cycle. Contracts with no monthly value filled in are counted and shown, so nobody reads the MRR as complete. (Avoid: Summing the contract value across contracts and months.)
4. **Movement from the client's month-end snapshot.** Compare the client's recurring MRR in the month with the previous month. No MRR before and some now: new, or reactivation if they have paid before. Some before and none now: churn. Higher: expansion. Lower and above zero: contraction. In agencies' churn spreadsheets, 'partial churn' is contraction and 'scope swap' is not churn: the client stays. (Avoid: Counting each row of the churn spreadsheet as a lost client.)
5. **Churn does not erase the past.** A client marked as churned was still a client in the reference month if the exit date is on or after the first day of that month. Date precedence: churn date, then contract end, then status change date. With no date at all, keep the client. Active contracts run up to the current month; do not project the future as actuals. (Avoid: Filtering today's active status to compute past months' portfolio.)
6. **Contract and invoice match on CNPJ.** The invoice uses the legal name and the contract uses the trade name. Match on the recipient's CNPJ (company tax ID); when only a name exists, normalize it (lowercase, no accents, letters and digits only) and keep an alias table for groups with several CNPJs. Publish the list of both unmatched sides: invoices without a contract and active contracts without an invoice. In a real base, the residue was close to 4% of 12-month billing. (Avoid: Joining contract and invoice by name and silently losing whatever did not match.)
7. **Only valid invoices from outside the group.** Use invoices with issued status, not canceled, and with a value above R$ 1 to drop test invoices. Exclude the group's own CNPJs (intercompany). When the agency is a client of another company in the group, separate the row without deleting it. (Avoid: Summing canceled invoices and charges between companies in the same group.)
8. **Future accrual months stay flagged.** Recurring contracts and charges are generated through the end of the term, sometimes more than a year ahead. Flag future-month rows instead of cutting them, and filter on read. Summing the whole table adds the schedule to what was delivered. (Avoid: Summing every installment in the table as revenue.)
9. **An MRR drop with no change in clients is a registry edit.** The registry only holds the current value and overwrites it when scope changes. Save a monthly MRR snapshot per client or read the adjustment history. When MRR falls and the client count does not move, check the history before calling it churn: it is usually a registry correction. (Avoid: Reporting an MRR drop as churn without checking the adjustment history.)
10. **Client phase by contract month.** Count the client's month of life from the first month with recurring MRR. Months 1 to 3 are onboarding, 4 to 12 first year, 13 onward renewed. Contracts with no start date use the client's first contract. (Avoid: Using the current contract date, which resets on each renewal.)

### How each status enters the metrics

Status and contract type from the client registry.

- **active + retainer** → Paying client: counts in MRR and in the churn base
- **onboarding** → Current portfolio; strictly active once the retainer is billed
- **paused** → Current portfolio, outside strict active
- **churned** → Out of MRR; counts until the exit date (`churn_date > contract_end_date > status_changed_at`)
- **one-off or closed** → Out of MRR and churn; one-off equivalent kept apart
- **exempt or the agency itself** → Out of revenue; inside operational counts

### Definitions

| Metric | Definition |
|---|---|
| Active paying client | client with active status and a recurring contract (retainer) on the last day of the month, excluding one-off, exempt and the agency itself |
| Current portfolio | active, onboarding and paused clients on the last day of the month |
| Client MRR | sum of the accrual month's recurring installments, net of discounts, with semiannual and annual cycles divided by the months in the cycle |
| One-off MRR equivalent | sum of one-off projects invoiced in the last 12 months / 12, by client and month, outside MRR |
| Churned MRR | previous month's MRR of clients that end the month with zero recurring MRR |
| Contraction | previous MRR - current MRR, when current is lower and above zero; includes the spreadsheet's partial churn |
| GRR | (starting MRR - contraction - churn) / starting MRR, only for clients in the starting base |
| NRR | (starting MRR + expansion - contraction - churn) / starting MRR, without new and reactivated clients |
| Revenue by service line | 12-month billing per CNPJ split by the share of each service line in the contract's MRR |

### Pitfalls

- **High MRR from a stale registry.** The registry total was above the real value because a contract had changed scope and the old value was still there. It was not churn, it was the registry. How to avoid: Save the monthly snapshot and check the adjustment history.
- **Client counted three times.** The renewals consolidation combined retainer contracts, separately contracted solutions and manual renewals. A client with a retainer and two solutions appeared three times. How to avoid: Count distinct clients, never contract rows.
- **A new column that never arrived.** A column created in the portal database did not appear in Nekt after a full sync, because the source's field selection had been saved in its configuration. How to avoid: Redo the source's column selection step and run it again.
- **Billing measured by a stage flag.** A sale's 'invoiced' flag switched to 'paid' after payment. Measuring billing by it returned only a small fraction of the sales that had been charged. How to avoid: Invoiced = has a charge, whether invoiced or paid.
- **Half the revenue vanished in the join.** Revenue was joined to deliveries by client and month with an inner join. More than half of the money sat in months with no recorded delivery and disappeared without warning. How to avoid: Full join, with flags for which side exists on the row.
- **The same charge in two systems.** A charge appeared in one unit's billing ERP and in the other unit's finance system. Summing both tables duplicated revenue. How to avoid: One official source per business unit, never the sum of both.

### Acceptance test

- [ ] The count of active paying clients and the month's MRR match the contract registry on the same date (zero difference).
- [ ] For each client and month, previous MRR + new + reactivation + expansion - contraction - churn = current MRR.
- [ ] 12-month billing allocated by client plus the unmatched list adds up to the total of valid invoices in the ERP.
- [ ] No churned client disappears from the months before the exit date.
- [ ] GRR never exceeds 100%.

### Context document

```text
Agency revenue and portfolio
- Active paying client = active status and a recurring contract (retainer). Out: one-off, closed, exempt and the agency's own account. 'How many clients do we have' gets this definition.
- Current portfolio = active + onboarding + paused. Use it only when asked for the portfolio.
- MRR = sum of the accrual month's recurring installments. One-off enters only as 12-month total / 12, in a separate column. Pass-through media budget and on-behalf billing are not revenue.
- Movement per client and month: new, reactivation, expansion, contraction, churn or flat, from the MRR difference against the previous month. Partial churn is contraction; a scope swap is not churn.
- A churned client counts in a month until the exit date (churn date, then contract end, then status change).
- Contract and invoice match on CNPJ. Whatever does not match is on the unmatched list; do not estimate.
- Future-month rows are schedule, not revenue.
- An MRR drop with no change in client count: check the adjustment history before calling it churn.
Client exceptions:
- <client>: group with CNPJs <list>, consolidated as one client.
- <client>: annual contract of <value>, MRR = value / 12.
```

### Variations

- **Client base in the task manager:** When each client is a top-level task in a list (ClickUp), count only tasks without a parent, not archived, by task status.
- **Contracts read by AI:** PDF contracts extracted with AI for scope, MRR and term, always checked against the invoice before entering MRR.
- **Revenue by service line:** Split each CNPJ's billing by the share of each service line in the contract's MRR, with a 'no breakdown' row for clients without a split.