Proposal Summary
This proposal outlines the implementation of an automated customer categorization system in NetSuite to classify customers into Gold, Silver, and Bronze categories based on revenue and order frequency over the past 12 months. The solution includes configuring saved searches, custom records, scheduled scripts, and workflows to automate classification and provide tracking of customer category.
The estimated effort for this implementation is approximately 32 hours
This proposal is based on discussions, expectations, and understandings derived from meetings and other communications. Any additional requirements or changes identified during development will be treated as change requests.
Requirement
The client requires a system to automatically categorize customers based on revenue and order frequency over the past 12 months. The categorization will be structured as follows:
- Gold Category: Revenue ≥ $50,000 AND Number of Orders > 5
- Silver Category: Revenue between $25,000 and $50,000 AND Number of Orders > 5
- Bronze Category: Revenue < $25,000 OR Number of Orders ≤ 5
The revenue calculation formula will be:
Revenue = Payments (Amount) + Customer Deposits (Amount) – Credit Memos (Remaining / Unapplied Amount).
To ensure accurate categorization, the system must extract and calculate this data automatically. The client also requires flexibility to update threshold values without modifying scripts or workflows.
Additionally, the categorization must be visible on the Customer Record, allowing easy access for sales and management teams.
Deliverables
A customer grading system will be implemented in NetSuite to automatically categorize customers into Gold, Silver, or Bronze tiers based on their revenue and order frequency over the past 12 months. The solution will include saved searches, scheduled scripts, custom records, and dashboards to ensure seamless automation and visibility.
A Customer Category field will be added to the Customer Record to display the assigned category. Supporting fields such as Revenue (Last 12 Months) and Order Frequency (Last 12 Months) will also be created to provide transparency into the categorization process. These fields will be updated once a day based on scheduled automation, ensuring periodic but consistent updates.
A custom record called “Customer Grading Thresholds” will be created to store the revenue and order frequency criteria for customer categorization. This record will include the Silver Threshold, Gold Threshold, and Order Frequency Threshold fields. An entry will be added to the custom record with the current threshold values, and categorization will be based on these values.
Currently, the threshold values are as follows:
- Silver Threshold = 25,000
- Gold Threshold = 50,000
- Order Frequency Threshold = 5
If the threshold values need to be adjusted in the future, the fields in this custom record can be updated. This will enable authorized users to modify the thresholds without the need for script changes. Access to this record will be restricted to specific roles to ensure unauthorized users cannot make modifications.
A scheduled script will be developed to automate customer grading. It will fetch revenue and order frequency data from saved searches and assign the appropriate category based on predefined thresholds. The script will run at scheduled time intervals (once a day), ensuring updates are done without manual intervention. If a customer’s revenue or order count changes, their category will be updated by the end of the day according to the schedule.
The revenue for the last 12 months in the customer record will be calculated using the following formula:
Revenue = Payments (Amount) + Customer Deposits (Amount) – Credit Memos (Remaining / Unapplied Amount).
The order frequency for the customer record will be determined by the number of billed sales orders for that customer over the past 12 months.
Based on these values in the customer record, the category will be assigned as follows:
- Gold Category if Revenue ≥ $50,000 AND Number of Orders > 5
- Silver Category if Revenue is between $25,000 and $50,000 AND Number of Orders > 5
- Bronze Category if Revenue < $25,000 OR Number of Orders ≤ 5
Additionally, a separate workflow will be implemented to restrict unauthorized modifications to the Customer Grading Thresholds custom record.
A Saved Search will be created to retrieve data from the customer record, displaying the revenue for the last 12 months, the number of orders in the last 12 months, and the customer’s category.
Notes
- Inactive customers will be excluded from the categorization process.
- Only approved Sales Orders will be considered for order frequency calculations.
- Customer grade updates will occur once per day, ensuring periodic but consistent categorization.
- The revenue and order frequency in the customer record will be calculated based on data from the last 12 months.
Assumptions
- The revenue calculation will follow the formula:
- Revenue = Payment (Amount) + Customer Deposit (Amount) − Credit Memo (Remaining or Unapplied Amount).
- Only authorized users (Glen, Ollie, Katie, and Tyler) will have permission to modify the Customer Grading Thresholds custom record to prevent unintended modifications.
- Since cash sales are not currently using in NetSuite, we will not be considering Cash sales in the revenue formula.
- Since the base currency is GBP, the revenue will be calculated in GBP, regardless of the currencies in which the transactions are made.
Risks
- The Customer category update will occur as per the scheduled time, not in real-time.
Queries
- We will be calculating the customer’s order frequency based on billed sales orders. Should we also include sales orders in other statuses, such as fulfilled or partially billed?