How to create a Customer Loyalty program using Appsheet?

[tatiya]

I would like to give Loyalty Points to my customers which they can use for future purchases. how to do this in appsheet?

[Aleksi]

@tatiya I believe you need to open your request little more so community members could help you with your case, thanks.

[tatiya]

To give more details, I would like to create a Loyalty Program for Customers to make sure they purchase from us in future aswell.

For every Purchase I would like to give them 50 Points, whenever they collect 1000 Points they can convert it to Money for Getting discount. 1000 Points = $10, so they get a discount of $10. This is the same the discount coupons which are used @ Wallmart or Amazon.

From app a Unique Discount Code must be generated which can be mailed to customer. When they use that Code discount will be applied for their purchase. Onced used it cannot be used again. So App needs to keep a record of all Codes given to customer and they should be used only once.

Codes will also have an expiry date so that customers use them within a given time.

[tatiya]

Not only Purchase I can give these coupons for customers who give referal or give reviews on social media.

[Koichi_Tsuji]

I m running my site, introducinng Appsheet as partner, and one gentleman recently get in touch me and asked question. This gentleman is now preparing to open up his own Gym in town, and trying to develop a booking site to join the athletic programme etc.

To be honest, there are loads of good tools rather than Appsheet in my honest and humble opinion, and I introduced WIX.com as a tool. He was happy and go for it.

I believe there is generic and native standard functions are prepared to give coupon etc as well.

Just as a side info.

[Henry_Scott]

@Aleksi, @tatiya nad @tsuji_koichi
Would my thinking on an app design below work?

I have a Customer List in Google Sheet, a Product List in another Google Sheet and then Purchases in another Google Sheet
Thinking is that when a Customer (onsite at my brick and mortar premises) purchases a product through my employees, then my employees will log the purchase and I will then create a Loyalty Point for that Customer for that Product in a “Loyalty Point” Google Sheet.

I then have a Lookup Google Sheet that drives my ENUMs and thinking is to have a tab that drives the config of the number of loyalty points per product from this Sheet.

A workflow will then use the Loyalty Points Sheet to email the Voucher with a QR code to the customer when he reaches a certain level of points.

The customer can then visit my premises and redeem his loyalty points when he purchases a product by displaying his QR code we emailed. In this case, if the purchase is done with this QR Voucher then he does not accumulate more loyalty points.

How to create these records in the Loyalty Points Sheet with my limited Appsheet coding knowledge, is another discussion

[Henry_Scott]

@Aleksi and @tsuji_koichi what is the method in Appsheet to create an entry/record from a Workflow in a Table when the Workflow fires. One Part of the Workflow will send an email Voucher with QR Code and the Other Part must automatically create the Voucher Record in a Table so that with the QR we can search for that Voucher when the Customer comes to redeem it on premises

[Koichi_Tsuji]

Easy but effective work around is to create quick table just for scanning. When your customer come to you with qt code then you scan that with a particular form which are generated by this table, and then it automatically open up target table which you want to open . Then you may place some action to toggle to change the flag, the ticket is consumed yes or no type of flag to make the coupon not reusable something like that.

[Henry_Scott]

@tsuji_koichi thank you for the response. I have the scanning functionality sorted in a different way (scanning is not my problem) and the Email is going out to the Customer (me for now while I am testing). I however want a Record created at Workflow Email time so that I have a record for it and when the Customer does not redeem then I know. I want to pick your guys brains how to achieve that in Appsheet at Workflow time

[Koichi_Tsuji]

I’m happy to help but need to get more of the details how your current table/column arrangement to give a useful guide for you.

[Henry_Scott]

I need the same feature set for a similar Use Case. Any guidance @Aleksi?

[Aleksi]

Is the app public or does it need authentication?

[tatiya]

The App which I have developed is to maintain customer database like Name, Phone, address. So its not open to all, just I can access it. Now as per the new feature I am asking for, I should be able to generate a random UNIQUE Alphanumeric code (i call it coupon code) which I can allocate to a particular customer (already present in my appsheet database). Once I allocate the code to a customer, an automated SMS should go from appsheet to that customer mobile and also keep it in database assigned to that customer for record.

Next time whenever that customer uses the Coupon to get discount I will enter it back in the appsheet database. This time the validity of the coupon should get disabled as it is already used (to make sure customer doesnt use it twice).

Hope I am clear?

[Henry_Scott]

My use case will have a logged in Business capture info that will award their Customers on their Appsheet app a point. Appsheet will then mail them a QR Voucher that they will redeem onsite next time they visit

[Aleksi]

Another side note… with the Wix, you can use add-on like 123formbuilder where you can collect the data with the gSheet and then connect that with your app for other purposes.

[Aleksi]

Create it with the UNIQUEID() and then check the validity in another table like AND(TRUE,IN([Code],TableName[Code])) if it’s used or not.

[Aleksi]

@Henry_Scott You could use the UNIQUEID() value for that QR and then check is it used or not when they visit.

[Aleksi]

Are these loyalty points tied to product? Or are the points the same what ever product the customer is buying? What is the point “rule”?

[Henry_Scott]

@Aleksi - yes tied to a Product

[Aleksi]

I’m thinking this… how to do this without extra loyalty points table. When the employee records the purchase, it will read/write points into that record with initial value. Then you could read is the limit reached from records where the voucher field is blank after the last purchase with the voucher code.

[Henry_Scott]

@Aleksi so basically you mean that one keeps a tally of points on the Customer profile or on the Purchase table? So no need for another table with Loyalty Points.

[Henry_Scott]

@Aleksi - ok I have thought through it now, so use the Purchases Sheet/Table, build a slice with a Customer & Product purchased as Key and if the Payment Type is Voucher then do not tally of points. If Payment Type is anything else, then tally points and subtract from that if the item has a Payment Type of Voucher
One needs to do this in the Workflow that sends the QR Code
For display purposes one can create a VC on the Customer Profile and do the same logic to get a running total. This way one can also tell the Customer when they visit how many Loyalty Points they have
How do you do the Sort on Date so that it stays in Order?
What do you do if the Table gets too big with datasets for the combination of the Customer & Product as key?

[Aleksi]

When the customer makes a purchase, is it happening only with one product?

[Henry_Scott]

One Product (actually a Service of some sorts)

[Aleksi]

Products have different points? Do you need to buy the same product so you could use the voucher? Or does it matter what products the customer is buying?

[Henry_Scott]

@Aleksi. Each Service Product is bought a number of times over a period. Each time it is bought a point is allocated. Then each Service Product has a setting that determines if a Voucher will be issued for say 4 or 5 or 6 points.
Then depending on the pionts, an email will be sent with a FREE Voucher with QR Code that can only be redeemed for that Product that the Voucher was issued for. A Record must be created in the sheet for that Voucher that was issued

[Phil]

See https://help.appsheet.com/en/articles/1363923-changing-data-from-a-workflow-rule-or-scheduled-report

[Phil]

Hi Henry,

I add topic “Adding a Record to Another Table” in the article I referred to above, that is https://help.appsheet.com/en/articles/1363923-changing-data-from-a-workflow-rule-or-scheduled-report

It describes how to add a new record to a table from a workflow rule.
Does that meet your need?

[Lynn]

Hi @Henry_Scott
Here is a link to a demo app and documentation for it. IT may be useful.

Column values for related tables Questions

Hi @Andrea Here is the App and documentation