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

Modified on Thu, 14 Mar at 12:17 PM

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.)


Customer

Date

XXX

01-Feb-2020

XXX

22-Mar 2020

YYY

15-Feb-2020

YYY

12-Mar-2020

 

So, the output will be,

Customer

Date

XXX

22-Mar 2020

YYY

12-Mar-2020

 

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.


Customer

City

Sales

Date

XXX

AAA

100

22-Mar 2020

XXX

BBB

150

01-Mar-2020

YYY

CCC

200

12-Mar-2020

YYY

DDD

50

05-Mar-2020

 

Here is the aggregated dataset.


Customer

Date

XXX

22-Mar 2020

YYY

12-Mar-2020

 

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.


Customer

City

Sales

Date

Customer_1

Date_1

XXX

AAA

100

22-Mar 2020

XXX

22-Mar-2020

XXX

BBB

150

01-Mar-2020

Null

Null

YYY

CCC

200

12-Mar-2020

YYY

12-Mar-2020

YYY

DDD

50

05-Mar-2020

Null

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.

 

Customer – Date

Customer

Date

Most Recent City

Xxx:2020-03-22

Xxx

22-Mar-2020

AAA

Xxx:2020-02-01

Xxx

01-Feb-2020

Null

Yyy:2020-03-12

Yyy

12-Mar-2020

CCC

Yyy:2020-02-15

Yyy

15-Feb-2020

Null

 

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’.


Customer

City

Sales

Date

Customer_1

Most Recent Date

Most Recent City

XXX

AAA

100

22-Mar 2020

XXX

22-Mar-2020

AAA

XXX

BBB

150

01-Mar-2020

Null

Null

AAA

YYY

CCC

200

12-Mar-2020

YYY

12-Mar-2020

CCC

YYY

DDD

50

05-Mar-2020

Null

Null

CCC

 

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.

 

recent city aggregation max

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article