Setting Up The Excel Delivery Provider

Note: If you're a StoreFlow Cloud customer, the Excel delivery provider is included in your subscription. Otherwise, contact Support to purchase this plugin.

Overview

The Excel delivery provider uses an Excel spreadsheet to calculate the shipping price of your order. This gives you flexibility in offering dynamic shipping prices, assigning different prices based on calculations, and enables you to provide discounts based on different combinations of delivery packages or containers, product properties, or to set prices according to customization values.

During checkout the shipping calculations will be driven from the Excel Delivery template(s).

Prerequisites

  • uProduce PersonalEffect

  • uProduce development/test server

  • Prior to installing/upgrading the Excel Delivery Provider plugin, check with XMPie Professional Services to ensure the plugin is compatible with the new release.

Main features

  • Ability to offer dynamic shipping prices using Microsoft Excel.

  • Ability to assign Excel templates to different stores.

  • Ability to assign different prices based on calculations:

    • Default price

    • Fixed price by package name

    • Price based on package weight

    • Price based on product property

    • Shipping charges based on cart total

Getting started with the Excel delivery provider

When setting up a store, you define how products will be shipped and delivered to customers. In the Delivery Settings tab (Store Setup) you define which delivery providers will be available in your store. Customers can then distribute their products by selecting from a list of the available delivery providers.

Define a delivery package

Delivery packages allow you to add custom packages that will be used to ship ordered products.

Start by creating a delivery package that may be assigned to products.

  1. Open a web browser and login to: http://your_ustore_backoffice_domain/ustoreadmin

  2. On the menu bar click Presets, and then select System Setup.

  3. From System Setup list, select Delivery Package.

    The list of all defined packages is displayed. Here you can add new packages for your store to be used for delivery, or edit existing ones.

    Using the paging arrows you can browse to see the two default Excel delivery packages which were created for the Excel delivery provider: IDs 10000-10001. You may create additional packages. For example, you may want to add a business card box that holds 500 or 1000 business cards.

  4. Click Add New to create a new delivery package.

  5. In the Delivery Package page, fill in the following fields:

    • Shipping Package Id: Unique Id of the package. Auto-generated and cannot be modified.

    • Name: Name of the package to be displayed during product setup, as described in Setting Up Delivery.

    • Height, Length, Width: Package dimensions. The measurement units are determined in the Length Unit field.

    • Tare Weight: Package weight. The measurement units are determined in the Tare Weight Unitfield. Choose pounds, kilograms, grams, or ounces.

    • Max Content Weight: Maximum loading weight allowed. The measurement units are determined in the Max Content Weight Unit field. Choose pounds, kilograms, grams, or ounces.

    • Code: Code that will be referenced in Excel pricing templates.

    • Custom: Specify whether the package is a custom-made package or a standard carrier package.

  6. Click Save.

Define a delivery service

The Delivery Services table lists all of the predefined delivery services in the system. You can view and add new delivery services.

  1. Return to the System Setup list and click Delivery Services.

    The list of all delivery services is displayed.

    There are two default Excel delivery providers: ES1 and ES2.

  2. Click Add New to create a new service.

    Each delivery provider has a unique name, description, code, tracking link format and fixed address. In the following example, the name of the ES1 delivery service name has been set to Excel 2-Day. Service status can be set to active or inactive - which will make the service unavailable in the storefront.

  3. Enter the required information, and click Save.

Define a delivery service package

The Delivery Service Package table allows you associate a delivery service with a delivery package. For example, a small box and a poster tube could be shipped using the same service, i.e. Next Day, 2-Day.

  1. Return to the System Setup list and click Delivery Service Package.
    The list of all delivery service packages is displayed.

  2. Click Add New to associate a delivery package with a delivery service.

  3. Enter the required information, and click Save.

Set up delivery accounts

The accounts you add here are made available to the store administrators when they configure the store during the store setup process, as detailed in Defining Delivery Settings.

  1. On the menu bar click Presets, and then select Delivery Setup.

    The Delivery Provider list is displayed, listing all currently defined delivery providers.

  2. Select the Delivery Excel Pricing link.

  3. In the Delivery Provider Details page, click Add Account.

  4. Enter the required information.

    • Account Name: Unique name of the Excel Delivery Provider.

    • Description: Description of the account.

    • StoreDebugInfo: Select to generate debug messages when required.

    • Currency:The currency you select here must be identical to the one used at the store.

    • Customer Pays for Delivery:When selected, charges the customer with the delivery expenses. It is recommended to leave this setting enabled.

