# Customer Order Frequency

> Build a cumulative order-frequency chart in AQL using dimensionalize, percent-of-total, and running_total to show how item counts distribute across orders.

## 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
<iframe width="560" height="315" src="https://www.youtube.com/embed/Tiy9gBcAgSc?si=yH4-9DWAqPY80iuo" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" referrerpolicy="strict-origin-when-cross-origin" allowfullscreen></iframe>

## 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 
```aml
// 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

```aml
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`

```aml
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](/as-code/aql/cookbook/aql-percent-of-total) directly for this use case, we still need this metric to define the `Cumulative % Orders` metric later on

```aml
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`

```aml
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