Skip to main content

Analyze Customer Order Frequency

Knowledge Checkpoint

A grasp of these concepts will help you understand this documentation better:

Introduction

Understanding customer order frequency is essential for businesses seeking to optimize their sales and marketing strategies. By analyzing how often customers make purchases, businesses can uncover valuable insights into customer behavior, loyalty, and revenue potential.

In this guide, we will walk you through how to produce effectively reusable metrics and visualizations in Holistics to showcase Order Frequency metrics

Setup

For this use case, we will work with order_items and orders tables from e_commerce datasets. orders_items has an N-1 relationship with orders

// order_items.model.aml
Model order_items {
...
dimension id {...}
dimension name {...}
}
// orders.model.aml
Model orders {
...
dimension id {...}
dimension created_at {}
}
// e_commerce.dataset.aml
Dataset e_commerce {
...
models: [order_items, orders]
relationships: [
relationship(order_items.user_id > orders.id, true)
]
}

High-level flow

  1. Dimensionalize the measure: We already have a quantity dimension in order_items, and we’ll extract the same dimension for orders without making any changes upstream. This dimension will serve as the X-axis later on.
  2. Create Count Orders metric: This metric will be used for the Y-Axis bar
  3. Create percent of total metric: This metric will be used to calculate cumulative percent.
  4. Create nested aggregation and cumulative metric : Once we have Percent of Total metric, we can use the running_total function to compute Cumulative % by running it on Quanity per Order dimension. This metric will be used for the second Y-Axis line

Implementation

1. Dimensionalize the measure

Traditionally, in order to re-use an aggregation as a regular field within a model, the process requires pre-calculation in upstream modeling before bringing it to BI tool or either creating an additional Query Model at the BI layer. This constraint leads to a coupling between the BI and modeling layers.

In AQL, you can leverage exact_grains along with cross-model calculation capability directly at the BI layer to bypass that constraint. In the definition of cohort_quantity_per_order below, exact_grains helps perform sum(orders_item.quantity) expression to the specific grain of orders , without being coerced to a scalar value like other measures. Due to this reason, we’re allowed to materialize this expression as dimension

Dataset e_commerce {
(...)

dimension cohort_quantity_per_order {
model: orders
label: "Cohort Quantity per Order"
type: "number"
definition: @aql order_items
| sum(order_items.quantity)
| exact_grains(orders.id)
;;
}
}

2. Create Count Orders measure

This metric will be used for the Y-Axis bar to view the Count Orders of Quantity per Order

Dataset e_commerce {
(...)
dimension cohort_quantity_per_order {...}

metric count_orders {
label: "Count Orders"
type: "number"
definition: @aql count(orders.id);;
}

}

3. Create percent of total measure

Even though we don’t use Percent of Total directly for this use case, we still need this metric to define the Cumulative % Orders metric later on

Dataset e_commerce {
...
dimension cohort_quantity_per_order {...}
metric count_orders {...}

metric pot_orders {
label: "% Orders"
type: "number"
definition: @aql (count_orders*1.0) / (count_orders | of_all(orders)) ;;
}
}

4. Create cumulatively nested aggregation metric

This metric will be used to draw a percentage line on our combination chart. We’ll utilize running_total to calculate sum of % Orders

Dataset e_commerce {
...
dimension cohort_quantity_per_order {...}
metric count_orders {...}
metric _pot_orders {...}

metric cumulative_percent_order_quantity {
label: "Cumulative % Orders"
type: "number"
definition: @aql orders
| group(cohort_quantity_per_order)
| sum(orders.pot_orders)
| running_total(cohort_quantity_per_order)
;;
}
}

5. Bring it together


Let us know what you think about this document :)