Analyze Customer Order Frequency

Knowledge Checkpoint

A grasp of these concepts will help you understand this documentation better:

• Cross-Model Reference
• dimensionalize

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

// 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)
  ]
}

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)
  ]

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