Shopify Dynamic Price

Dynamic pricing in e-commerce is a strategy where you can adjust prices in real time based on different conditions. Here are some examples:

• Daily price fluctuations based on cost: e.g., if you sell gold jewelry, and product prices depend on the daily cost of gold.

• Price increase based on conditions:

  • If products are low on inventory, in order not to run out of stock before restocking.
  • If a certain product gets viral because of some promotion, there is a huge surge in demand.

• Price decrease: For goods whose expiration dates are approaching to clear stock.

You can set dynamic pricing with Mixtable spreadsheet formulas and below we’ll show you how.

Leverage the power of spreadsheets to automatically update your Shopify product prices daily, reflecting the current market value of gold and other precious metals.

Dynamic product prices with formulas

Price fluctuations

Here’s an example formula you can use for calculating dynamic prices for jewelry:

Final Price = (Metal Weight * Metal Price + Stone Weight * Stone Price) * Profit Margin + Other Costs

Where:

• Metal weight: The weight of the precious metal in the piece of jewelry (e.g., grams of gold). You might already have this in a metafield.
• Metal Price: The current market price of the metal per unit of weight (e.g., price of gold per gram). It’s crucial to keep this updated.
• Stone Weight: The weight of the precious stone(s) (e.g., carats of diamond). You might already have this in a metafield.
• Stone Price: The price per unit weight of the stone (e.g., price per carat of diamond). Again, you’ll need to keep this updated.
• Profit Margin: The percentage or fixed amount of profit you want to make on the piece. This can be a percentage or a fixed value. If you want to have a 47% profit margin, use “1.47” as a multiplier in the formula.
• Other Costs: This includes manufacturing costs, labor, setting fees, packaging, etc. This could be a fixed value or calculated based on other factors.

Price increase

Example formula: 20% price increase if the inventory level drops below 10 units:

=IF(C2<10, A2*1.2, A2)

Where:

• C2: This cell contains the current inventory level for the product.
• <10: This is the condition. The formula checks if the inventory level is less than 10.
• A2: This is the cell with the original price.
• A2*1.2: This is the value if the condition is true. It calculates a 20% price increase (i.e., multiplying the original price by 120%).
• A2: This is the value if the condition is false. The price remains the same (original price).

Example formula: 25% price increase if average daily sales (Net Quantity Sold) for the last 2 days are more than double the average daily sales from the previous 30 days.

=IF(D2/7>E2/30*2,C2*1.25,C2)

Where:

• D2: Represents the dynamic value for Net Quantity Sold for last week (with Dynamic date range in Mixtable you calculate the Net Quantity Sold for a dynamic time period in the past, e.g., last week, last 30 days. If you select this option, the Net Quantity Sold will be recalculated daily as the date range window moves forward.)
• E2: Represents the dynamic value for Net Quantity Sold for the last 30 days.
• The condition here is that if the average number of quantities sold per product variant for the last week is higher than double the average quantity sold for the last 30 days, we increase the price by 25%.
• C2: This is the cell with the original price - if the condition is true, we increase the price by 25%, if false, we keep the original price.

This dynamic price can be set up in Mixtable using Analytics and Bulk editing features. Here’s how to do it:

1. Add а new worksheet:

   In an existing Mixtable workbook, click the (+) button to add a new worksheet.

   

2. To load new Shopify data, start by selecting an empty column — any column with a non-green header (green headers mean the column is already linked to Shopify data). Then, click the ⚡️ button in the column header to open the selection window to choose the data you want to pull in.

   

In the worksheet, pull the following data:

• Product title (Column A)
• Product handle (Column B)
• Price (Column C)
• Analytics > Net Quantity Sold > Dynamic date range: Last week (Column D)
• Analytics > Net Quantity Sold > Dynamic date range: Last 30 days (Column E)

3. Add the formula to Column F and drag it down to all products.

Price decrease

Example formula for a 50% discount if the item expires in 7 days or less, a 25% discount if it expires between 8 and 30 days, and no discount otherwise:

=IF(B2-TODAY()<=30, A2*0.7, A2)

• Breakdown of the Formula B2: This is the cell containing the expiration date.
• TODAY(): This function returns the current date.
• B2-TODAY(): This calculates the number of days remaining until the product expires.
• <=30: This is the condition. The formula checks if the days remaining are 30 or fewer.
• A2: This is the cell containing the original price.
• A2*0.7: This is the value if the condition is true, which calculates a 30% discount (i.e., multiplying the original price by 70%).
• A2: This is the value if the condition is false. The price remains the same (original price).

How to set dynamic prices with the Mixtable Shopify spreadsheet app

To use such a formula for setting up your product prices and to change the corresponding variable price components in one place, you can use the Mixtable spreadsheet app.

• After installing the Mixtable Spreadsheet Editor app, click the Create a Workbook button. Now, load the Full Product Info template.

• You will find all Product Variant data here, including prices and metafields.

• Now, you can bulk edit the fields for your Shopify products and use a formula for the price calculation.

• If you have information related to the product details (e.g., weight, etc.) in metafields, you can use it for formula calculation.

• For the variable prices, such as the precious metal prices, you can add additional fields and change the values when necessary. That will automatically change the product prices for all products, as in an Excel spreadsheet.

You're ready!

Mixtable is an online spreadsheet designed to sync data with Shopify. It looks, feels, and behaves like Excel (including formulas), but runs in your browser. With Mixtable you can:

• Bulk add products to collections, edit metafields, add or remove tags;
• Change product prices, including international prices, and review price history;
• Manage customers with a spreadsheet CRM for Shopify;
• Use Excel features like sort ascending or descending, find-replace, filter, and more.

Best of all, you can sync changes back to Shopify with a single click. Find out more about the Mixtable suite of products here.