Purchase history by customer or phone number

[iberiabati]

Hi again.
I need to search in some place (like Tickets or other screen) all the purchase history of one customer filtering by name or Phone.
I read similar posts, but no one come with a way to do it as i need.

For example:
I want to search all the tickets bought by phone number and see the totals of the items.
Pizza Mozzarella 4 Units For a total value of $20
Pizza Pepperoni 2 Units for a total value of $15
Coke 6 units for a total of $10

And then the total of the money… (in this case $45)

All this between dates i can define in the moment and sort for Most Item purchase to less item purchase.

I Dont even know where can i start looking at it.

Hope it can be done.

Thanks a lot for your help.

Regards,

[Shaker_Natour]

i used to use tickets screen, and filter by customer for certine period. this is not enough but itwill help

[iberiabati]

Shaker, Thanks for your answer. I saw that, but it is not what i need it.
Anyone else knows something about it.
Thanks

[Jesse]

I have not attempted this but with Custom Entity screen and the widgets and automation/scripts available it should be doable. I cant really give you a step by step because I have not done it myself but with my knowledge of the system it appears it might be doable. My recommendation is for you to study some of the advanced tutorials that use Custom Entity screens and get a solid understanding of the built in scripting available then try a few things and post here with your ideas we can help you along the way.

Few things to consider:

1. You could use NumberPad in TIcket View mode to load and display tickets using a search parameter. However you would need to automate some stuff to make it use Date for searching. This would only show 1 ticket at a time not a range.
2. You need to be more concrete on what your wanting. What do you want to do with these tickets and why do you need this function? There could be alternatives to accomplish what your trying to do.
3. You probably wont get a step by step guide anytime soon because more than likely nobody has attempted this or if they have they are not sharing it. We can help you but your going to need to do a lot of the work and study it.

[iberiabati]

Thank you Kendash.
I will look into it.
Where are the advanced tutorials?

2. I need to see all the things some customer bought. This is to make a loyalty program. I m not sure if the word is Loyalty.
   The Thing is i want to reward customers who spent certain amount of money in a period so for example i can send them extra foods or make them a discount.
   Also I need that to give them a bottle of wine for example on theirs birthday, as a gift. (Only if they spent that certain amount of money)

Hope i make myself clear, but if you have any doubts, please ask me, and i will try to be more specific.

I will try to read the tutorials but i dont have program skills so i guess it could be hard for me to do that.

Thanks again Kendash

[QMcKay]

@Jesse, I was expecting to see a Custom Report using Report Tags that showed this information. We can parameterize it later.

Are you eluding that we cannot display Tickets/Orders filtered by Entity using Report Tags?

I was thinking a report like this would be very useful, and I was going to start something in SQL, but figured I’d wait to see if someone could come up with a version using Report Tags.

The Account Statement Tutorial partially does this in SQL, but it only shows Ticket Totals, not Order Lines.

[Jesse]

It probably could Q. The problem with Report Tags would be searching by Date. SQL is more flexible in this regard.

[Jesse]

@iberiabati

2. I need to see all the things some customer bought. This is to make a loyalty program. I m not sure if the word is Loyalty.

Woohoo finally someone interested in KPI

@iberiabati

The Thing is i want to reward customers who spent certain amount of money in a period so for example i can send them extra foods or make them a discount.Also I need that to give them a bottle of wine for example on theirs birthday, as a gift. (Only if they spent that certain amount of money)

We can do this with some simple automation once we iron out the first part.

So what your requesting is a form of KPI and a way to feed that KPI result into promotions. My next question is should we attempt to do this automated or do you want to view the data manually and decide your promotions manually?

PS: thank you for clarifying it makes more sense to me what your asking for now. @QMcKay I wasnt quite sure on exactly what he needed but I think I understand it now.

KPI is short for Key Performance Indicator and it can be simple or complex. Simply put what you described is using a simple KPI metric (customers who spent certain amount of money in a period) to decide on a promotion. You could even track the promotion effectiveness which would be another KPI indicator.

[Jesse]

@iberiabati at this point what we need first is to decide how you want to gather your KPI data. So give us an idea of exactly what your wanting to track then for each item you want to track you can link a reason for example: Free Drink Promotion, Customer Loyalty Program etc.

Basically we need to start with how you wish to gather just the raw data and how you plan to use it so we can customize the way the KPI is presented. It can be a complex report or it can be as simple as a single value. It could even be graphs or charts. Or we could go very complex and automate a system around it.

Spend some time and get detailed with each type of data you wish to track.

[iberiabati]

QMcKay I read all the post (Account Statement Tutorial) This is not what i need but if you can do that, i guess we can make what i m thinking. Thanks, its pretty awesome what you did. (i will try to replicate in my SambaPOS)

Kendash Perfect. I will think about that and come back.

I think that i preferred to view the data manually and decide the promotion manually

I wrote the answer, but im still thinking hehe.

Do you think that its better if we try to do it automated?
Now that you know what i need, did you read again the first post? (the part that i put the example)
Do you need me to give you another example?

[QMcKay]

Here is a god start using SQL. Just insert the Customer Name in the @EntityName variable, and the Start/End Dates:

