Find aborted customers based on several conditions

[Temminck]

Hi there,

I need to detect customers having issues to register for a product.

To find the customers having issues I thought of adding a calculated column in my product table. However maybe a measure would be better? I don’t know… please help

Here an example product table with 6 customers:

Order Id	Order Status	ContactId	CatalogType	CatalogName	ProductLabel	OrderDate	Calculated column: Needs help?
1	Paid	65445	Membership	Membership	Membership 2018-2019	01/01/2018	no
2	Paid	65445	Membership	Membership	Membership 2020-2020	01/01/2020	no
3	Aborted	65445	Membership	Membership	Membership 2021-2022	06/08/2020	no
4	Order	65445	Membership	Membership	Membership 2021-2021	07/08/2020	no
5	Order	65445	Membership	Membership	Membership 2021-2021	07/08/2020	no
6	Paid	45894	Membership	Membership	Membership 2018-2019	01/01/2018	no
7	Paid	45894	Membership	Membership	Membership 2020-2020	01/01/2020	no
8	Aborted	45894	Membership	Membership	Membership 2021-2021	07/24/2020	yes
9	Aborted	45894	Membership	Membership	Membership 2021-2021	07/25/2020	yes
10	Aborted	45894	Membership	Membership	Membership 2021-2021	07/28/2020	yes
11	Paid	45689	Events	Parties	Party45	08/09/2020	no
12	Aborted	89564	Events	Parties	Party45	10/12/2020	yes
13	Aborted	77895	Exam	Exam 2020	Milan 3/10/2021	01/10/2021	yes
14	Aborted	77895	Exam	Exam 2020	Paris 4/11/2021	01/10/2021	yes
15	Aborted	66658	Exam	Exam 2021	Avignon 3/10/2021	01/10/2021	No
16	Aborted	66658	Exam	Exam 2021	Dubai 4/11/2021	01/10/2021	No
17	Paid	66658	Exam	Exam 2021	Berlin 5/01/2021	01/10/2021	no

If the customer failed one or more times to purchase a product we need to intervene (= OrderStatus aborted).   

There are 4 scenarios when customers purchase a product:

1. The customer failed one or more times (aborted)
2. The customer paid successfully after the first time (paid)
3. The customer failed one or more times and then managed to pay successfully (aborted, paid)
4. The customer failed one or more times and has one or several open orders (aborted, order)

Additionally, when customers fail to purchase the product the first time and try again it regularly happens that they chose a different productlabel but in the same catalogtype. This only happens for the catalogtypes Membership and Exam.

In summary, we need to find customers where :

EVENT: customer has one or more times status aborted for the catalogtype ‘Event’ and the same ‘ProductLabel’ 

EXAM: customer has one or more times status aborted for the catalogtype 'Exam‘ and same 'CatalogName’

MEMBERSHIP: customer has one or more times status aborted for the for the catalogtype ‘Membership‘ and ‘ProductLabel’ where StartYear <= SelectedYear &&  EndDate >= SelectedYear

Someone knows how to create such calculated column or measure?

Please let me know if it’s not clear enough.