Profitability per client

Ad delivery and boosting billed on the client's behalf, pass-through media budget and production paid to third parties stay out of both sides: they are neither revenue nor cost. Media commission is agency revenue; confirm it through the finance subcategory and by the absence of payments to the media vendor in the same base. Also show the margin without media commission, which measures how much the agency depends on it.

Allocate the month's real operating cost across the deliverables (pieces, jobs) recorded in the month. Where a deliverable has a list price, use it as a complexity weight within the priced subset; unpriced deliverables get the simple allocation. The sum of allocated cost ties exactly to the month's cost. Record each row's route (weighted or simple). Partner draws are not cost and come out after margin.

Treat a month as closed when its number of expense entries reaches at least 30% of the median of recent months. An open month gets a null cost with a written reason, never zero: zero would make the current month's margin explode.

Publish the coverage of each effort measure: hours logged over deliverables, actions over accounts. If hours cover a small fraction of deliverables, they cannot support cost per hour. Hourly cost and allocated cost sit side by side for comparison and are never added: they are the same cost by two paths.

Media agencies without timesheets can measure effort through the ad accounts' activity log: count only actions by people (actor_name different from Meta itself) and split ad creation, status changes and budget changes. Convert with a declared factor (for example, 1 action = 10 minutes) and call the result estimated hours until real hours exist.

The number of tasks, messages and actions measures operational energy, not results, and logged hours are not capacity. Read volume next to the client's results (leads, cost per result) and pressure signals (stalled tasks, tasks with no owner). Never rank people by count alone.

In finance, the client appears under the legal name, which often contains none of its brands. Match revenue and cost on CNPJ (company tax ID), or CPF (individual tax ID) for individual clients. When a source only has a name, normalize it (lowercase, no accents, letters and digits only) and keep an alias table. Deliverables with no identified client go on their own flagged row and never mix with a real client.

Use a full join between cost and revenue by client and accrual month. When revenue is missing in a month, the margin is not the negative cost; when deliverables are missing, it is not the whole revenue: the monthly margin is null, with the missing side written down. Because the deliverable lands in one month and the invoice in the next, the client ranking uses the margin summed over the whole window, not the monthly one.

Classify each time entry by the department of whoever logged it (creative, media, content, planning, production) and the free-text description by rules: client meeting, internal meeting, changes and jobs. Rework requested by the client and internal rework are different codes and are not added together as one thing.

The margin table exposes how much each relationship earns and which ones lose money. It never goes to the client portal or into the context of an agent that serves clients. Named payroll enters aggregated by month, so no employee data reaches the table.