work / reporting / client operations hub

Every client, one record.

Client and staff data was spread across tools. I built a central operations hub and rebuilt the client scorecard to be far more efficient.

role
Sole automation engineer
client
Canadian accounting firm
status
Delivered

Languages

JavaScript

Tools & platforms

Airtablen8nGoogle Apps ScriptQuickBooks Online API

AI & LLM

to build it
ClaudeChatGPTPerplexity

Skills

Data modelingAPI optimizationWorkflow automationMaster data management
12 → 1

API calls per client for the scorecard

85

fields in the client master record

01// how I approached it

The thinking behind it

  1. 01 · What I noticed

    Client data lived in four places, so nobody trusted any of them.

  2. 02 · What I decided

    Create one master record per client and staff member, and rebuild the scorecard to be efficient.

  3. 03 · What I delivered

    One source of truth, and a scorecard cut from 12 calls to 1 per client.

02// old vs new

Old system vs new system

Old systempeople as the glue
  1. Look in four tools
  2. Guess which one is right
  3. Run a slow scorecard
New systemthe system does the work
  1. Sources sync to one hub
  2. One record per client
  3. Scorecard in one call per client
03// how it works

Watch it run

Click through the four steps. In step 3, click any box to see what it does.

synthetic data · no client screens
Reconstruction with synthetic data · no client screens
04// the hard part

One record per client, and a faster scorecard.

Client data lived in four places, and the scorecard made twelve API calls per client to build. The hub creates one master record per client and staff member, and the scorecard was rebuilt to need one call per client.

05// key decisions

Why it's built this way

decision 1

One master record

An 85-field client record becomes the reference for everyone.

Trade-off: the hub must be kept in sync.
decision 2

Fewer, bigger API calls

The scorecard pulls a year at once instead of month by month.

Trade-off: more processing per call.
decision 3

Staff in the same hub

People and clients live side by side, so ownership is clear.

Trade-off: more data to protect.
06// lessons

What made it work

  1. 01

    Pick one place to trust

    Four sources means no source.

  2. 02

    Count your API calls

    Cutting 12 calls to 1 is often the easiest speed win.

  3. 03

    Structure beats searching

    A good master record saves daily hunting.