Agency revenue and portfolio

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.