# Analyze Customer Order Frequency

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**: This metric will be used for the Y-Axis bar`Count Orders`

metric**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)

;;

}

}