Rokt Finance Hub — Executive Overview

The Problem We Solved

From siloed systems to a single source of truth

Finance data at Rokt lived across more than half a dozen disconnected systems. Every report required manual exports, cross-referencing, and reconciliation — a slow, error-prone process that delayed decisions and buried the team in spreadsheet maintenance.

❌ Before: Data Silos

AR aging locked inside NetSuite — manual exports required every time someone needed a number

Headcount and cost data in BambooHR and Pigment with no connection to AR or revenue trends

Trino queries required an engineer — Finance couldn't self-serve on raw transaction data

Collections tracked in Google Sheets with no link to live balances or Jira workflows

No cross-subsidiary consolidated view — Rokt and mParticle reported separately

Leadership reporting assembled manually, often days stale by the time it circulated

✓ After: Finance Hub

✓

AR data synced daily from NetSuite automatically — no exports, no manual steps

✓

Pigment, BambooHR, and Trino data flowing into the same warehouse alongside AR

✓

Finance analysts query BigQuery directly — no engineering dependency for ad-hoc analysis

✓

Collections Kanban with Jira integration, notes, and next-action fields — all live

✓

All subsidiaries consolidated into a single USD view in one dashboard

✓

KPI dashboard refreshes automatically each morning — always current for leadership

Connected Data Sources

Every financial system, in one warehouse

The Hub ingests data from across Rokt's financial and operational stack. Rather than each team maintaining their own export process, all systems feed a single BigQuery warehouse — making cross-source analysis available to everyone.

🟦

NetSuite

Source of record for AR, invoices, credit memos, customers, and payments across all subsidiaries.

ERP · Daily sync

🌱

Pigment

Financial planning and budgeting platform. Budget vs actuals, headcount plans, and forecast data ingested for cross-referencing against AR and revenue.

FP&A · Planned

⚡

Trino

Distributed SQL query engine over Rokt's data lake. Provides access to raw transaction, event, and product data that can be joined against financial records.

Data lake · Planned

👥

BambooHR

People data — headcount by entity, department, and region. Enables Finance to model cost per subsidiary alongside AR and revenue exposure.

HR · Planned

🔄

Workato

Integration and orchestration layer. Moves data between NetSuite, BigQuery, and Google Sheets on a nightly schedule — fully automated and idempotent.

Orchestration · Live

🗂️

Google Sheets

Existing Finance workflows (collections status, override fields, last-contact dates) synced bidirectionally so the team doesn't need to change habits.

Ops data · Live

🎯

Jira

Collections escalations linked directly to Jira issues. Status changes in Jira surface in the Hub's Kanban board — no duplicate tracking.

Workflow · Live

➕

Extensible

The warehouse architecture is source-agnostic. Any system with an API or export can be connected — HubSpot, Salesforce, payroll, or custom internal tools.

Open architecture

Data Architecture

End-to-end automated pipeline

Data flows from every source system through a single automated pipeline into BigQuery, where it powers every view in the Hub. No manual intervention required after initial setup.

🔌

Source Systems

NetSuite, Pigment, Trino, BambooHR, Sheets — each connected via API or scheduled export.

⚙️

Connectors

Custom RESTlets, MCP connectors, and API integrations extract and normalise data per source.

🔄

Workato

Nightly orchestration. Transforms, validates, and MERGEs records into BigQuery safely.

🗄️

BigQuery

Central warehouse. Live tables, append-only history, and views. All USD-normalised.

📊

Finance Hub

React dashboard + FastAPI backend. Sub-second queries via 15-min L1 cache layer.

🟦 NetSuite RESTlet (live AR)

A custom SuiteScript 2.1 module deployed inside NetSuite runs a SuiteQL query against live transactions — returning per-customer 10-bucket aging that exactly matches the NS AR Aging Summary report. This eliminates report scheduling lag and gives Workato fresh data on every run.

🌱 Pigment & BambooHR (FP&A + People)

