“10FORGOOD” Voucher $10 OFF, >$99 one-use per SG household
This discount code is for the first purchase order with the company. You will get 10 dollars off for a minimum order of $100. This first-time purchase was not per user but rather per household.
However, there were a number of customers who would use this discount multiple times by changing their email address or phone number, or delivery address. This change would bypass Shopify checks. The Ops I.C was tasked to find these users. This list was then passed on to customer service. Which would notify them that their voucher is invalid and that they were going to be charged an additional 10 dollars. The customer would then have the option to cancel the order or to go through with their purchase.
In order to generate the list of misuse vouchers, the Ops I.C would go through the Shopify database and compare all the orders with the 10FORGOOD voucher applied against all past orders. Looking for similar phone numbers or addresses used previously. This method was not scalable. So I worked on a solution that could speed up this process.
I created a MySQL Database and learned certain search queries to perform the task of finding customers who have misused the solution. The following would be the only steps required for users to catch misuse cases.
Export the customer data from that day’s orders
Upload into MySQL
Show Misuse Orders
[If required] Zoom into Misuse Order (user could find out which two orders were matching that the database regarded as misuse. This would be used in case the customer challenged the company.)
With the new method, the user only took about 10 minutes to do this whole task. It was about an hour before this solution was produced.
This solution solved the problem and saved time. However, as it was an offline database. Only a single computer could utilize the features. If another machine was to be added and mixed. There would be a limitation. The database would be independently hosted on each machine. Both machines would have to constantly and consistently update. This is so the information remains true.
I chose to keep it ‘offline’ and locally on the machine because at the time, I was afraid of hacking and I had no security measures in place. The only security was that the database was ‘offline’.