Calculate customer return rate

[pedrohenriqueperna]

Hi!

I'm trying to calculate the customers return rate by period, but I'm not sure what would be the best approach.

Ideally, I wish to display in a line chart the % variance by month/year. I could do it combining Count and Aggr and aim for customers that the registry_date is lower than the purchase_date, then divide the total count by the total sales count. The problem is that I need to consider different time intervals (ex. <= 30 days, > 30 days and <= 45 days, > 45 days, etc.)

First of all I'm trying to understand if it'd be better to create a dedicated field in the Loader or if I should use chart expressions...

I actually built this in Excel using macros. First I added the sales records then I looped each existing customer_id  through the sales records and added every purchase date for each customer_id in ascending order (side by side). Then I looped through every date (from right to left) subtracting the previous date (which gave me the days difference between the two dates). For each subtraction I added +1 count to a Interval Table for the corresponding interval found and month. Finally I had a final table with counting for each interval which I used to built the line chart.

Maybe I should do the same in Qlik? Or it'd be better to use expressions and/or set analysis?

Any help is appreciated!!

Here's a screenshot of the Excel solution and tables:

[sales date by customer]

[counting table by interval and month]

[final result]

PS: Each line in the chart represents a different interval

[pedrohenriqueperna]

Anyone? 😕

I'm thinking maybe I should add a column to the sales fact table containing the days difference for each purchase. Maybe I could do that using a For loop using NoOfRows(SalesFact) and Previous(sale_date) to check for the previous date and find out the difference. I tried to follow this path, but I'm not sure how to order the table to be able to get the correct values, considering that I would have to order the table by customer_id, sale_id and sale_date...

I'm supposing that if I have this additional column I could link the table to a bridge/intervalmatch table holding the different days interval by lowerbound and upperbound.

Any help is welcome... If anyone could shed a light on this I'd be very grateful!