Skip to main content

The process of collecting and interpreting raw sales data from the intermediary is only the beginning of data cleaning. Once intermediary outputs are mapped into the sales reporting system and advisor details and codes have undergone initial scrutiny, a thorough data cleaning process is a crucial step for accurate sales reporting, a process that demands meticulous attention to detail and a dedicated team effort. Data cleaning ensures that the intermediary data is primed for analysis and precise sales insights.

Cross Referencing

During data cleaning, names, abbreviations and partial identifiers within the data, whether they be full names, partial names or team names, are meticulously examined and researched. Comparing and contrasting this information against reference databases to ensure accuracy, the data cleaning efforts are geared toward identifying and rectifying inconsistencies, an essential step before advancing to more complex data cleaning tasks.

Direct Outreach

Any gaps left by automated systems involved in compiling the data are addressed through direct outreach, such as emails or phone calls, an integral part of effective data cleaning. This manual intervention fills in any blanks, ensuring that no data point is overlooked. As part of an ongoing data cleaning process, the information gleaned enriches and updates the reference databases, streamlining future data cleaning cycles. Updating data for representatives who have moved between firms or locations, whether they have made recent sales or not, is a valuable side-effect of a comprehensive data cleaning effort.

The Advisor CRD

The unique advisor identifier, the CRD number, follows the representative throughout their career. Data cleaning that continually tracks advisors by their CRD numbers is pivotal, serving as the linchpin in tracking advisor movements accurately. Regular updates to the reference database based on FINRA and other regulatory databases in addition to ongoing data cleaning insights keeps advisor employment and location information fresh and valuable for sales reporting.

Dirty Data

Clean Data


Data cleaning facilitates the accurate summarization of data, transitioning it from raw, unstructured information to complete and correct information across all data points in a format that can be aggregated and understood in any desired way. This absolute completeness and accuracy is the essential mission of data cleaning, enabling stakeholders to view and understand data at a glance, enhancing the clarity and utility of the information provided.


Data cleaning also allows for the aggregation of transactions by advisor rather than by intermediary or platform, a nuanced yet significant distinction. Effective data cleaning enables the alignment of data with the reporting the client needs for advisor-level insights.

The Final Product

On completion of the data cleaning process, the data is in a state that supports comprehensive and accurate sales reporting capable of revealing sales performance, trends, rankings and changing investor attitudes. Data cleaning is an indispensable part of ensuring data integrity and relevance. Without data cleaning, attempting to generate meaningful sales insights from reporting and visualizations is not just challenging but likely to lead to inaccurate conclusions.

Data cleaning is an indispensable preparatory step that must be undertaken with diligence and precision for sales reporting that can inform and inspire sales strategy. It is the backbone of a fund’s ability to leverage its sales performance to understand the current sales environment. That understanding must be built on a foundation of complete accurate, reliable information. Without comprehensive data cleaning, any insights drawn from any sales reporting would be questionable, underscoring the indispensable role of the data cleaning process for data analysis and reporting.