Pigment's API exposes budget, forecast, and actuals data by entity and period. BambooHR exports headcount by department and subsidiary. Both are ingested into BigQuery on a scheduled basis, enabling Finance to build cross-dimensional reports that combine AR exposure with budget and people cost — without leaving the Hub.

⚡ Trino (data lake access)

Trino sits on top of Rokt's underlying data lake and exposes raw product, event, and transaction data via SQL. By federating Trino queries into the same BigQuery warehouse, Finance analysts gain access to granular data that was previously only accessible to engineering — enabling revenue reconciliation and customer-level attribution.

🗃️ BigQuery as the single warehouse

All source data lands in the ar_hub dataset in BigQuery. Live tables are refreshed daily; history tables are append-only for trend analysis. The v_ar_aging view uses a sliding window on synced_at so pipelines can re-run safely. Any analyst with GCP read access can query the warehouse directly — no code changes needed to access new dimensions.

Platform Capabilities

What Finance can do today

The Hub ships with six purpose-built modules that cover the full AR and collections lifecycle — from executive KPIs down to individual invoice follow-up.

📈

Executive KPI Dashboard

Headline KPI cards show total AR, current balance, total overdue, and risk tiers (1–90d, 90–180d, 180+d) — consolidated across every subsidiary in USD. Trend sparklines and delta arrows show direction at a glance. Refreshes automatically each morning after the Workato sync, no manual action required.

Live daily

🔍

10-Bucket AR Aging Analysis

Full 10-bucket aging breakdown (Current through 360+d) matching the NetSuite AR Aging Summary report exactly. Drilldown by subsidiary, customer, or risk tier. Includes a sortable customer table with search, concentration analysis (top 5/10/25% of AR), and overdue exposure heatmaps.

Live daily

📅

Historical Trend Analysis

Month-over-month comparison using end-of-month NetSuite report snapshots stored in BigQuery. Select any two months to compare buckets, totals, subsidiary breakdown, and per-customer movement side by side. New months are added by Finance directly — no engineering involvement needed.

Monthly snapshots

🚨

Triage & Risk Prioritisation

Automated risk classification buckets every customer into Clean, Recent, At Risk, Escalating, or Critical based on their aging profile. Collections teams see a pre-sorted triage list each morning — no manual filtering required. Individual alerts can be dismissed once actioned to keep the queue clean.

AI-assisted

📋

Collections Kanban Board

Customer cards flow through a shared Kanban pipeline (New → In Progress → Escalated → Resolved) with per-card notes, next-action fields, last-contact dates, and direct Jira issue links. Card state persists in Google Cloud Storage, shared across all Finance team members in real time.

Jira integrated

📊

Subsidiary & Movement Analysis

Per-subsidiary AR rollup shows which legal entities are driving exposure. Movement analysis tracks which customers improved or deteriorated between periods — surfacing who paid down and who is trending toward write-off risk before it becomes critical. Complements Pigment's entity-level budget view.

Multi-entity

For Finance Teams

Building actionable reports without engineering

The Hub is designed so Finance can add new data, build new views, and answer new questions independently — without waiting for an engineering sprint or raising a ticket.

Pull the monthly NS report — no setup required

At month-end, export the AR Aging Summary Standard w/ Account ID report from NetSuite as an XLS. The Hub's parser reads the "As of" date from the report header automatically and maps all 10 bucket columns — no manual formatting or column matching needed.

Parse and load in one step

Drop the XLS into the Hub and run a single script: parse_ns_ar_report.py. It outputs a BQ-ready JSONL that matches NS to the penny. Upload to BigQuery via the console UI — the new month is live in the Analysis tab immediately. Multiple months can be processed in bulk at once.

Instant month-over-month comparison

Select any two months in the Analysis tab for a side-by-side comparison of total AR, bucket breakdown, subsidiary exposure, and per-customer movement. The comparison tab shows exactly who moved between risk tiers between periods — so collections can prioritise based on trajectory, not just current balance.

Cross-source analysis in BigQuery — no code needed

