Use AMC’s AI SQL Generator to Catch Prime Day Waste in Shopify Cohorts

Amazon has been adding AI-assisted SQL workflows to Amazon Marketing Cloud, and by 2026 the pitch is obvious: fewer blank-query screens, faster audience cuts, less analyst bottleneck.

Fine. That part helps.

But the useful move before Prime Day is narrower than “ask AI for retail-media insights.” I want AMC to show me which exposure paths look good inside Amazon Ads but fall apart once I connect them to Shopify cohort revenue. A campaign can drive attributed sales and still be a lousy use of July budget if those buyers never come back, only buy discounted hero SKUs, or already had a strong relationship with the brand before the ad touched them.

I have seen this most often with brands that run Shopify Plus, Amazon Ads Sponsored Products, Sponsored Brands, Sponsored Brands video, DSP remarketing, and at least one organic channel that actually moves demand, usually Klaviyo email, TikTok Shop content, creator whitelisting, or SEO pages that rank for product comparisons. Inside the platform reports, the retail-media story looks tidy. Spend went in. Sales came out. ROAS cleared the target.

Then Shopify cohorts ruin the mood.

Why AMC’s AI SQL Generator matters now

Amazon Marketing Cloud is still the place to answer questions that the standard Amazon Ads console cannot handle cleanly. You can query event-level ad signals in a privacy-safe clean room, build audiences, and analyze paths across Sponsored Ads, DSP, and Amazon-owned inventory. That has always been powerful. It has also been annoying.

The older AMC workflow expected someone on the team to know the schema, write SQL, understand user-level privacy thresholds, and translate marketing questions into joins. In a 12-person DTC team, that person is usually a growth lead doing SQL between budget meetings, or an agency analyst juggling 14 accounts. Nobody enjoys being the human bridge between “are these video viewers worth anything?” and a 90-line query.

Amazon’s AI-assisted query tooling changes the first 30 minutes of the job. A marketing operator can start with a plain-English prompt like, “Show conversion paths for users exposed to Sponsored Brands video and DSP retargeting in the 21 days before purchase, grouped by first exposure campaign.” The generator can draft the SQL, surface the relevant tables, and get the team close enough to inspect, edit, and run.

Close enough matters. I still would not let an AI-generated query decide a $180,000 Prime Day media shift without checking the joins, attribution windows, and filters. But I would absolutely use it to get 6 candidate analyses running before lunch instead of waiting two days for a clean-room specialist.

That speed is the unlock.

The wrong question gives you pretty waste

Most teams walk into AMC with a platform-native question: “Which campaigns drove attributed sales?” That is a reasonable starting point, but it is too shallow before Prime Day.

Prime Day warps the buyer pool. In July 2025, Amazon said independent sellers sold hundreds of millions of items during the event. Discounts, urgency, Lightning Deals, badge placement, and competitor noise all pile into the same two-day or multi-day window, depending on the year’s format. A buyer who converts during that crush may be a future customer. Or they may be a promo tourist.

If you only look at attributed Amazon sales, both buyers look the same.

Shopify cohort revenue gives the second half of the picture. I care about first purchase date, repeat purchase rate at 30, 60, and 90 days, gross margin by SKU family, discount depth, subscription start, refund rate, and whether the same email or phone hash later shows up in owned-channel revenue. For a consumable brand, a $42 first order with a 41% 90-day repeat rate is a different animal from a $58 Prime Day order with a 7% repeat rate and a 30% coupon attached.

That is where wasted retail-media spend hides. Not in the campaigns with zero sales. Those are easy to cut. The expensive waste sits in campaigns that produce sales the ad platform can claim, while the business gets thin-margin, one-and-done customers.

The data connection I actually want

My preferred pre-Prime Day setup is simple enough to explain on one whiteboard.

AMC holds the ad exposure path. Shopify holds the customer economics. A customer data layer connects them using privacy-safe identifiers and time windows.

