Part Synergy Price Calculations Script
Purpose of this document is to outline the custom work which we need to do in order to calculate the prices for each channel and generate ready to import file
| Version | Date | Name | Applied changes |
|---|---|---|---|
| v1.0 | 15.01.2025 | Bogomil Pavlov | First Publish |
| v1.1 | 01.05.2025 | Bogomil | Additional Triggers |
The client has very complicated calculations which depends on many factors and logics. I really do not know how to give and outline all the details into understandable format but will try to give you an overview of the whole picture.
There are multiple different data points we will need to utilize to get to the final selling prices. Those will be provided by the seller and their array of distributors and we need to store them in Hemi in an orderly fashion for 2 purposes:
- Ease and performance of actual calculations
- Allow the end user a UI option to manually interfere and update data or manually input new records
Data points for the calculations will include but might not be limited to:
- Target Profits
- Shipping Target Profit
- Channels
- Warehouses
- Exclusions
Once we run the calculation script and have the values for each affected product we want to update our listing records so the new values get sent to the relevant marketplace. We’d like to also keep a record of the information regarding the calculation as a log too for audit trail and script evaluations.
There should be two types of triggers for the system to represent delta updates and full updates.
- Delta updates - these are the partial updates we want to be able to run every 15-30 minutes for example. To achieve this we need to track what has updated in the last running period and take only these records that have been updated in this period. This includes the
Item Location Quantity Pricestable & the structures to be created for all of the above mentioned “Data points” for calculation. Whenever a record is updated in any of these it should fall into the delta updates so it can be calculated in the next available script run. The cases we want to cover and calculate are:
- Target Profits - If there is a change in any of the Profits percentages we want to trigger the price calculations for the relevant channel and warehouse or only channel or only warehouse or on everything if the default case is updated
- Shipping Target Profit - If there is a change in any of the Shipping Profits percentage we want to trigger the price calculations for the relevant channel and warehouse or only channel or only warehouse or on everything if the default case is updated
- Channels - If there is a change in any of the Fee percentages we want to trigger the price calculations to the relevant channel
- Warehouses - If there is a change in any of the Warehouses state we want to trigger the price calculations for the relevant location which will affect all channels which has it in the account location.
- Exclusions -Any changes in the exclusion list should affect the channel and warehouse or just the channel or just the warehouse depends on the setup.
- Full updates - preferably we should have an option to run this as a full price calculation for all products in case a force of this is needed or we want to switch on a once a night full price reconciliation run
‼ The script should calculate the products per account because for each account we will have different markups and profits. ‼
Each channel and warehouse has a unique identifier and a name which we will be using for the validations. Examples:
So just to confirm - the script requested should only perform the calculations and validations needed with already supplied data in Hemi. Below the needed steps for the calculation to happen together with all the checks and validation we have to make
Step 1:
Get all SKUs that need updating based on the provided option (full or delta). Please remember calculations need to happen per account. Check in Location Quantities all available locations for each SKU and obtain Item Location Quantity Prices > Price and Item Location Quantity Prices > PackQty . This is our starting point to the SKU price for each location.
e.g.
Step 2:
Once we have the SKU, Account and Price we can get all Account Locations which will represent the warehouses from which this SKU can be sold on the Account.
Which means once we have SKU we can see the Item Accounts of that SKU and get all Account Locations and we want to obtain the prices for all Location Quantities where the Location is part of the Account Locations.
e.g.
Then once we have the channel and the location we want to validate the SKUs against the exclusion list
Where we have to compare the Channel and Warehouse and if we have a match we want to remove from the next steps all SKUs which have the mentioned MasterLC (MaterLineCode). At the moment the line code is added as a prefix on each SKU. If there is no match we want to compare just the channel and if we have a match we want to remove from the next steps all SKUs which have the mentioned MasterLC. If there is no match we want to compare just the warehouse and if we have a match we want to remove from the next steps all SKUs which have the mentioned MasterLC.
Step 3: On this step we already know the SKU, Account, which Locations can be used for that Account, and the Prices so we are ready to start the validations and building the formula. So on this step we want to check if the Location is TRUE or FALSE, this can be checked on the Warehouses section where each warehouse has a state and if the state is False we do not want to use it for the calculations. e.g.
Step 4: Gathered all the details we will start building the formula so first we have to check what is the Chanel Fee and Catalog Fee. This can be done by using the channel unique identifier and the listing account and match the fee.
e.g.
For example if the Listing is for PS Amazon we want to get the Channel Fee - 0.119 and if the Listing is catalog product we add the Catalog Fee - 0.03 (0.119+0.03).
In order to check if the listing is a catalog or not we will have a custom field called Item Custom Fields > CatSKU and if the CatSKU = Y we treat the listing as a catalog product and add the Catalog Fee otherwise if we have CatSKU = N or CatSKU = “” we treat the listing as a NON catalog product and we do not add the Catalog Fee.
*Since Hemi will charge the client 0.20 cents per order they want to add an addition 0.20 on top of the Item Location Quantity Prices > Price
The formula so far looks like - (Item Location Quantity Prices > Price * Item Location Quantity Prices > PackQty ) / (1- (Channel Fee + Catalog Fee))
Step 5: On this step we will be calculating the Target Profit which is defined but the channel, warehouse and price range. If any of these criteria is met we will have a default value which will be used.
e.g.
The checks we want to perform here is first we want to compare if the channel and the warehouse match if yes we use the Min and Max cost to define the Value based on the Item Location Quantity Prices > Price.
If there is no match we compare just the channel and if yes we we use the Min and Max cost to define the Value based on the Item Location Quantity Prices > Price.
If there is no match for the chanel then we use the warehouse and if we have a match we we use the Min and Max cost to define the Value based on the Item Location Quantity Prices > Price.
If there is no match we use the default value which will have empty Channel, Warehouse, Min and Max Cost.
The formula so far looks like - (Item Location Quantity Prices > Price * Item Location Quantity Prices > PackQty ) / (1- (Channel Fee + Catalog Fee)) / (1 + TPValue )))
Step 6: On this step we will be calculating the Shipping Target Profit which is defined but the channel, warehouse and price range. If any of these criteria is met we will have a default value which will be used.
e.g.
The checks we want to perform here is first we want to compare if the channel and the warehouse match if yes we use the Min and Max Ship to define the Value based on the Item Location Quantity Prices > Price.
If there is no match we compare just the channel and if yes we we use the Min and Max Ship to define the Value based on the Item Location Quantity Prices > Price.
If there is no match for the chanel then we use the warehouse and if we have a match we we use the Min and Max Ship to define the Value based on the Item Location Quantity Prices > Price.
If there is no match we use the default value which will have empty Channel, Warehouse, Min and Max Ship.
The formula so far looks like - (Item Location Quantity Prices > Price * Item Location Quantity Prices > PackQty ) / ((1- (Channel Fee + Catalog Fee)) / (1 + TPValue )) + (Item Location Quantity Prices > Shipping Cost / ((1 - (Channel Fee + Catalog Fee)) / (1 + STPValue)))
This is the whole formula and on this step we have the price for each SKU location
Step 7:
Once we have all the prices we want to compare each price with Item Location Quantity Prices > Map Price and if Item Location Quantity Prices > Price < Item Location Quantity Prices > Map Price to use the Item Location Quantity Prices > Map Price
Step 8:
The final listing price will be determine by the lowest calculated price and Item Location Quantity > Quantity > 3 and this price we want to set for the Listing
<v1.1>
We want to add additional triggers when we are updating any of the following fields:
Part Synergy Target Profits >Channel Part Synergy Target Profits >Warehouse Part Synergy Target Profits >Min Price Part Synergy Target Profits >Max Price Part Synergy Target Profits >Profit Value If any change occur in the current data or a new record is added we want to trigger price calculations only for the affected products (Based on Part Synergy Target Profits >Channel and Part Synergy Target Profits >Warehouse)
Part Synergy Shipping Target Profits > Channel Part Synergy Shipping Target Profits >Warehouse Part Synergy Shipping Target Profits >Min Price Part Synergy Shipping Target Profits >Max Price Part Synergy Shipping Target Profits >Shipping Profit Value If any change occur in the current data or a new record is added we want to trigger price calculations only for the affected products (Based on Part Synergy Shipping Target Profits >Channel and Part Synergy Shipping Target Profits >Warehouse)
Part Synergy Channels >Fee Part Synergy Channels >Catalog Fee If any change occur in the current data or a new record is added we want to trigger price calculations only for the affected products in the relevant Channel
Part Synergy Warehouses > Enabled If any change occur in the current data or a new record is added we want to trigger price calculations only for the affected products in the relevant Warehouse
This way we will recalculate the prices but in the same time reduce the load on the system.
</v1.1>
The full configuration file -
configuration - Optimized.xlsx
I have also added the descriptions provided from the client below
For the Shipping Cost Calculations please first read Read Me File (I think we have to discuss this as well how to incorporate it)
File 1: Configuration-Optimized.xlsx (Important)
This is the most important file for us that has the information regarding Profitability, Quantity Range, Warehouse selection or exclusion, and Brand selection or exclusion using 6 different sheets.
Sheet 1 named Channel is for the Fee calculation for each Marketplace mentioned in Column C "Fee" (excluding Wheel Synergy Shopify store) and Column D "Catalog Fee" is the additional Fee from a store for some items mentioned in Master Files.
Sheet 2 named Warehouses has the list of Warehouses active for routing.
Sheet 3 named Target Profit is for setting the required target profit in Column F "Value" depending on the Store, Warehouse, Min Cost, and Max Cost.
Sheet 4 named Shipping Target Profit is for setting the required target profit in Column F "Value" depending on the Store, Warehouse, Min Cost, Max Cost, Min Ship, and Max Ship.
Sheet 5 named Quantities is for setting the quantity ranges in columns F and G "MinQty" and "MaxQty" depending on the Store, Warehouse, Min Cost, Max Cost, Min Ship, and Max Ship.
Sheet 6 named Exclusion is for excluding the Warehouse or Brand (mentioned in MasterLC) completely for a particular store mentioned in Column A Channel.
Two important points to consider here:
Rule 1) If the column is blank it means it is the default value.
For the given screenshot in the 2nd row, the default cost-profit is 5% for all items and all stores
Rule 2) The values are updated as going downwards:
For the given screenshot in the 2nd row, the default cost-profit is 5% for Parts Authority items having prices between $0 - $10.
In the 8th row, the cost-profit is updated to 4.5% for Parts Authority items having prices between $0 - $10.
In the 15th row, the cost-profit is updated to 5% for Parts Authority items having prices between $0 - $10 listed on Part Synergy Walmart Store.
In the 20th row, the cost-profit is updated to 5% for Parts Authority items having prices between $0 - $10 listed on Bill Smith Walmart Store.
File 2: BSA Master.csv(Total listings)
The Master file has a total number of items listed on BS Amazon. It will proceed with the price and quantity for only these items.
In addition to this, there is a column CatSKU with (Y, N) values.
If there is Y then the store fee % will be higher by a certain amount defined in Configuration File, Channel Sheet in Column C + Column D. For value N the fee will be only given in Column C.
This additional Column exception is for Three Stores Part Synergy Amazon, Bill Smith Amazon, and Part Synergy eBay due to different fee structures for certain items by the store itself.
Just to add an exception here for 3 eBay stores Part Synergy eBay, Baabs eBay, and MMM eBay have some SKUs listed without "|" are due to system generated listings (Mecka or WHI) managed by item numbers.
File 3: BS Amazon.csv(Price File for Channel Spyder)
A sample price file for Bill Smith's Amazon Store is used by the channel spyder to send the price and quantity as per defined rules.
The important columns in this file are Shipping, Markup, ShipMkup, PackQty, MinQty, and MaxQty.
File 4: shipping.rar(Contain 20 files)
After extracting the Rar file Read the file named 1 Read Me.xlsx first.
As we have discussed earlier warehouses have different criteria regarding Freight Charges.
Warehouses like Dorman and Jante Charge no Freight for their order so their shipping is set to 0.
Warehouses like PFG and WTD provide freight in their inventory file.
Warehouses like Keystone and Motorstate provide freight that varies with the item cost.
Warehouses like NPW have flat-rate shipping of $11.99 from this year 2024, before they charge according to the order
Warehouses like Turn 14 and Tonsa charge the actual shipping charge per order so we do some estimations for every item, if we get a loss we update shipping manually.
Warehouses like Parts Authority have some exceptions like charging flat $12 for most items but for LTL orders it charges more than $100 shipping so we update them manually.
Warehouses like Burco and Spyder have different shipping varies from store i.e. PS Amazon and AP Fusion 2.
File 5: Manual Calculator - Optimized.xlsx (Optional)
It's an additional file to check markups manually, by entering the cost, shipping, markups, ship markups, and pack quantity to check the final price. Basically, it's good to check for orders manually to see whether the price is okay to proceed from the Warehouse with or not.
Here is the Markup Formula:
Markup = {1 - Fee (Store Fee)} / {1 + Profit (Calculated from the Configuration-Optimized.xlsx file)}
Ship Markup = {1 - Fee (Store Fee)} / {1 + Profit (Calculated from the Configuration-Optimized.xlsx file)}
File 6: Wheel Synergy Shopify - Listings Info.xlsx (Unique Case)
As you know we have listings on Shopify, the unique case for the listings is that their quantity, pricing, and orders are not managed by Channel Spyder. Its price and quantity are managed by our Scripts and the orders are manually managed by the Support team manually. In the given file you can check the pricing structure which is different than Channel Spyder Markups.
The work structure is complex here so make updates carefully.
Let me know if you have any questions
Files:
Price Calculation & Comparison.xlsx
configuration - Optimized.xlsx
Manual Calculator - Optimized.xlsx
Please also find attached a video with all the details https://drive.google.com/file/d/14TssewcnkMJnbfMlgKbNZZTIl0CJ7_p4/view