See llms.txt for all machine-readable content.

Back to Templates

Score COD RTO risk for Shopify orders with GPT-4o-mini, WhatsApp and Sheets

Created by

Created by: Rahul Joshi || rahul08
Rahul Joshi

Last update

Last update a day ago

Categories

Quick overview

This workflow scores Shopify cash-on-delivery (COD) orders for RTO risk using Google Sheets history, an India pincode lookup API, and an OpenAI address quality check, then logs outcomes and uses WhatsApp and Slack to confirm, convert to prepaid, cancel, or hold risky orders.

How it works

  1. Triggers when a Shopify order is created or when a customer replies via WhatsApp.
  2. For new orders, keeps only unpaid COD orders and normalizes customer, phone, and shipping address details.
  3. Looks up pincode RTO history and the customer’s past COD behavior in Google Sheets, validates the pincode via an India Post lookup API, and checks address quality with OpenAI (gpt-4o-mini).
  4. Combines these signals into a 0–100 risk score with a risk band and reasons, skips orders already scored, and appends or updates the result in a Google Sheets COD Ledger.
  5. In shadow mode, posts the score to Slack only, and in live mode tags medium-risk orders in Shopify and places high-risk orders on fulfillment hold while adding high-risk tags.
  6. For medium/high risk with a valid phone number, sends a WhatsApp template asking the customer to confirm COD, switch to prepaid, or cancel, and alerts the team in Slack for high-risk orders.
  7. When a customer replies on WhatsApp, matches the response to the ledger and either releases the Shopify hold and confirms, creates a discounted Shopify draft order and returns its payment link, or cancels the Shopify order and updates the ledger.

Setup

  1. Connect Shopify (trigger plus HTTP header auth) and set your Shopify store domain in the configuration.
  2. Connect WhatsApp Cloud API (WhatsApp Trigger plus HTTP header auth), set the phone number ID/version, and get the message templates (cod_confirm_request and cod_prepay_nudge) approved.
  3. Connect Google Sheets and create the required tabs/columns for “COD Ledger” and “Pincode Stats”, then replace the Google Sheet ID placeholders used throughout the workflow.
  4. Connect OpenAI credentials for the gpt-4o-mini model and add a Slack OAuth credential plus the target channel ID for alerts.
  5. Update the configuration with your India pincode lookup API base URL and the WhatsApp Cloud API base URL, then start with mode set to shadow and switch to live after validating scores.