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
your metric would perhaps look something like this:
SELECT <SUM Metric> BY <Attribute> WHERE <Date Dimension> = THIS
SELECT £ Amount BY Customer ID WHERE Month/Year (Order Date) = THIS
By adding the "THIS" macro, it references the current value for the specified attribute of the Date dimension.
Or if you would like to lock the level of aggregation to a specified attribute, as in your case this may be Month/Year - Your metric might perhaps look like this:
SELECT (SELECT <SUM Metric> BY <Attribute>) BY <Date Dimension>
SELECT (SELECT £ Amount BY Customer ID) BY Month/Year (Order Date)
In its basic implementation, the BY clause stops breaking down report values by anything smaller than the referenced attribute level. If the above isn't quite what you're looking for, please provide more details on your specific use-case and we'll be happy to assist further.
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:
@Michael Ullock and @Petr Olmer
Thank you for your responses!