How To Detect Coupon Code Abuse Using ChatGPT

Time needed: 10 minutes

We’ll make 2 workflows to achieve the desired results. The first workflow will add every order total and its discounted totals to a database in MESA. The second workflow will ask ChatGPT if the values appear higher than usual. If so, the workflow will send an email notification.

To get started, be sure to install MESA on your Shopify store. Then, follow these simple, step-by-step instructions.

1. For the first workflow, choose the Shopify trigger app, then choose the Order Created step

   The trigger step is the event that starts this workflow.

2. Next, add the target app, Data by MESA

   Use the search field to locate the Data by MESA. This built-in app lets you store data in MESA without setting up and configuring an external database.

3. Choose the Create Record

   This action step will create a database record every time a new order is received.

4. Configure your new database

   Create a new table called Orders Coupon Code Abuse. Then under the Columns section, click the button to Add Column. We’ll be adding custom columns for various order data defined in the subsequent steps.

5. Use the variable selector to connect new order data to database table columns

   For your first custom column, enter “Order #” in the name field, choose Number for the type field then use the variable selector to choose the Number variable from the previous Order Created step.
   
   Repeat this to create these additional columns:
   
   1. (Name) Order Total, (Type) Number, (Value) Order Created > Current Total Price
   2. (Name) Order Total Discounts, (Type) Number, (Value) Order Created > Current Total Discounts
   3. (Name) Order Date, (Type) Date, (Value) Order Created > Created At
   

6. Save, then name your workflow

   We’ll call this workflow, New Orders to Orders Database. Then, be sure to add your email to receive any notifications about this workflow.

7. Turn your automation On

   Be sure to enable your new workflow automation. This workflow is complete so let’s make a new workflow based on the saved database records.

8. For the next workflow, select the Data by MESA trigger app then, choose the Record Created step

   On the table field, be sure to select the database called Orders Coupon Code Abuse that we created in the previous workflow. Under the advanced options, you can change how often this workflow run after the event occurs.

9. Next, choose the Data by MESA app. Then, select the Query step.

   

10. Select the Orders table that we created from the previous workflow

    Be sure the Orders table is selected.

11. Under Advanced Options, add this custom query

    This step will query the database table we specify and fetch all the data within it. This query is looking for the same database columns we created in the first workflow so be sure they match.
    
    SELECT TO_CHAR("Order Date", 'yyyy-mm-dd'), SUM("Order Total") as total, SUM("Order Total Discounts") as total_discounts, ROUND(SUM("Order Total Discounts") / SUM("Order Total"), 2) as percentage FROM "Orders Coupon Code Abuse" GROUP BY TO_CHAR("Order Date", 'yyyy-mm-dd') ORDER BY TO_CHAR("Order Date", 'yyyy-mm-dd')
    

12. Next, add Code by MESA to the workflow

    We need to make the results of the database query available as a JSON variable for use in another step of this workflow. Optionally, add a description value of “Convert query to JSON”

13. Click Edit Code to open the editor. Then, paste this code snippet.

    Under the code comment Add your custom code here, add this snippet:
    
    Mesa.log.info("payload", payload);
let newPayload = {}; newPayload.json_results = JSON.stringify(payload);
    
    Save then, close the editor view.
    
    

14. Next, choose the ChatGPT action app

    Be sure to add your account credentials by following the simple link and instructions to obtain your API key.

15. Edit the Step Configuration then choose the highest Model preferred.

    Your API key will decide which model is available. Click the Add Set button when you’re ready.

16. Instruct ChatGPT to detect coupon abuse

    Use the following prompt in the Content field:
    
    Included is a JSON result showing order totals by day including their discount amounts. Is the discount rate higher than usual? Reply only "Yes" or "No" :
{{ custom.json_results })
    
    The custom variable comes from the Code by MESA step and isn’t available from the variable selector view so include it when pasting the instructions.

17. Next, add Filter by MESA

    Search then, select the Filter by MESA app action.

18. Add the ChatGPT response to filter against

    Search then choose the Content variable. When prompted, click the Return first item button. Since we’re not engaging in a conversion, we’re only interested in the first response.

19. Update the filter to look for the response, Yes

    After the variable has been added to the condition logic, be sure the filter is looking to see it equals the value, Yes.

20. Next, add Email by MESA to send yourself a notification

    Add the Email by MESA app action, then enter your email, subject, and a brief message to investigate your order activity for coupon code abuse.

21. Save then, turn on your workflow

    Be sure to name your workflow and enter an email address for any related workflow notifications.