Count purchases with previous demos

[fluxfrog]

Hi!

I have an app that monitor software sales. Customers can buy "full" products or get a "demo". I would like to visualise how useful the demos really are by comparing the number of purchases that were preceded by a demo to the direct purchases.

My attached table shows some example data, and also the numbers I would like to get out of this. Can you help me to figure out the best way of achieving this?

Thank you very much!

[asinha1991 · Accepted solution]

you can do it in pivot chart, under columns add 

create a dimension

if( wildmatch(aggr(concat(Type,',',Date),CustomerID,ProductID),'Demo*Full'),'fullafterdemo',

if( wildmatch(aggr(concat(Type,',',Date),CustomerID,ProductID),'Full'),'Fullonly',

if( wildmatch(aggr(concat(Type,',',Date),CustomerID,ProductID),'Demo'),'demoonly')))

in expression(values) create count( CustomerID)

[fluxfrog]

Wow, what a dirty trick (in a good way). But, it does require that the data is sorted according to date, right? This doesn't explicitly compare the dates but rather look for concatenated strings with "Demo" followed by "Full" if I understand the code correctly.

[asinha1991]

in concat you can add an ordering field, I did add date

concatenation will done in order of date field

[fluxfrog]

Clever! I didn't know that. And yes, it works! But, unfortunately, it is a bit too slow with millions of lines in that table. However, I think I could get what I want in the load script using GROUP BY and the same strategy, to create some sort of helper table with this information pre-calculated. I'll give it a try!

Thank you very much!

[fluxfrog]

For the sake of completeness. Using GROUP BY and concat , and then wildmatch to create a new table worked fine. Adds a few seconds during load, and is then instant in the app more or less. Great!