Icon

Analyze Sales Data Solution

Step 1:Drag-and-drop these files from the /data folder:- CRM1.xlsx- CRM2.csv- Transactions.csvThe appropriate reader nodes will be created. Step 2:Concatenate the two CRM files together into a single table.Remove whitespace from the email addressese using the replace() function inthe String Manipulator node.Change the type of the Newsletter field to string using a Table Manipulator node.You can also reorder the columns if you like by clicking the dots next to each fieldin the configuration dialog, and dragging them up and down. Step 3:Use the Cell Replacer to change the Newsletter field from 0/1 toYes/No. The lookup table you will need is present in the TableCreator node.Remove all duplicates from the cleaned dataset using theDuplicate Row Filter node. Step 4:Join the cleaned CRM data with the Transactions data from Step 1.Join on Customer ID. Ensure that the resulting table doesn't containany personally identifying information. Use the Rule Engine to translate month names to month numbers.Finally, sort the resulting table on year and month number so that thetransactions are in chronological order. Step 5:In one branch, use a GroupBy node tocalculate, by sales unit, the total price.In a separate branch, use a Pivotingnode to calculate, by year and monthnumber, the total price. Create columnsfor each sales unit. Step 6:For the top branch, create a bar chart of sales by unit.For the bottom branch, create a stacked area chart plotting each of thethree sales units. Use RowID for the X-axis. Step 7 (Optional):From the top port of the pivoting node, attach a Table View node. Putboth the stacked area chart from Step 7 and the Table View into acomponent, and generate the combined view. Write the table to anExcel file using and Excel Reader node. CRM 1CRM 2Combine the customer dataRemoveduplicatesTransactionsAdd Sales Unitto TransactionsRevenue per Month and SalesUnitVisualize Revenue overtimeWrite Results into an Excel TableNewsletter Yes/NoSumsales byUnitNewsletter field to stringremove whitespace in email addressesNewsletter code lookupCreatemonth numberOn year and month numberSales by Unit Excel Reader CSV Reader Concatenate DuplicateRow Filter CSV Reader Joiner Pivoting Stacked Area Chart Excel Writer Cell Replacer GroupBy Table Manipulator String Manipulation Table Creator Rule Engine Sorter Bar Chart Table View Step 1:Drag-and-drop these files from the /data folder:- CRM1.xlsx- CRM2.csv- Transactions.csvThe appropriate reader nodes will be created. Step 2:Concatenate the two CRM files together into a single table.Remove whitespace from the email addressese using the replace() function inthe String Manipulator node.Change the type of the Newsletter field to string using a Table Manipulator node.You can also reorder the columns if you like by clicking the dots next to each fieldin the configuration dialog, and dragging them up and down. Step 3:Use the Cell Replacer to change the Newsletter field from 0/1 toYes/No. The lookup table you will need is present in the TableCreator node.Remove all duplicates from the cleaned dataset using theDuplicate Row Filter node. Step 4:Join the cleaned CRM data with the Transactions data from Step 1.Join on Customer ID. Ensure that the resulting table doesn't containany personally identifying information. Use the Rule Engine to translate month names to month numbers.Finally, sort the resulting table on year and month number so that thetransactions are in chronological order. Step 5:In one branch, use a GroupBy node tocalculate, by sales unit, the total price.In a separate branch, use a Pivotingnode to calculate, by year and monthnumber, the total price. Create columnsfor each sales unit. Step 6:For the top branch, create a bar chart of sales by unit.For the bottom branch, create a stacked area chart plotting each of thethree sales units. Use RowID for the X-axis. Step 7 (Optional):From the top port of the pivoting node, attach a Table View node. Putboth the stacked area chart from Step 7 and the Table View into acomponent, and generate the combined view. Write the table to anExcel file using and Excel Reader node. CRM 1CRM 2Combine the customer dataRemoveduplicatesTransactionsAdd Sales Unitto TransactionsRevenue per Month and SalesUnitVisualize Revenue overtimeWrite Results into an Excel TableNewsletter Yes/NoSumsales byUnitNewsletter field to stringremove whitespace in email addressesNewsletter code lookupCreatemonth numberOn year and month numberSales by Unit Excel Reader CSV Reader Concatenate DuplicateRow Filter CSV Reader Joiner Pivoting Stacked Area Chart Excel Writer Cell Replacer GroupBy Table Manipulator String Manipulation Table Creator Rule Engine Sorter Bar Chart Table View

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: kathrinmelcher
Created at: 2021-11-09
On NodePit since: 2022-11-10
Last update: 2024-04-27
Created with KNIME version: v4.6.3

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!