Ranking

IntroductionCopy Anchor Link

The requirement to rank items in a report is a common business requirement. For example, you need to know the top-performing products in a certain aspect so you can double-down on them, or knowing the worst performing items so you can investigate for further problems.

In this guide, we will walk you through some examples of how to use the rank() function to answer the following questions:

• What are the products with the highest sales in the whole platform?
• What are the products rankings within each category?
• Compare products’ ranks within the category and across all categories

Overall, ranking falls into two categories: Dynamic Ranking and Static Ranking.

• Dynamic Ranking: Affected by filters and dimensions. A dynamic ranking field is a metric.
• Static Ranking: Not affected by filters and dimensions. A Static Ranking field is a dimension.

The first two questions can be answered by a ranking metric (dynamic ranking), while the third question need a ranking dimension (static ranking)

Example 1: Ranks all products by total salesCopy Anchor Link

SetupCopy Anchor Link

In this example, the e_commerce dataset will contain the following models: order_items , products .

// order_items.model.aml
Model order_items.model.aml {
  ...
  dimension order_id {...}
  dimension product_id {...}
  dimension quantity {...}
}

// products.model.aml
Model products {
  ...
  dimension id {...}
  dimension price {...}
  dimension category_id {...}
}

// e_commerce.dataset.aml
Dataset e_commerce {
  ...
  models: [order_items, products]
  relationships: [
    relationship(order_items.product_id > products.id, true)
  ]
  
  metric revenue {
    label: 'Revenue'
    type: 'number'
    definition: @aql sum(order_items, order_items.quantity * products.price);;
  }

  metric ranking {
    label: 'Ranking'
    type: 'number'
    definition: @aql rank(order: total_sales | desc());;
  }

}

High-level flowCopy Anchor Link

1. Create a Revenue metric: In the e_commerce dataset, we create a simple revenue metric that calculate order value by summing over order_items.quantity multiplied by products.price.
2. Create a Ranking metric: Use the rank() function to take revenue as in put and generate ranking numbers in descending order (larger revenue value comes first)
3. Visualize: Include the Ranking metric with Product Name in the visualization to answer your questions

ImplementationCopy Anchor Link

1. Create a Revenue metricCopy Anchor Link

In the definition file of the e_commerce dataset, we can easily create a Revenue metric that combines fields from two different models (order_items and products). This will be the basis for our ranking:

Dataset e_commerce {

  ...

  metric revenue {
    label: 'Revenue'
    type: 'number'
    definition: @aql coalesce(
      order_items | sum(order_items.quantity * products.price),
      0
    ) ;;
  }
}

In cases where a dimension (like products, or users) have no order records, the corresponding revenue will be 0 instead of NULL. This way we do not need to worry about different ways that different databases handle NULL when ranking.

2. Create a Ranking metricCopy Anchor Link

We continue to create the Ranking metric that use the Revenue metric as the ordering field. The syntax revenue | desc() is necessary if we want the revenue values are ranked in descending order.

Dataset e_commerce {

  ...

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

  metric ranking {
    label: 'Ranking'
    type: 'number'
    definition: @aql rank(order: revenue | desc());;
  }
}

3. VisualizeCopy Anchor Link

You can combine the Ranking metric with any dimensions to calculate it ranking basing on revenue. For example, here we rank products by revenue:

The ranking metric can also be used to filter. For example, filter(product_rank <= 3) keeps only the top 3 products by revenue:

Example 2: Ranking within a categoryCopy Anchor Link

Sometimes knowing what sells the most across the whole platform is not insightful enough - we want to know the top products within each category. In this example, we will explore how to answer this question.

SetupCopy Anchor Link

The setup will be quite similar to Example 1. The only difference is that we will add the categories model to the dataset:

// order_items.model.aml
Model order_items.model.aml {
  ...
  dimension order_id {...}
  dimension product_id {...}
  dimension quantity {...}
}

// products.model.aml
Model products {
  ...
  dimension id {...}
  dimension price {...}
  dimension category_id {...}
}

// merchants.model.aml
Model merchants {
  ...
  dimension id {...}
  dimension name {...}
}

// categories.model.aml
Model categories {
  ...
  dimension id {...}
  dimension name {...}
}

// e_commerce.dataset.aml
Dataset e_commerce {
  ...
  models: [order_items, products, merchants]
  relationships: [
    relationship(order_items.product_id > products.id, true)
    relationship(products.merchant_id > merchants.id, true)
    relationship(products.category_id > categories.id, true)
  ]
  
  metric revenue {
    label: 'Revenue'
    type: 'number'
    definition: @aql sum(order_items, order_items.quantity * products.price);;
  }

  metric ranking_by_cate {
    label: 'Ranking by Cate'
    type: 'number'
    definition: @aql rank(order: revenue | desc(), partition: categories.name);;
  }
}

High-level flowCopy Anchor Link

1. Create a Ranking by Category metric: In this new ranking metric, we will include Category Name as the partition field
2. Visualization: For this ranking metric with partition to work, we will need to include the partition field (Category Name) field in the visualization.

ImplementationCopy Anchor Link

1. Create a Ranking by Category metricCopy Anchor Link

When defining the ranking metric, we reuse the Revenue metric that we have defined. We will pass the categories.name field into the partition argument of the rank() function to specify that the ranking should be calculated within a category:

