# Shopify Customer Spending Analyzer

> Segment your Shopify customers by lifetime value, spot at-risk buyers before they churn, and export everything to a spreadsheet for your next campaign.

Source: https://cotera.co/solutions/ops/shopify-customer-spending-analyzer

---

- **Team:** Operations
- **Tools:** Shopify, Google Sheets
- **Difficulty:** easy
- **Setup time:** 5 min
- **Time saved:** 3-4 hrs/analysis

## How it works
1. **Pull Order History** — Retrieve complete purchase data for each customer
2. **Calculate Metrics** — Compute LTV, AOV, order frequency, and recency
3. **Segment Customers** — Assign VIP, Regular, At-Risk, or New tiers
4. **Export to Sheets** — Build a campaign-ready spreadsheet with all data

## Example requests
- Analyze spending for these 20 customer emails and segment by lifetime value
- Find my at-risk customers — anyone who spent $200+ but hasn't ordered in 60 days
- Build a VIP list from my top customers and export to Google Sheets

## Prompt

```markdown
## Task

Use @Shopify/Get Customer Orders by Email to pull order history for a list of customers, analyze their spending patterns, and use @Google Sheets/Add Rows to build a segmented customer spreadsheet. Optionally use @google_search to research best practices for the segments you find.

**Example:** Analyze spending for our top 20 customers, segment them into VIP / Regular / At-Risk tiers, and export the results to a Google Sheet.

## Input

The user will provide:
1. A list of customer email addresses to analyze
2. The Google Sheet ID to export results to
3. Optional: custom segment thresholds (e.g., VIP = $500+, Regular = $100-499, At-Risk = no orders in 90+ days)

**Example:** "Analyze these 15 customer emails and segment them. VIP is anyone who spent over $1,000 total. Flag anyone who hasn't ordered in 60 days as at-risk."

## Context

### Segmentation Criteria

**VIP Customers (default: $500+ total spend):**
- High total lifetime spend
- Multiple orders (3+ orders)
- Recent activity (ordered within last 60 days)

**Regular Customers (default: $100-499 total spend):**
- Moderate spending
- 1-2 orders
- Some recent activity

**At-Risk Customers:**
- No orders in 90+ days regardless of spend
- Previously active but gone quiet
- Single purchase with no repeat

**New Customers:**
- First order within last 30 days
- Still evaluating — potential to move up

### Metrics to Calculate Per Customer

For each customer, compute:
- **Total lifetime spend** (sum of all order totals)
- **Order count** (number of completed orders)
- **Average order value** (total spend / order count)
- **First order date** and **last order date**
- **Days since last order**
- **Top products purchased** (most frequent line items)
- **Segment assignment** based on thresholds

### Spreadsheet Structure

Export to Google Sheets with columns:
| Email | Name | Segment | Total Spent | Order Count | Avg Order Value | First Order | Last Order | Days Since Last Order | Top Products | Currency |

### Analysis Summary

After processing all customers, provide:
- Segment distribution (how many in each tier)
- Total revenue across all analyzed customers
- Average metrics per segment
- Recommendations for each segment

## Output

**Google Sheet:** One row per customer with all computed metrics and segment assignment.

**Summary Report:**

## Customer Spending Analysis

**Customers Analyzed:** [Count]
**Total Revenue:** [Sum]

### Segment Breakdown
| Segment | Count | % of Customers | Total Revenue | Avg Order Value |
|---------|-------|---------------|---------------|-----------------|
| VIP | X | Y% | $Z | $W |
| Regular | X | Y% | $Z | $W |
| At-Risk | X | Y% | $Z | $W |
| New | X | Y% | $Z | $W |

### Key Findings
- [Insight about VIP concentration — e.g., "Top 3 VIPs account for 45% of revenue"]
- [Insight about at-risk customers — e.g., "5 previously active customers haven't ordered in 90+ days"]
- [Recommendation — e.g., "Consider a win-back campaign for at-risk segment"]

### Recommended Actions
1. **VIP segment:** [Specific action, e.g., "Send exclusive early access to new collection"]
2. **At-Risk segment:** [Specific action, e.g., "Trigger a 15% discount win-back email"]
3. **New segment:** [Specific action, e.g., "Send product education series to drive second purchase"]
```

## Related reading
- [AI Customer Support Shopify](https://cotera.co/articles/ai-customer-support-shopify.md)
- [Automate Customer Success Workflows](https://cotera.co/articles/automate-customer-success-workflows.md)
- [Calendly Workflow Automation](https://cotera.co/articles/calendly-workflow-automation.md)