DocumentationCommerceCommerce Intelligence User Guide

[PaaS only]{class="badge informative" title="Applies to Adobe Commerce on Cloud projects (Adobe-managed PaaS infrastructure) and on-premises projects only."}

Event Number Calculated Column

Last update: Mon Jul 24 2023 00:00:00 GMT+0000 (Coordinated Universal Time)

CREATED FOR:

  • Beginner
  • Intermediate
  • Admin
  • Developer
  • User

This topic outlines the purpose and uses of the Event Number calculated column available in the Manage Data > Data Warehouse page. Below is an explanation of what it does, followed by an example, and the mechanics of creating it.

Explanation

The Event Number column type identifies the sequence in which events occurred for a particular event owner, like a customer or user. If you are familiar with SQL, this column type is identical to the RANK function. It could be used to observe differences in behavior between first-time events, repeat events, or nth events in your data.

In cases of a tie, this column contains the same rank for the tied events, and skips the subsequent numbers. For example, if it were ranking the numbers 5,8,10,10,12, the ranks would be 1,2,3,3,5.

The most common use case of this column is to analyze first time buyers and repeat buyers. First time buyers are identified by adding a filter (to a metric or report) on Customer's order number = 1. Customer's order number is a column of the type Event Number.

Example

event_id
owner_id
timestamp
Owner's event number
**1
A
2015-01-01 00:00:00
1
**2
B
2015-01-01 00:30:00
1
**3
A
2015-01-01 02:00:00
2
**4
A
2015-01-02 13:00:00
3
**5
B
2015-01-03 13:00:00
2

In the above example, the column Owner's event number is an Event Number column. It ranks the owner’s events in the order in which they occurred (based on the timestamp column).

For instance, consider all rows where owner_id = A. The first row in the table is the earliest timestamp for this owner, followed by the third row in the table, followed by the fourth row in the table.

Mechanics

Here are some instructions on creating an Event Number column:

  1. Navigate to the Manage Data > Data Warehouse page.

  2. Navigate to the table on which you want to create this column.

  3. Click Create a Column and choose the EVENT_NUMBER (…) column type: under the Same Table section.

  4. The first dropdown Event Owner specifies the entity for which the rank is to be determined. In the case where a Customer's order number, a customer identifier such as customer_id or customer_email would be the Event Owner.

  5. The second dropdown Event Rank specifies the column that enforces the sequence that determines the rank of the row. In the case where a Customer's order number, the created_at timestamp would be the Event Rank.

  6. Under the Options dropdown, you can add filters to exclude rows from being considered. The excluded rows have a NULL value for this column.

  7. Provide a name to the column and Click Save.

  8. The column is available to use immediately.

recommendation-more-help
e1f8a7e8-8cc7-4c99-9697-b1daa1d66dbc