Plumbers AI: Turn Supplier Price Lists Into a Parts Markup Sheet

Export the merchant's price list as a CSV, ask ChatGPT or Claude to tidy it into one table of code, description, pack size and net cost each, then put your markup rules in a small lookup table so the sheet calculates sell prices by formula. Spot-check a sample against the original, and keep the AI out of the arithmetic.

Two passes work better than one clever prompt. In the first, AI does what it is good at: reading messy descriptions, spotting pack sizes and writing formulas. In the second, a spreadsheet does what it is good at: applying the same rule to thousands of rows without drifting. People go wrong by asking the chatbot for finished sell prices and then trusting the total.

Follow me on Instagram@sagnikteaches

What a merchant export looks like, and the traps in it

Merchant exports are built for the merchant's systems, not for your quotes. This illustrative extract shows the shape. The column names differ between merchants, but the habits do not.

Connect on LinkedInSagnik Bhattacharya

UOM is the unit of measure and PER is the quantity the price covers. Read the elbow row carefully: PER is 10, so the NET of 4.08 buys ten elbows, not one. That single column is behind more wrong quotes than any other. The traps, in order of how often they bite:

Subscribe on YouTube@sagnikteaches

• The PER column. Prices per 10 or per 100 hide in a column most people never read.
• Units of measure. A length of tube is not a metre of tube, and a pack is not an each.
• Discounts. Some exports show a net price that already includes your discount; others show list price only. Know which you have before you mark anything up.
• Dead rows. Discontinued items and "price on application" lines with a zero in the price column.
• Duplicates. The same code twice with different pack sizes.
• Price date. Copper and brass fittings can change between lists, so a price with no date is a guess.

Step one: ask AI to tidy the export, not to price it

Upload the CSV file rather than pasting rows into the chat, so the tool can run code over the whole file instead of reading it like text. Anthropic's help pages say Claude's file creation and code execution can analyse uploaded CSV and TSV files and create Excel files, with a 30 MB limit per file, on the Free, Pro, Max, Ultra, Team and Enterprise plans. On an individual plan you may need to switch it on first, under Settings > Capabilities ("Create and edit files" in the newer settings layout). ChatGPT also accepts spreadsheet uploads, with its own size limits, so check the current limit on OpenAI's upload help page before you start. A merchant export is usually far smaller than either limit.

I am uploading a CSV export of a plumbers' merchant price list. Columns: CODE, DESCRIPTION, UOM (unit of measure), PER (the quantity the price is for), LIST, DISC (percent), NET.

Make one clean table with these columns: code, description (sentence case, sizes written as "15 mm" and lengths as "3 m"), category (pipe, fittings, valves, tap fittings, sanitaryware, consumables, other), pack_qty (from PER), net_pack (the NET price exactly as given), cost_each (net_pack divided by pack_qty, to 4 decimal places), flag.

Rules:
- Do not change any price. Do not guess. If a description, unit or size is unclear, leave the cell as it is and explain in flag.
- Check that NET equals LIST x (1 - DISC/100) to the nearest cent, and flag any row that does not.
- List separately any duplicate codes, rows with a zero or missing price, and rows marked discontinued or POA.
- At the end report: rows in, rows out, rows removed (with codes), and the sum of net_pack in the output.
Return the table as a downloadable CSV.

The prompt does three things worth copying. It forbids guessing, which turns doubt into a flag instead of a silent invention. It asks the model to validate NET against LIST and the discount, which catches rows where the export disagrees with itself. And it asks for a closing report of rows in, rows out and a sum, which is what you will check in a moment. The cost_each column it returns is only a cross-check: in step three the sheet recalculates it by formula, so the AI's division never reaches a quote. This is an illustrative extract of the answer:

The two flags are the useful part. The model could have guessed "300 mm" and moved on; instead it asked. You answer from your own knowledge (it is 300 mm), fix the cell and carry on. Resist the urge to reply "just assume the obvious". Flags are how the process tells you where it was unsure, and a prompt that suppresses them gives you a cleaner-looking table with hidden errors.

Keep the original export untouched in its own folder. The cleaned file is a new document with a date in its name, so you can always compare back to what the merchant sent. If you want the general method for tidying other awkward spreadsheets, the older tutorial on cleaning messy data with AI covers the habits that apply here.

Step two: write the markup rules as a table the sheet reads

A flat percentage on every part is easy and wrong. A 45% markup that works on a $50 valve produces a ludicrously small figure on a 40-cent elbow and a very large one on a $217 cistern frame. Many plumbing firms use bands: the cheaper the part, the higher the markup, because small parts carry handling, stock-holding and the trip to fetch them. These bands are illustrative; set yours from your own overheads.

