Customer Order Frequency

Introduction

This guide builds a cumulative order-frequency distribution: a chart that answers "what percent of our orders contain ≤ N items?" The X-axis is quantity-per-order, the Y-axis is order count, and a secondary line plots the running percentage.

Along the way, it walks through four reusable AQL building blocks: dimensionalize, percent-of-total, nested aggregation, and running_total, all chained on top of each other.

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

// 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

1. Dimensionalize the metric: 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 Quantity 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) 
    ;;
  }
}

5. Bring it together