Use case for a Jewellery agent’s order count calculation where orders and customers can be divided amongst multiple transactions for the same order

Scenario: 

This use case requires calculation of number of orders divided amongst multiple agents. For example, think of a sales industry where there are multiple agents involved in any sales. If there are multiple agents involved for a single sale, order and order amount will be shared amongst agents based on logic where agents get share based on the time period of their engagement with the customer. 

Assumptions:

In a practical scenario, the data will look vast but, in this scenario, we will focus on columns needed to achieve the solution. Data is divided into two following tables:
 

1. Leads Table: This table represents all the leads generated during online interaction with a potential customer. Whenever there is a lead generated, there is an agent assigned to that lead. Below is the structure of the data.
   
    

1. Orders Table: Orders Table will have all the data for the sales which are done organization wide. This data will also have sales which are not a part of the Leads Data. Below is the structure of the data:

Following are the assumptions related to this use case:

1. One customer can have multiple number of leads.
2. Similar to leads, one customer can have multiple orders.
3. Mapping of leads data will be done on the basis of customer mobile and customer email. Priority will be given to mobile. If a record is matched on mobile, email will be ignored.
4. It is assumed that the customer can use a different mobile number and email address in both the leads table and the orders table.
5. If an order is placed within 30 days from the lead date that lead will be considered as converted otherwise it will be considered as not converted.

Solution:

The steps below show how the outcome is achieved logically

Step1: Create datasets for leads table and orders table.

Step 2: Make two copies of orders table- one for Mobile and one for Email. In the first copy remove Order_Email and keep ‘Order Timestamp’, ‘Customer Email’, ‘Invoice No’ and ‘Amount’.

In second copy remove Order_Mobile instead of Email. You will be left with ‘Order Timestamp’, ‘Customer Email’, ‘Invoice No’ and ‘Amount’

Step 3: Left Join leads table and orders table on customer mobile. This action will join all the records if a customer has used same mobile number in leads table and orders table. 

Step 4: Left Join leads table and orders table on customer email similar to step 3

Step 5: After joining the leads table twice, there will be duplicate columns for Order Date, and Order Amount. Make a new column for the both the columns where priority is given to Mobile using below logic.
 if Orders CustomerMobile != null, MobileAmount, EmailAmount. Using this, if a particular row has been joined on the basis of mobile number, we take that value, otherwise we take the value coming from Orders data joined on email.

Step 6: Make two copies of Orders table- one for Mobile and one for Email.

Step 7: In order for us to calculate the proportion count for each agent for one given order, we will make two copies of leads data and left join with Orders data created in step 6:

(i): Left join leads table with orders table on customer mobile. This will be copy to record proportion count of agents joined on mobile number

(ii): Left join leads table with orders table on customer Email. This will be another copy to record proportion count of agents joined on customer email.

Step 8: For dataset which was joined on mobile number in step 7, discard all the rows which are not joined on mobile number. This will make sure all the irrelevant rows are discarded.

Step 9: In this step, we make a new column- ‘Proportion Count’ for all the orders which lie in a 30-day period from the lead date. This column will have a static value of 1 for all the orders within a 30-day period otherwise it will be null value.

Step 10. Discard all the rows with null value in column- ‘Proportion Count’. This way you will only be left with leads which have been converted.

Step 11: Discard all the columns except ‘Invoice No’ and ‘Proportion Count’ and do an aggregation on Invoice No as dimension and sum of Proportion Count. This will derive how many agents should a particular order be divided into.

Step 12: Use the dataset from step 5 and left join it with the dataset in step11 on Invoice No.

Please note: This time Invoice No. from the former dataset will be used which was acquired after joining the Orders Mobile data in step 3.

Step 13: Repeat step 9 to step 13 for Orders Email dataset.

Step 14: Now when you have a final dataset ready, it will two have columns for proportion count and Invoice No. similar to step 6. In this step also, we will give priority to the Mobile No. Along with that, we will also consider the 30-day factor while deriving the final column because while doing a join in step 12 on Invoice No, there was a many to many join and we are only interested in leads which were converted in a 30-day period from the lead date.

Step 15: In this step we will create a Unique ID using multiple columns to identify unique leads which were converted into orders. We are doing this because while doing a many to many join in step 3, 4 & 12, there are records which have been duplicated. Unique ID in this use case, will be created by concatenating ‘Lead Email’, ‘Lead Mobile’, ‘Lead Date’ and ‘Invoice No.’

Step 16: Final step is to create a front-end report where dimension will be Agent Name, and the measure will be Order Count. We will do a distinct sum of Order Count on Unique ID created in previous step to achieve the desired result.

Steps in Smarten:
Step 1: Create Leads dataset DT_Leads and two copies of Orders Dataset in Smarten. Name the first copy as DT_MobileOrders and discard Order_Email column from this dataset. Name the other copy as DT_EmailOrders and discard Order_Mobile column from this dataset.

