How to Build a Unit Rate Database From Your Own Past Jobs

The estimator is pricing a storm sewer job and needs an hours-per-metre rate for 600 mm pipe in a trench. There's a number in the estimating template. Nobody remembers where it came from. It might be from a job six years ago, a supplier's rule of thumb, or a figure someone typed in to make a bid land. Meanwhile three storm sewer jobs have closed out in the last two years, each one with timesheets, cost codes and installed quantities, and none of that has ever made it back into the template.

That gap is common, and it's expensive in a quiet way. You don't lose money on the job where the rate is wrong. You lose it across every job that uses the wrong rate, or you lose the bids where a padded rate priced you out. The fix isn't a cost database subscription. It's a closeout habit that turns each finished job into a few trustworthy rows.

What a unit rate row actually needs

A unit rate on its own is close to useless. "0.42 hours per metre" tells the next estimator nothing about whether it applies. A row worth keeping has:

• The activity and unit, in the estimate's terms (install 600 mm concrete pipe, per metre; form pile caps, per square metre of contact area)
• Quantity installed and hours spent, the two raw numbers, not just the ratio
• Crew makeup (a crew of four plus an excavator operator is a different rate from a crew of three)
• Conditions: depth, ground, access, dewatering, tidal windows, winter work, whatever changed the pace
• The job and year, so anyone can go back to the source
• A confidence note: was the quantity measured or estimated, were the hours coded cleanly or reallocated after the fact

Keep the raw quantity and hours, and let the spreadsheet calculate the rate. When you later combine three jobs, you add up hours and quantities and divide once. Averaging three rates weights a 40 metre job the same as a 900 metre one, which is how a database quietly drifts.

Where the numbers come from

This only works if the job produced two things along the way: hours coded to activities, and installed quantities in the same units the estimate used. If your crews code time to the right cost codes and someone records installed quantities weekly (the same inputs a weekly productivity report uses), the closeout step is an afternoon. If they don't, no amount of closeout effort will recover what wasn't written down, and the honest answer is to fix capture on the next job before building the database.

One practical check before you trust a job's numbers: tie the coded hours back to payroll. If payroll says 4,100 hours and the cost codes add up to 3,300, the missing 800 hours went somewhere, and your rates are flattering you by roughly that share.

Normalise before you record

The raw ratio from a finished job is almost never the rate you want to bid with. Before a row goes in, strip out the things that aren't the activity:

• Change order work. If the owner added 60 metres of pipe on a change, those metres and their hours belong in the row only if they were the same kind of work. Extra work under different conditions goes in its own row or stays out.
• Standby and delay. Hours lost to a late RFI answer, a missing permit or equipment down for two days are real costs, but they're a risk allowance, not a production rate. Record them separately so the estimator can decide how much delay to carry, instead of having it buried inside every metre.
• Rework. Same logic. Track it, don't let it inflate the base rate unless it's genuinely part of doing that work in those conditions.
• Learning curve on short runs. If an activity only ran for three days, say so in the confidence note. A short run is a real data point, but it shouldn't carry the same weight as six weeks of steady production.

This is the step most closeout checklists skip, and it's where the value is. An unnormalised rate either double counts risk (you carry contingency on top of a rate that already includes the delays) or hides it (the next job has no delays and you've overpriced it).

Keep ranges, not a single number

After a few jobs you'll have several rows for the same activity. Resist the urge to collapse them into one figure. Show the estimator the spread: the best job, the worst job, the combined rate, and how many jobs sit behind it. A rate backed by one job and a rate backed by six should look different on the page.

The spread is also where the conditions column earns its keep. If pipe in firm ground runs consistently faster than pipe in wet silt with dewatering, those aren't one activity with a wide range. They're two activities, and the database should say so. On the coast that distinction shows up all the time: marine work tied to tide windows, or in-water work limited to reduced-risk timing windows, simply doesn't produce at the same pace as the same activity on land.

A useful rule from people who do this well: one job running long is noise, the same activity running long across several jobs is a pricing problem. The database is how you tell the two apart.

Who owns it, and when it happens

The closeout review needs two people in the room: whoever estimated the job and whoever ran it. The estimator knows what assumptions went into the bid. The superintendent or PM knows why the numbers came out the way they did, which is the part that never lands in a timesheet. Without the field side, you get rates with no conditions. Without the estimating side, you get stories with no numbers.

Timing matters more than format. Do it within a few weeks of substantial completion, while people still remember why week nine was slow. Six months later the timesheets are still there but the reasons are gone.

For a contractor running a handful of jobs a year, this is a two to four hour sitting per job, and it produces maybe ten to twenty rows: the activities that carry most of the labour. You don't need a rate for every line in the estimate. You need good rates for the lines that decide whether you win and whether you make money.

A spreadsheet is fine

One sheet, one row per activity per job, the columns above, and a second tab that groups rows by activity and shows count, combined rate and range. That's the whole tool. Estimating platforms with built-in historical databases are worth considering once you have a few years of clean rows and more estimators than one, because then the problem becomes consistency across people. Before that, a platform just gives you a nicer place to store numbers you don't yet trust.

The one discipline that matters: every rate in the estimating template should point back to rows in the database, or be labelled as an assumption. When someone asks where 0.42 hours per metre came from, the answer should be three jobs and a date, not a shrug.

Where AI fits, honestly

Once the rows exist, AI tools are good at the tedious parts: pulling quantities out of daily reports, flagging a job whose coded hours don't tie to payroll, or drafting the conditions note from the superintendent's end-of-job summary for someone to check. They can't produce the rows if the job never captured quantities and coded hours. The database is the prerequisite, not the product.

If your estimating template is full of numbers nobody can trace, or your closeouts end at the final invoice, that's the kind of workflow we look at in a discovery call. It's 30 minutes, free, and usually enough to tell whether the fix is a habit, a spreadsheet, or something bigger.