To GoodData homepage
Solved

Difficulty creating new MAQL metric based on Customer Segment

  • 10 March 2021
  • 3 replies
  • 49 views
A

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

icon

Best answer by Petr Olmer 10 March 2021, 18:04

Hi @Ashleigh,

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:

 

View original

3 replies

Michael Ullock
Rank

Hi Ashleigh, 

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. 

M. Ullock
Petr Olmer
Rank
Userlevel 2

Hi @Ashleigh,

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:

 

A

Hi @Michael Ullock and @Petr Olmer 

Thank you for your responses!

@Petr Olmer your metric allows me to be more flexible with the reporting period. I’ve tried it out and it’s giving me the correct values so I’m going with that approach.

Thanks :slight_smile:

 

Reply


Join us on