Unified donor analytics warehouse for Blue Dragon Children's Foundation
Salesforce, BigQuery, Givecloud, and PayPal unified into one source of truth. The finance team now closes monthly donor reports in hours — not days of manual reconciliation.
- Systems unified
- 4
- Monthly close time saved
- 40h
- Source of truth
- 1
- Data freshness
- Daily
The challenge
Blue Dragon Children's Foundation is one of Vietnam's most recognised NGOs, working to rescue and rehabilitate children from poverty, trafficking, and exploitation in Hanoi and across the country. Their fundraising operation spans multiple channels and platforms: individual donors give through PayPal and credit card via Givecloud, major donors and partnerships are managed in Salesforce, and programme expenditure data lives in BigQuery. Each system works well in isolation — but together they created a reconciliation problem. Every month, the finance team spent several days manually exporting CSVs from each platform, reconciling donor IDs across systems that used different identifiers, and pasting figures into a master spreadsheet to produce the consolidated donor report. The process was error-prone. Discrepancies required hours of back-checking. Data was always at least 30 days stale by the time leadership saw it. And there was no single view of a donor's total lifetime value across all giving channels. The team needed a system that would pull all four data sources together automatically, deduplicate and reconcile donors across platforms, and produce a live dashboard — without requiring engineering expertise to maintain.
What we built
L'inno designed and built a multi-source ETL pipeline that treats each platform as a data source and the Google BigQuery warehouse as the single canonical store. **Data ingestion layer.** We built scheduled connectors for each source: Salesforce (REST API), Givecloud (webhook + API), PayPal (transaction API), and the existing BigQuery tables. Each connector runs on a daily schedule and loads new or updated records into a staging dataset in BigQuery. **Identity resolution.** The hardest part of the project was reconciling donors across systems that use different identifiers — a Salesforce contact ID, a Givecloud donor ID, and a PayPal payer ID may all refer to the same person. We built a deterministic matching layer using email address as the primary key, with name + country as fallback signals, producing a unified donor_id that persists across all records. **Transformation layer.** Staging data is transformed into a clean analytical schema: one donors table (unified identity), one transactions table (all giving events normalised to a common schema with amount, currency, channel, date, and programme tag), and one programmes table linking expenditure to donor-attributed gifts. **Reporting dashboard.** We built a Looker Studio dashboard connected to the BigQuery views, giving the finance team a real-time overview of: total donor count, new vs. returning donors by month, lifetime value by channel, top donors by rolling 12-month giving, and programme-level fund attribution. The dashboard refreshes daily and requires no manual data entry.
The result
Before the warehouse, the finance team's monthly donor report was a multi-day exercise in manual reconciliation. Today, the same report is generated automatically overnight and is waiting in the dashboard each morning. The team can now answer questions that were previously impossible: a donor's total giving across PayPal and credit card over three years; which programme received the most first-time donor gifts in a given quarter; how campaign-specific appeals converted compared to organic traffic. The identity resolution layer proved particularly valuable — it surfaced a group of donors who had given through both PayPal and Givecloud under slightly different names, allowing the team to consolidate their records and personalise outreach correctly for the first time. Leadership now reviews a single dashboard on the first working day of each month rather than waiting 5 days for a manually assembled report.
Technologies used
Python (ETL orchestration), Google BigQuery (data warehouse), Salesforce REST API, Givecloud API, PayPal Transactions API, Cloud Scheduler (cron jobs), Looker Studio (reporting dashboard). All pipelines run on GCP infrastructure with automated error alerting to Slack.
“Instead of spending the first week of every month chasing down data and reconciling spreadsheets, we now have a dashboard that gives us a clear picture of our donor landscape in real time. It's been a game-changer for how we understand our supporters and make strategic decisions.”
Related services & products
Have a similar challenge?
Tell us what’s slowing you down. We reply within 24 business hours with whether we can help and what a first step looks like.
Discuss a data project