The third column is the one to look at. Markup is measured on cost; margin is measured on the sell price, and it is what you actually keep. The conversion is margin = markup / (1 + markup), and the other way round, markup = margin / (1 - margin). A part that costs $10 with a 40% markup sells at $14, and you keep $4 of $14, which is a 28.6% margin. To keep a 40% margin on the same part you need a 66.7% markup and a $16.67 price. The markup versus margin pricing sheet tutorial goes further on this for whole jobs. Choose your target margin first, then convert.

Banded markup has one quirk that surprises people: a cliff at each edge. With the table above, a $9.95 part sells at $17.95 (cost plus 80%, rounded up to the next 5 cents) while a $10.05 part sells at $14.60 (cost plus 45%). The dearer part is $3.35 cheaper to the customer. Nobody notices until two similar items sit on one quote. You have three options. Move the band edges to gaps where you stock nothing. Ask the AI for a progressive version, where each rate applies only to the slice of cost inside its band, which has no cliffs at all. Or keep the bands and add a check column that flags any part priced below a cheaper one.

Step three: formulas the AI writes and you read

Keep the rules in cells, never inside formulas, so changing 45% to 48% happens in one place. Put the table above on a sheet called Rules, lower edges in column A (0, 1, 10, 50, 200) and markups in column B (1.5, 0.8, 0.45, 0.3, 0.2). The cleaned list goes on a sheet called Parts. Ask ChatGPT or Claude to write the formulas, then read them yourself before pasting.

Parts sheet columns:
A code | B description | C category | D pack_qty | E net_pack | F cost_each | G markup | H sell_each | I margin | J check

F2  =E2/D2
G2  =XLOOKUP(F2, Rules!$A$2:$A$6, Rules!$B$2:$B$6, "CHECK", -1)
      (older Excel with no XLOOKUP:  =VLOOKUP(F2, Rules!$A$2:$B$6, 2, TRUE))
H2  =CEILING(F2*(1+G2), 0.05)
I2  =(H2-F2)/H2
J2  =IFERROR(IF(OR(F2<=0, I2<0.12, F2>500), "CHECK", ""), "CHECK")

Read them as plain sentences. F: cost each is the pack price divided by the pack quantity. G: look up the cost in the rules table and take the markup of the nearest band at or below it (the -1 means "exact match, or the next smaller item"). H: add the markup and round up to the next 5 cents. I: the margin you actually keep. J: flag a row if the cost is zero or negative, the margin falls below 12%, or the cost each is over $500 (on a parts list, a figure that size deserves a second look), and flag it too if any earlier formula returned an error, which is what IFERROR catches. XLOOKUP works in current Excel and in Google Sheets; the VLOOKUP line is for older versions and needs the lower edges in ascending order. If the AI cannot explain a formula in one line, do not paste it.

Ask for the formulas rather than the answers, and the sheet stays live. When the merchant raises a price next month, you change one cell and every sell price follows. If the chatbot had pasted in finished numbers, you would be back to asking it again, and trusting it again.

Which unit to price in: the length, not the metre

Banded markup has a second trap, and it comes from units. The band is chosen by cost each, so the unit you price in changes the band. Take the 15 mm copper tube from the export, $10.53 for a 3 m length.

• Priced by the length: the cost of $10.53 sits in the $10 band, so the markup is 45% and the sell price is $15.30 a length, or $5.10 a metre.
• Priced by the metre: the cost is $3.51 a metre, which sits in the $1 band, so the markup is 80% and the sell price is $6.35 a metre.

For the 18 metres in the bathroom job below (six lengths), the first route gives $91.80 and the second $114.30. It is the same copper from the same merchant, and the quotes differ by $22.50. Neither route is wrong; they are different rules. What matters is that you choose one on purpose. I would apply the bands to the unit you buy and cut (the length, the pack) and treat a per-metre figure as something derived from it. Say so in the prompt ("price tube per 3 m length; do not convert to metres before applying the bands") and write the rule beside the bands on the Rules sheet, so whoever edits it next knows.

A bathroom first-fix parts list, from cost to sell price

Here is the sheet doing its job on a quote. The illustrative job is a bathroom first fix: new supply pipework, a cloakroom basin and a wall-hung toilet frame. Quantities come from the plumber's site notes; costs and markups come from the sheet.

The parts cost $453.08 and sell for $628.10, a blended markup of 38.6% and a blended margin of 27.9%. That blended margin is lower than the 31% to 60% on most rows, and the reason is worth understanding. The WC frame is 48% of the cost of the whole list and carries only a 20% markup. Always judge the margin on a typical job's total, not row by row, because the heavy lines dominate.