Dataset e_commerce {

  ...

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

  metric ranking_by_cate {
    label: 'Ranking by Category'
    type: 'number'
    definition: @aql rank(order: revenue | desc(), partition: categories.name);;
  }
}

2. QueryCopy Anchor Link

When specifying the categories.name field as the partition field, we must include it as a dimension in the explore for the ranking to work correctly. Filtering on rank <= 3 then keeps the top 3 products within each category.

Example 3: Static Product RankingCopy Anchor Link

The two ranking examples we have done so far fall under the category of “Dynamic Ranking”. The ranking is dynamic in the sense that the rank values are affected by new filters and dimensions that we include in the explore.

For example, without any filter, the product Chips 1 has rank 5, but when applying the filter Category Name is “Snack”, the ranking is re-calculated, and Chips 1 has rank 1.

What if we want to display only Products in the Snacks category, but still retain their overall rankings? In this example, we will explore how to produce a Static Ranking that is unaffected by filters.

SetupCopy Anchor Link

The only difference with the other examples is how we create the ranking field. In previous examples, the ranking fields are metrics, but in static ranking, the field will be a dimension.

  // order_items.model.aml
  Model order_items.model.aml {
    ...
    dimension order_id {...}
    dimension product_id {...}
    dimension quantity {...}
  }
  
  // products.model.aml
  Model products {
    ...
    dimension id {...}
    dimension price {...}
    dimension category_id {...}
  }
  
  // merchants.model.aml
  Model merchants {
    ...
    dimension id {...}
    dimension name {...}
  }
  
  // categories.model.aml
  Model categories {
    ...
    dimension id {...}
    dimension name {...}
  }
  
  // e_commerce.dataset.aml
  Dataset e_commerce {
    ...
    models: [order_items, products, merchants]
    relationships: [
      relationship(order_items.product_id > products.id, true)
      relationship(products.merchant_id > merchants.id, true)
      relationship(products.category_id > categories.id, true)
    ]
    
    metric revenue {
      label: 'Revenue'
      type: 'number'
      definition: @aql sum(order_items, order_items.quantity * products.price);;
    }
  
    dimension product_ranking_dim {
      label: 'Product Ranking (Dimension)'
      type: 'number'
      model: products
      definition: @aql  rank(order: revenue | dimensionalize(products.id) | desc());;
    }
  
  }

High-level FlowCopy Anchor Link

1. Create a Ranking dimension: Instead of creating a ranking as a metric, now we will define it as a dimension.
2. Visualization: For this ranking metric with partition to work, we will need to include the partition field (Category Name) field in the visualization.

ImplementationCopy Anchor Link

1. Create a Ranking DimensionCopy Anchor Link

We will reuse the revenue metric in the dataset as the basis for the ranking. Then, we define the product_ranking_dim dimension in the dataset file, but specify that this dimension belongs to the products model.

Dataset e_commerce {
  ...
  metric revenue {
    label: 'Revenue'
    type: 'number'
    definition: @aql sum(order_items, order_items.quantity * products.price);;
  }

  dimension product_ranking_dim {
    label: 'Product Ranking (Dimension)'
    type: 'number'
    model: products
    definition: @aql  rank(order: revenue | dimensionalize(products.id) | desc());;
  }
}

For more details about this process of dimensionalize a metric, please refer to our dimensionalize doc.

2. VisualizationCopy Anchor Link

To compare the result created by the ranking metric and dimension, we will use the following fields: Product ID, Product Name, Ranking, Product Ranking (Dimension). Without any filtering, the two return the same ranking values:

Now if we add a Category Name is "Audio Gadgets" filter, the Ranking metric's values are re-calculated, while the Product Ranking dimension's values are maintained. There are gaps between the dimension values because products of other categories have been removed from the result:

Example 4: Performance tiers with ntile()Copy Anchor Link

Sometimes you don't want exact ranks. You want to bucket items into tiers (top 25%, bottom quartile, etc.). ntile() splits an ordered set into N equal-sized buckets.

The setup is the same as Example 1. We define a dimension on products that assigns each product to a quartile based on revenue:

Dataset e_commerce {
  ...
  metric revenue { ... }

  dimension product_performance_tier {
    model: products
    label: 'Product Performance Tier'
    type: 'text'
    definition: @aql
      case(
        when: ntile(4, order: revenue | desc()) = 1, then: 'Top 25%',
        when: ntile(4, order: revenue | desc()) = 2, then: '25-50%',
        when: ntile(4, order: revenue | desc()) = 3, then: '50-75%',
        else: 'Bottom 25%'
      )
      | dimensionalize(products.id)
    ;;
  }
}

ntile(4, order: revenue | desc()) ranks products by revenue and divides them into 4 buckets. Bucket 1 contains the top 25%. We wrap it in dimensionalize(products.id) so the tier is fixed to each product and reusable as a regular dimension.

Use it to analyze the distribution of products across tiers:

explore {
  dimensions {
    products.product_performance_tier
  }

  measures {
    product_count: count(products.id),
    total_revenue: revenue,
    avg_revenue_per_product: revenue / count(products.id)
  }
}

Swap 4 for 10 to get deciles, or use ntile(100, ...) for percentiles.