DATA STORIES | DATA WAREHOUSE | KNIME ANALYTICS PLATFORM

Data Warehouse (but not really) Project To Increase Close Value Sales

How I helped a computer hardware company construct a sales training model that enhanced the performance of underachieving sales agents

Photo by Amoz on Unsplash.

A data warehouse is an advanced data management system that consolidates and organizes data from multiple disparate sources into a single, cohesive database. It is optimized for query and analysis rather than transaction processing, facilitating the extraction of valuable insights from vast amounts of data.

Key Features of a Data Warehouse

1. Centralized Data Storage (The main section we didn’t create)

• Integrated Data: Aggregates data from various sources such as transactional databases, CRM systems, ERP systems, and external data feeds.
• Consistency: Ensures data consistency and accuracy by applying data cleaning and transformation processes.

2. Optimized for Analysis

• Query Performance: Designed to handle complex queries and large-scale data analysis efficiently.
• Historical Data: Stores historical data, enabling trend analysis, forecasting, and time-series analysis.

3. Structured Data

• Schema Design: Uses schemas like star schema or snowflake schema to organize data in a structured format.
• Data Modeling: Employs techniques such as dimensional modeling to facilitate easy access and analysis.

4. ETL Processes

• Extraction: Pulls data from various source systems.
• Transformation: Cleanses, formats, and transforms data to ensure consistency and quality.
• Loading: Loads the transformed data into the data warehouse.

5. Business Intelligence and Reporting

• Support for BI Tools: Integrates with business intelligence tools like Tableau, Power BI, and others to provide rich visualization and reporting capabilities.
• Ad Hoc Queries: Allows users to perform ad hoc queries and generate reports to answer specific business questions.

Common Applications

1. Business Intelligence

• Provides a foundation for BI activities, enabling organizations to generate insights, create dashboards, and produce reports.
• Facilitates performance measurement and monitoring through key performance indicators (KPIs).

2. Data Analytics

• Supports advanced analytics, including statistical analysis, data mining, and predictive modeling.
• Enables data-driven decision-making by providing a comprehensive view of the business.

3. Data Consolidation

• Acts as a single source of truth by consolidating data from multiple systems.
• Enhances data quality and consistency across the organization.

4. Historical Analysis

• Maintains historical data, allowing for trend analysis and historical reporting.
• Supports compliance and auditing by preserving historical records.

Advantages of a Data Warehouse

1. Improved Decision-Making

Empowers stakeholders with timely and accurate information, leading to better business decisions.

2. Efficiency

Streamlines data access and analysis processes, reducing the time and effort required to gather and analyze data.

3. Scalability

Can handle large volumes of data and scale to accommodate growing data needs.

4. Data Consistency and Quality

Ensures data consistency and quality through rigorous ETL processes.

Overall, a data warehouse is a crucial component of modern data architecture, providing a centralized platform for data integration, storage, and analysis. It enables organizations to leverage their data assets effectively, driving insights and supporting strategic decision-making.

Build a Data Warehouse & Sales Analysis Pipeline

Building a data warehouse pipeline in KNIME involves several steps, from setting up the data sources to creating the ETL processes and designing the data warehouse schema. Once the data has been loaded and transformed, it becomes valuable for extracting insights by means of an interactive dashboard, using BI tools such as Tableau.

Recently, I took on a project that required me build a data warehouse using the dataset of a computer hardware company.

Business problem: The computer hardware company wanted to increase the sales of low-performing, in-house sales agents by updating its training methods. The only way to do this was to gather regional sales data to find out who the top sales agents are, the management behind these performers, and which regional offices they occupy.

Goal: Helping the computer hardware company construct a sales training model that enhanced the performance of underachieving sales agents.

Project Overview

To achieve our goal, the project involved two key components:

1. Warehouse Optimization with KNIME
2. Sales Performance Analytics with Tableau

In the following sections, we will walk through the process of building the warehouse and analyzing sales performance.

1. Identify the appropriate metric: the Close Value

The first step was trying to discover the most important data point within this department. After a careful analysis of the data, we decided the best measure should be based on the “Close Value” metric.

In the context of sales, “Close Value” refers to the final value of a sale when a deal is successfully closed. This can include the total revenue generated from the sale, encompassing all products or services sold, discounts applied, and additional fees or charges. The close value is critical for measuring sales performance, revenue forecasting, and understanding the effectiveness of sales strategies.

Key Aspects of Close Value

• Total Revenue: The gross amount of money received from the sale of products or services before any deductions.
• Net Revenue: The amount of money received after deducting returns, allowances, and discounts.
• Sales Price: The final agreed-upon price that the customer pays, which might include discounts or negotiated terms.
• Quantity Sold: The number of units or volume of services included in the closed deal.
• Add-ons and Upsells: Additional products or services sold along with the primary offering, which increase the total close value.
• Recurring Revenue: In the case of subscription-based services, the close value might include the initial payment as well as the expected recurring revenue over a specified period.

Importance of Close Value

