Pricing with Excel Pricing Engine

Overview

uStore enables you to set up prices using a Microsoft Excel spreadsheet.

There are three main reasons to switch from regular pricing to Excel pricing:

· When pricing is complex and requires more flexibility in price calculation. E.g. user group discount, large format pricing by paper size.

· When you want to have a central place for defining the price of a whole store and allow easy management of pricing of numerous products.

· When there is a complex dependency between production options (aka product properties) and you want to prevent the shopper from selecting non-applicable options, e.g. silk paper cannot be laminated. For more information, see Setting Property Dependency with Excel Pricing.

When is the Excel calculated?

The Excel is calculated in these situations:

· In the category page, in order to calculate the minimum prices of products.

· During the product ordering process in the Storefront, while the shopper changes the quantity, selects property values, and uploads a document (in Composite product). The information from the Storefront is inserted into the Excel in order to calculate the price and cost, and to change property settings according to the shopper's selections.

· In the shopping cart, to update the price and cost in case the Excel was replaced.

· In the back office, when assigning a fulfillment property to an order item, to update the cost.

Getting started with Excel pricing

To set pricing using an Excel spreadsheet, you need to perform the following tasks:

· Set up product properties for a product profile or for a specific product

· Set the Excel Pricing Engine pricing method

· Download the appropriate Excel template

· Edit the Excel pricing file with pricing formulas

· Upload your Excel pricing file

· Debug your Excel pricing calculation

Set the Excel Pricing Engine Method

To have the product price calculated by Excel rather than regular pricing table configuration, select the Excel Pricing Engine option as follows:

1. In case of an individual product,

– In the Store list select the store name and then select an individual product.

– In the product's setup page, click the Product Pricing Setup link.

In case of a product profile,

– On the top toolbar, click the Stores tab and then Product Profiles.

– Select a specific profile on the Product Profiles page.

– In the Setting page, click the Product Pricing Setup link.

In case of a store,

– In the Store list select the store name and then select Store Setting > Setup Store.

– Select the Advanced tab.

– Locate the Excel Pricing section. In this section you can download the template for Regular and Upload products, add your pricing and then upload the file with prices.

2. In the Product Pricing Setup page, click the Set Prices tab.

3. From the Pricing Method list, select the Excel Pricing Engine option.

Download the Excel template

To define pricing with Excel pricing, you'll need to first download an Excel template.

uStore provides different Excel template files for different product types:

· For regular products - Static, Dynamic, Composite and Email Products: PricesData_Template.xlsx
This Excel allows to price the product according to its property selections.

· For Upload products: PricesData_Upload_Template.xlsx
This Excel allows to price the upload part of a composite product according to properties and page setup.

· For Easy Upload products: PricesData_EasyUpload_Template.xlsx
Download this template directly from the Easy Upload product itself, and not from the store's setup.

You can download the Excel template from wherever convenient - product, product profile or store.

To download a pricing Excel file per product/profile:

· In Product Pricing Setup window > Set Prices tab, click Download Template.

To download up a pricing Excel file per store:

1. Select the required store, and the click Store Settings > Set Up Store.

2. Select the Advanced tab.

3. In the Excel Pricing section, download the Excel file for regular products (Static, Easy Upload, Dynamic, Composite, Kit and Email products) or Upload products.

Note that the Easy Upload product has unique settings that are relevant only for this product type. To access the template with these settings, you will need to download the template from the Easy Upload product itself.

Edit the Excel template

Once you have downloaded the appropriate Excel file, you can make your changes to it, and then upload it.

The Excel template includes two tabs:

· Instructions: Provides all the instructions you may need on how to define the pricing within the Excel.

· Info: Contains all the input information from uStore, the price calculations and the output price that is returned to uStore.
Many of the fields contain comments. Hover over the red triangles to read them.

The info tab contains the following sections:

Note that some of the fields are specific only to the Regular or Upload Excel.

· General inputs: Prepopulated information from the Storefront that is used for calculating the price, such as quantity, productID and user information.

– Total Quantity: This is a calculated total number of items (no. of recipients x no. of copies per recipient). Use it to look up the correct price break, and multiply the item price by it.

– Profile ID, ProductID and StoreID: These fields are useful when you want to use a single Excel to price products of an entire store.

– User information: These fields may be used for a discount per user, per user group or per department.

· Automatic Pricing? : Prepopulated by uStore. This field denotes whether the price is automatically refreshed for the product being calculated.
When the pricing is automatically calculated, there may be properties with no values in the Storefront and therefore in the Excel.
When the pricing is not automatically calculated, a Recalculate button is present and the recalculation is blocked when there are mandatory properties with missing values. In this case, the Excel will never get a property with a missing value.
Currently, automatic pricing is available only for static, dynamic and Easy Upload products in NG stores, with Single Page mode.

