Customer usage, with the whole picture.

Overview

Company: Transfr

My role: Requirements discovery, warehouse modeling, export development, validation, and iterative delivery

Stack: BigQuery, dbt, Python, Airflow on Cloud Composer, Cloud Storage, Terraform

THE STACK

Google CloudBigQuery · Composer · Storage

dbtModeling & tests

PythonExport logic

Apache AirflowDaily orchestration

TerraformInfrastructure as code

The problem

The initial request was straightforward: make product usage available in a customer-success tool. Defining useful usage data took more discussion.

Which activities counted? Did the team need an individual organization's activity or a parent account's combined usage? How should we count people active across multiple organizations? How often should the data refresh, and what decisions would it support?

Those answers shaped both the metrics and the pipeline.

My approach

I mapped the requirements against the warehouse and separated the work into stages. I started with an existing metric and its daily export, then developed new calculations using available data, and finally integrated additional sources where the requirements demanded them.

I used dbt to build the daily usage model, with contracts and grain tests to validate its structure. Python and Airflow handled the daily export, while Terraform managed the destination bucket and third-party access. Documentation made the metric definitions available alongside the data.

The engineering decisions

Model at the account grain. A customer's Salesforce account could represent several organizations. I rolled usage up through an account-hierarchy bridge so parent accounts reflected their descendants.

Resolve identities across sources. A single Salesforce field was insufficient to map all active organizations. Using an organization-to-account bridge spanning multiple sources prevented roughly 100 active customer organizations from being dropped from reporting.

Recalculate distinct metrics after rollup. Adding organization-level distinct counts could count the same activity more than once across an account's organizations. I rebuilt event-based measures as distinct event counts at the account grain.

Constrain the date spine. Expanding the hierarchy across every reporting day produced an oversized intermediate design. Restricting the ancestor side to reportable accounts brought the resulting table from roughly 200 million rows in the draft design to a few million rows.

Results

The team received an automated daily feed backed by documented warehouse metrics, supporting reporting across approximately 2,000 organizations. The account-level implementation corrected identity and aggregation problems, and resolved blank names for roughly 300 accounts.

What this project shows

A reliable data product depends on agreeing what the data means, choosing the right grain, and validating how records move between systems. I stayed involved through those decisions and continued investigating gaps after the initial delivery.