Coupon Code Abuse Detection with Slack Alerts | Shopify Fraud Prevention

Detect Coupon Code Abuse and Receive Slack Alerts

8 min setup

No coding required

Runs automatically

Safeguard your promotions by monitoring for coupon code abuse with this MESA workflow template. It compares daily order totals and discounts, using AI analysis to identify any unusually high discount rates. If potential abuse is detected, you'll receive a Slack notification, allowing you to respond quickly. Note: This template requires the “Organize Shopify Orders by Order Date” template to be enabled.

How it works

Record Created

App connector: Data• Time to complete: 0 minutes (Auto-configured)

Why this matters: This trigger regularly monitors your order database to detect new records, providing the foundation for hourly discount analysis without requiring constant manual data checks.

This trigger checks your "Shopify Orders" MESA Data table every hour for new records using the schedule. When new order data is detected, the workflow activates and begins the discount analysis process.

Query Daily Discount Totals

App connector: Data• Time to complete: 1 minute

Why this matters: Aggregates your order and discount data by day to calculate discount percentages, transforming raw order records into actionable metrics that reveal usage patterns and potential abuse.

This step runs a custom SQL query against the "Shopify Orders" MESA Data table to aggregate order data by day. The query calculates:

  1. Date: Orders grouped by date (formatted as yyyy-mm-dd)
  2. total: Sum of Total Price for all orders that day
  3. total_discounts: Sum of Total Discount for all orders that day
  4. percentage: Discount rate calculated as total_discounts divided by total (rounded to 2 decimal places)

SQL query breakdown:

SELECT
  TO_CHAR("Created At", 'yyyy-mm-dd'),  -- Group by date
  SUM("Total Price") AS total,           -- Daily revenue
  SUM("Total Discount") AS total_discounts,  -- Daily discounts
  ROUND(SUM("Total Discount") / SUM("Total Price"), 2) AS percentage  -- Discount rate
FROM "Shopify Orders"
GROUP BY TO_CHAR("Created At", 'yyyy-mm-dd')
ORDER BY TO_CHAR("Created At", 'yyyy-mm-dd');

The results return rows showing each day's totals and discount percentage, enabling trend analysis to identify spikes in discount usage.

Format Discount Data for AI

App connector: Code• Time to complete: 1 minute

Why this matters: Transforms the SQL query results into a clean JSON format that AI can easily analyze, ensuring accurate interpretation of discount patterns without data formatting errors.

This custom JavaScript step formats the SQL results into a JSON string. The code takes the array of daily discount data from the previous step and converts it to a JSON string format that's optimal for AI analysis. The formatted data is returned as json_results containing the structured discount information.

// Creates new payload object
let newPayload = {};
// Converts SQL results to JSON string
newPayload.json_results = JSON.stringify(prevResponse);
// Passes formatted data to next step
Mesa.output.next(newPayload);

Detect Coupon Abuse with AI

App connector: AI• Time to complete: 3 minutes

Why this matters: Uses AI to intelligently detect abnormal discount patterns by comparing current rates against your baseline average, providing smart analysis that adapts to your specific business patterns rather than rigid thresholds.

This AI step analyzes the formatted discount data to detect abuse patterns. The AI receives the JSON results containing daily discount rates and compares them against a baseline assumption of 10% typical discount rate.

AI instructions:

  1. Compare each day's discount rate against the 10% baseline
  2. Determine if any recent discount rates are significantly higher than typical
  3. Respond with only "Yes" (abuse detected) or "No" (normal patterns)

Filter: Was coupon abuse detected?

App connector: Filter• Time to complete: 1 minute

Why this matters: Acts as a gatekeeper that only allows the workflow to continue and send alerts when AI has confirmed suspicious activity, preventing alert fatigue from false positives or normal discount activity.

This filter checks if the AI response equals "Yes". If true (abuse detected), the workflow proceeds to send a Slack alert. If false (AI responded "No"), the workflow stops here without sending a notification. This filtering ensures your team only receives alerts for genuine concerns, not routine discount activity.

Send Slack Message

App connector: Slack• Time to complete: 1 minute

Why this matters: Delivers immediate notification to your team when coupon abuse is detected, enabling quick investigation and response to protect revenue before the situation escalates.

This step sends a Slack message to your configured channel. Configuration: You must set two values during setup:

  1. Slack channel: Select the channel where alerts should post (like #fraud-alerts, #finance, or #operations).
  2. Alert message: Customize the notification text. Default is "Coupon code abuse detected" but you can enhance with additional context, instructions for investigation, or formatting for visibility.

The message posts immediately when abuse is detected, ensuring your team can investigate while patterns are recent.

Required: The following must be used with this workflow.

Frequently asked questions

How do I set up the "Shopify Orders" data table that this workflow requires?

The easiest way is to add the template "Store Shopify Orders in a Database". This will setup the table exactly as needed.

Can I change the 10% baseline discount rate to match my business?

Yes, edit the AI prompt in the "Compare discount rates to identify coupon abuse" step and replace "assume 10% if unspecified" with your actual typical discount rate.

What if I want to check more or less frequently than every hour?

Edit the trigger's poll schedule field. Adjust based on your order volume and how quickly you need to detect abuse.