How to derive the recently purchased city of each customer from daily customer transactions from various cities

Scenario: 

Shows daily customer transactions from various cities. The data also contains multiple purchase transactions of the same customer from different cities on the same day.

Requirement: 

Derive the recently purchased city of each customer.

DATASET AS PER THE SCENARIO

Steps:

1. To find out the recent city, the recent purchase has to be found out. The recent purchase can be derived from the date column by checking the latest date of purchase of each customer. So, the customer details have to be aggregated based on the ‘MAX’ date (ie; the latest date) in Smarten.

To achieve this,

• Take a copy of the current dataset first. (Assume the current dataset is “Dt_CustomerTransactions” and copied dataset name is “Dt_RecentDate”).
  
  Make the below changes in Dt_RecentDate.

• Keep only the customer column and purchase date in the dataset as it has to be aggregated based on the recent date.
• Aggregate the dataset by clicking on the Aggregate symbol.

• Select Max operation for the date column and click APPLY.

                                            MAX OPERATION IN AGGREGATION

The data will be aggregated and the customers and their recent purchase date will be displayed.

Example: (Highlighted are the recent purchase date of the customers xxx and yyy.)

So, the output will be,

Below given is the output in the aggregated dataset (Dt_RecentDate):

                                                            AGGREGATED DATASET OUTPUT

2. After deriving the most recent date, we need to find out the city from where the recent transaction has taken place on these dates. Since the city details are in the parent dataset (Dt_CustomerTransactions), both the datasets should be JOINed. Both the datasets can be joined based on Customer Name and Date.

To achieve this,

• Open the main dataset (Dt_CustomerTransactions)
• Click on the JOIN option and select the LEFT JOIN as shown in the below screen.
• Give the JOIN conditions as Customer (Name) = Customer(Name)& Date = Date and click APPLY.

                                                             JOIN CONDITION

For example, below is the main dataset.

Here is the aggregated dataset.

Below will be the output after the JOIN. Highlighted are the JOINed rows where the city and recent date matches together. Where ever the date values aren’t matching (ie; they are not recent dates), the output is shown as null.

RESULTANT DATA AFTER JOIN

3. Now, we have all the columns together. We need to create the ‘most recent city’ column where only the recent cities are displayed.  

To achieve this,

• Right click on any column and click on Add Column.
• Add the below expression (“ifCase(Name == Name_1, City, Null)”) in the expression window as shown below.

                                                MOST RECENT CITY EXPRESSION

The most recent city will be added as below.

DATASET AFTER ADDING THE MOST RECENT CITY

4. Now, we need the customer wise most recent purchases together on the top. So, we need to merge the customer name and their purchase date together in a new column and sort those in descending order.

To achieve this,

• Right click on any column.
• Go to Merge Column.
• Select the Customer (Name) and Date columns. Give ‘:’ as the separator and click OK.

                                                            MERGE COLUMN

Example: Using ‘:’ as a separator, sorted output (in descending order) will be displayed like the below table.

The merged output in dataset is as below.

MERGED COLUMN IN THE DATASET

5. As you see in the output there are Null values in the ‘Most recent City’ column. We need to fill that with the respective customer’s recent city value.

To achieve this,

• Right click on the MostRecentCity column and click on Fill -> As Previous Value option as shown in the below screenshot.

Example: As per the below table, XXX has recently purchased from City ‘AAA’ and other values were Null. After filling, the Null values will be replaced with ‘AAA’ for the customer ‘XXX’.

In the output as shown below, the customers and their recently purchased cities will be displayed.

CUSTOMERS WITH RECENTLY PURCHASED CITY

Note: In some cases, there can be multiple transactions from different cities / stores on the same day. In such cases, it will be better to have the Purchase date column with the purchase timestamp. Else, all the cities for the same date will be displayed for each customer.

Note: This article is based on Smarten Version 5.x. This may or may not be relevant to the Smarten version you may be using.