Page 1 of 1

Customer Retention Report

Posted: 29 Jan 2018, 10:59
by mwerner
Hi
I'm trying to build a simple report that will show the number of invoices created per customer to give us some idea of our customer retention. I am using the countunique(INVOICE.INVOICE_NUMBER) expression but it seems to count the number of invoice items not the number of invoices.
For example
If I have a customer that we have had 2 interactions with (ie 2 invoices) and on the first invoice there were 2 products sold and on the 2nd occasion only 1 product sold. currently the report i'm trying using the above expression is returning a count of 3 (for the 3 items sold), I want it to only return 2 for the 2 invoices.

What am i doing wrong and how can I fix this. Apologies if this is a daft question but I'm a beginner on Sage reporting but do have some understanding of tables and building expressions etc from using MSAccess years ago.

Many thanks
M

Re: Customer Retention Report

Posted: 29 Jan 2018, 11:12
by brucedenney
The issue is that your report also refers to invoice items, as such the data set you are getting returned form all the joins includes a record for each item as well as each invoice.

If you go into joins and remove the invoice_items table then your report will work.

Re: Customer Retention Report

Posted: 29 Jan 2018, 11:24
by brucedenney
Your report has a more issues, first it does not tell you about customer that did not purchase at all, the second is that it is a snap shot, it doesn't really tell you about the trend how things are changing.

I would look to do an excel spreadsheet, using a pivot table to analyse the data, you could have customers down the left, and time across the top, the main data area being the count of the number of invoices. This way you can spot the trends in buying patterns, give them a ring gee them up.

You can go much further with this, rework it to give you the products / product groups, then you can see if you have customers who are buying across all products, or just buying some, you can see if sales of products are growing, shrinking. You can look in terms of values, counts, or qty's. Some customer might not know about some of the other things you sell, or perhaps they have an alternative supplier who gives them a better deal on those products. If you see they are not buying a line you would expect, then you can ring them up and ask them about the line where they currently source it and explore if there is a n opportunity for you there.

Excel is really flexible for this sort of thing. You can hook it up to your live data and review it every week/month/quarter, see who/what has changed, keep customers on board, open up new sales avenues, discontinue products as they loose favour rather than when they are dead and you are left with a ton of stock. The uses are almost endless.

Re: Customer Retention Report

Posted: 29 Jan 2018, 11:47
by mwerner
brucedenney wrote: 29 Jan 2018, 11:12 The issue is that your report also refers to invoice items, as such the data set you are getting returned form all the joins includes a record for each item as well as each invoice.

If you go into joins and remove the invoice_items table then your report will work.
Hi
Thanks for this.
I assume that removing this table from the joins will only affect this report and nothing else?
I'm not sure why there are any other tables there when I have only asked for data from the INVOICE table, please can you advise?

Many thanks

Re: Customer Retention Report

Posted: 29 Jan 2018, 16:25
by brucedenney
Yes, they add a default set of tables to reports, I wonder if you used something form the items table?