declare @EntityType varchar(50) = 'Customers'
declare @EntityName varchar(50) = ''
declare @EntityId int = 0
declare @DateBEG datetime = '2016-01-01'
declare @DateEND datetime = '2016-04-01'

SELECT
[EntityName]
--,[Date]
--,[TicketId]
--,[TicketNumber]
,[MenuItemName]
,[PortionName]
,sum([Quantity]) as [Qty]
,[Price]
,(sum([Quantity])*[Price]) as [ItemTotal]
FROM (

SELECT
 o.[Id]
,[TicketId]
,tkt.[TicketNumber]
,e.[Name] as [EntityName]
--,[MenuItemId]
,[MenuItemName]
,[PortionName]
,[Price]
,[Quantity]
,([Price] * [Quantity]) as [ItemTotal]
--,[PortionCount]
--,[CalculatePrice]
--,[DecreaseInventory]
--,[IncreaseInventory]
--,[OrderNumber]
,left(convert(varchar(30),[CreatedDateTime],126),10) as [Date]
--,[LastUpdateDateTime]
--,[PriceTag]
--,[Tag]
--,[Taxes]
--,[OrderTags]
--,[OrderStates]
FROM [Orders] o
JOIN [Tickets] tkt on tkt.[Id] = o.[TicketId]
JOIN [TicketEntities] te on te.[Ticket_Id] = tkt.[Id]
JOIN [EntityTypes] et on et.[Id] = te.[EntityTypeId] and et.[Name] = @EntityType
JOIN [Entities] e on e.[Id] = te.[EntityId]
WHERE 1=1
--and e.[Name] = @EntityName
--and e.[Id] = @EntityId
and o.[CreatedDateTime] >= @DateBEG
and o.[CreatedDateTime] <  @DateEND
--ORDER BY o.[TicketId], o.[CreatedDateTime]

) allorders

GROUP BY
[EntityName]
--,[Date]
--,[TicketId]
--,[TicketNumber]
,[MenuItemName]
,[PortionName]
,[Price]

ORDER BY
 [EntityName]
--, [Date]
, [Qty] desc
, [MenuItemName]

[Jesse]

Well we can do this with reports. So for example you can use a report that shows Items bought with tickets matching specific phone number.

We would use Custom Entity Screen, Editor Widget, and Custom Reports Widget for this.

[QMcKay]

Custom Entity Screen with Report Viewer Widget and some Editor Widgets for Customer Search and Date Filter…

If this is what you are wanting, I can put together a Tutorial.

If there are more fields you want to see, such as Phone Number, let me know …

[iberiabati]

QMcKay

   I tried that but nothing come as a result.

   I see there are a lot of things commented. Maybe i have to uncomment some line.

   I put the entitie name and the dates. I ll keep trying.

Never mind about that, i made it work, Thanks!

Kendash, That would be great! And we can totalize them at the end?

Thanks again to both of you!

QMcKay,
Thats great! Thats what i have in mind!!
Yes! I need to search by phone number, cause in my case, i decide to split the field of the customer in 2. So i use the default field for the name, and in other field i put the surname. (in that way i will be able to do more things with the data in the database) but im sure that they always call for the same number.
Also if its posible, i need to make the sum of all… In your screenshot will be $840.
Really, thanks a lot.

[QMcKay]

@iberiabati

Also if its posible, i need to make the sum of all… In your screenshot will be $840.

No problem on the Total for all Tickets.

The issue with this, since it is reading from the [Orders] table, is that the Ticket Total might not match the Order Totals if Addons or Discounts are applied at the Order-line Level via Order Tags.

For example, each of the Items for this customer were discounted by -10, and while the Ticket is aware of this, the Order is not.

Similarly, if the Customer had an Order that was Tagged with something that increases the final Order Price.

---

Unfortunately, it is very difficult to parse out the Order Tag Prices using SQL, and the complexity involved to do so is simply not worth it. This is where Report Tags can do a better job of tallying the information.

In my case, I simply don’t care enough, since ultimately, I just want to get a ballpark figure of Customer spending, and Item Quantity. So this is where it stands at this time, and it works very well, if you can ignore the fact that the Grand Total does not match the Order-line Totals …

[iberiabati]

QMcKay,
I understand what you mean and its perfect.
That is exactly what i need and i can deal with that little issue.
In fact, i think i dont have a scenario where i extra charge some things.
I could have some discounts but it does not affect what im trying to do.

Just because i curious, Did you have this done or you just make it today?

Its really great! Hehe

Thanks!

[QMcKay]

I just made it today. I will post a full Tutorial in a little while.

[QMcKay]

Tutorial here …

Customer Purchase History Reporting

This is a Report to show Historical Purchases on a per-Customer basis. You can select a Date Range to Report on and use Entity Search to load any Customer Purchase History. [image] This Tutorial uses to SQL scripts to pull data from the [Orders] table and the [Tickets] table. The data in the [Orders] table does not include Additional costs or Discount values that might be applied via Order Tag Prices, so the Item Total Prices could be incorrect (too high or too low). Note in th…

[pauln]

Remarkable work @QMcKay!
Anyone using Customer Accounts this is another valuable addition, thank you Q.

[iberiabati]

QMcKay,
Thanks a lot for your help.
I marked the post as solved.
If you come to Argentina, let me know, and you can came here to eat something, (For free, of course!! hehe)
Best Regards,