How to combine agency client reports into one spreadsheet

Why agency roll-ups break

Most agencies produce one report per client, often from different templates built by different account managers over several years. Each one is fine on its own. The trouble starts when a director asks a portfolio question — which clients are over budget, which channel has the best cost per lead across the book, which accounts fell off last month — and the answer lives in 30 workbooks with 30 slightly different layouts.

• Different metric names: “Conversions”, “Leads”, “Results” and “Purchases” may mean the same thing for two clients and different things for two others.
• Different grains: one report is monthly by channel, another is weekly by campaign.
• Different currencies: an international client reports in EUR or USD while the rest are in GBP.
• Wide layouts: months run across columns, which looks good in a client deck but can't be stacked.

The fix is a single long table with one row per client, month and channel. Everything else — pivots, charts, rankings — is built from that table, not from the client files directly. This is the same pattern as combining data from multiple sources, applied to a portfolio.

Step 1: agree a common shape

Pick the smallest set of columns every client report can supply. For most performance agencies that is:

Write down, per client, what “conversions” means. A lead-gen client's form fill and an e-commerce client's purchase can sit in the same column, but you should never add them up across clients or rank them against each other without that caveat.

Step 2: unpivot wide reports

If a client report has months across the top (Jul, Aug, Sep as columns), turn it into rows first. In Excel, Power Query's Unpivot Other Columns does this: select the label columns, right-click, choose Unpivot Other Columns, and you get attribute/value pairs you can rename to month and spend. In Google Sheets you can do it by hand for small reports, or with a FLATTEN/SPLIT formula pattern for larger ones.

Don't skip this. A wide layout can't be stacked with another client's wide layout unless both have exactly the same months in exactly the same order.

Step 3: stack the files with a client column

With every report in the same long shape, stacking is mechanical. Options, roughly from smallest to largest portfolio:

1. Copy and paste into a master sheet, filling the client column as you go. Fine for five clients, painful for fifty.
2. Power Query from a folder: save each client's cleaned export into one folder and use Get Data → From Folder. The Source.Name column gives you the file name, which you can map to a client name. See combining files from a folder with Power Query.
3. VSTACK in Microsoft 365 or Google Sheets if the cleaned tables live in one workbook, with a helper column holding the client name.
4. SQL if the files are CSVs: load each one and UNION ALL them with a literal client value.

SELECT 'Northwind' AS client, month, channel, spend, conversions, revenue FROM northwind
UNION ALL
SELECT 'Brightside', month, channel, spend, conversions, revenue FROM brightside
UNION ALL
SELECT 'Harbour & Co', month, channel, spend, conversions, NULL FROM harbour;

Use UNION ALL, not UNION. Plain UNION removes duplicate rows, and two clients with identical spend and conversions in the same month and channel would silently collapse into one.

Worked example: September across three clients

Grouping by channel gives the portfolio view:

Search costs £8,300 / 348 = £23.85 per conversion across the book, Social £4,900 / 107 = £45.79. Revenue totals only cover the two clients that track it (£42,350), so ROAS should be calculated on those clients' spend (£11,700), not on all £13,200 — a common mistake in roll-ups.

Keep the roll-up maintainable

• One mapping sheet listing each client's raw metric name and the target column it maps to. When an account manager renames a column, you change one row.
• Month as a real date, so “Sep 26” and “September 2026” don't become two months.
• Currency converted once, with the rate and date recorded next to it.
• Raw exports kept unchanged in a dated folder, so any number in the roll-up can be traced back.

If you'd rather ask the questions than build the pivots, you can load the cleaned client files into Analistable and ask things like “which clients spent more in September than August but got fewer conversions?”. The files are read in your browser and the client data is not uploaded or stored on a server, which matters when your contracts restrict where client data goes. For more cross-file question patterns, see the guide to answering questions across spreadsheets.

Frequently asked questions

Should I combine client reports or the raw platform exports?

If the client reports are already cleaned and agreed with the client, combine those — they match what clients have seen. Go back to raw platform exports only when you need a grain (campaign, day) the reports don't have.

How do I handle clients with different conversion definitions?

Keep them in one conversions column but add a goal_type column (lead, purchase, booking). Compare cost per conversion within a goal type, not across them.

Is it safe to put all client data in one file?

Check your client contracts and data processing agreements first. Many restrict sharing one client's data with another or with third-party tools. Restrict access to the roll-up to the people who need the portfolio view, and prefer tools that don't upload the data.

How often should the roll-up refresh?

Monthly is enough for most portfolio reviews. If you refresh weekly, add a week column and keep the monthly table as a separate summary so the two grains never mix.

Related guides

• How to combine data from multiple sources
• How to combine clinic appointment exports into one spreadsheet
• How to combine donation exports into one spreadsheet
• How to combine event ticket exports into one spreadsheet
• How to combine LinkedIn Ads and HubSpot lead data