Analyze Customer Order Frequency

info

This is part of our beta expression language AQL. Learn more. Request beta.

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

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) 
      | exact_grains(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 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.
Create Count Orders metric: This metric will be used for the Y-Axis bar
Create percent of total metric: This measure will be used to calculate cumulative percent.
Create nested aggregation and cumulative metric : Once we have Percent of Total measure, we can use the running_total function to compute Cumulative % by running it on Quanity per Order dimension. This measure 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 measure 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 measure

This measure 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

Introduction​

Setup​

High-level flow​

Implementation​

1. Dimensionalize the measure​

2. Create Count Orders measure​

3. Create percent of total measure​

4. Create cumulatively nested aggregation measure​

5. Bring it together​