Set the store's delivery setting

When you set up the store, you define which delivery providers will be available in your store. Customers can then distribute their products by selecting from a list of the available delivery providers. Fore more information, see Defining Delivery Settings.

  1. Select the required store, and then click Store Setting > Set Up Store > Delivery Settings tab.

  2. In the Shipping providers section, select Delivery Excel Pricing.

  3. Choose a Delivery Excel Pricing Provider from the list.

  4. Click Save, and then proceed to the Delivery Services tab.

  5. Enable the Delivery Excel Services that you want to be available in the store.

  6. Click the Save & Place Online button.

The Excel delivery worksheet

The Default Deliver Excel Pricing worksheets calculate shipping prices based on the packages and services that were previously configured.

Excel files can be accessed from Presets > Delivery Set Up > New excel Provider.

The Default .xls is used by default for the Excel delivery account. To apply a custom worksheet to an account, its naming convention should be as follows: ACCID_account number.xls

You can download the file to edit it, and then upload it with the same name.

Default Excel Pricing Engine worksheet

The Lookup sheet of the Excel file contains the delivery services, packages, and prices based on weight.

The package codes can be found in the Delivery Services list.

Prices are calculated using Excel formulas. Click the Outputs sheet.

It is recommended to save a backup copy of the Default Excel Pricing template(s) prior to making changes.

Click on cell A4. This is the formula that calculates the delivery price:

=IFERROR(VLOOKUP(CONCATENATE(inDeliveryService_Code," ",inPackage_0_

Code),Lookup!$C$4:$M$13,1+MATCH(inPackage_0_Weight,Lookup!D3:M3,1),0),0)

Editing the formula is not required, however only the Lookup and Output sheets should be modified if desired.

Variables used in the Output formula are located in the various template worksheets.

Click on the Inputs sheet, and then click on the B4 cell. The variable used for Delivery Service Id is inDeliveryService_Id.

Additional variables can be found throughout Input_{name} sheets. For example, Input sheet, cell C4 contains the variable inDeliveryService_Code which is displayed in the default Output formula shown above.

For more information regarding the Excel Lookup function, see Lookup function.

Excel spreadsheet structure

The default Excel spreadsheet includes eight main worksheets:

  • Service Input parameters and some global parameters

  • uStore Packages calculations

  • Address From

  • Address To

  • Cart Items List

  • Product properties List

  • Output

  • Lookup

Lookup worksheet

Lookup tables contain any information that customers would like to use for delivery calculation. A specific example demonstrates the lookup table of the Default_PriceDependOnPackageWeightCalculation.xls sample file.

The Output worksheet can contain formulas for data retrieval from the Lookup tables and calculations. For example, A4 of the Output worksheet contains the following formula:

=IFERROR(VLOOKUP(CONCATENATE(inDeliveryService_Code,"

",inPackage_0_Code),Lookup!$C$4:$M$13,1+MATCH(inPackage_0_Weight,Lookup!D3:M3,1),0),0)

In pseudo language, this example will concatenate the service code with the package code, take the weight of the first package and match it against the Weight Starting Point table to find the appropriate value. For service code value ES1, package code 1000 and package weight 100, the price will be 4.39.

Output worksheet

The Output worksheet contains a single mandatory variable: outTotalPrice. This variable will be returned to the user as a calculation value for the service. uStore understands only numeric values.

The calculation can have a zero value. A non‐numeric or null value will result in uStore not showing a particular service for the customer selection. The Excel Delivery provider will populate the worksheet with data per each delivery provider service of the Excel delivery provider.

For debug purposes, select the Store Debug Info checkbox in the Excel Delivery Account configuration.

Additional default Excel Pricing Engine worksheets

In addition to the Default Excel Pricing worksheet, there four optional pricing files that can be used instead.

Go to: [drive]:\XMPie\uStore\App\uStoreShared\DeliveryExcelPricingEngine\Defaults

  • Fixed Price By Package Name - Fixed price(s) can be assigned to package name(s).

  • Price Depends On Package Weight - Conditional price determined by the weight of the package.

  • Price Depends On Product Property - Conditional price determined by a product property assigned to the product.

  • Shipping Charges By Cart Total - Conditional shipping price determined by the shopping cart total amount.