Track creator commissions and PDF payouts with Shopify, Google Sheets, Gmail and Slack

Quick overview

This workflow onboards Shopify creators with unique discount codes, tracks attributed orders and refunds in Google Sheets, calculates monthly commissions with TDS and tier bonuses, emails PDF statements via Gmail and Gotenberg, posts leaderboards to Slack, sends WhatsApp sale alerts, and answers payout questions using OpenAI in Slack.

How it works

1. Receives a creator onboarding form submission, validates the inputs, and loads existing creators from Google Sheets to keep codes unique.
2. Creates a unique tracking link and Shopify discount code via the Shopify Admin GraphQL API, then saves the creator in Google Sheets, emails their code/link via Gmail, and announces the onboarding in Slack.
3. Triggers on new Shopify orders, calculates net sales and new-customer status, matches the order to a creator from Google Sheets by discount code or tracking-link handle, logs a pending commission row, and optionally sends a WhatsApp sale alert.
4. Triggers on Shopify refunds and cancellations, finds the related affiliate order in Google Sheets, recalculates/voids commission, and flags clawbacks to deduct in the next statement when a refund happens after payout.
5. Runs monthly on a schedule to select orders past the return window, totals sales and commission per creator from Google Sheets, applies tier bonuses and TDS rules, and uses OpenAI to generate a short performance note.
6. Renders an HTML statement to PDF through Gotenberg, emails each creator their statement via Gmail, appends payout records in Google Sheets, posts a Slack leaderboard, and marks included orders as paid/settled to prevent double counting.
7. Triggers when the bot is mentioned in Slack, uses an OpenAI agent with Google Sheets tools to answer questions from the Affiliate Orders and Payouts tabs, and replies in the Slack thread.

Setup

1. Create a Shopify custom app with read_orders, read_customers, and write_discounts, connect Shopify access-token credentials for the order/refund/cancel triggers, and add the Admin API token as header auth for the Shopify GraphQL request (replace REPLACE_STORE).
2. Create a Google Sheets file with Creators, Affiliate Orders, and Payouts tabs and replace all REPLACE_WITH_GOOGLE_SHEET_ID values (and any referenced Slack channel IDs) in the workflow.
3. Add credentials for Google Sheets, Gmail, Slack (including permissions for app_mention), WhatsApp Business Cloud (phone number ID), and OpenAI.
4. Update the store URL used to build tracking links and set the Slack channel ID and WhatsApp phone number ID placeholders.
5. Run a reachable Gotenberg instance (for example gotenberg/gotenberg:8) and update the PDF render URL if your host name differs from http://gotenberg:3000.
6. Set this workflow as your instance’s error workflow (or keep the included error trigger active) so failures post to Slack during order, refund, or statement runs.