topic Re: Count purchases with previous demos in App Development

Count purchases with previous demos

fluxfrog — Mon, 07 Oct 2019 21:24:30 GMT

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!

Re: Count purchases with previous demos

asinha1991 — Mon, 07 Oct 2019 21:36:01 GMT

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)

Re: Count purchases with previous demos

fluxfrog — Mon, 07 Oct 2019 22:17:26 GMT

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.

Re: Count purchases with previous demos

asinha1991 — Mon, 07 Oct 2019 22:31:15 GMT

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

concatenation will done in order of date field

Re: Count purchases with previous demos

fluxfrog — Tue, 08 Oct 2019 08:12:05 GMT

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!

Re: Count purchases with previous demos

fluxfrog — Thu, 10 Oct 2019 06:39:37 GMT

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!