Designing Analytics for Impact: Non-profit’s Dual-Dashboard System

Goal

Our client needed two fundamentally different analytics products serving different decision-makers. Internally, the team wanted a real-time, interactive dashboard to support daily operational decisions such as reallocating marketing spend, understanding content engagement, and investigating performance drops across channels. This required drill-downs, flexible filtering, and the ability to explore raw data when questions arose.

Externally, the organization needed a static, visually polished monthly report for its board of directors, government sponsors, and annual reports. This report had to show 8–15 core KPIs with targets and year-over-year comparisons, strictly follow client’s brand guidelines, and be easy to embed into presentations—without any interactivity or manual work.

At the same time, the underlying data infrastructure had to be modernized: replacing Google Sheets with a proper data warehouse, automating ingestion from all sources, introducing a custom engagement metric, and preserving full historical data for long-term trend analysis—all within the budget constraints of a nonprofit organization.

Result

We designed and implemented a dual-dashboard analytics system backed by a centralized data warehouse in BigQuery.

At the infrastructure level, we replaced manual Google Sheets workflows with automated pipelines pulling data from eight sources, including WordPress/MySQL, Google Analytics 4, Google Ads, Reddit Ads, Reddit posts, Tumblr, Statistics Canada, and selected Google Sheets used by the client. Data ingestion is orchestrated via Airflow, with native BigQuery integrations used wherever possible to reduce cost and operational overhead.

For GA4 specifically, we built an optimized pipeline with adaptive API-quota handling, historical backfills, and a dedicated scheduled runner to ensure stable and complete data collection over time. All transformations and metric logic—including a custom engagement metric—were standardized and versioned using Dataform, ensuring consistency across dashboards and reports.

On top of this foundation, we delivered two distinct analytics products:

• Internal dashboards: a real-time, interactive Looker Studio dashboards for daily use by the team. They support filtering by channel, date, campaign, and content type, allows drill-downs into granular performance data, and enables fast investigation of anomalies or trends.

• External dashboard: a static, branded monthly snapshot exported as a PDF/image and automatically distributed via email. It highlights key KPIs against targets, includes year-over-year comparisons, and is designed specifically for board members and sponsors who need clarity, not exploration.

The result was a reporting system that reduced manual work, improved trust in data, and allowed each stakeholder group to see exactly what they needed—no more, no less. The solution is now used as a core operational and reporting asset and can be easily extended to other nonprofits or mission-driven organizations with similar constraints.