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:
- The customer failed one or more times (aborted)
- The customer paid successfully after the first time (paid)
- The customer failed one or more times and then managed to pay successfully (aborted, paid)
- 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.
