how to find Last 5 redirects

[viralparekh]

How to get Last 5 redirects ?

If there are 5 or more redirects by the same customer, the revenue is distributed with 1/5 of the revenue to the 5 latest redirects. If there are fewer than 5 redirects, the revenue is distributed evenly on the existing redirects.

[orders.csv] contains one row for each order. The fields are
[order_id]: Unique identifier for each order. Unique in this table.
[customer_id]: Unique identifier for each customer. The “customer” concept is cookie based and the cookie is reset when a customer makes a purchase. Thus, no customer has made multiple orders, and therefore also this id is unique in this table.
[created] The date and time the order was placed. Example: ‘2022-07-01 00:22:35.429147’
[revenue]: Total revenue of the order
[redirects.csv] contains one row for each redirect.
[customer_id]: Unique identifier for each customer. Customers typically enter through several redirects, so there are often many rows corresponding to the same customer.
[created]: The date and time when the redirect was made.
[channel]: The channel through which the redirect came

[F_B]

Hi @viralparekh ,

let's see if the following works for you.

First, load your data:

Orders:
LOAD
[order_id],
[customer_id],
[created] as OrderCreated,
[revenue]
FROM [orders.csv]

Redirects:
LOAD
[customer_id],
[created] as RedirectCreated,
[channel]
FROM [redirects.csv]

Next, you'll need to rank the redirects:

SortedRedirects:
LOAD
[customer_id],
[RedirectCreated],
[channel],
AutoNumber([RedirectCreated], [customer_id]) as RedirectRank
Resident Redirects
Order by [customer_id], [RedirectCreated] DESC;

Now, filter only the last 5 redirects per customer:

Last5Redirects:
LOAD
[customer_id],
[RedirectCreated],
[channel],
RedirectRank
Resident SortedRedirects
Where RedirectRank <= 5;

Join the orders with these last 5 redirects and calculate how the revenue should be distributed:

JoinedData:
Inner Join (Orders)
LOAD
[customer_id],
[order_id],
[OrderCreated],
[revenue]
Resident Orders;

Left Join (JoinedData)
LOAD
[customer_id],
[RedirectCreated],
[channel],
RedirectRank
Resident Last5Redirects;

Calculate the revenue distribution:

RevenueDistribution:
LOAD
[order_id],
[customer_id],
[RedirectCreated],
[channel],
[revenue] / Min(5, Count(DISTINCT RedirectRank)) as DistributedRevenue
Resident JoinedData
Group by [order_id], [customer_id], [RedirectCreated], [channel];

[KateWinset1]

To distribute the revenue based on the last 5 redirects, you can process the data by first grouping the redirects.csv file by customer_id and sorting the redirects by the created timestamp. For each order in orders.csv, identify the associated customer_id and retrieve the last 5 redirects for that customer. If there are fewer than 5 redirects, distribute the revenue evenly across all of them. If there are 5 or more redirects, allocate 1/5 of the revenue to each of the last 5 redirects. Here's a step-by-step outline:

Load the data from orders.csv and redirects.csv.
Group the redirects by customer_id, sort by created in descending order.
For each order, find the corresponding customer_id in the redirects data.
Select the last 5 redirects for that customer_id. If fewer than 5 redirects exist, take all available ones.
Distribute revenue: if 5 or more redirects are found, allocate 1/5 of the revenue to each. If fewer, distribute evenly among the available redirects.