Retention report

[Dudik]

Hi, i would like to build a retention chart / table in qlik sense:

month of user registration

count people registered 

count people who bought our product 

count people who bought our product next month (from those who registered in previous)

count people who bought our product in 2 months after registration

and so on...

I have a table with user registration date and linked invoices table

[JustinDallas]

This isn't a question, this is a job...

[Dudik]

Ok, than here's my count function (which is not working correctly):

COUNT(
{$<
[Tarif] -= {'card-bind'}
,[Order status] = {'success'}
,[Order created at.autoCalendar.Date] = {">$(=Date(Min([First payment date])))"}
>} DISTINCT [users.user_id]
)

(I'm trying to count users who made payments [not 'card-bind'] after first payment)

And here's something i would like to achieve (count of payed users in next month)

COUNT(
{$<
[Tarif] -= {'card-bind'}
,[Order status] = {'success'}
,[Order created at.autoCalendar.Date] = {">=$(=MonthStart(AddMonths(Date(Min([First payment date]])), 1)))"}
,[Order created at.autoCalendar.Date] = {"<=$(=MonthEnd(AddMonths(Date(Min([First payment date])), 1)))"}
>} DISTINCT [users.user_id]
)

[micheledenardi]

Can you share some example data?

[Dudik]

Well, my data columns in cyrillic, so i guess i'll have to provide text example:

table user:

[user_id],
[email],
[first_payment_date];

table order:

[order_id],
[user_id],

[created_at],
[order_status],
[order_price_code],
[order_rank];

user example data:

1;mail@me.com;2021-01-01

2;mail2@me.com;2021-02-01

3;mail3@me.com;2021-03-01

order example data:

1;1;2021-01-01;pending;tarif1;null

2;1;2021-01-02;success;card-bind;null

3;1;2021-01-05;success;tarif1;1

4;2;2021-01-06;success;card-bind;null

5;2;2021-01-10;success;tarif1;1

6;1;2021-02-05;success;tarif1;2

7;2;2021-02-10;success;tarif1;2

in postgres my query for 2nd column (payment next month) would be:

SELECT COUNT(*) FROM (
SELECT DISTINCT views.order.user_id
FROM views.order
LEFT JOIN views.user ON views.user.user_id = views.order.user_id
WHERE order_status='success'
AND order_captured_at >= '2021-07-01'
AND order_captured_at < '2021-08-01'
AND order_price_code != 'card-bind'
AND views.user.first_payment_date >= '2021-06-01'
AND views.user.first_payment_date < '2021-07-01'
) a;