Need more, need help now? we offer pay-as-you-go support, no contract, tenth of an hour billing, expert help, fast service, no call queue
We can offer lower cost Sage50cloud subscriptions for customer using pay-as-you-go support or who are self-supporting.
Need integration, your shipping system, your website, your invoicing system, your crm.
Need a bespoke solution, Mini Manufacturing, Spreadsheet order import, Back to back orders.
Want web hosting, email, your own domain name, a shop, we do it all!

Customer Retention Report

Report design issues and solutions.
Post Reply
mwerner
User
User
Posts: 2
Joined: 29 Jan 2018, 10:51
Sage Version: v2016 22 UK/Europe/Africa

Customer Retention Report

Post by mwerner » 29 Jan 2018, 10:59

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

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4256
Joined: 28 Feb 2006, 09:56
Sage Version: v2017 23 UK/Europe/Africa

Re: Customer Retention Report

Post by brucedenney » 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.
For just about anything Sage :- switch to subscription, pay-as-you-go support, sagecover, upgrades, application integration, reports, layouts, analysis or any other help making life with sage easier/less time consuming Contact me. Image

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4256
Joined: 28 Feb 2006, 09:56
Sage Version: v2017 23 UK/Europe/Africa

Re: Customer Retention Report

Post by brucedenney » 29 Jan 2018, 11:24

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.
For just about anything Sage :- switch to subscription, pay-as-you-go support, sagecover, upgrades, application integration, reports, layouts, analysis or any other help making life with sage easier/less time consuming Contact me. Image

mwerner
User
User
Posts: 2
Joined: 29 Jan 2018, 10:51
Sage Version: v2016 22 UK/Europe/Africa

Re: Customer Retention Report

Post by mwerner » 29 Jan 2018, 11:47

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

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4256
Joined: 28 Feb 2006, 09:56
Sage Version: v2017 23 UK/Europe/Africa

Re: Customer Retention Report

Post by brucedenney » 29 Jan 2018, 16:25

Yes, they add a default set of tables to reports, I wonder if you used something form the items table?
For just about anything Sage :- switch to subscription, pay-as-you-go support, sagecover, upgrades, application integration, reports, layouts, analysis or any other help making life with sage easier/less time consuming Contact me. Image

Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest