I have a dataset containing sale orders, which includes:
- Order Date - Date the order was placed
- Customer ID - Unique ID for each customer
- Order ID - Unique ID for each order
- Customer Type - ‘New’ if it’s the first order placed by the customer, ‘Repeat’ for all subsequent orders
- £ Amount - Total order amount in GBP
We would like to introduce a Customer Segment, which is dependent on the reporting period (week/month/year) and is defined as:
- ‘New’ if the customer placed their first order within the reporting period (regardless of whether they also placed repeat orders within the reporting period)
- ‘Existing’ if the customer placed their first order before the reporting period
I want to create a metric ‘£ New Customer Sales Monthly’ which is the SUM of the sale order amounts (£ Amount) for New customers each month.
Note - if the customer places their first order in a given month and places repeat orders within that same month, then all of those sale order amounts should be included in the ‘£ New Customer Sales Monthly’ metric, not just the first order they placed.
|Order Date||Customer ID||Order ID||Customer Type||£ Amount|
‘£ New Customer Sales Monthly’ would be £300.
I have attempted to create this metric but I can’t make it work.
Any help would be appreciated!
Best answer by Petr OlmerView original