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
Hi
You need to first identify all the customers that have at least one “New” order and then to sum all their orders. Here’s the metric:
SELECT SUM(Amount) WHERE (SELECT COUNT(Order Id) BY Customer Id WHERE Customer Type = New) > 0
Please note “New” has to be selected from the Attribute Values, so it will show up as orange like here:
