Skip to content
Faisal Mahmud TusherDigital marketing, SEO & analytics
with Faisal Tusher

AI in Marketing › Expert

Checking AI-Written SQL and Tracking Code for a Sewing Machine Retailer

You take two pieces of AI-written work at an invented sewing machine retailer, a revenue query bound for the board pack and a tag for fabric sample requests, and prove each before anyone relies on it. You finish with a corrected query reconciled to the shop's own report, a tag rebuilt to its tracking plan row and tested under both consent choices, and a clear record of what was fixed, what was saved and what was written down.
ExerciseExpert levelModule 1: Checking what AI writes for data and tracking5 minUpdated

Why this matters

The advanced AI in marketing level tested prompts before trusting them. Here AI-written technical work turns straight into numbers other people act on. The routine for checking AI-written SQL and the routine for checking AI-written tracking code own the method; your job is to run both on real work and decide what may ship.

The retailer, its revenue query and its sample request tag

Jomurel Sewing is an invented online retailer of sewing machines, overlockers and fabric that posts free fabric samples on request. Its marketing manager asked an approved AI tool for September's revenue by channel, for the board pack, and a tag for sample requests. A part-time analyst built the warehouse; a freelance developer publishes the tag container.

Illustrative example — not a real result

Jomurel Sewing: the revenue query

The model received only the request and each table's column names and types.

TableOne row isWhat the model was not told
ordersOne ordersession_id is the order's session, empty when none was recorded; is_test flags staff test orders; timestamps are in UTC; net_value excludes VAT and delivery
order_linesOne product in one orderline_value includes VAT and a share of delivery
sessionsOne sessionsession_id is unique; customer_id is filled only for logged-in visitors

The owner's definition: revenue is the net_value of orders completed in the calendar month, UK time, excluding cancelled and test orders, refunds left in. Channel is that of the session the order was placed in; orders with no session are reported as "Unattributed".

SELECT
  s.channel,
  COUNT(*) AS orders,
  SUM(l.line_value) AS revenue
FROM orders AS o
JOIN order_lines AS l ON l.order_id = o.order_id
LEFT JOIN sessions AS s ON s.customer_id = o.customer_id
WHERE o.status = 'completed'
  AND s.channel <> 'internal'
  AND o.created_at BETWEEN '2026-09-01' AND '2026-09-30'
GROUP BY s.channel
ORDER BY revenue DESC;

It returned 2,310 orders and £301,250 across 7 channels. The shop's own September report shows 980 completed orders worth £176,780 net of VAT and delivery.

Illustrative example — not a real result

Jomurel Sewing: the plan row and the generated tag

Plan columnEntry
Eventfabric_sample_request, on the data layer message pushed when the form's success response returns
Parametersfabric_sku (the shop's fabric code); delivery_country (two-letter code)
Location and consentPages whose paths start /fabric/; consent category analytics
Owner and verifierMarketing manager; part-time analyst
Generated tagDetail
TriggerClick on any element reading "Request sample", on paths matching fabric
Sentsample_requested with fabric_name from the page title, country and postcode from the form
ConsentCustom HTML that sets its own cookie, jm_sample; no consent settings in the container

Test pages: 2 fabric pages, the same with a colour in the query string, a sewing guide on choosing fabric, the fabric scissors page and the staff screen for dispatching samples.

Running is not the same as right

The query returned a clean table and the tag fired in preview, the only tests the manager ran. Neither says whether the numbers are true. The query is proved against the definition and a total from another source, the tag against its row in the tracking plan and the pages it runs on, and both pass the review checklist for AI-written code before anything ships.

Task

  1. Read the query against the definition and say what the schema left out.
  2. Check grain, joins, filters and the month boundary.
  3. Reconcile the corrected total to the shop's report and explain any gap.
  4. Check the tag's names against the plan row, and the personal data it could send.
  5. Test the trigger against pages that should and should not match, and plan preview under both consent choices.
  6. Say how the tag is released, and what is fixed, saved and recorded.

Worked answer

What the model was not told. The schema gave names without meanings, so the model chose the date column, the revenue field and the channel rule itself. The first fix: the request now carries the definition and the analyst's notes.

Grain and joins. order_lines holds one row per product, so COUNT(*) counts lines and line_value is not the defined revenue: a grain fault. Worse, customer_id repeats in sessions, so each line repeats once per session the customer ever had. The right Join Key is the order's own session_id.

Filters and dates. The s.channel condition, in the WHERE clause of a left join, drops orders with no session. The definition excludes no channel, so the condition goes. The query also keeps test orders. The date filter reads created_at in UTC and stops at the first moment of the last day.

Illustrative example — not a real result

Jomurel Sewing: the reconciliation trail

Fix applied, cumulativelyOrdersRevenue
As generated2,310 (rows)£301,250
Join on session_id1,690 (rows)£212,300
Count distinct orders; sum net_value from orders938£168,850
Channel condition dropped; no session reported as "Unattributed"979£176,020
is_test orders excluded973£175,480
completed_at in UK time, start inclusive and end exclusive982£177,100
Shop report for September980£176,780

Gap: 2 orders worth £320, both refunded in full on 30 September. The shop's report drops refunded orders; the definition keeps them. Explained, so the query stands.

Names and personal data. The event and both parameter names differ from the plan, so reports would split from the parameters it expects. postcode comes out: the address form holds personal details, and the plan asks for nothing from it. The page title gives way to the fabric code.

The trigger. The pattern fabric matches every test page, staff screen included, and the click fires on a failed form and again on its correction. Both faults go when the developer pushes fabric_sample_request on the success response and the tag listens for that message alone.

Both consent choices. Preview runs once after accepting the banner and once after refusing it, with invented form details. On the generated version both runs send the request and set the cookie, the warning sign the tracking routine names. The rebuild uses the built-in event tag, drops the cookie and leaves the container's consent mode set-up to govern it, then repeats both runs.

Release, save and record. The query reaches the board pack only after reconciliation, saved with its definition, checks and verifier, and its use enters the AI register. The workspace description names the AI tool; the developer publishes it against the publish checklist and checks it live. The request, fixes and tests stay with the plan row, which the analyst then marks verified, with the container version.

Common mistakes

  • Trusting a plausible table. Nobody had checked its total.
  • Column names without meanings. The schema's gaps caused most of the errors.
  • Fixing the number instead of the query. A gap is explained or the checks run again.
  • Testing only where the tag should fire, with consent granted. The quiet pages and the refusal found the faults.
  • Letting the requester verify her own work. The plan row names someone else.

Pages this lesson applies

Terms used on this page

Also related

Related