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.
| System | Usually owns | Usually owned by |
|---|---|---|
| CRM | Customers, deals, territories | Sales |
| Accounting or ERP | Invoices, payments, costs | Finance |
| Billing or e-commerce | Orders, subscriptions, refunds | Product or operations |
| Web analytics | Visits, sign-ups | Marketing |
| Spreadsheets | Whatever the others don't hold yet | Whoever 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.
| Source | Owner | One row is | Invoice dates | Exported |
|---|---|---|---|---|
invoices_jul_aug | Sydney | An invoice on export day | Jun 30 to Aug 29 | Sep 1 |
invoices_sep | Sydney | An invoice on export day | Aug 28 to Sep 23 | Oct 1 |
customer_register | Don | A customer from a date | None | Current |
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.
| Column | Example | Use it to |
|---|---|---|
customer_id | C5 | Connect tables |
customer | Dunder Mifflin | Show 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.