Volume Price Export for price Updates

[eldon99]

I need to increase prices 10% across the board for my products. The main products are simple enough with my spreadsheet and import. But I need to update the volume prices as well. Not so easy when there is no export. Or can we export the Volume Prices via Feed Template and/or Toolkit?

In the meantime, I am trying to dig into the tables (I found s01_VolumeDiscountOverrides table, which appears to be where the rates are stored). However, I am not the greatest on SQL joins to get the product code from the product id.

Any thoughts?

[eldon99]

I am happy to say I figured out the SQL statement so I could export directly from the database. If anyone is interested (and is brave enough), here is my SQL statement, with several caveats:

1. You must lookup your price group ID for your volume pricing (29 in our case)
2. We only use the same 3 levels of price break quantities across all products, (2, 21, 51)
3. This will combine all rows by product so that each tier is together

This will allow you to follow the import format on the following miva doc:
https://docs.miva.com/how-to-guides/volume-pricing

4. Your mileage may vary
5. Some assembly required
6. If you are not tech savy, hire a Miva developer to do this for you

SELECT
p.code AS 'PRODUCT_CODE', p.name AS 'PRODUCT_NAME',
GROUP_CONCAT(pv.amount) AS '2,21,51'
FROM s01_Products as p
LEFT JOIN s01_VolumeDiscountOverrides AS pv
ON p.id = pv.product_id
WHERE pv.pgrp_id = 29
GROUP BY pv.product_id
ORDER BY p.code ASC;

[Kent Multer]

If anyone would like a process that's easier -- and less likely to damage your database if you make a typ --, I have a volume discount module with several types of imports and exports built-in.

[lesliekirk]

If anyone would like a process that's easier -- and less likely to damage your database if you make a typ --, I have a volume discount module with several types of imports and exports built-in.

Hey Kent, do you have a link to the module? Would it replace the default Marketing Price Group method? I have a store owner that wants to be able to use percentages in the volume pricing along with attributes. Could you drop me an email with more details? Thanks!

[Kent Multer]

Hi Leslie, I'll summarize the module features here, for anyone else who may be following this thread. I do have a file containing complete instructions, which I can send to anyone who wants more details.

This is a "semi-custom" module. I originally wrote it for one client, and have sold copies to several others since then. It's been in use since 2013, so it's well tested and very reliable.

It uses the discount-module API, so it plays nice with other price-group discounts, and is controlled from the Price Groups admin page like all the others. It also has a separate admin page in the Utilities section, which is used for imports and exports, as well as for debugging features.

Its unique feature is the ability to create volume discounts that are assigned to specific customers, as well as "default" discounts that apply to all shoppers. If both types of discounts are assigned to a single product, the customer-specific one has priority.

The module can import discounts in plain CSV format, as well as proprietary formats for Salesforce and Macola. It can also import in provisioning XML format.

Also, for users upgrading from the old Emporium Plus Volume Pricing module, it can transfer the discounts with one click.

It can export data in plain CSV and Salesforce formats.

If anyone needs an import or export in a different format, I'll be glad to figure a price for adding that to the module.

The module provides discount data to the page templates, so it's easy to display the discounts on the page in a nicely formatted table or list. You can customize this as needed to fit the look of your store.

I think that's everything ...

[lesliekirk]

Hi Leslie, I'll summarize the module features here, for anyone else who may be following this thread. I do have a file containing complete instructions, which I can send to anyone who wants more details.

This is a "semi-custom" module. I originally wrote it for one client, and have sold copies to several others since then. It's been in use since 2013, so it's well tested and very reliable.

It uses the discount-module API, so it plays nice with other price-group discounts, and is controlled from the Price Groups admin page like all the others. It also has a separate admin page in the Utilities section, which is used for imports and exports, as well as for debugging features.

Its unique feature is the ability to create volume discounts that are assigned to specific customers, as well as "default" discounts that apply to all shoppers. If both types of discounts are assigned to a single product, the customer-specific one has priority.

The module can import discounts in plain CSV format, as well as proprietary formats for Salesforce and Macola. It can also import in provisioning XML format.

Also, for users upgrading from the old Emporium Plus Volume Pricing module, it can transfer the discounts with one click.

It can export data in plain CSV and Salesforce formats.

If anyone needs an import or export in a different format, I'll be glad to figure a price for adding that to the module.

The module provides discount data to the page templates, so it's easy to display the discounts on the page in a nicely formatted table or list. You can customize this as needed to fit the look of your store.

I think that's everything ...

Thank you, Kent. A couple of questions have come to mind. Will this work with Product Attributes (Inventory Variants) and does it enable the store owner to use percentage instead of the other options?

[Kent Multer]

Percentage-off discounts: Yes.

Inventory variants: It depends ... Are you saying that you want to set different discounts for variants of a single product? I'm honestly not sure; I don't know if any of my clients have tried that. But I think the answer will be Yes, since the module follows the Miva API, and I assume the API is designed to handle variants. If your client needs this functionality, I'll install the module for free, so they can try it.