Old Customers who're not ordering?

V5 Question
1

Hi! I’m operating a small delivery joint.
Over the past few years we’ve developed a relatively big database of customers.

I want to know if there’s way to know which customers haven’t ordered from our restaurant within the last 6 months?

2

Hey @paxi I like where you are heading with this too. You probably may need to drop an image of how you have mapped out your Customer Entity (custom fields) here. Then the SQL guru’s maybe be able to script up a binding of last ticket sale date report thingy.

Lots of further applications like promotional emails, SMS’s and even when they come back in a “Welcome Back” voucher. All about the Customer experience right… :smile: I can’t help you yet but only if to give my support to the idea.

3

It might not help you now but entity state logging is available and since entity states are used in tickets you could easily see when last state change was for entities and see activity level in a report sorted by date. Basically you could use a report to see activity last reported and filter dates etc.

I could demonstrate a basic system if that interests you.

4

I can’t speak for paxi as that is where the request came from but I would certainly be interested. Quite often the question is raised when was the last time a Customer was in at a Club Level for a whole heap of other reasons. A quick search of the forum suggests it has not been done before…

PS: Should add you are correct if you were speaking to me regarding:

So nothing urgent from me.

5

Yes please that would be really beneficial.

This is how my “customer” custom fields are mapped out currently. I would be interested in the “Phone” and “Type” customer fields.

phone number.png788×208 39.1 KB

6

I’ll prepare something tonight for you. In meantime you can get idea of how entity state log works by studying the time clock tutorials.

7

Thank you so much!

I will.

8

Sorry I got busy and forgot to show you. I bookmarked this tomorow I will try and throw something together.

9

Thanks Kendash.

Awaiting your input :smile:

10

Dear Kendash - any update :)?

11

Woo yes I will update you today. Sorry been very very busy last few days. I have lots of free time today :stuck_out_tongue:

12

I want to say before we do this. I did some thinking and I think it is already possible to do this via reports. Let me play around with some reports and I will give you my results.

13

Try this:

[Customer Activity:1,1]
>Customer Name|Ticket Total
@{REPORT TICKET DETAILS:EN.Customers,T.TotalAmount.sum:(TCET=Customers):{0}:,}
$1|{REPORT TICKET COUNT:(TEN.Customers=$1)}

We can improve on that basic report. Selecting a Date Range in report viewer will give idea of customer names and how many tickets for that date range they had. So now we need to show Customers With No tickets.

14

Getting closer here is one that displays all Customers and shows Ticket Counts for each customer within selected Date Range. @emre how can I filter this to only show those Customers with 0 tickets?

[Inactive Customers:1,1]
>Customer Name|Tickets
@{REPORT ENTITY DETAILS:E.Name:(ET=Customers)::,}
@{REPORT TICKET COUNT:(TEN.Customers=$1)}
$1|[=FF('$2','0')]

PS the above report would work for you but if you have a TON of customers it might get tedius looking for those with 0 tickets. I think we are closer but I am not sure how to filter it for our needs.

15

Hmm… Great idea but I think that won’t be possible to filter Report Entity Details by ticket count. We’ll need a SQL join for it.

16

Ok I figured that I hoped maybe we could think of something. You know I like Report Tags.