All data from NetSuite, Pigment, BambooHR, and Trino lives in the ar_hub BigQuery dataset. Any Finance analyst with GCP read access can write SQL directly against the tables — building custom Looker Studio dashboards, board-level reports, or ad-hoc queries that join AR data against budgets, headcount, or raw transaction events.

New data sources connect without rebuilding the platform

The Hub's warehouse architecture is source-agnostic. Adding a new system (e.g. Salesforce, payroll, or a new internal tool) means writing one connector and one BigQuery table — the frontend, dashboard, and reporting layer all benefit immediately. Finance teams define what they need; the platform adapts.

Technical Architecture

Built for reliability, scale, and low cost

A lightweight, cloud-native stack designed to run at near-zero infrastructure cost while handling Rokt's full global financial data portfolio.

🖥️ Backend API

Python 3.12 / FastAPI with fully async endpoints. A 15-minute in-memory L1 cache means BigQuery is only queried once per refresh cycle, keeping costs near zero. Deployed on Google Cloud Run — containerised, auto-scaling, zero infra to manage.

Python 3.12 FastAPI Cloud Run GCS persistence

🌐 Frontend Dashboard

React 18 single-page application with Recharts for data visualisation. Fully responsive. No third-party UI framework — custom component system built on Rokt brand tokens. Fast iteration via Vite build tooling.

React 18 Vite Recharts Custom design system

🗃️ Data Warehouse

BigQuery ar_hub dataset with live tables, append-only history, and daily snapshot aggregates. Partitioned by snapshot_date for query efficiency. The v_ar_aging view uses a 5-minute sliding window on synced_at so pipelines can re-run safely without duplication.

BigQuery ar_aging (live) ar_aging_history Partitioned tables

🔗 Integrations

Direct read/write integrations with the systems Finance already uses — no new logins or workflows to adopt. MCP connectors provide a standardised interface for adding new sources.

NetSuite RESTlet Workato Pigment API Trino BambooHR Jira Google Sheets

Why It Matters

The value of unified financial data

When AR, FP&A, headcount, and raw transaction data live in the same warehouse, Finance moves from reactive to proactive — and the whole business benefits.

⚡

Faster collections action

At-risk customers are automatically flagged each morning. Collections teams action the right accounts at the right time — not three days after a report was emailed around.

🎯

Single source of truth

One number for AR. One number for overdue. No more "which spreadsheet is right?" — the Hub and NetSuite agree to the penny on every metric, every day.

📉

Reduced write-off risk

The critical 180+ day segment is surfaced immediately. Escalation candidates are visible before debt ages into uncollectable territory — protecting revenue.

🔗

Cross-functional insight

Joining AR data with Pigment budgets, BambooHR headcount, and Trino product events in a single warehouse reveals connections that no individual system can show alone.

📊

Self-service for Finance

Finance adds monthly snapshots, builds new queries, and creates Looker Studio dashboards independently. No engineering ticket needed for new reporting requirements.

🔒

Audit-ready data lineage

Every monthly snapshot traces directly back to a NetSuite report export. Historical data matches NS to the penny — no estimated or computed values in the warehouse.

What's Next

Platform roadmap

The Finance Hub is a living platform. Current AR and collections capabilities are fully live — near-term additions expand the data footprint across FP&A, people, and product.

✅ Live now

10-bucket AR aging dashboard

Executive KPI cards

Collections Kanban + Jira

Triage & risk classification

Month-over-month comparison

Subsidiary rollup (all entities)

Automated daily NetSuite sync

BigQuery self-service layer

🔜 Near-term

Pigment budget vs actuals integration

BambooHR headcount by entity

Trino data lake connector

DSO (Days Sales Outstanding) metric

Automated month-end upload

Collections performance tracking

Email alerts for critical accounts

🔮 Future

Looker Studio embedded reports

AR forecast & predictive aging

Bank reconciliation module

Multi-currency P&L integration

HubSpot CRM cross-reference

Board-level reporting templates

Payroll cost allocation