Order and Customer Insights - User Guide

Overview

The Order and Customer Insights spreadsheet is a powerful customer analysis tool designed to help you track customer purchasing behavior, segment customers by value, and identify both at-risk customers and high-value opportunities. This guide will walk you through how to effectively use this spreadsheet to gain valuable insights about your customer base.

Table of Contents

Getting Started
Understanding the Sheets
- Dashboard
- Order Data
- Customer Analysis
Key Features and Insights
- Customer Segmentation
- At-Risk Customers
- High-Value Opportunities
Customizing Your Analysis
Data Synchronization
Troubleshooting

Getting Started

When you first open your Order and Customer Insights spreadsheet, you'll see three sheets:
- Dashboard
- Order Data
- Customer Analysis

The spreadsheet comes pre-configured with default settings, but you can adjust these to better suit your business needs:

- High Value Customer Threshold: Default is $500 - customers with total purchases above this amount are classified as high-value
- Small Value Customer Threshold: Default is $200 - customers with purchases below this are small-value, between this and high-value are medium-value

Understanding the Sheets

Dashboard

The Dashboard provides a visual overview of your customer data with key metrics and charts:

- Customer Segmentation (Count): A pie chart showing the distribution of customers across value segments (High, Medium, Small)
- Customer Segmentation (Value): A pie chart showing the distribution of total revenue across customer value segments

This sheet gives you an at-a-glance view of your customer base composition and where your revenue is coming from.

Order Data

The Order Data sheet contains all your transaction information. This sheet is automatically populated through scheduled data synchronization and includes:

- Order dates
- Customer information
- Order amounts
- Product details

The data in this sheet is refreshed daily at 1:00 AM, ensuring you always have the most up-to-date information for your analysis.

Note: This is a report sheet, so you should not manually edit the data in this sheet as it will be overwritten during the next scheduled update.

Customer Analysis

The Customer Analysis sheet provides detailed insights about each customer based on their purchasing behavior:

Customer Metrics (Columns A-F):
- Customer Email: Unique identifier for each customer
- Customer Value: Total spending by each customer
- Customer Value Segments: Classification as High, Medium, or Small value customer
- Last Order At: Date of the customer's most recent purchase
- Customers at Risk: Identifies customers who haven't ordered in 90+ days
- High-Value Customer Opportunities: Identifies customers who order frequently but spend below average

Summary Statistics (Columns H-J):
- Summary of customer segments
- Count of customers in each segment
- Total value of customers in each segment

The sheet uses color coding to make it easy to identify different customer segments and statuses:
- High Value customers: Light blue
- Medium Value customers: Light green
- Small Value customers: Light amber/orange
- At-Risk customers: Light red
- Not At-Risk customers: Light green
- High-Value Opportunities: Light green

Key Features and Insights

Customer Segmentation

The spreadsheet automatically segments your customers into three categories:

High Value: Customers with total purchases greater than $500 (default threshold)
Medium Value: Customers with total purchases between $200-$500
Small Value: Customers with total purchases less than $200

This segmentation helps you understand your customer base and tailor your marketing and retention strategies accordingly.

At-Risk Customers

The spreadsheet identifies customers who haven't made a purchase in 90 or more days and marks them as "At Risk" in column E. These customers may need targeted re-engagement campaigns to prevent churn.

To take action:
Filter column E for "Yes"
Export this list for follow-up campaigns
Consider special offers or personalized outreach to re-engage these customers

High-Value Opportunities

Column F identifies customers who order frequently but spend below average per order. These customers represent growth opportunities as they already show loyalty through frequent purchases.

Strategies for these customers:
Filter column F for "Yes"
Analyze their purchasing patterns
Consider upselling campaigns, bundle offers, or premium product recommendations

Customizing Your Analysis

While the spreadsheet comes with default thresholds, you may want to adjust these based on your specific business model:

- Adjusting Value Thresholds: The default high-value threshold is $500 and small-value is $200. Your business may have different appropriate thresholds.
- At-Risk Time Period: The default at-risk period is 90 days without a purchase. For businesses with different purchase cycles, you might want to adjust this.

To customize these settings, contact your administrator who can update the template settings for your spreadsheet.

Data Synchronization

The Order Data sheet syncs automatically according to the following schedule:

- Frequency: Daily
- Time: 1:00 AM
- Data Source: Your order database

This ensures your analysis is always based on the most current data. The sync happens automatically in the background, and no action is required on your part.

If you need an immediate update outside the scheduled time:
Contact your system administrator
Request a manual sync of your spreadsheet file
The administrator can trigger a sync using the API endpoint

Troubleshooting

Here are solutions to common issues you might encounter:

Issue: Data in Order Data sheet seems outdated
Solution: Check the last sync date (usually shown in cell A1). If it's more than a day old, contact your administrator to check the sync status.

Issue: Formulas in Customer Analysis sheet show errors
Solution: Verify that the Order Data sheet contains data. If formulas still show errors after confirming data exists, contact support.

Issue: Customer segments don't match expectations
Solution: Check the thresholds being used for segmentation. The default high-value threshold is $500 and small-value is $200.

Issue: Charts not showing or displaying incorrect data
Solution: Ensure data in the Customer Analysis sheet is properly populated. If charts still don't display correctly, try refreshing the page.


For additional help or feature requests, please contact your system administrator or the support team.

Updated on: 28/03/2025