How to combine donation exports into one spreadsheet
By the Analistable team · Updated · 5 min read
Export donations from each platform at gift level, rename columns to a shared set (date, donor name, email, amount, fees, net, platform, Gift Aid flag), append them with a Platform column, then build a donor table by grouping on a cleaned email or donor ID. Report gross and net separately, and treat donors who gave through two platforms as one person.
Part of our guide: How to answer questions across multiple spreadsheets
Why donations end up scattered
A small charity might receive gifts through an online fundraising platform, a card payment provider on its website, a peer-to-peer page for an event, standing orders at the bank and cash or cheques logged by hand. Each produces its own export with its own columns. Questions like "how much did we raise this year", "who are our regular givers" and "which donors haven't given in a year" need all of them in one table.
Combining has two stages: stack every gift into one gift table, then roll up to a donor table. Getting the first stage right — consistent amounts and dates — matters more than any formula in the second.
Step 1: a shared gift table
| Column | Notes |
|---|---|
| Gift date | Date the donor gave, not the payout date |
| Platform | Added per export |
| Donor name, Email | Email trimmed and lower-cased |
| Gross | What the donor gave |
| Fees | Platform and payment fees |
| Net | Gross minus fees: what reached the charity |
| Gift Aid | Yes / No, as declared on that platform |
| Type | One-off, Regular, Event sponsorship |
- Load each export and rename its columns to the shared set; drop the rest.
- Where a platform only reports net, record fees as unknown rather than zero.
- Add bank standing orders from the bank statement export, filtered to the donation references.
- Append everything and check the total against each platform's own report before going further.
Some platforms claim Gift Aid on your behalf and pay it out with the donations; others leave it to you. Record which is which, so the same gift isn't claimed twice. Matching gifts to declarations is covered in matching donations to Gift Aid declarations.
Worked example: one quarter, three sources
| Platform | Gifts | Gross (£) | Fees (£) | Net (£) |
|---|---|---|---|---|
| Fundraising platform | 140 | 6300 | 189 | 6111 |
| Website card payments | 85 | 3400 | 68 | 3332 |
| Bank standing orders | 60 | 1200 | 0 | 1200 |
| Total | 285 | 10900 | 257 | 10643 |
Fees take 2.4% of gross overall (£257 of £10,900), but 3.0% on the fundraising platform and nothing on standing orders. Grouping the 285 gifts by cleaned email might give 210 donors, of whom 12 gave through more than one channel — perhaps a standing order plus an event sponsorship. Without combining, those 12 would be counted twice and their total giving understated in each system.
SELECT lower(trim(email)) AS donor,
COUNT(*) AS gifts,
COUNT(DISTINCT platform) AS channels,
SUM(gross) AS total_given,
MAX(gift_date) AS last_gift
FROM gifts
GROUP BY 1
ORDER BY total_given DESC;Matching donors without email
Standing orders and cheques often arrive with only a name or a bank reference. Match these to the donor table on name plus postcode where you have it, review the matches by hand, and give each confirmed donor an ID used across every source from then on. Avoid automatic matches on name alone; two donors called J Smith are common.
Membership charities face the same problem with subscriptions; matching a membership list to payment records walks through it. For contact de-duplication in general, see merging contact lists without duplicates.
Questions to ask the combined table
- Lapsed donors: last gift more than 12 months ago, ordered by lifetime giving.
- Upgraders: donors whose giving this year exceeds last year's.
- Channel mix: share of gross and net by platform, to see what each channel really costs.
- Regular giving: count of donors with a Regular gift in every month of the quarter.
Analistable can load the platform exports and the bank export together and answer these in plain English, writing the grouping and joins in SQL you can inspect. Donor data stays in your browser rather than being uploaded, and the tool reads your exports without changing your CRM or platform records. More examples are in the guide to answering questions across spreadsheets.
Checks before you trust the totals
- Totals per platform: after appending, the gross for each platform should equal that platform's own report for the same dates. A difference usually means a date filter or time zone mismatch.
- Refunds and failed payments: some exports include failed or refunded transactions with a status column. Filter them out for income totals, but keep refunds for reconciliation.
- Recurring gifts counted correctly: a monthly donor appears as twelve gifts a year. Count gifts for income and distinct donors for headcounts; don't mix them.
- Duplicated imports: if the same export was saved twice in the folder, every gift doubles. A COUNTIFS on platform transaction ID greater than one finds them.
- Bank versus platform: gifts paid out by a platform also appear as a lump sum on the bank statement. Exclude platform payouts from the bank export, or they are counted twice.
That last point catches many charities: individual gifts come from the platform export, and the bank export should only contribute money that went directly to the bank, such as standing orders and transfers.
Frequently asked questions
- Should I report donations gross or net of fees?
- Both. Gross is what donors gave and is what most supporter-facing figures use; net is what the charity received. Keep both columns so each report can choose and the difference stays visible.
- How do I avoid counting a donor twice across platforms?
- Group gifts on a cleaned email (trimmed, lower-case) or, better, a donor ID you assign after matching. Review donors with similar names but different emails by hand.
- Which date should I use, the gift date or the payout date?
- Use the gift date for donor analysis and fundraising totals. Payout dates matter for reconciling to the bank, which is a separate step.
- Can I include cash and cheque donations?
- Yes. Log them in a simple sheet with the same columns (gift date, donor, amount, Gift Aid flag), set Platform to Cash or Cheque and fees to zero, and append it with the other exports.