In practice, that might mean Shopify Plus exporting orders to Snowflake through Fivetran, customer events streaming through Segment, Klaviyo profiles carrying email hashes, and Amazon Marketing Cloud receiving matched audience inputs through Amazon Ads clean-room workflows. Some teams use Hightouch or Census to move cohorts. Others use a warehouse-native dbt model and upload audience segments manually during planning. The tooling matters less than the grain of the table.

I want one row per customer or household-level matched identifier, with these fields available before the AMC query work begins: first Shopify order date, LTV through 90 days, number of Shopify orders, first SKU family, discount code used, contribution margin band, subscription flag, refund flag, and acquisition source as recorded outside Amazon.

Then I want AMC exposure summaries by campaign, placement, ad type, and sequence. Did the customer see Sponsored Products first, then Sponsored Brands video, then DSP? Did they see only retargeting? Did they click nothing but convert anyway? Did the path start after they had already visited the Shopify PDP from organic search?

The last question is usually where someone in the room gets quiet.

A concrete Prime Day waste test

Say a skincare brand spent $240,000 across Amazon Ads in the 21 days before Prime Day 2025. The account shows $720,000 in attributed sales, so platform ROAS is 3.0. Nobody is panicking.

Now split those buyers into Shopify-informed cohorts.

Campaign group A is Sponsored Products on non-brand terms like “vitamin c serum” and “retinol cream.” It spends $90,000, drives $260,000 in Amazon-attributed sales, and maps to a Shopify-like cohort with 28% 90-day repeat purchase behavior when the same product family is bought direct. Average first-order margin after discount is 54%. That campaign is doing real acquisition work.

Campaign group B is DSP retargeting against product viewers and cart abandoners. It spends $70,000, drives $250,000 in attributed sales, and looks even better in Amazon reporting. But the matched cohort tells a different story. Forty-six percent of buyers had purchased on Shopify before May 1, 2025. The 90-day repeat rate after the Prime Day purchase is 9%. Refunds run high on the bundle SKU. Margin lands at 31% after the event discount.

Campaign group C is Sponsored Brands video on competitor conquesting terms. It spends $80,000 and drives $210,000 in attributed sales. The repeat rate is 19%, but the cohort has a weird tell: customers who first touched the brand through YouTube creator traffic in June, then saw the Amazon video ad in July, are overrepresented. AMC gives the exposure path. Shopify and GA4 give the prior demand signal.

I would not cut all three groups. I would cut B hard, protect A, and rebuild C with exclusions so Amazon does not keep getting paid to harvest demand seeded by creators.

That’s a $70,000 decision, and it is hiding behind a campaign that reports a 3.57 ROAS.

How I would prompt the AI SQL tool

I would start rough, then tighten.

The first prompt might be: “Create an AMC SQL query that groups users by ordered exposure path across Sponsored Products, Sponsored Brands video, and DSP impressions in the 21 days before conversion. Include campaign name, first exposure timestamp, last exposure timestamp, impression count, click count, and purchase count. Filter to campaigns active between June 15 and July 20, 2026.”

The generated SQL will need inspection. I would check whether it uses the correct advertiser instance, whether Sponsored Ads and DSP tables are joined at the right identifier level, whether conversions are deduped, and whether the ordering logic handles multiple impressions from the same campaign. AI is good at scaffolding a query. It is less good at knowing the exact business definition your CFO uses for “new customer.”

Then I would add the Shopify cohort layer outside AMC or through the clean-room input tables available to the account: “Join matched cohort labels for Shopify customers: new_to_brand_direct, prior_shopify_customer, high_margin_first_sku, subscription_started_90d, repeat_purchase_90d, refunded_90d. Group path performance by these cohort labels.”

If privacy thresholds suppress small rows, widen the buckets. Use campaign group instead of campaign. Use SKU family instead of SKU. Use weekly exposure windows instead of daily ones. I would rather have a slightly blunt answer I can act on than a beautiful row-level idea that never clears aggregation limits.

The metric that changes the budget conversation

ROAS is the wrong hero metric here. It still belongs in the room, but it should sit below cohort-adjusted payback.