Notice what is missing: clips, PTFE tape, solder and flux. Parts that cost a few cents each do not belong on a quote line by line. Add a sundries line instead, as a percentage of the itemised parts. At 8%, this job's sundries are $50.25 (8% of $628.10), bringing the parts total to $678.35. Say what the sundries cover in one line, so the customer sees a reason and not a surcharge.

Five spot-checks between the merchant export and a quote

The sheet is only as good as the export and the cleaning, and neither is perfect. These checks take about twenty minutes the first time and less after that.

1. Counts and sums. Rows in must equal rows out plus the rows the AI listed as removed. Then add up the net_pack column in the original and in the cleaned file, in a spreadsheet, not in the chat. They must match to the cent. If they do not, a row has changed.
2. Pack sizes. Filter the cleaned sheet for pack_qty above 1 and check five of those rows against the merchant's printed list or portal. This is where the worst errors hide. If the elbows had been read as $4.08 each instead of $0.408, the sheet would sell them at $7.35, and 24 elbows would cost the customer $176.40 instead of $25.20, a $151.20 overcharge on one cheap fitting.
3. Extremes. Sort by cost each and look at the ten cheapest and ten dearest rows. You know roughly what a ball valve and a cistern frame cost. Anything that looks wrong probably is.
4. A random sample. Pick fifteen rows by dice or a random function, not the first fifteen, and compare each with the merchant's current price. A mismatch in more than one row means stop and find out why.
5. The cliff and the floor. Sort by cost each and confirm that sell price never falls as cost rises, and that no row shows CHECK in the last column.

One more habit costs nothing. When the sheet feeds a real quote, recompute three lines by hand with a calculator. If you can reproduce the quote total from the merchant's net prices and your rules, the process is sound.

Getting the sheet into your quoting app

A markup sheet that lives only in a spreadsheet gets used for big jobs and forgotten for small ones. Most field service apps can take a CSV of products and services, and Jobber is a useful example of what to check, because its help pages are specific. As of early November 2026, Jobber's Products and Services list imports a CSV in a set format with columns for name, description, unit price, taxable, category and active, plus unit cost on select plans. A few details decide whether the import works:

• Download the sample file first and keep its headings unchanged. Paste your sell prices and costs under them.
• Numbers only in the cost and price columns. A dollar sign in a cell will cause an issue with the import.
• Category means Service or Product. Jobber's Category column takes only those two values, so your own groupings (pipe, fittings, valves) belong in the name or description, not in that column.
• A 2.5 MB limit per import, so a long list must be split into several files.
• "Update existing items" is a checkbox that overwrites items already in the app with the new cost, price, description and category. Tick it on purpose.
• On the Grow plan, markup is recalculated at import when a new unit cost or price comes in, and the sample file has no markup column. Import a new cost without a new price and the price stays put while Jobber adjusts the markup instead (it shrinks when costs rise), so always send both columns from the sheet. Check the markup figure after the first import.
• Accounting sync. If the app syncs with accounting software, the help pages say the products list may have to be changed there instead, or that an import will be overwritten at the next sync. Read that section before you import.

Export your current list first. It is your restore point if the import overwrites something you wanted to keep. Other apps have their own rules, so look for the same four things: the file format, the size limit, whether an import updates or duplicates, and how it interacts with accounting sync. The tutorial on whether a plumber can use AI to write quotes covers what happens next, when the priced parts go into the quote itself.

Merchant terms and what you should upload

Your trade discount is a commercial arrangement, and some merchants treat their price files as confidential. Before uploading anything to a chatbot, read the merchant's terms or ask your account manager. If there is doubt, you can delete the list-price and discount columns and upload net costs only, or even upload a sample of twenty rows to design the prompt and run the real file through a spreadsheet.

The AI plan matters too. ChatGPT Business and Claude Team do not train on your content by default, according to the vendors. The consumer plans of both have a switch in privacy settings that lets chats be used for model training, so turn it off, and remember that the setting names change. A cost price is not personal data, but it is commercially sensitive, and the safest habit is to treat it like a customer list.

Keeping the sheet current without starting again

The first clean-up is the long one. After that, a monthly refresh should take under an hour, because AI can compare the new list with the old sheet and show you only what moved.

I am uploading two files: last month's cleaned parts sheet (cleaned_october.csv) and this month's merchant export (export_november.csv). Using the same cleaning rules as before, produce a change report with four sections: (1) lines whose net price changed, with old price, new price and percentage change, sorted by size of change; (2) new codes; (3) codes that have disappeared; (4) any pack size that changed. Do not output unchanged lines. Do not guess; flag anything unclear.

