Expressions: Aggregations On The Fly - Pattern Match

The Pattern Match functionality of on the fly Aggregations has multiple applications - examples are shown below.

See also:

Expressions: Aggregations On The Fly - Pattern Match Return Value On Transactional Table

Expressions: Aggregations On The Fly - Pattern Match Positional Wildcards

Expressions: Aggregations On The Fly - Pattern Match First Matched Pattern

Expressions: Aggregations On The Fly - Pattern Match Any Matched Pattern

 

Scenario:

Find people who

  1. have been to the same destination 4 times

  2. been to France and then anywhere (which could be France) and then France

  3. been to Germany, then anywhere (which could be Germany), repeated the booking to the same "anywhere", before then going to Germany

     

  • Click on the Add Aggregation button and then on the tab entitled Frequency(Bookings) that opens up

  • In the Type drop down select Pattern Match

  • Set the Grouping Table to People and Transactional Table to Bookings

  • Drag the Booking Date variable onto the Order records by drop-box and leave the From as Earliest to Latest

  • Drag the Destination selector variable onto the Pattern Match Variable drop box

  • Specify the Maximum days between transactions, if required; here enter 400 days to approximate to a person's annual holiday

The above settings allow us to search for patterns in people's holiday choices, ordered by their booking date.

Next:

  • Click Set pattern - a new window will open

Using the Show drop-down, choose if you would like to define patterns using the selector variable Codes or Descriptions

  • Select Descriptions

  • Enter Greece as Value 1 on the first row

It is possible to manually enter patterns in priority order using the Freeform Edit Mode or, alternatively switch to Dropdown Edit Mode to select the categories in the required order.

Right-clicking on a cell gives access to the menu options:

  • Right-click the 'Greece' Value 1 cell, select Copy Across and set to Copy this value across to 4 times

This results in the same outcome as manually entering Greece 3 times and allows us to identify people who have been to Greece on 3 consecutive occasions.

  • Name the pattern

Positional Wildcards

A number of wildcards are supported - * , ? , =, <> , < , > , <= , >=

Use of the * allows you to specify and find a desired sequence of transactions transactions – e.g. that a person should have booked a holiday to Australia first, followed by one to New Zealand; it does not matter how many other bookings fall in between, nor does it matter what the other destinations are – as long as there is a sequence of Australia followed by New Zealand.

We can use ? to represent a single value in that position and an = to match the previous value in the pattern; in a marketing context this might be used to examine sequences of the same result – for example, to find people who are repeatedly buying the same product.

Use 'not equal' <> when a value should be different to the value in the previous record - for example to find patterns of transactions where each product is different to the last.

Use the 'inequalities' to compare the ordinal value in this record to the ordinal value in the previous record. Note: These options are only supported for ordinal selectors and will not be offered in the drop-down menu for nominals.

 

Example 1

In the case of the above defined settings:

P1 identifies people who have been to Greece 4 times in succession

P2 identifies people who have been to France and then anywhere (which could be France) and then France

P3 identifies people who have been to Germany, then anywhere (which could be Germany), repeated the booking to the same "anywhere", before then going to Germany

 

To select people who meet the defined patterns:

  • Drag the Pattern Match Expression onto a new Selection window

  • Enter the Pattern Name into the freeform text window

    Note: In this example, to search for people meeting the criteria of all 3 patterns, select Ranges and Wildcards and ensure the Match case check-box is unticked.

  • Enter P* into the free-form text window

 

As an alternative approach:

  • Drag the Pattern Match Expression onto a Cube as a dimension and Build

  • Highlight and drag off the required cells to create the selection:

 

A Data Grid can be used to validate the results:

Example 2

Aim: To find people who have consecutive sequences of products where each transaction is considered to be an upgrade on the one before.

To demonstrate this in the context of Holidays, we have created a version of the Destination variable (Destination_Ascending) which is ordinal; the categories are ordered according to their distance from the UK, on the assumption that greater distance represents a higher value or upgraded type of transaction.

We are looking for sequences of holiday bookings where the destinations are getting steadily further away.

To start the analysis:

  • Open a new Expression window and click on the Add Aggregation icon

To access the ‘on the fly’ options:

  • Click onto the Frequency(Bookings) tab

  • Select Pattern Match from the Type drop-down menu options

  • Set the Grouping Table to People and the Transactional Table to Bookings

  • Order records by Booking Date

  • Add Destination_Ascending as the Pattern Match Variable

  • Click Set Pattern to open the Define pattern for Destination_Ascending window

  • Click on the drop-down menu in the Value 1 cell to access the available options:

  • Select >

  • Complete the settings as in the screenshot below and enter the Pattern Name as Destination_Ascending

    Right-click on the Value 1 cell to access the copy and fill options.

Using a Cube and dragging the Expression on as a dimension allows us to determine that the longest of such sequences on the Holidays system is 5 bookings, the behaviour demonstrated by 14 people:

  • Drag this cell from the Cube to create a selection and add a Data Grid

  • Add the Booking Date and Destination_Ascending variables to the display and build

  • Group by PURN and then sort into ascending order by Booking Date

The screenshot below demonstrates the booking sequences:

 

Click to return to Expressions: Aggregations on the Fly