Pricing Margin Check

Overview

This is a handy tool for checking margins and updating prices in bulk. It checks pricing for every product by looking at the ex-GST unit cost and calculating whether pricing meets target margins.

It contains a record of most products that we sell on the website (including variants e.g. 4 packs, cases of 16 etc), their last known unit cost, pack and case sizes if applicable, and prices for the single, pack and case variants of each product. Each record should also contain the product’s unique Dear URL suffix (this tells Airtable which product to update in Dear when the 'Update Price' checkbox is ticked).

Any new products that have been created and uploaded need to be imported into the Margin Checker via the New Craft Beer Products to Sync tab.
See Import into Pricing Margin Check in New Products - Create & Upload (Beer Cartel) for how to do this.

When stock (of an existing product) is received into Dear via a purchase order, Airtable runs an automation to pull the new unit cost. If the unit cost has changed, it then checks whether the product’s pricing (single, pack and case where applicable) is meeting target margins.
If the unit cost has increased and existing pricing is not meeting margins, Airtable triggers BigCommerce to hide the product from the website and the product will appear in the 'Pricing to Review' view in the Craft Beer Product Pricing tab of the airtable with proposed new pricing.
If the unit cost does not change, Airtable won’t propose any changes and the product will remain visible on the website (assuming it is already visible).
If the unit cost has decreased and existing pricing exceeds target margins, Airtable won't flag the product in 'Pricing to Review' nor hide the product, but it will still recalculate pricing and you can trigger pricing to be updated via the 'Active Products' view if desired. Just search in that view for the product's SKU, tick the 'Update Price' checkbox and follow the steps below.

Note: the unit costs that Airtable pulls in from Dear will include any discounts applied to line items in a PO, as well as a portion of any Additional Costs entered into the PO (e.g. overall discount, freight, CDS, minimum delivery top-up fees, etc). Additional Costs are split evenly between all products in a PO. This means all discounts a supplier has applied, whether to the line item or as an overall discount, will be included in the unit cost sent to Airtable.

Generally we want to calculate pricing for a product based on its ex-GST unit cost including additional costs and excluding any discounts (there are some exceptions e.g. expensive USA beers via EI).
For any products received with a discount, we will have to manually calculate the ex-discount unit cost and enter this into the Cost Price column for that product.

Updating Prices via Pricing to Review

1. Once you have completed a PO in Dear (invoice authorised and stock received), open the Pricing Margin Check airtable and go to the Craft Beer Product Pricing tab. Select the Pricing to Review view.

Any products whose current prices aren’t meeting target margins will appear in this view with proposed new pricing, and you will have the option to either update or ignore the proposed prices.

Try to keep note when entering an invoice whether there are line or overall discounts that we don’t want included in a product’s unit cost.
If there are, work out the product’s non-discounted unit cost, search for the SKU in the Raw Data view of the margin checker and manually change the unit cost in the Cost Price column as shown above.

2. Tick the checkbox in the Update Price column for any products you want to update.
Tick Ignore Proposed Price for any you want to ignore.

When the Update Price checkbox is ticked, the new price is pushed through to the App Upload tab.

Airtable will also push any updated prices through to Dear (only to the variants that are correctly linked - this is where the Dear URL is important).
The single price (if updated) will also be changed in BigCommerce.

Note: any products for which you've ticked Ignore Proposed Price will still be hidden from the website, so will need to be made visible later on.

3. Update Retail prices in Dear

If the Single price of any product has been changed, you’ll have to manually change the Master product’s Retail price in Dear - this won't be automatically updated by Airtable as the only products linked are the single SKU (e.g. 12345_SNG), and where applicable the pack and case SKUs.

Go to the Pricing tab of the Master product in Dear and change the Retail price tier to the new single price.

Custom App Sync

If the single price of any products have been changed, you’ll need to run a Quick Sync to pull the new single price into the app, before we can upload new pricing.

In the custom app, a Quick Sync will sync info for all products with stock in BigCommerce.
Running a Full Sync will sync all products even if they have 0 stock in BigC - run a Full Sync if changing the single prices of products that have 0 stock.

4. Download CSV from App Upload tab

Once you've updated everything you want to via Airtable (and once any single prices have been changed in Dear and a sync run), go to the App Upload tab in Airtable. Download a CSV of the new prices.

5. Uploading new prices into the custom app

To update pack and case prices on the website, we need to update them in the app.

In the custom app, go to the Beer and Glassware Packagings tab and click Import File in the top right hand corner. Choose the App Upload file and then click Upload.

6. Search for hidden products in BigCommerce

Go to BigCommerce, select Products from the menu on the left and choose Search.

Set Inventory Level from 1 to any number over a few thousand (ensures the search captures everything in stock including key pack stock) and Product Visibility to ‘Only invisible products’.

This will give you a list of every product that is in stock and hidden from the website, so any product the margin checker has hidden should appear here.

7. Unhiding products

Select the products you’ve updated and want to make visible on the website, and click Bulk Edit.

Once in Bulk Edit, click the eye so it is ‘open’ and drag down the list, then Save. This will unhide these products from the website.

8. Run Searchspring

Open Searchspring and click Update Index to make sure the updated products appear in the website search bar and prices are updated in search engines.

Page updated

Report abuse