Problem
Finance and campaign data often sit in different analytical paths, which makes profitability after ad spend harder to inspect in one reporting surface.
archive case study
Greenweez Finance & Campaign Analytics
A dbt analytics engineering case study that unifies e-commerce finance reporting with paid marketing performance. It runs on BigQuery and on a DuckDB twin that CI builds and tests on every pull request.
Quick technical review
The case study in four points.
Architecture
dbt separates staging, intermediate, and mart logic. The same code runs on BigQuery and on DuckDB through cross-adapter macros, and a source_system variable switches the raw input between the original ad-platform tables and a Google Sheets export.
Results
Creates unified finance and marketing marts for reporting use, including ads_margin for profitability after marketing spend.
Limitations & scope
The default mode is verified on synthetic seed data because the raw warehouse dataset is empty. ROAS exists only at the blended level, since revenue is not attributed to a source. The sheet export has a refund column whose meaning is unconfirmed, so…
Read complete note
The default mode is verified on synthetic seed data because the raw warehouse dataset is empty. ROAS exists only at the blended level, since revenue is not attributed to a source. The sheet export has a refund column whose meaning is unconfirmed, so it is reported separately and never subtracted from revenue or margin. The dashboard is a demo, not a BI product.
Summaries reuse the project’s documented evidence. Review the complete case study for evaluation boundaries.
Evidence-led visual review
Explore the actual work.
Concept art, dated captures and repository figures are identified separately.
Portfolio illustration
Original portfolio illustration. Diagrams are conceptual; verified methods and results appear in the linked case study. This is not an application screenshot.
Project proof
Verified external evidence
Last verified 2026-10-06
Problem and approach
Problem
Finance and campaign data often sit in different analytical paths, which makes profitability after ad spend harder to inspect in one reporting surface.
Architecture
dbt separates staging, intermediate, and mart logic. The same code runs on BigQuery and on DuckDB through cross-adapter macros, and a source_system variable switches the raw input between the original ad-platform tables and a Google Sheets export.
Business logic
Finance reporting is extended with campaign cost and performance data: ads_margin for profitability after ad spend, blended ROAS, and per-source CPC and CTR. Every ratio is a ratio of sums, never an average of daily ratios, and a zero denominator gives NULL.
Validation
CI runs dbt seed and dbt build on DuckDB for both source modes (schema tests, reconciliation tests, unit tests) plus a headless dashboard smoke test. The BigQuery run was a read-only query that returned the same numbers as DuckDB.
What is not claimed
The default mode is verified on synthetic seed data because the raw warehouse dataset is empty. ROAS exists only at the blended level, since revenue is not attributed to a source. The sheet export has a refund column whose meaning is unconfirmed, so it is reported separately and never subtracted from revenue or margin. The dashboard is a demo, not a BI product.
Why it matters
This repo shows analytics engineering with the checks left in: structured marts, reusable business logic, tests that actually run, and an honest account of what the data can and cannot support.
Architecture and analysis
- Keeps finance and marketing data inside one modular dbt architecture instead of separate ad hoc SQL analyses.
- Builds daily and monthly reporting models that connect revenue, costs, operational margin, and ad spend, plus ROAS, CPC, and CTR marts.
- Runs its tests in CI on every pull request (dbt build on DuckDB in both source modes, lint, dashboard smoke test) rather than only parsing the project.
- Found and fixed three silent errors in the original models: orders without a shipping row dropped out of margin, ad spend on days without orders disappeared, and the monthly basket averaged daily averages.
Testing and validation
- Staging models for sales, product, shipping, four ad platforms, and the Google Sheets export
- Intermediate models such as int_orders_operational and int_campaigns_day
- Reporting models including finance_days, finance_campaigns_month, marketing_performance_month, and finance_refunds_month
- Streamlit dashboard declared as a dbt exposure, so it shows in the lineage graph
Operational value
- Creates unified finance and marketing marts for reporting use, including ads_margin for profitability after marketing spend.
- Adds blended ROAS and margin ROAS plus per-source CPC and CTR, all computed as ratios of sums.
- On the real Google Sheets export (13,362 orders), revenue is 967,261, margin after ads is 144,727, and blended ROAS is 14.69, checked against independent BigQuery sums.
- Keeps the refund column out of net revenue and margin because it does not behave like refunds (a value on every order, 3.8 times total revenue).
How to inspect
- Inspect the mart model names and the README architecture snapshot before reading individual SQL files.
- Read the README sections on source systems and refunds to see what the data was and was not allowed to support.
- Use this project as warehouse-first proof; the dashboard is a thin demo on top of the marts.