Before Prime Day, I like a simple score: ad-attributed gross profit from first order, plus 90-day repeat gross profit, minus media spend. You can call it cohort contribution if the finance team wants cleaner language. The name matters less than the discipline.

Use real numbers. If a campaign spends $25,000, drives $82,000 in attributed revenue, carries a 42% gross margin, and the matched cohort adds $18,000 in 90-day repeat gross profit, the campaign produces about $27,440 in first-order gross profit plus $18,000 later. After spend, it clears $20,440.

Another campaign can beat it on ROAS and still lose. A $25,000 campaign with $100,000 in attributed revenue at 28% margin and $2,000 in repeat gross profit clears only $5,000 after spend. That gap does not show up when the dashboard worships revenue.

This is the conversation founders need before the event, not during the 11 p.m. Slack thread when CPCs are already up 38% and everyone is arguing from screenshots.

Watch the attribution traps

The first trap is branded demand. If a shopper searched your brand name on Amazon after getting three Klaviyo emails and seeing a TikTok review, Amazon Ads may still sit close enough to the conversion to claim credit. AMC can help show the path, but the operator has to bring the outside context.

The second trap is retargeting saturation. DSP impressions can look efficient because the audience is warm. That does not mean the last 12 impressions did anything. I like cutting frequency bands before cutting whole campaigns: 1 to 3 impressions, 4 to 7, 8 to 12, and 13 plus. If the 13-plus bucket has weak repeat behavior and low incremental lift, cap it before Prime Day traffic spikes.

The third trap is discount-trained buyers. Shopify makes this visible fast. Tag customers whose first purchase used a Prime Day-equivalent discount, then compare their 90-day reorder rate with customers who entered through evergreen offers. In categories like supplements, pet products, skincare, coffee, and cleaning supplies, the reorder curve is the business. A cheap first sale that never repeats is inventory movement, not acquisition.

The fourth trap is channel cannibalization. I have seen Amazon retargeting soak up credit from email, affiliate, and creator programs because the ad exposure happens late and the conversion happens on Amazon. That does not make Amazon the villain. It means the budget model is lazy.

What to cut before Prime Day

I start with campaigns that have three strikes: high attributed sales, low 90-day repeat revenue, and heavy overlap with existing Shopify customers. Those are the cleanest cuts because they let you reduce spend without starving new demand.

Next I trim frequency-heavy retargeting segments. If a user needed 17 DSP impressions to buy a discounted bundle, I do not want to pay that tax during the most expensive retail week of the summer.

Then I rebuild conquesting. Competitor-term campaigns deserve budget when they introduce buyers who behave like customers later. They do not deserve automatic budget because the Amazon Ads table says they converted. Segment by first exposure, prior Shopify activity, and SKU margin. Keep the pockets that create valuable first purchases. Dump the rest into a smaller test cell.

Finally, I protect the boring winners. Non-brand Sponsored Products with healthy margin and repeat revenue may look less exciting than video or DSP, but boring acquisition is still acquisition. If the cohort holds up through 60 or 90 days, it gets funded.

The operating cadence

Do this work at least three weeks before Prime Day. For a July event, I want the first AMC and Shopify cohort readout by mid-June, budget changes locked one week before the event, and a live monitor that checks spend, CPC, conversion rate, and cohort proxy signals daily during the sale.

You will not know true 90-day repeat revenue during Prime Day. Use proxies. Subscription starts, second-item add-ons, full-price SKU mix, email opt-ins, refund requests, and prior cohort analogs are enough to make better calls than ROAS alone. After the event, rerun the analysis at 30, 60, and 90 days. The August readout catches obvious mistakes. The October readout tells you which campaigns deserved the money.

The AI SQL Generator makes this cadence less painful because the team can ask more questions without turning every request into a ticket. That is its best use. It lowers the cost of curiosity.

But curiosity still needs a business spine. Ask AMC which paths drove sales, then force those paths to answer to Shopify cohorts. If a campaign cannot produce customers who come back, buy with margin, or expand into owned channels, cut it before Prime Day makes the mistake expensive.