How to fill costs for 500 products without 500 clicks

This page assumes the Cost of Goods Sold feature is already switched on. If it is not, the guide to enabling it is shorter than this page and comes first: while the switch is off, the costs you enter never reach a single order.

First work out how many products you actually need

The instinct is to fill in everything. That is the most expensive possible order, because the first eighty percent of the value goes in during the first hour and the rest takes the rest of the week.

A profit report does not need a complete catalogue. It needs coverage - what share of revenue has a known cost behind it. A store that knows the cost of the fifty products making 80% of turnover has a usable report; a store that knows the cost of four hundred long-tail products and not of its ten bestsellers has nothing.

So there is only one sensible order: sort the catalogue by revenue and work down, not alphabetically and not by date added. Stop when coverage stops rising fast enough to justify the typing.

The spreadsheet - three columns and one decision

Download the CSV template or build your own; the layout is what matters:

id,sku,cost
1042,,41.50
,TSHIRT-XL-BLACK,24.00
1198,,

• id or sku - one of the two, not both. The script takes id, and when it is empty it looks the product up by sku.
• cost - net cost for a single unit, dot as the decimal separator. No currency, no spaces, no symbols.
• An empty cost means “we don’t know” and the script skips that row. This is not politeness towards the data, it is necessary: a stored zero and a missing cost are the same thing in the WooCommerce database, so typing zero “for now” deletes information instead of adding it.

The decision to make once, before the first row: what “cost” means in your store. Purchase price alone? Including inbound shipping? Duty, packaging, platform fees? Every one of those answers is valid and none of them is valid for half the catalogue. A margin computed from two different definitions of cost is not comparable to anything - including itself a month ago. Write the choice into the first row of the sheet so you are not guessing in six months.

Where to get the product list: WooCommerce → Products → Export gives you a CSV with ID and SKU columns. Keep those two, add a third, and fill it in the spreadsheet where you can see everything at once.

The script - one pass over the file

The script below runs through WP-CLI. It reads the CSV, finds the product, writes the cost and reports what it did. It starts in dry-run mode - the first line decides.

<?php
/**
 * Fills the native Cost of Goods Sold field from a CSV file (id,sku,cost).
 * Run with:  wp eval-file fill-costs.php costs.csv
 */

$DRY_RUN = true;   // <- switch to false only after a successful trial

$file = $args[0] ?? 'costs.csv';
$handle = fopen( $file, 'r' );
if ( ! $handle ) {
    WP_CLI::error( "Cannot open file: {$file}" );
}

fgetcsv( $handle );          // header row: id,sku,cost
$written = 0;
$skipped = 0;
$failed = 0;

while ( ( $row = fgetcsv( $handle ) ) !== false ) {
    list( $id, $sku, $cost ) = array_pad( $row, 3, '' );

    $cost = trim( str_replace( ',', '.', $cost ) );
    if ( '' === $cost ) {               // empty is not zero - skip it
        $skipped++;
        continue;
    }
    if ( ! is_numeric( $cost ) || (float) $cost < 0 ) {
        WP_CLI::warning( "Bad cost value: '{$cost}' (id={$id}, sku={$sku})" );
        $failed++;
        continue;
    }

    $id = (int) $id;
    if ( ! $id && $sku ) {
        $id = wc_get_product_id_by_sku( trim( $sku ) );
    }
    $product = $id ? wc_get_product( $id ) : null;
    if ( ! $product ) {
        WP_CLI::warning( "Product not found (id={$id}, sku={$sku})" );
        $failed++;
        continue;
    }

    if ( $DRY_RUN ) {
        WP_CLI::log( sprintf( '[dry run] %d %s: %s -> %s',
            $product->get_id(), $product->get_name(),
            var_export( $product->get_cogs_value(), true ), $cost ) );
    } else {
        $product->set_cogs_value( (float) $cost );
        $product->save();
        WP_CLI::log( sprintf( '%d %s = %s', $product->get_id(), $product->get_name(), $cost ) );
    }
    $written++;
}

fclose( $handle );
WP_CLI::success( sprintf( '%s: %d, skipped (empty cost): %d, failed: %d',
    $DRY_RUN ? 'To write' : 'Written', $written, $skipped, $failed ) );

Save it as fill-costs.php next to costs.csv and run:

wp eval-file fill-costs.php costs.csv

The script is here to be read, not downloaded in one click - deliberately. It writes to your database, so it should pass your eyes before it passes your products.

Why not a shell loop. The tempting wp eval called once per row boots the whole of WordPress for every product: at five hundred products that is a quarter of an hour instead of a few seconds, and five hundred chances to break halfway. One script, one boot.

If you have no SSH. The same pair of methods - set_cogs_value() and save() - works anywhere you can run PHP inside WordPress. The REST API carries the same value on the products and variations resources, as cost_of_goods_sold with values, total_value and - on variations - defined_value_is_additive. Two things to know before you write a loop against it: effective_value and total_value are read-only, and an empty values array means a cost of zero, with everything that implies further down this page. Read one product back (GET /wp-json/wc/v3/products/<id>) and check the shape against your own version first.

The trial run worth not skipping

1. A database backup. Not a “product export” - a backup. The script writes, and saving a product can touch more than one field.
2. Ten rows first. Copy ten lines into trial.csv and run the whole thing on them: dry, then for real, then look at those ten products in the admin.
3. Only then $DRY_RUN = false on the full file.
4. Verify afterwards - in the store, not in the spreadsheet. Place one test order for a product with a cost and check that the order stored it. That is the only test that covers the whole chain rather than the field in the product record.

Variations - the script will not decide these for you

A variation inherits the parent product’s cost until it has one of its own, and when it does, one toggle decides whether that cost replaces the parent’s or is added on top. That is a decision about what the number means, not what it is, so the script does not make it.

In practice: bulk-fill the costs of parent products, and only touch variations where the cost genuinely differs - then set the mode in the product editor, deliberately, where you can see it. A shirt costing 24 with an “embroidered” variation in additive mode at 3 comes to 27; the same variation in replace mode comes to 3. Details in costs on variations.

One thing to know when checking your results: WooCommerce’s helper column stores the entered value, not the effective one, so a variation happily inheriting its parent’s cost looks like a variation with no cost in a SQL query. Any “products without cost” list built straight on that column will be full of false alarms.

What this will not fix

• History. Orders placed before you entered the costs stay without cost forever; there is no backfill, and that is not an oversight.
• Freebies. A product that genuinely costs zero is stored as no cost at all, because core deletes the value instead of writing a zero. It will stay on the missing-cost list and there is nothing you can do about it.
• Refunds. Issuing a refund makes WooCommerce recalculate the cost of every line on the original order from today’s product cost - so freshly entered costs can rewrite the very history they describe. What happens then.
• Variable costs. Shipping, payment gateway fees and returns to the supplier are not cost of goods and the cost field does not cover them. Gross profit is not net profit and we do not pretend otherwise.

Checklist

1. The cost feature switched on - without it none of the rest does anything.
2. One definition of cost, written into the sheet.
3. Export products → id, sku, empty cost column.
4. Sort by revenue, fill from the top.
5. Backup, trial on ten rows, only then the whole file.
6. A test order to check the whole chain.
7. Variations separately and deliberately - replace mode or additive mode.

If any step behaves differently in your store than described here, it is worth writing in - these pages get corrected when they turn out to be wrong.