This topic contains instructions for clients that are using the original architecture and new architecture. You are on the new architecture if you have the Data Warehouse Views
section available after selecting Manage Data
from the main toolbar.
This topic demonstrates how to set up a dashboard that track the performance of your free shipping threshold. This dashboard, shown below, is a great way to A/B test two free shipping thresholds. For example, your company might be unsure whether you should offer free shipping at $50 or $100. You should perform an A/B test of two random subsets of your customers, and perform the analysis in Commerce Intelligence.
Before getting started, you want to identify two separate time periods where you have had different values for your store’s free shipping threshold.
This analysis contains advanced calculated columns.
If you are on the original architecture (for example, if you do not have the Data Warehouse Views
option under the Manage Data
menu), you want to reach out to the support team to build out the below columns. On the new architecture, these columns can be created from the Manage Data > Data Warehouse
page. Detailed instructions are given below.
sales_flat_order
table
Order subtotal (buckets)
Original Architecture: created by an analyst as part of your [FREE SHIPPING ANALYSIS]
ticket
Order subtotal (buckets)
New Architecture:
base_subtotal
, that can be used as the basis of this new column. If not, it can be a calculated column that excludes shipping and discounts from revenue.The “bucket” sizes depend on what is appropriate for you as a client. You could start with your average order value
and create some buckets less than and greater than that amount. When looking at the calculation below, you see how to easily copy part of the query, edit it, and create additional buckets. The example is done in increments of 50.
Column type - Same table, Column definition - Calculation, Column Inputs-
base_subtotal
, or calculated column
, Datatype
: Integer
case when A >= 0 and A<=200 then 0 - 200
A< 200
and A <= 250
then 201 - 250
A<251
and A<= 300
then 251 - 300
A<301
and A<= 350
then 301 - 350
A<351
and A<=400
then 351 - 400
A<401
and A<=450
then 401 - 450
No new metrics!!!
Make sure to add all new columns as dimensions to metrics before building new reports.
Average order value with shipping rule A
Average order value
Metric A
: Average Order Value
Time period: Time period with shipping rule A
Interval: None
Chart Type: Scalar
Number of orders by subtotal buckets with shipping rule A
Number of orders
You can cut off the tail end by showing the top X
sorted by
Order subtotal
(buckets) in the Show top/bottom
.
Metric A
: Number of orders
Time period: Time period with shipping rule A
Interval: None
Group by: Order subtotal (buckets)
Chart Type: Column
Percent of orders by subtotal with shipping rule A
Metric: Number of orders
Metric: Number of orders
Group by: Independent
Formula: (A / B)
Format: %
Metric A
: Number of orders by subtotal (hide)
Metric B
: Total number of orders (hide)
Formula: % of orders
Time period: Time period with shipping rule A
Interval: None
Group by: Order subtotal (buckets)
Chart Type: Line
Percent of orders with subtotal exceeding shipping rule A
Metric: Number of orders
Perspective: Cumulative
Metric: Number of orders
Group by: Independent
Formula: 1- (A / B)
Format: %
Metric A
: Number of orders by subtotal
Metric B
: Total number of orders (hide)
Formula: % of orders
Time period: Time period with shipping rule A
Interval: None
Group by: Order subtotal (buckets)
Chart Type: Line
Repeat the above steps and reports for Shipping B and the time period with shipping rule B.
After compiling all the reports, you can organize them on the dashboard as you desire. The result may look like the image at the top of this page.