Most agencies log into AMC manually, run a query, download a CSV, and repeat. I built a pipeline that does all of it automatically — and adds AI-generated insights on top.
By Shuqing Ke — UK-based independent AMC analytics consultant
Logging in, writing queries, downloading CSVs, formatting reports — all done manually, all repeatable.
AMC has LTV, NTB, lapsed buyers, time-to-convert — most agencies only scratch the surface.
Without automation, you can't consistently identify at-risk buyers before they churn.
Managing 5+ client AMC instances manually? Each one needs separate logins, separate queries.
One script. Runs automatically. Pulls live data from every connected AMC instance.
grant_type = refresh_token
refresh_token = {your_refresh_token}
client_id = {your_lwa_client_id}
client_secret = {your_lwa_secret}
{
"workflow": {
"sqlQuery": "SELECT COUNT(DISTINCT user_id)
AS buyers, ..."
},
"timeWindowStart": "2025-03-23T00:00:00",
"timeWindowEnd": "2026-03-23T23:59:59",
"timeWindowType": "EXPLICIT"
}
Poll every 15 seconds
PENDING → RUNNING → SUCCEEDED
→ REJECTED (SQL error — check statusReason)
→ FAILED
{
"downloadUrls": [
"https://s3.amazonaws.com/...presigned..."
]
}
→ Download CSV directly
Total buyers, revenue, lifetime value per buyer, and repeat purchase rate — from Amazon's purchase attribution table.
Buckets all buyers into Active / At Risk / Lapsed / Churned based on days since last purchase — as of today.
Days between first ad exposure and purchase — from Amazon's traffic and conversion timing table.
New-to-brand purchases, NTB%, attributed revenue and ad spend per ASIN per month.
SP and DSP spend by month from Amazon's ad spend tables, with total ROAS across both channels.
DSP is the only Amazon ad type that tracks impressions at user level — meaning AMC can tell you exactly who saw your ads. We join DSP exposure data against conversion events to isolate high-intent non-buyers.
Trailing 12 months · Illustrative figures based on typical Amazon brand accounts · Data structure is exactly what you receive for your clients
| Metric | Brand A | Brand B |
|---|---|---|
| Total Buyers | 420,000 | 18,000 |
| Total Sales | £3,200,000 | $210,000 |
| LTV / Buyer | £7.60 | $11.70 |
| Repeat Buyers | 29,400 | 360 |
| Repeat Purchase Rate | 7.0% | 2.0% |
| Same-day converters | 90% | 90% |
| Segment | Buyers | Revenue |
|---|---|---|
| Active 0-30d | 28,000 | £240,000 |
| At Risk 31-60d | 52,000 | £400,000 |
| Lapsed 61-90d | 44,000 | £340,000 |
| Churned 90d+ | 296,000 | £2,220,000 |
Roughly 296,000 buyers haven't purchased in 90+ days. That's over £2.2M in revenue from people who already trust the brand — the most efficient retargeting audience you have.
The At Risk + Lapsed segments represent around 96,000 buyers who purchased in the last 30–90 days. A well-timed DSP retargeting push now costs a fraction of acquiring new customers.
Both brands show ~90% same-day conversion from ad exposure. This means 30-day retargeting windows are wasteful — 7 days is optimal, saving budget on non-converting impressions.
Nearly all buyers are one-time purchasers. A structured retargeting programme could realistically move this to 5–7%, directly impacting LTV without increasing ad spend on new customer acquisition.
SP impressions don't carry user IDs — only DSP does. If you're running DSP, AMC can identify every user who saw your display ads and didn't purchase. That audience is already warm. Retargeting them costs a fraction of prospecting cold traffic.
I set up the full pipeline for your AMC instances — automated queries, lapsed buyer segments, AI-generated insights, and retargeting audience builds. Ready in days, not months.
Book a Free Discovery Call →Or email shuqingke1404@gmail.com
I'm Shuqing Ke, a UK-based independent data consultant specialising in Amazon Marketing Cloud (AMC) analytics. I build and operate automated reporting pipelines for marketing agencies and DTC brands selling on Amazon — turning raw AMC data into weekly decision-ready dashboards with AI-grounded insights.
Current clients include marketing agencies serving DTC brands across Japan, the UK, and EU. Engagements range from one-off template builds (LTV, NTB, retention, churn analysis) to ongoing monthly retainers covering pipeline maintenance, new-seller onboarding, and feature additions.
Get in touch: Book a 15-min call · shuqingke1404@gmail.com · LinkedIn
Built and operated by Shuqing Ke · UK-based independent AMC analytics consultant · shuqingke1404@gmail.com
Data shown is from live client accounts, anonymised. Results will vary by brand, category, and ad investment. · Built with Amazon Ads API + Claude AI