Sorting customers to find purchasing trends

[Nicolai]

I have a list of purchases by customers that includes customer name, product purchased and date of purchase.

Is there a way to get a list of everyone who bought item A and then item B, in that order. (ie: Not people who purchased B and then later purchased A)

Here’s a sample sheet I’ve been trying with:

Customer Path Test Sales Data

The purpose of this workflow is to determine how many people who “purchase” product #10 (which is the free sample) end up becoming paying customers and purchase product #20.

From the sample data I would like to get freebie@gmail.com but not payee@gmail.com Even though they both ‘purchased’ both products, only freebie@gmail.com got the free sample first.

If I could also find out how long in between free and paid that would be the cherry on top!

This is something I have been trying to do for years in many CRM and ecommerce platforms I have used but it seems incredibly difficult if not impossible to do in most.

It would be very valuable to business owners who want to know which products lead customers to make bigger purchases later.

Any hints, suggestions or ideas about which filters or transforms I should focus on or even a general strategy would be great!
Thanks

[Admin]

Yes. It is do-able. It just needs a few steps.

1. Use Extract to convert the datetime into a date.
2. Use 2 Filters to find those who purchased product 10 and those who purchased product 20.
3. Rename Cols the date cols for convenience.
4. Join the 2 datasets using the emails as keys.
5. Compare cols to show where product 10 was purchased before product 20.
6. Filter to find keep only instances when product 10 was purchased before product 20.

See this .transform file:
https://www.easydatatransform.com/forum/nicolai.transform.zip

Maybe someone can come up with a solution that requires less steps?

[DanFeliciano]

It can also be done using the Unique Transform.

[Nicolai]

Thank you both. These are fantastic and the possibilities all these EDT techniques open up are incredible.

[DanFeliciano]

I think you’re on to something interesting; purchasing patterns and decisions.

[DanFeliciano]

If I knew how to upload the transform, I would.

[Admin]

Unfortunately the forum software only allows upload of images, not other files. Perhaps you could upload it to DropBox, Google Docs or similar and then link to it?

[Admin]

@DanFeliciano I have tweaked the forum setting to (hopefully) allow you to upload a .transform file in a post, in the same way as you can upload an image.

test.transform (4.6 KB)

[DanFeliciano]

EDT fans, this is the transform I created using @Nicolai data and requirements. I used the Unique Transform to come up with essentially the same result as @Admin 's.

I added the calculation between dates. I think @Admin is trying to teach me to code. This is my Pièce de résistance.

Purchase order sequence V2.transform (5.2 KB)

[Admin]

Using Javascript to calculate the difference between 2 dates is covered here:

How do I? > Find the difference between dates/datetimes