• Performance Measurement: Sales teams and individuals are often evaluated based on the total close value they achieve within a certain period.
• Revenue Forecasting: Accurate close values help businesses predict future revenues and plan accordingly.
• Sales Strategy Evaluation: Analyzing close values can provide insights into the effectiveness of pricing strategies, discount policies, and sales techniques.
• Commission Calculations: Sales commissions are frequently based on the close value, incentivizing sales representatives to maximize the value of each deal.

2. Build and optimize a Data Warehouse to enhance sales performance across regional stores

We decided to build our data warehouse using KNIME Analytics Platform and the creation of a detailed sales performance dashboard with Tableau.

KNIME (Konstanz Information Miner) is a free an open-source platform used for data analytics, reporting, and integration. It is widely utilized by data scientists and analysts for various tasks related to data processing and analysis, which also includes automation.

KNIME Logo.

We decided to use the KNIME Analytics Platform for automation as well as analysis.

Warehouse Optimization with KNIME

Objective: To optimize warehouse operations by analyzing inventory levels, order processing times, and storage utilization, ultimately reducing costs and improving efficiency.

KNIME Workflow

The KNIME workflow was designed to integrate various data sources, preprocess the data, and provide actionable insights. Here’s a breakdown of the key steps involved:

• Data Integration: We imported and merged data from multiple sources, including inventory management systems, order logs, and warehouse sensors. This ensured we had a comprehensive and accurate dataset for analysis.
• Data Preprocessing: The data was cleaned and transformed to facilitate analysis. This included handling missing values, normalizing data, and creating relevant features for analysis.
• Advanced Analytics: We applied machine learning algorithms to predict demand patterns, optimize stock levels, and identify bottlenecks in order processing.
• Visualization and Reporting: Interactive reports and dashboards were created within KNIME to visualize key metrics and trends. These visualizations provided warehouse managers with clear, actionable insights.

Overview of the KNIME workflow.

KNIME nodes we used

Here are some of the key KNIME nodes that we used in the project. You can find them (along with their documentation) on the KNIME Community Hub.

Error Detection

There’s an error message that shows up whenever a node is configured incorrectly. This makes things very simple to deal with because there’s no coding or any extra data to parse through. You simply look into the node that shows the error message.

Results of Data Warehouse pipeline

We were able to build the workflow to create an automated process that will automatically update once new data enters the dataset. We put the data through the workflow process so it will automatically clean and aggregate the data. It will also extract the data and we will then load that data into a BI platform such as Tableau.

3. Maintain documentation & training materials

To ensure proper and successful usage of the data warehouse pipeline, knowledge about it should be spread across the team and properly documented.

Training Materials (If necessary)

If it’s essential for departments to learn how to use the workflow, we can set aside time for training them on how to use the KNIME platform and how to use the workflow to add data or to observe that particular flow for back testing, building prediction models, etc.

We can use various methods such as Zoom training workshops, on-site training workshops, video tutorials (YouTube private, Vimeo, etc.).

Maintain Documentation

1. Regular updates

• Keep documentations and training materials up-to-date with any changes to the workflow.
• Review and update at regular intervals or whenever significant changes occur.

2. Feedback Loop

• Collect feedback from users on the documentation and training materials
• Use the feedback towards improvements and address any gaps.

3. Version Control

• Use version control systems (e.g. Git) to manage changes to documentation and training materials.
• Track changes and maintain a history of updates.

4. Create Sales Performance dashboard

To create a sale performance dashboard, we exported the results of the KNIME workflow to Tableau. The dashboard was developed to provide an interactive and user-friendly interface for sales analysis.

Key Metrics

The dashboard focuses on close value as the primary metric, highlighting total revenue from closed deals, regional comparisons, top-performing stores, and sales trends over time.

Dynamic Filters

Users can filter data by Top 5 Sales Agents, Bottom 5 Sales Agents, Top Performing Sales Managers by Region, Top Performing Products and the Accounts associated with the sales.

Tableau Dashboard.

Dashboard Results

• Enhanced Visibility. The dashboard provided enhanced visibility into sales performance across regions, leading to a more comprehensive look at which regions they need to focus their attention to for sales agent development.
• Identified High Performers. We identified high-performing Agents that the company can model their sales training modules after. I suggested that they shadow these top agents and observe their techniques. This observation will help them frame their training and protocols around how their lower performing agents should approach the sales process and improve their results.
• Improved Efficiency. Observing the top performing agents will also help the company develop training videos that will display how these agents build on the sale process to close their deals. The training videos will eliminate any guess work or any type of assumptions on the sales process. It will give the company a concrete look at what the agents are doing on the phone and in the field.

Conclusion

The integration of KNIME and Tableau in our data warehouse project has significantly enhanced our ability to manage large amounts of new data that will come in and analyze sales performance through big projects and ad-hoc projects.

By leveraging advanced data analytics, we have improved sales training, and sales process and gained valuable insights into our sales activities. This project not only showcases the power of data-driven decision-making but also underscores our commitment to continuous improvement and operational excellence.