New Customer Count

• Version 1.0

• Prepared by: @Lorraine Sebata

• Approved by: @Feli Capron

• Reviewed date: 2024-07-25

• Next review date: 2025-07-25

1. Purpose

This SOP establishes a consistent process for calculating new customer acquisition at Easyterms, enabling data-driven decisions to improve customer acquisition strategies. Its key objectives include defining "new customer" criteria, outlining data sources and methods, establishing reporting processes, and ensuring data compliance. Implementing this policy will provide valuable insights into customer acquisition trends to optimize marketing, sales, and retention initiatives.

2. Scope

This SOP applies to all members of the Accounts department. The data and reporting produced through this SOP is generated using the loan platform and exported to Excel. It will be reviewed by the Marketing Manager and shared with the executive leadership team on a monthly basis.

3. Related Policies

• Customer Data Privacy Policy

4. Roles and Responsibilities

• Marketing Manager: Reviews and approves the new customer count, shares reports with executive leadership.

• Marketing Coordinators/Staff: Responsible for executing the steps outlined in this SOP, including data download, combination, formula application, and filtering.

5. Procedure: New Customer Count

5.1 Download Relevant Data

• 5.1.1 Export the aging report from the loan platform for the current month in question.

• 5.1.2 Export the aging report from the loan platform for the previous month.

5.2 Combine Data

• 5.2.1 Open a new Excel workbook.

• 5.2.2 Copy the data from the current month's aging report and paste it into "Sheet1" of the new workbook.

• 5.2.3 Rename "Sheet1" to reflect the current month (e.g., "Jul").

• 5.2.4 Copy the data from the previous month's aging report and paste it into "Sheet2" of the same workbook.

• 5.2.5 Rename "Sheet2" to reflect the previous month (e.g., "Jun").

5.3 Run the Query to Identify New Customers

• 5.3.1 In the worksheet for the current month (e.g., "Jul"), locate the first blank column after your data.

• 5.3.2 In the first row of this blank column, type "New" as the column header.

• 5.3.3 In the second row of the "New" column, insert the following formula, replacing XXX with the worksheet name of the previous month (e.g., Jun): =ISNA(VLOOKUP(C2, XXX!C:C, 1, FALSE)) (Note: This formula assumes your client/customer ID is in column C. Adjust C2 and C:C if your customer ID is in a different column.)

• 5.3.4 Copy the formula down to the end of your data set in the current month's worksheet.

5.4 Filter the Data

• 5.4.1 Highlight the entire "New" column.

• 5.4.2 Go to the "Data" tab in Excel and select "Filter."

• 5.4.3 Click the filter dropdown arrow in the "New" column header.

• 5.4.4 Deselect "FALSE" and ensure only "TRUE" is selected, then click "OK."

5.5 Count the New Customers

• 5.5.1 With the "TRUE" data highlighted, observe the count displayed in the bottom right-hand side of the Excel status bar.

• 5.5.2 Deduct 1 from this displayed count (to account for the "New" column header) to arrive at the final new customer count for the month.

• 5.5.3 Record this final new customer count.

6. Reporting

• 6.1. The calculated new customer count will be reported monthly by the Marketing Coordinators/Staff to the Marketing Manager.

• 6.2. The Marketing Manager will review the reported count and share it with the executive leadership team on a monthly basis.

7. Related Resources

• Loan Platform (for data export) - Loom Video

• Microsoft Excel

  

8. Review and Revision

This SOP will be reviewed annually by the Marketing Department to ensure its continued effectiveness, compliance, and alignment with business objectives.