Data & Analytics
Data modeling: decide what you're counting before you count it
Nine rows, four customers
Join customers to their jobs and every customer appears once per job. Count the rows and you've counted jobs and called them customers. Nothing errors. The total just quietly says nine when the answer is four.
Every report built straight on exports makes some version of this choice, usually without anyone noticing it was a choice. That's why the marketing report, the operations report and the accountant all have a different number for the same month.
Start with what one row means
Every question about jobs becomes a measure sliced by a description: average price by service, jobs by source, duration by team. Because each dimension is described once, “Riverside” and “repeat customer” mean the same thing in every report.
Same jobs, three grains
| Job | Customer | Price |
|---|---|---|
| J-301 | Acme Properties | $480 |
| J-302 | B. Ortiz | $260 |
| J-303 | Acme Properties | $1,150 |
| J-304 | J. Park | $140 |
| … 5 more rows · 9 in total | ||
What's a typical job worth?
Average job value: $412
| Invoice | Job | Paid |
|---|---|---|
| INV-881 | J-301 | 2 Mar |
| INV-882 | J-303 deposit | 3 Mar |
| INV-889 | J-303 balance | 2 Apr |
| INV-884 | J-302 | 6 Mar |
| … 7 more rows · 11 in total | ||
How much cash came in during March?
Collected in March: $3,140 (two invoices were paid in April)
| Customer | Jobs | First job |
|---|---|---|
| Acme Properties | 3 | 2023 |
| B. Ortiz | 3 | 2025 |
| Hillcrest HOA | 2 | March |
| J. Park | 1 | March |
| 4 rows in total | ||
Who came back?
Repeat customers: 2 of 4
A metric, written down
Written down, a metric stops being whatever the last spreadsheet did. Leave warranty callbacks in and the average drops for reasons that have nothing to do with pricing. The definition makes that choice once, names who owns it, and every report inherits it.
Average job value
- Calculation
- total price ÷ number of completed jobs
- Grain
- completed job
- Period
- by completion date
- Excludes
- warranty callbacks (no charge), test records
- Owner
- Operations manager
- Changed
- only with the owner's agreement, and dated
What a model defines
01
Things you count
What each entity is, and how it's identified
A customer, a job, an invoice and a lead are different things with different IDs. The model says which, so they're never counted as each other.
Watch for: “customer” meaning a person in one system and a company in another.
02
Relationships
How one thing leads to another
One customer has many jobs; one job may have several invoices. Relationships decide what a join will repeat.
Watch for: many-to-many links that multiply rows silently.
03
Grain
What one row stands for
Stated for every table, so nobody has to infer it from the columns.
Watch for: tables that mix daily and monthly rows.
04
Metric definitions
How each number is calculated
The calculation, the period, what's excluded, and who owns it.
Watch for: the same metric name with two calculations.
05
Ownership
Which system is right
When the CRM and accounting disagree about a customer, the model says which one wins for which fields.
Watch for: two systems both editing the same field.
What modeling hands off
In
Out
Worth building when the numbers have to agree
Good fit
- Several reports or systems need the same account of customers, work and outcomes.
- People argue about which report is right.
- You're about to build dashboards or automation on top of the data.
Not the right fit
- The model already represents the business well and you only need a presentation change.
- A single tool's built-in reports already answer every question you ask.
Questions about data modeling
What's the difference between a database design and a data model?
A database design is how one application stores its records so it runs well. An analytics model describes the business for measurement: what's counted, what it's sliced by, how each metric is calculated. They often read the same data and look quite different.
Do we need a data warehouse to have a model?
No. For a modest system the model can be a set of SQL views in one database. A warehouse earns its place when there are many sources or a lot of history. The definitions matter more than where they run.
What is “grain”, in plain terms?
What one row of a table stands for: one job, one invoice, one customer per month. Mixing grains, or not knowing which one you have, is how totals end up counting the wrong thing.
Who should own the metric definitions?
The business, not whoever builds the report. We draft them from how your team talks about the numbers, surface the edge cases, and record an owner for each one.