Rank
Rank and compare values in your data. The Rank tool provides a seamless way to organize your data, allowing for meaningful assessments. The Rank tool offers the flexibility to rank rows based on your specified criteria, either in ascending or descending order, ensuring alignment with your goals.
For example, consider a scenario where you need to rank your top employees for performance bonuses. Using the Rank tool, you can easily rank the top 10 Employees and award them a $1000 payout, then rank the next 10 Employees and award them $500.You can continue the pattern from there, adjusting as needed for the remaining bonuses.
Input
The Rank tool requires one data input.
Configuration
Use the following configuration options to configure the Rank tool.
Go to the Pipes module from the side navigation bar.
From the Pipes tab, click an existing pipe to open, or create a new pipe. To create a new pipe, read the Creating a pipe documentation.
In the Pipe builder, add at least one data source to your pipe. For more information on adding a data source, see the Data Input tool.
Click
+ Tool.The Tools modal opens, where you can add tools, such as the Aggregate tool, to your pipe.
In the Tools modal, search for Rank and then click + Add Tool.
Tip
You can also find the Rank tool in the Organize section.
Click the tool node and drag the line to the next tool to connect the tools. If you need to undo the action, click the line and then click Unlink.
In the configuration pane, under Rank type, select the rank type:
Rank: Ranks your data in order. When there are ties in the data, the next entry in the rank sequence starts at the number of items before it, and there is a gap in the rankings. For example, if you have 75 entries and 60 of them are tied at rank 1; the following rank starts ranking at 61.
Percent: Ranks your data in percentages.
Dense: Ranks your data in order. When there are ties in the data, the next item in the sequence starts at the next number after it. This means that there are no gaps in the rankings. For example, if the tie is at rank 4, the following item starts ranking at 5.
Under New column name, type the name for your new column.
Under Group by, select the columns to group together to rank based on the values of an existing column.
Under Sort by, select the text column in your data set that you want to sort by.
Under Sort direction, select the sort direction, ascending or descending order.
Optionally, click + Add order by to add a new sort condition.
Click on the tool name to rename your tool node to a meaningful name. Name your tools in a way that describes the function, not the object or the data action. For example, use “Look up rate” instead of “Join to rate table”.
Usage example
In this example, let's say you're a sales manager who wants to identify the top 10 customers based on the number of daily calls (Day Calls). Using the Rank tool, each customer is assigned a rank based on their Day Calls value, with Rank 1 awarded to the customer with the highest call volume.
Customer ID | Age | Gender | Voicemail Plan | Voicemail Messages | Day Calls | Day Mins |
IDF8A6CF92 | 48 | Male | Yes | 33 | 139 | 153 |
IDC422757D | 31 | Female | No | 0 | 103 | 152 |
ID4C84EF51 | 39 | Male | No | 0 | 104 | 280 |
ID68E1FDFA | 32 | Male | No | 0 | 139 | 144 |
IDDD842DBB | 12 | Male | No | 0 | 79 | 162 |
ID84A37E44 | 12 | Male | No | 0 | 127 | 195 |
IDA32CA4E1 | 25 | Male | No | 0 | 139 | 278 |
ID6B06EBED | 38 | Female | No | 0 | 79 | 156 |
IDB035E62B | 68 | Female | Yes | 31 | 132 | 197 |
IDD45E395A | 53 | Female | Yes | 25 | 141 | 215 |
Next, take your data and configure the Rank tool:
Setting | Value | Description |
|---|---|---|
Rank type | Rank | Assigns sequential ranks; when ties occur, the next rank skips ahead by the number of tied entries. |
New column name | Day_Calls_Rank | Appends a new integer column containing each customer's rank. |
Group by | None | All customers compete in a single ranking pool — no grouping is needed. |
Sort by | Day Calls | Ranks customers by their daily call volume. |
Sort direction | Descending | Assigns Rank 1 to the customer with the highest Day Calls value. |
Add order by | Customer ID - Ascending | Breaks ties consistently when multiple customers have the same Day Calls value. |
Next, run the Rank tool. You'll see a new Day_Calls_Rank column appended to your data. Filter the output to Day_Calls_Rank < 10 using the Filter tool to isolate the top customers.
Day_Calls_Rank | Customer ID | Day Calls |
1 | IDD45E395A | 141 |
2 | ID68E1FDFA | 139 |
3 | IDA32CA4E1 | 139 |
4 | IDF8A6CF92 | 139 |
5 | IDB035E62B | 132 |
6 | ID84A37E44 | 127 |
7 | ID4C84EF51 | 104 |
8 | IDC422757D | 103 |
9 | ID6B06EBED | 79 |
10 | IDDD842DBB | 79 |
IDD45E395A is ranked first with 141 Day Calls. The three customers tied at 139 calls are ranked 2, 3, and 4, respectively, with the tiebreaker (Customer ID ascending) determining the order within the tie group. Because the Rank type is used, the next rank after the tie group of three starts at 5, not 4.
Rank tool behavior with tied values
When a data set has ties, the Rank tool can handle them in different ways. Use the Rank type when you need strict sequentia positioning with gaps. Use the Dense type when you want continuous tier assignments with no gaps, such as to assign bonus tier logic.
With our data set example, this is how the Rank tool handles the tied values:
Rank type: With customers tied at 139 calls, they are assigned ranks 2, 3, 4 (with a tiebreaker), or rank 2 without a tiebreaker. The next rank after the tie group is 5.
Dense type: Customers tied at 139 calls are all assigned rank 2. The next rank after the tie group is 3.
Percent type: Customers tied at 139 calls are expressed as a value between 0 and 1, relative to all rows. No tiebreaker rank.
Common patterns
The Rate tool is commonly used in conjunction with the following tools:
Case tool: Use the Case tool to label your data in a specific way. The Case tool adds a label to data according to logical tests.