Detect Coupon Code Abuse and Receive Slack Alerts

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.

Critical requirement: You must have a MESA Data table named "Shopify Orders" that contains order information including "Created At", "Total Price", and "Total Discount" columns. This table is typically populated by another workflow that syncs Shopify orders to MESA Data storage.

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.

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);

This formatting step ensures AI receives data in a consistent, parseable format regardless of variations in SQL output.

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)

The temperature setting of "1" balances between consistent analysis and nuanced pattern recognition. The AI considers context like whether high discount days are isolated incidents or sustained patterns, and whether the increase is dramatic enough to indicate abuse versus normal promotional variation.

Baseline customization: The default assumes 10% average discount rate. You can modify the prompt to specify your actual typical discount rate for more accurate detection.

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.

Alert precision: The AI's analysis combined with this filter prevents notification fatigue by distinguishing between normal promotional activity (like legitimate sales events) and suspicious discount abuse patterns.

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). First invite the MESA Slack app to the channel by typing @MESA and clicking Invite.
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.

Message customization examples:

1. "🚨 Coupon abuse detected! Discount rate is significantly higher than normal baseline. Check recent orders for suspicious patterns."
2. "⚠️ Unusual discount activity detected. Review dashboard for details."
3. Include links to your order dashboard or analytics tools

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