Daily MAP violation and unauthorized reseller alerts in Slack

Every morning at 6am, check whether anyone is undercutting our minimum advertised price or selling our products without authorization. This runs on a daily cron schedule.

Start by reading our product list from Google Sheets with the Get Values operation, from a tab called Products. Each row holds a product name or model number, our MAP floor price for that product, and the list of authorized sellers allowed to carry it. Treat the authorized sellers cell as a comma separated list, and compare seller names case insensitively with surrounding whitespace trimmed so that "BestDeals Inc" and "bestdeals inc " are the same seller.

Before scanning, also read the Violations tab of the same spreadsheet with Get Values. Use it to build the set of seller and price combinations already logged in the last 7 days, so we do not re-alert on something we have already reported. Anything older than 7 days no longer suppresses an alert.

For each product in the list, run a SerpApi Google Shopping Search using the product name or model number as the query, and pull back every live offer with its price and seller name. If a product returns no offers, skip it completely: log nothing and alert on nothing for that product.

For every offer that comes back, apply two checks. First, if the offer price is below our MAP floor for that product, flag it as a below MAP violation and compute the gap as the MAP floor minus the offer price, in both dollars and as a percentage of the floor. Second, if the seller name is not on the authorized list for that product, flag it as an unauthorized seller violation. An offer can be both at once, in which case log it as a single row that names both reasons rather than logging it twice.

Drop any flagged offer whose seller and price combination already appears in the last 7 days of the Violations tab. Everything that survives that filter is a new violation.

Append every new violation to the Violations tab in the same Google Sheet using the Append Values operation, with one row per flagged offer containing the date, the product, the seller, the offer price, our MAP floor, the gap versus MAP in dollars and percent, the violation type (below MAP, unauthorized seller, or both), and the offer link when one is available.

Finally, post one Slack Bot message with Send a Message to our brand protection channel, grouping the new violations by seller so the brand protection lead can act on one seller at a time. Under each seller, show how many products they are violating on and list each offer with the product, their price, our floor, and the gap. Put the sellers with the largest total gap at the top, and open the message with a one line count of new violations and sellers involved. If there are no new violations after deduplication, do not post anything at all.