Analyze Customer Order Frequency
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
Video Tutorial
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
- Initial Setup
- Final Setup
// 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)
]
}
// 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)
]
dimension cohort_quantity_per_order {
model: orders
label: "Cohort Quantity per Order"
type: "number"
definition: @aql order_items
| sum(order_items.quantity)
| dimensionalize(orders.id)
;;
}
metric count_orders {
label: "Count Orders"
type: "number"
definition: @aql count(orders.id);;
}
metric pot_orders {
label: "% Orders"
type: "number"
definition: @aql (count_orders*1.0) / (count_orders | of_all(orders)) ;;
}
metric cumulative_percent_order_quantity {
label: "Cumulative % Orders"
type: "number"
definition: @aql orders
| group(cohort_quantity_per_order)
| sum(pot_orders)
| running_total(cohort_quantity_per_order)
;;
}
}
High-level flow
- Dimensionalize the metric: We already have a
quantity
dimension inorder_items
, and we’ll extract the same dimension fororders
without making any changes upstream. This dimension will serve as the X-axis later on. - Create
Count Orders
metric: This metric will be used for the Y-Axis bar - Create percent of total metric: This metric will be used to calculate cumulative percent.
- Create nested aggregation and cumulative metric : Once we have
Percent of Total
metric, we can use therunning_total
function to computeCumulative %
by running it onQuanity per Order
dimension. This metric will be used for the second Y-Axis line
Implementation
1. Dimensionalize the metric
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 dimensionalize
along with cross-model calculation capability directly at the BI layer to bypass that constraint. In the definition of cohort_quantity_per_order
below, dimensionalize
helps perform sum(orders_item.quantity)
expression to the specific grain of orders
, without being coerced to a scalar value like other metrics. 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)
| dimensionalize(orders.id)
;;
}
}
2. Create Count Orders
metric
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 metric
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)
;;
}
}