MySQL MCP for WooCommerce: each order’s campaign next to its ad spend

Since WooCommerce 8.5, an order that came from a tagged link keeps its UTM campaign, right in your store’s MySQL database. Let Claude or ChatGPT read it through a MySQL MCP server, next to your Facebook Ads or Google Ads account, and you get order totals against spend, by campaign, from one question.

WooCommerce already saves the campaign on each order

WooCommerce’s order attribution stores utm_campaign, utm_source and utm_medium with each order. They sit in the wp_wc_orders_meta table, under keys like _wc_order_attribution_utm_campaign, next to the orders in wp_wc_orders. Your ad account knows what each campaign cost. Neither one alone tells a client what each campaign brought in for what it cost.

Order totals and ad spend, by campaignExample rows. The ai_orders view holds order 1041 for $120.00 and order 1043 for $200.00 from spring_sale, and order 1042 for $80.00 from retargeting. Facebook Ads shows $150.00 spent on spring_sale and $40.00 on retargeting. Claude puts them in one answer: spring_sale, 2 orders, $320.00 in order totals, $150.00 spend, $2.13 per dollar; retargeting, 1 order, $80.00, $40.00 spend, $2.00 per dollar.Order totals and ad spend, by campaignExample rows: two sources, one answerai_orders view, from your store’s MySQLordertotalutm_campaign1041$120.00spring_sale1042$80.00retargeting1043$200.00spring_saleFacebook Ads, last monthcampaignspendspring_sale$150.00retargeting$40.00One answer in Claudecampaignorderstotalsspendper $1spring_sale2$320.00$150.00$2.13retargeting1$80.00$40.00$2.00
Example rows, not a real store. “Per $1” is order totals divided by spend.

The two only line up when utm_campaign holds the campaign’s name. In Meta Ads, add utm_campaign={{campaign.name}} to each ad’s URL parameters. Google Ads auto-tagging doesn’t pass the name, and Google only offers {campaignid}, not the name, so type the name into each campaign’s final URL suffix. Orders placed before 8.5, or with attribution turned off, have no campaign.

What you can ask once it’s connected

Start each question with “Using SyncWith” and name the view:

  • “Using SyncWith, total last month’s orders in ai_orders by utm_campaign, put Facebook Ads spend by campaign next to them, and show order totals per dollar spent.”
  • “Using SyncWith, which utm_campaign values in ai_orders last month don’t match any Facebook Ads campaign name?” Ask this one first. Each value it lists is a tag that needs fixing, or orders that didn’t come from your ads.
  • “Using SyncWith, count last month’s orders in ai_orders by utm_source.” That splits Facebook, Google, email and the rest.

This is your store’s own count, by the campaign each visit carried, so it won’t match the purchases or ROAS in Ads Manager. The view counts completed and processing orders, and its totals are before refunds, which WooCommerce keeps as separate rows.

Give the assistant one view, not your tables

First, ask your host whether your store’s database accepts connections from outside. If it does, send the SQL below to whoever runs the database. It needs WooCommerce’s own order tables, the default for new stores since 8.2. Change shop to your database’s name and wp_ to your table prefix.

-- 1. One view: paid orders and the campaign each came from
CREATE VIEW shop.ai_orders AS
SELECT o.id AS order_id,
       o.date_created_gmt,
       o.status,
       o.currency,
       o.total_amount,
       c.meta_value AS utm_campaign,
       s.meta_value AS utm_source
FROM shop.wp_wc_orders o
LEFT JOIN shop.wp_wc_orders_meta c
  ON c.order_id = o.id
 AND c.meta_key = '_wc_order_attribution_utm_campaign'
LEFT JOIN shop.wp_wc_orders_meta s
  ON s.order_id = o.id
 AND s.meta_key = '_wc_order_attribution_utm_source'
WHERE o.type = 'shop_order'
  AND o.status IN ('wc-completed', 'wc-processing');

-- 2. A login for each SyncWith address, capped at 500 queries an hour
CREATE USER 'ai_reader'@'3.227.95.35' IDENTIFIED BY 'change-me'
  WITH MAX_QUERIES_PER_HOUR 500;
CREATE USER 'ai_reader'@'34.199.138.158' IDENTIFIED BY 'change-me'
  WITH MAX_QUERIES_PER_HOUR 500;

-- 3. Both can read the view and nothing else
GRANT SELECT ON shop.ai_orders
  TO 'ai_reader'@'3.227.95.35', 'ai_reader'@'34.199.138.158';
  • A view runs with its creator’s permissions by default, so ai_reader sees only what the view shows: no email, IP address, browser or customer note. Whoever creates it needs the CREATE VIEW privilege and SELECT on the two tables.
  • The part after the @ in CREATE USER means each login works only from that address. We connect from two, so there are two logins, and one GRANT covers both.
  • Whatever MCP server you use, the cap of 500 queries an hour stops an assistant that gets stuck in a loop.
  • An open report on the benborla MySQL MCP server says its read-only mode doesn’t stop SELECT ... INTO OUTFILE, a SELECT that writes a file. This login can’t run it: it needs the FILE privilege, and no grant on a single view can give FILE.

Use the view in Claude.ai or ChatGPT

Claude.ai and ChatGPT on the web only reach remote servers, and ours is one, so there’s nothing to install. Add https://mcp.syncwith.com/mcp as a custom connector (steps for Claude, on any plan, and ChatGPT, on Business, Enterprise or Edu). Allow 3.227.95.35 and 34.199.138.158 through your firewall, then ask it to connect MySQL and paste the connection string for ai_reader into the form it links. To check, ask “Using SyncWith, what columns can you see in ai_orders?” If you’d rather run a MySQL MCP server on your own computer in Claude Desktop or Cursor, the view suits it too.

There’s no separate charge for the MCP server today. Each database query uses one refresh from your SyncWith plan, and the free Hobby plan includes 35 refreshes a month. Listing tables doesn’t use any.

Want these numbers in a sheet that updates itself? See MySQL to Google Sheets. Every tool the server has is in the SyncWith MCP docs.