01
Step 1
Collect the order history with customer identifiers, order dates and refund treatment, derive each customer's first-purchase date, and fix the cohort interval and the observation window length before any cohort is built.
Back to topic
Spark playbook scenarios · Spark: AI Store Assistant
Analyze customer retention using comparable observation windows
Retention looks poor simply because newly acquired customers are measured alongside ones who have had months to reorder. In this case, the immediate task is to analyze customer retention using comparable observation windows. The workflow should produce a repeat-purchase cohort analysis, built from the merchant's own order history and first-purchase dates.
Topic
Customer Analytics and Segmentation
Target keyword
shopify customer retention analytics
Copyable AI prompt
A prompt you can drop directly into Spark
First replace every [bracketed] placeholder with real store scope and constraints, then add any actual records or data you have, and paste it into your preferred AI assistant or Spark agent. Spark will follow this page's workflow to produce structured output and verification steps.
ai-prompt.txt
Act as a Shopify operations specialist.
Help me analyze customer retention using comparable observation windows.
My store context is [describe the business, products and target audience].
My scope is [specify the affected products, pages, customers or reporting period]. Use these inputs: [the order history with customer identifiers, order dates and refund treatment, each customer's first-purchase date, the cohort interval, and the observation window length].
Build cohorts by the period of first purchase and measure repeat purchase only over the window length every cohort has completed, keeping the share of customers who returned separate from the average number of orders per returning customer, and excluding customers whose window has not yet elapsed.
Return a repeat-purchase cohort analysis with membership verified and customers lacking observation time noted for the specified task.
Show the relevant evidence or before-and-after examples and explain how I can verify the result.
If essential data is missing, ask for it and mark unsupported conclusions as unknown.
Do not invent metrics, product claims or customer facts.
For any publication, message, account or store change, use only connected tools within my explicitly authorized scope and report success only after verification; otherwise provide the draft or proposed steps.How to solve
A recommended resolution workflow
Split the work into input, judgment, and deliverable phases. Verify each step's output before moving to the next.
02
Step 2
Build cohorts by the period of first purchase and measure repeat purchase only over the window length every cohort has completed, keeping the share of customers who returned separate from the average number of orders per returning customer, and excluding customers whose window has not yet elapsed.
03
Step 3
Deliver a repeat-purchase cohort analysis with one row per cohort and one column per elapsed window, verify that the customers across all cohorts equal the distinct buyers in the order history, and list every cohort still awaiting enough observation time.
FAQ
Common questions when running this scenario
Cover decision rules, frequent misunderstandings, and required inputs so the team aligns on prerequisites before execution.
Why is the window truncated for recent cohorts?
Because a cohort acquired recently has not had the same chance to reorder as one acquired much earlier; each cohort is measured only over the span it has completed, and the remaining columns are left blank rather than filled with a lower figure that would read as decline.
Why report return rate and orders per returner separately?
The first counts a customer once when a second eligible order appears, the second counts every subsequent order, so a cohort can hold its return rate while orders per returner fall; both are reported because they move independently and point to different action.
How is a cohort's membership checked?
Every customer appears in exactly one cohort, fixed by their first-purchase date and never reassigned when they buy again later, so cohort sizes sum to the distinct buyers in the order history; anyone whose first purchase predates the history is listed separately as having an unobserved start.
Related scenarios
Continue with adjacent or same-topic scenarios
Start with scenarios under the same topic, then expand into cross-topic matches suggested by similarity.
Next step
Want these AI scenarios embedded directly into daily operations?
Ciwi Spark builds Shopify-native AI tools and agents around the workflows merchants actually run: research, ads, translation, and customer support. Stop rewriting the same prompts by hand.