Client Tracking & Document Management System

BY KAGNA EM

FOR A US-NZ LAW FIRM IN AUCKLAND

About the Project

I was engaged by a US–NZ law firm, to resolve long-standing issues in client information management.

Previous consultants had proposed solutions that were often vague, overly complex, or too narrow to be effective.

My approach was focusing on delivering a solution grounded in operational reality — aligning with client objectives and project scope.

​

​

​

2

20XX

Project Activities

Conference presentation

3

20XX

The project focused on enhancing the client tracking system and restructuring client files and folders into a more organized, accessible format. Project activities include:

​

Data Preparation & Standardization�

The project began by consolidating multiple client tracking files into a single, centralized tracker using data integration and validation tools, including Excel Data Tables and Microsoft Access queries. During this process, several significant data quality issues were identified, such as

• Invalid data entries (e.g. incorrect dates, mismatched data types)
• Missing or inconsistent validation rules, allowing human error
• Client ID conflicts, such as duplicate IDs, missing IDs, and mismatches
• Unstandardized service data, making reporting and categorisation difficult

Key Actions Taken:

• Cleaned and validated all client records to ensure accuracy
• Standardized Client IDs across systems and resolved duplicates
• Recategorized services using a consistent classification aligned with NEL's offerings
• Applied validation rules and formatting for long-term data reliability
• This foundational work ensured the integrity of the new tracking system and enabled reliable, scalable data management going forward.

​

System Development

System Development: A Central Client Tracking Tool

After completing data quality checks and file consolidation, the new Client Tracking system was developed using the same Microsoft Excel platform, enhanced with Data Tables and built-in automation and validation tools. This approach minimized disruption and maintained user familiarity, while delivering significant improvements in functionality, accuracy, and ease of use.

Key Enhancements:

• Systematic data lookup for faster and more accurate access to client information
• Built-in data validation to reduce human error
• Refined formulas for improved calculation accuracy and performance
• Holistic design to manage all client types in a single unified system
• Improved client coverage and user-friendly interface to streamline workflows and reporting

​

The Client Tracking System

While this project is based on real work I completed, the data shown in the this slide has been replaced with mockup data to safeguard privacy and confidentiality

Smart Filtering and Validation Techniques

• Filter Functionality: Filters allow users to show or hide specific data in a table, helping them focus on relevant information and reduce visual clutter. Users can apply filters using combo boxes or by clicking filter buttons next to column headers (see areas 1–5 on the interface).
• Lookup Integration: All filter areas are connected to lookup tables in the Lookup Value sheet, making them easy to manage and update without affecting the core data.

Conference presentation

7

20XX

Automation & Calculation

To enhance efficiency and reduce manual effort, several automated features were built into the system:

• Client Summary Insights: Key headings such as #of Clients by Type (Active, Archived, Pending, Declined and Total) and # of Records by Client Type are auto-generated to provide a quick overview of client distribution.
• New ID Suggestions: The system automatically generates the next available Client ID to prevent duplication and ensure consistency.
• These features support faster decision-making and minimize human error in client management.

​

Conference presentation

8

20XX

Lookup Sheet

• The lookup value list is maintained in a separate sheet and serves as the data source for the Main Client Tracking system. By embedding these values into column validation rules, user input errors—such as typos—are effectively prevented.

Facilitating a Smooth Transition

To ensure a seamless transition from the old to the new system, Microsoft Teams and SharePoint were used as the central communication hub. It served as the main channel for:

• Sharing information and resources
• Tracking updates and changes
• And Recording progress in real time
• This collaborative approach helped keep everyone aligned and informed, making the transition smooth and efficient with minimal effort required from the team.

​

Conference presentation

10

20XX

Client File & Folder Management

Conference presentation

11

20XX

• As part of the project, a standardized document naming convention was developed and agreed upon internally to ensure consistency across all client files and users. This was a critical component, as it directly impacted how client information was recorded, accessed, and managed across the team.

​

• As the Data Management Consultant, I was responsible for producing the naming guide and reinforcing the rules across teams to prevent recurring issues and maintain long-term consistency.

Summary

• This project delivered a practical and scalable client tracking system that significantly improved data accuracy, reduced human error, and enhanced team collaboration—all within a 3-month timeframe.
• A key takeaway from this work is that effective solutions don’t always need to be complex or high-tech—practicality and functionality are what truly drive success.

CONFERENCE PRESENTATION

12

20XX