Multi-tenant agency: consolidated media and CRM per client

An agency does not manage one business, it manages dozens at the same time. Each client has their Meta Ads account, their Google Ads account and the your CRM, each one in a different panel, with different field names and separate login. The traffic manager opens ten tabs to create a report, the data never matches between platforms, and at the end of the month no one can quickly answer “how was client X’s week”. This playbook shows the pattern Nekt uses to transform this patchwork quilt in a single, multi-tenant base, with media and CRM consolidated by customer, which serves reporting, internal dashboard and AI agent from the same model.

1. Context: N customers, same challenge repeated

What defines the operation of an agency is the horizontal scale: the same work, replicated for many clients. Each customer usually has the same triad of sources, Meta Ads, Google Ads and a CRM, but isolated from each other. The challenge has two faces that need to be resolved together.

• Vision per customer without turning into chaos. The agency needs look at each client in a consolidated way (how much they invested, how many leads generated, which channel yielded the most) without keeping a report handcrafted by a client that no one can keep when their portfolio grows from ten to fifty accounts.
• Real multi-tenant. Each customer's data need to be isolated. Customer A can never see the number client B, and a report from client A can never mix, for accident, a client campaign C. Isolation is not a detail aesthetic, it is contract and trust.

The good news is that, because it is the same pattern repeated, the solution also is replicable: you model it once, with the customer as a dimension of first class, and each new account falls into the same model instead of become another loose report. It is this pattern that the next steps detail.

2. Typical agency sources

The data sources are repeated from client to client, with small variations. Each one marked with the customer it belongs to.

• MetaAds. Campaigns, ad sets, spend (the field is usually called spend) and results (leads, messages, purchases). It is the most common paid media source in the portfolio of an agency.
• Google Ads. Cost (here the field is usually called cost, no spend), impressions, clicks and conversions. Tells the same story as Meta, but with names and different units.
• Customer CRM (e.g. HubSpot, RD Station, Pipedrive). What happens after the click: leads that came in, deals opened, closed value. It’s what connects investment in media to results real business of the client.
• Goal worksheets (sometimes). Goals of investment, leads or billing defined in the contract with the customer. They serve as an additional source to compare what was done with the combined.

3. Recommended modeling: multi-source consolidation at Gold

The objective is to reach a central table in the layer Gold:o consolidated media, with one line per customer, channel and day. The raw sources of each customer enter the Bronze, each data source is clean and typed separately in Silver, and the standardized union of them all lives in the Gold. It is the consolidation pattern multi-source, applied to the multi-tenant reality of an agency.

The common key: customer, channel and day

All consolidation depends on finding the key that joins sources different. Here she is the combination customer + channel + day. The customer says who the data belongs to (the multi-tenant dimension), the channel says which platform it came from (Meta, Google, and so on), and the day gives the temporal grain. With this key, a Meta campaign and a Google campaign on the same day, for the same client, saw two comparable rows from the same table, rather than two separate reports.

Standardization across platforms

Before stacking the sources, the fields need to speak the same language. O spend of Meta and the cost from Google saw a single column investimento. The leads of one and the conversions from the other they saw leads or conversoes, as defined by the agency. The dates, that each platform delivers in a format, they become a column data for real. This translation is the heart of modeling of agency: without it, you cannot add total investment or compare channels.

4. Multi-tenant: isolate without duplicating work

Multi-tenant is serving many customers from one base shared, keeping each person isolated. There are two paths, and they don't are exclusive.

• Customer column at all (recommended). A single modeling, with the column cliente present in all tables, from Bronze to Gold. Isolation comes at the time of consumption: all report, dashboard, or query filters by cliente. One modeling, many clients, maintenance in one place.
• Naming or prefix per client. Sort by name, for example a set of tables or a prefix per customer (silva_consolidado, norte_consolidado). Give stronger physical insulation, but multiplies maintenance: each model change needs to be replicated in each client. It makes sense when there is a contractual requirement for total separation, not as a standard.

In practice, most agencies do well with the client column and filter discipline in consumption. The prefix per customer is reserved for cases that actually require physical separation.

5. Use case: report, dashboard and agent on the same model

With the consolidated Gold, three consumptions are born based on the same model, each serving a specific public different.

Automatic report per customer. Instead of the manager assemble ten reports by hand, each client gets their own, generated from from the same table, filtered by the column cliente. Investment, leads, revenue and return by channel, updated alone. Adding a new customer means pointing the report to one more value of cliente, don't build from scratch.

Internal agency panel. The vision that the manager of the agency didn't have: all the clients side by side, whoever is performing, who is burning money with no return, which channel yields more in the entire portfolio. It is the portfolio view that only exists because data is in a single model rather than spread across panels isolated.

An AI agent, via MCP, answers questions in natural language about the same consolidated: "which campaign yielded more this week for client X", "how much client Y invested in Google this month", "which channel has the best cost per lead in the Store Silva." Whoever needs the answer asks and receives the number immediately, without waiting for an analyst to open ten tabs.

What makes the agent trustworthy is the Semantic Layer describing consolidated: what is it each column, which investimento already unite spend and cost, that every query is always per client, which each channel means. Without this description, the AI guesses and, worse still, multi-tenant context, can mix clients. With it, the AI responds with the same rules that the report and the panel use, and all three match.

6. Implementation checklist

The end-to-end path, in the order that usually makes sense. Each step is based on the previous one, so it's worth following it from top to bottom.