Portfolio/WalletFlow Analytics

FINTECH ANALYTICS ENGINEERING CASE STUDY

WalletFlow Analytics

A reporting layer for a fictional digital-wallet business, built to turn customer, wallet and transaction events into consistent product and performance metrics.

Data: all records are generated for this project. No employer, customer or production data is included.
View repositoryKey decisions

01 / PROBLEM

Operational transaction data is not automatically reporting-ready.

The project starts with synthetic customer, wallet and transaction records. The modelling challenge is to create a stable analytics layer where transaction performance, customer activity and retention use consistent definitions.

Instead of calculating metrics independently in dashboards, the reporting logic is moved into dbt models so the same definitions can be reused across analysis and BI tools.

How much transaction value is processed each day?
What percentage of transaction attempts succeed, fail or reverse?
How many customers are active each day and month?
How many monthly active customers return in the following month?
How do first-success customer cohorts retain over time?

02 / REPORTING OUTPUT

Transaction and engagement snapshot

Metrics calculated from the deterministic seed-42 dataset.

JAN TO JUN 2026
Gross transaction value₦145.85M5,000 attempts
Transaction success rate88.42%successful / attempts
Successful value₦128.11Msuccessful transactions
Fees generated₦409.8Ksuccessful transactions
June MAU238monthly active users
June avg. DAU25.8daily active users
June DAU/MAU10.84%average DAU / MAU
June retention95.06%vs May MAU

Monthly engagement

MonthMAUAvg DAUDAU/MAURetention
Jan24225.6810.61%N/A
Feb24026.8211.18%95.87%
Mar23926.4211.05%96.25%
Apr24425.5710.48%97.91%
May24327.3211.24%97.13%
Jun23825.8010.84%95.06%

Metric definitions

DAUdaily distinct customers
MAUmonthly distinct customers
DAU/MAUavg DAU / MAU
Monthly retentionreturning / prior MAU
Cohort retentionretained / cohort size
Success ratesuccessful / attempts

03 / ARCHITECTURE

Layered models keep source cleanup, reusable logic and reporting separate.

SOURCES

Raw events

Customers, wallets and transactions declared as dbt sources.

→
STAGING

Standardise

Explicit fields, types, categories and source-level checks.

→
CORE

Facts and dimensions

Incremental transaction fact and reusable customer model.

→
MARTS

Reporting

Daily KPIs, monthly engagement and cohort retention.

04 / KEY DECISIONS

The project is built around choices that affect correctness, maintainability and cost.

INCREMENTAL PROCESSING

Reprocess recent history

The transaction fact uses BigQuery MERGE processing and re-reads a configurable three-day window. This gives delayed transactions and recent corrections another chance to enter the fact table without rebuilding all history.

vars: incremental_lookback_days: 3 unique_key: transaction_id strategy: merge
GRAIN

One row per transaction

`transaction_id` defines the fact-table grain and also acts as the MERGE key. Matching records can be updated instead of appended as duplicates.

transaction_id wallet_id customer_id amount_ngn status created_at
ENGAGEMENT

Transaction attempts count as activity

DAU and MAU are based on customers who attempt a transaction. A failed payment is still evidence that the customer used the product. Successful activity remains useful for other measures, including the first-success cohort definition.

RETENTION

Monthly and cohort retention answer different questions

Monthly retention compares current activity with the immediately previous month. Cohort retention groups customers by first successful transaction and tracks how those groups behave in later months.

WAREHOUSE DESIGN

Partition and cluster for common access patterns

The transaction fact is partitioned by transaction date and clustered by customer ID, transaction type and status. These fields match common reporting and filtering patterns.

DEPENDENCIES

Explicit columns instead of SELECT *

Transformation and test SQL names the fields it depends on. This makes upstream schema changes easier to review and prevents unnecessary fields from flowing through the project.

05 / DATA QUALITY

Tests protect both structure and business meaning.

Structural checks

  • Primary keys are unique and non-null.
  • Transactions reference valid customers and wallets.
  • Statuses and transaction types use accepted values.
  • Successful transactions require completion timestamps.
  • Transaction amounts must be positive.

Business-rule checks

  • Status flags must be mutually exclusive.
  • Daily status counts must reconcile to transaction count.
  • Average DAU cannot exceed MAU.
  • Retained customers cannot exceed the comparison population.
  • Month-zero cohort retention must equal 100%.
  • Rate metrics must remain between 0 and 1.
PRODUCTION CONSIDERATION

Freshness and late-arrival logic would use true ingestion metadata.

The synthetic source does not contain a warehouse ingestion timestamp, so `created_at` is used as a freshness proxy. In a production implementation I would prefer a field such as `loaded_at`, `_ingested_at` or a reliable update timestamp. I would also choose the lookback window from observed source-arrival behaviour rather than treating three days as a universal rule.

06 / DELIVERY

Automated checks keep the repository reviewable.

GitHub Actions

Pull requests run repository checks, including protection against `SELECT *` in model and test SQL. When BigQuery credentials are configured, the workflow can also run `dbt seed`, `dbt build` and `dbt source freshness`.

Reporting layer

The final marts are designed for BI consumption so product metrics are defined upstream rather than recreated separately in Looker, Looker Studio or Power BI.

BACKPortfolio projects NEXT CASE STUDYCareFlow Health Analytics →