Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
This isn't a question, this is a job...
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]
)
Can you share some example data?
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;