Lesson 3 of 15 · Concept

Where business data lives: systems and exports

Business data lives in systems of record, such as a CRM for customers and an accounting system for invoices. What reaches you is usually an export, a copy taken on one date. Knowing a source's owner tells you who can fix it, and its dates tell you what it can't know.

Typical systems of record
SystemUsually ownsUsually owned by
CRMCustomers, deals, territoriesSales
Accounting or ERPInvoices, payments, costsFinance
Billing or e-commerceOrders, subscriptions, refundsProduct or operations
Web analyticsVisits, sign-upsMarketing
SpreadsheetsWhatever the others don't hold yetWhoever made it

Spreadsheets deserve a second look, because a target or a territory map often lives in one person's file.

A source inventory: three files and what they cover

At fictional Vandelay Coffee Supply, Q3 net value is €11,478 by the written definition, and it's built from these files. Sydney Swell runs Finance and Don Tremendous runs Sales.

Vandelay's sources
SourceOwnerOne row isInvoice datesExported
invoices_jul_augSydneyAn invoice on export dayJun 30 to Aug 29Sep 1
invoices_sepSydneyAn invoice on export dayAug 28 to Sep 23Oct 1
customer_registerDonA customer from a dateNoneCurrent

The "one row is" column is the table's grain. Several invoices for one customer are expected at this grain, while several register rows for one customer need explaining before anyone joins to them.

From the dates alone, what happens if you add up both invoice files?

The windows overlap on August 28 and 29, so anything dated then can appear twice. You can see that before opening a row. Two invoices are in both files, worth €1,440.

A fault belongs to whoever owns the fact

The less obvious owner is Don. Region comes from the register, and he owns it. Sydney's lookup lost a new customer, Initech, because Don hadn't registered it, and counted Dunder Mifflin twice because the register lists it twice. Finance's error began in Sales, and only Don can fix it.

An export also differs from the system. INV-1008 was pending on September 1 and booked on October 1, and both rows were true on the day they were exported. Check whether a file holds every record, only changes or one period.

Which owner would you call about each of these? A customer in the wrong region, an invoice that changed status and customer Excalibur, with no Q3 invoices.

The first is Don's, and the second is Sydney's. Excalibur goes to Don, who owns the account, because nobody yet knows why it stopped.

Join on stable keys

A label is what people read, and a key is what systems match on. "Harbor Ltd" can become "Harbor Limited", and two companies can share a name. Customer C5 stays C5.

One customer, two ways of naming it
ColumnExampleUse it to
customer_idC5Connect tables
customerDunder MifflinShow readers

When to keep a source inventory

Keep a table like the one above for any report you depend on: source, owner, grain, what it covers and when it was exported. It takes minutes, and it tells you who to call. Skip it for a one-off number nobody will reuse. Appending files shows what the overlap above does to a stacked table.

To stack your own exports and check for overlap, try Strata, Oplema's browser app for data prep, with no account needed.