Step 2: Left join DT_Leads dataset and DT_Mobile Orders on Mobile

Please note: Since this is many to many join, and hence will result in a cross join.

Step 3: Left join Leads dataset and DT_EmailOrders on Email.

Please note: Since this is many to many join, records will multiply in the main dataset.

Step 4: After joining DT_MobileOrders and DT_EmailOrders with DT_Leads, there will be two columns for Order Date- Order_Date and Order_Date_1. In this case, priority is given to mobile orders and hence we create a new column – FinalOrderDate using below expression.

Please note: Order_Date refers to date from DT_MobileOrders and Order_Date_1 refers to date from DT_EmailOrders

ifCase(Order_Date != null, Order_Date, Order_Date_1)

Step 5: Using the same logic in step 4, make a new column for Invoice number

Please note: DocNo refers to Invoice No. from DT_MobileOrders and DocNo_1 refers to InvoiceNo. from DT_EmailOrders

ifCase(DocNo != null, DocNo, DocNo_1)

Step 6: Create two copies of Leads Dataset like in step 1 and name them as DT_Mobile_PorportionCount and DT_Email_ProportionCount

Step 7: In DT_Mobile_ProportionCount, discard column- ‘Lead Email’
Step 8: Left join DT_Mobile_PropotionCount with DT_MobileOrders used in step2 on Mobile

Step 9: Discard all the rows with null values in Order_Mobile which is acquired after joining in step 8. This will make sure, all the leads which are not converted are discarded.

Step 10: Add a new custom column and name it ProportionCount where all the records have value ‘1’ for orders placed within 30 days using below logic:
 ifCase(dateDiff("d", Order_Date, Lead_Date) >= 0 && dateDiff("d", Order_Date, Lead_Date) <= 30, 1, 0)

Step 11: Now, discard all the columns except DocNo and ProportionCount
Step 12: Finally, aggregate the data where dimension is DocNo and taking sum of ProportionCount. This step will give you all the invoice numbers for which, proportion count tells you how many times a particular order has to be divided amongst agents.

Step 13: Repeat steps 7- 13 for DT_Email_ProportionCount to get the same results for Email dataset as well.

Step 14: Left join DT_Leads with DT_MobileProportionCount on DocNo
Please note: There are two DocNo columns aquired in DT_Leads after joining in step 2 and step3. Use the DocNo acquired from DT_MobileOrders
Step 15: Left join DT_Leads with DT_EmailProportionCount on DocNo
Please note: There are two DocNo columns aquired in DT_Leads after joining in step 2 and step3. Use the DocNo acquired from DT_EmailOrders this time.

Step 16: After doing joins in step 14 and step 15, there will be two columns for ProportionCount. In this case also, give priority to column acquired from DT_MobileOrders and create a new column- ‘FinalProportionCount’ using below logic:

ifCase(ProportionCount != null, ProportionCount, ProportionCount_1)

Please note: ProprotionCount refers to column acquired from DT_MobileOrders and ProportionCount_1 refers to column acquired from DT_EmailOrders

Step 17: In this step, we will divide the final proportion count acuqired with 1 to get the proportion assigned to each agent for that particular invoice no. We will also take into account the 30-day period assumption for order conversion.
 Create a new column- ‘OrderCount’ using below logic:

ifCase(dateDiff("d", FinalOrderDate, Lead_Date) >= 0 && dateDiff("d", FinalOrderDate, Lead_Date) <= 30, 1/FinalProportionCount, null)

Step 18: Finally, once we have an order count for every lead that is converted to an order, we will create a UniqueID to determine each unique lead that is converted. To create a Unique ID, we will concatenate – ‘LeadMobile’, ‘LeadEmail’, ‘LeadDate’ and ‘DocNo’. Create a new column using below logic:

ifCase(Lead_Email != Null, Lead_Email, "") + " " + ifCase(Lead_Mobile != Null, Lead_Mobile, "") + " " + ifCase(FinalDocNo != Null, FinalDocNo, "") + " " + ifCase(Lead_Date != Null, Lead_Date, "")
 We create an UniqueID because there was a many to many join while joining leads dataset and orders dataset on mobile number and email and thus, there was a cross join which will manipulate results of Order Count. This step will help us overcome this problem by doing a sum of OrderCount on distinct UniqueID.

Summary:

So using the above mentioned steps, Smarten solved the use case with minimum data redundancy and only three datasets in place hence not needing any additional storage space.

The use case solved the following:

• A scenario where an existing customer can either order using a mobile number or an email, but is supposed to be considered as same customer while billing.
• A scenario wherein a customer places an order after getting in touch with more than one agents
• A scenario where customer with multiple agents involved in the dealing, all the agents will have incentives divided based on the involvement duration.