Hi,
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.
Example
Order Date | Customer ID | Order ID | Customer Type | £ Amount |
---|---|---|---|---|
2021-01-01 | customer1 | order1 | New | 100 |
2021-01-14 | customer2 | order2 | New | 50 |
2021-01-19 | customer1 | order3 | Repeat | 150 |
‘£ 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!
Thanks
Best answer by Petr Olmer
View original