Review section one first. A single line moving by more than 10% deserves a look at the merchant's list, because it is either a real price move you want in your quotes today or an export error you do not. Then update the sheet, stamp every changed row with the new price date, and flag anything older than 60 days. The tutorial on updating your plumbing price book from supplier invoices shows the other route, catching price changes from the invoices you receive.

Finally, match the quote's validity to the price file. If your parts prices are refreshed monthly, a quote that says "valid for 30 days" is honest; one that says "valid for 90 days" quietly carries three months of price risk on your own account.

Markup sheet questions plumbers ask

Should I set parts prices by markup or by margin?

Decide the margin you want to keep, then convert it to markup for the sheet. Markup is measured on cost and margin on the sell price, so they differ: a 40% markup keeps a 28.6% margin, and a 40% margin needs a 66.7% markup. Mixing the two up is the commonest way to underprice parts.

Is it safe to upload my merchant's price list to a chatbot?

Check the merchant's terms first, because your trade discount may be confidential. You can delete the list-price and discount columns and upload net costs only. Business plans of ChatGPT and Claude do not train on your content by default; consumer plans have a training switch in privacy settings that you should turn off.

How often should the sheet be refreshed?

Monthly as a minimum, and every time the merchant issues a new list. Fast-moving materials such as copper can change between lists. Put the price date on every row and flag any row older than 60 days, then ask the AI to compare the new export with the old sheet so you only review the lines that changed.

Can the AI keep the prices current by itself?

Not reliably. You still export the new list, upload it and review what changed. What AI removes is the retyping and the comparing. Treat the review step as yours, because a wrong price in a sheet repeats on every quote that uses it.

Further reads

• HVAC Markup and Margin: A Worked Check on Past Jobs With AI — Test your markup rules against jobs you have already priced.
• Handyman Material Markup: Build a Cost-Plus Calculator With AI — A simpler cost-plus calculator for smaller material lists.
• Plumbing Flat-Rate Pricing: A Worked 30-Task Price List Built With AI — Turn parts costs into a flat-rate price list for common jobs.
• Plumbing Van Stock Lists: Set Reorder Levels From Job History With AI — Use the same cleaned list to set van stock reorder levels.
• QuickBooks for Plumbers: Set Up Job Costing and Let AI Sort the Bills — Where supplier bills go once the parts are priced.
• How to Compare Supplier Quotes Side by Side With AI — Compare two merchants' prices on the same parts list.
• Phone Repair Intake Form: Turn Customer Messages Into Tickets With AI — Paste a customer's message into an AI assistant and get a ticket draft: device, fault, questions to ask, a price-list quote and a parts check.
• Blinds Quoting Software: Measure-and-Quote Tools and What AI Adds — Five blinds quoting tools compared on price lists, on-site measuring and cost, plus what their AI really does and a 20-cell audit to run before you trust it.
• 10 ChatGPT Prompts for Plumbers: Call-Backs, Quotes, Complaints — Ten prompts for call-back texts, quotes and complaint replies, each with a filled example, a pass mark and a note on what never to paste in.
• Hourly Rate for a Tradesman: Work Out Yours Without Guessing — A four-step method for a tradesman's hourly rate, with a worked plumber example, a crew version, a billable-hours log and an AI check of the maths.
• How to Price Welding Jobs: Build a Rate Sheet With AI — A welding rate build-up, a fixed-price gate quote, per-inch pricing from timing, and an AI check of quoted against actual hours on twelve past jobs.
• Plumbing Change Orders: Four Worked Examples Drafted With AI — Four plumbing change orders (hidden pipework, access, code upgrade, customer extras) drafted from site notes with AI, with approval wording.
• Van Stock Management for HVAC and Electrical Vans: Reorder Levels With AI — Set min-max van stock levels for HVAC and electrical vans from your own job history, then run a weekly top-up list and shrinkage check with AI.
• Water Heater Installation Checklist: Photo Proof and Handover Pack With AI — The installer's photo-proof checklist for a water heater job, the tests you record yourself, and how AI drafts a plain-English handover pack.
• Plumbing Price Increase Letter: Raise Call-Out and Hourly Rates Clearly — One plumbing firm's price letter, built from the numbers up: domestic and landlord versions, what stays fixed, AI drafting and four complaint replies.
• Dental Lab Price List Template: Tiers, Rush Fees and AI Fee Notes — A dental lab price list with tiers, a worked rush surcharge, remake rules and AI-drafted fee notes and change-notice email.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: Jobber help article on the Products and Services list (CSV import and export); Anthropic help article on creating and editing files with Claude; OpenAI and Anthropic statements on business-plan data use.