# ABC classification

> Classify items into A, B and C by their share of a total, such as products by sales, with reusable AQL functions.

ABC classification sorts items by a measure, biggest first, and groups them by how much of the total they cover together. It's the 80/20 idea: a few items make up most of the volume.

- **A**: the top items that together cover the first 70% of the total.
- **B**: the next items, up to 90%.
- **C**: the rest, the long tail.

For example, it shows which products bring in most of your sales, so you know where to focus stock and promotion.

## How it works

A product's class doesn't depend on its own sales alone. It depends on how much the products bigger than it already cover.

| Product | Sales | Running total, biggest first | Share of total | Class |
|---|---|---|---|---|
| P1 | 50 | 50 | 50% | A |
| P2 | 20 | 70 | 70% | A |
| P3 | 15 | 85 | 85% | B |
| P4 | 10 | 95 | 95% | C |
| P5 | 5 | 100 | 100% | C |

The functions compute this in two parts:

- **Running total, biggest first:** `window_sum(m, order: m | desc())` adds this item and every item bigger than it.
- **Total:** `m | of_all(products, keep_filters: true)` is the total over all items, with your filters kept. If you filter to one year, the classes are computed for that year.

## Step 1: Add the function library

Add this `AqlFunctionModule` to your project.

```aml title="library/aql-functions/abc.aml"
AqlFunctionModule Abc {
  // Cumulative share of the total, items sorted by pMeasure descending.
  function CumulatedPct {
    param pMeasure {
      type: 'metric'
    }
    param pItemModel {
      type: 'model'
    }
    return_type: 'number'
    definition: @aql
      const pct = safe_divide(
        window_sum(pMeasure, order: pMeasure | desc()),
        pMeasure | of_all(pItemModel, keep_filters: true)
      );
      case(when: pct > 1, then: 1, else: pct)
    ;;
  }

  // 'A' up to pA, 'B' up to pB, 'C' after. null bounds use 0.7 and 0.9.
  function Class {
    param pMeasure {
      type: 'metric'
    }
    param pItemModel {
      type: 'model'
    }
    param pA {
      type: 'number'
    }
    param pB {
      type: 'number'
    }
    return_type: 'text'
    definition: @aql
      case(
        when: CumulatedPct(pMeasure, pItemModel) <= coalesce(pA, 0.7), then: 'A',
        when: CumulatedPct(pMeasure, pItemModel) <= coalesce(pB, 0.9), then: 'B',
        else: 'C'
      )
    ;;
  }
}
```

## Step 2: Import it and add the metrics

Import the library into your dataset, then define the measure to classify by and the ABC metrics.

```aml
Dataset ecommerce {
  models: [orders, order_items, products]

  function_module Abc: Abc

  metric sales {
    label: 'Sales'
    type: 'number'
    definition: @aql order_items | sum(order_items.quantity * products.price) ;;
  }

  metric abc_pct {
    label: 'ABC Cumulative %'
    type: 'number'
    definition: @aql Abc.CumulatedPct(sales, products) ;;
  }

  metric abc_class {
    label: 'ABC Class'
    type: 'text'
    definition: @aql Abc.Class(sales, products, 0.7, 0.9) ;;
  }
}
```

## Parameters

### `Abc.Class`

| Parameter | Type | What it is |
|---|---|---|
| `pMeasure` | `metric` | The measure to classify by, such as `sales`. It must add up across items, like a sum or a count. |
| `pItemModel` | `model` | The model of the items you classify, such as `products`. |
| `pA` | `number` | Upper bound of class A, from 0 to 1. `null` uses 0.7. |
| `pB` | `number` | Upper bound of class B, from 0 to 1. `null` uses 0.9. |

### `Abc.CumulatedPct`

Takes `pMeasure` and `pItemModel` as above. Returns the item's cumulative share of the total, from 0 to 1.

## Use it in a table

Put the item in the table and sort by the measure, biggest first.

```aql
explore {
  dimensions {
    product_name: products.name
  }
  measures {
    sales: sales,
    abc_pct: abc_pct,
    abc_class: abc_class
  }
  sorts {
    sales desc
  }
}
```

## Limitations

- The item must be in the table, for example `products.name`. The class is computed from the rows of the result.
- Classes are fixed to A, B and C. Change the bounds with `pA` and `pB`.
- The measure must add up across items. An average or a distinct count doesn't give a meaningful cumulative share.