I have a set of transactions each with a customer ID, including transactions where a user purchased a type of membership, while other transactions are other types of purchases not related to membership. For each transaction, I want to be able to identify what type of member made this transaction by using the customer ID, where the identification is based on the most recent membership purchase prior or equal to this transaction date.
An example that comes to mind is with Costco --
Every item (SKU) a member purchases is assigned to that member's ID. I want to be able to assign that item to that member's membership status based on the last time he paid for membership and what that membership type was (gold vs executive; new vs. renew). I've attached a dummy data set.
I'm scratching my head on how to get this started. I'd presume I will need some sort of calculated field but am stuck.
Thanks in advance!
|New Membership - Gold||50||1||11111||1/1/2017|
|New Membership - Gold||50||1||22222||2/1/2017|
|New Membership - Gold||50||1||33333||3/1/2017|
|New Membership - Gold||50||1||44444||7/1/2017|
|New Membership - Executive||75||1||55555||5/1/2017|
|New Membership - Executive||75||1||66666||6/1/2017|
|Renewed Membership - Gold||100||1||77777||1/1/2018|
|Renewed Membership - Gold||100||1||11111||1/1/2018|
|Renewed Membership - Gold||100||1||88888||1/31/2018|
|Renewed Membership - Executive||150||1||22222||2/1/2018|
|Renewed Membership - Executive||150||1||99999||3/1/2018|
Dummy tableau data.xlsx 10.3 KB