· Fulfillment properties / Composite Fulfillment Properties: Assigning values for fulfillment properties during order fulfillment in the Back Office, recalculates the price. These properties in the Excel get the assigned values.

· Customization: Contains dial values, as selected by the shopper.

· Property Table

– Property Name: Lists all the properties that are used for price and cost calculation and property dependencies.
Note that when using the Excel pricing model, you cannot use parentheses (or other special characters) to name product properties.

– Property Input: Prepopulated by uStore, this section brings the property values and whether the property is contained in the ordered product.

– Property Output: This section controls the dependencies between properties. You can write here formulas that will change the available options of a property (Included Options and Excluded Options columns), override the selected value (Selection column) and control the visibility of the property (Visibility column).
Currently, this is available only for static, dynamic and Easy Upload products in NG stores, with Single Page mode.

– Price/Cost Calculations: This section allows you to calculate the price and cost of an ordered product.

§ Cheapest Option: In this column enter the value of the cheapest option of the property. It will then be used for calculating the price when the shopper did not yet select a value for this property. The value of this field will be ignored when pricing is not being calculated automatically.

§ Value for calculation: This column is automatically calculated to present the value of the property that is used for the calculation, either the overridden selection (as denoted by the Property Output Selection column), the option the shopper selected (denoted by the Property Value column) or the cheapest option (denoted by the Cheapest Option column) in this precedence. Use this value for price lookup.

§ Price and Cost: Place the total price and total cost calculation for each property in these fields. Note that these should be the price/cost for the whole order, and not for an individual item in the order, meaning you may need to multiply the item price/cost by the Total Quantity.

§ Totals: Summary of the price and cost of all properties that are used in the product.

§ Custom output (advanced): Enter any additional information that will be saved to the database, such as pricing breakdown by components.

Use only:

§ Plain text: Any value that does not contain triangular braces (< or >)

§ XML:

– Every tag must be accompanied by a corresponding closing tag (<tag>, </tag>)

No unclosed tags should exist between the specified tags (<tag><unclosed></tag>)

– You may use self-closing tags (<tag />)

– Attributes can be included within each tag (<tag attribute='value' />)

· Pricing Outputs: This table returns the total price, total cost and minimum price to uStore, to be presented to the shopper. Custom output is used for passing data to be saved in the database for the ordered item, to be available for reports.

· Composite product specific settings

– Composite Properties: Each field contains the value of the composite property as selected by the shopper. You can add more properties according to the product setup.

– Actual Paper Size: A table containing the page sizes and number of pages of the Composite document.

– Page Enumeration: A table containing all pages of the Composite document, including their individual property values as selected by the shopper. This table can be used to price the document by its page setup.

· File Upload Information: This table is for the Easy Upload product only, and will not appear for other product types. It returns all the relevant information regarding the uploaded file.

The following information is returned: total number of pages, number of B&W pages, number of color pages, uploaded file extension, uploaded file page height (points), uploaded file page width (points).

Upload your Excel pricing file

You can set up an Excel file for an individual product, or have an Excel file that is assigned to an entire store or product profile, which prices multiple products.
Using a store/profile level Excel makes it easier to handle prices in one place. Using a product-level Excel allows for unique pricing of a specific product.

When a product is ordered, the appropriate Excel is selected as follows: first, uStore will try to find an Excel defined for the specific product. If not found and the product is linked to a profile, uStore will try to find an Excel uploaded to the linked product profile. If not found again, uStore will try to find the Excel file set for the store. If neither is found, price calculation will fail.

After saving your updated Excel pricing file, you can upload it to the store, profile or product.

· If this is the first time you have added a custom Excel price file, the Upload link will display.

· If you have already uploaded a custom Excel price file, the Replace link will display.

If you have already uploaded a custom Excel file to the store, product profile or product, uStore will also allow you to download the current spreadsheet that is in use by clicking Download File in Use.

Debug your Excel pricing calculation

When you develop the Excel pricing file, you may want to see the debug file that uStore used during the calculation, including the actual quantity and property values.

To turn on debugging:

1. Go to Store setup > Advanced Tab > Excel Pricing.

2. Select the Turn on Excel pricing debugging checkbox.

3. Click Take Offline.

4. Click Save & Place Online.

Note that debugging may incur performance degradation. Turn it off once you are done debugging.

To download the debug file:

1. In the Storefront, order a product that is configured to use Excel pricing.

2. Return to the back office and edit the product that you ordered.

3. Click Product Pricing Setup. On the Set Prices tab, you will find a link to Download Debug file which will download the last Excel file used to price this product including all the quantity, properties, input and output values.