Icon

Customer Transactions Analysis - Exercise

This workflow accesses customer and transactions data, transforms the data and generates an interactive view with multiple visualizations.

This workflow accesses customer and transactions data, transforms the data and generates an interactive view with multiple visualizations.

L1 Project - ExcercisesCustomer Transactions Analysis Step 1.1. Access the Data from File(s)Read CustomerInfoSystem1.xlsx with the Excel Reader nodeRead CustomerInfoSystem2.table with the Table Reader nodeRead Stores.csv with the CSV Reader node Step 1.2. Access the Data from a DatabaseConnect to the database with the SQLite Connector nodeWith two DB Table Selector nodes, select the tablesTransactionsProductNrAndPriceRead the tables with two DB Reader nodes Step 2.1. Clean the DataClean the Customer Information System 1 dataExclude the first row and first column with the Table Cropper nodeRemove rows with duplicate CustomerID with the Duplicate RowFilter nodeWhen the age is missing, insert the mean age of the dataset withthe Missing Value node Step 3.1. Transform the DataCreate a new column Age Group with values Adolescent, Adult, Older Adult according to the age value with the Rule Engine node<18 Adolescents, 18-65 Adults, >65 Older AdultsReplace the character "_" with a space "" from the column country Country with the String Manipulation nodeSplit the column CustomerID on the character "_" with the Cell Splitter nodeRename the columns created by the split with the Column Renamer nodeMerge the columns Email and CorporateEmail with the Column Merger node. Keep the Email when both are available.Convert the column Newsletter with the Number to String node Step 3.2.Tidy up the Workflow with a MetanodeRepeat the steps of Data Cleaning and Data Transformation for the Customer Information System 2 dataTidy up the workflow in a Metanode Step 5.1. Aggregate the Data ISum the Price for each CustomerID with the Row Aggregator node Step 6.1. Export the DataWrite the table containing the total price per customer with theExcel Writer node Step 6.2. Visualize the Data IConvert the BasketSize with the Number to String nodeShow in a BarChart the amount of orders for each BasketSize, forboth Online and Onsite Stores Step 6.4. Components and ReportingCreate a component containing the visualization nodesUse the Layout Editor to rearrange the interactive view of thecomponentSelect Enable Reporting in the Layout EditorAdd a Report Template Creator node before the component andexport its view as a PDF report with the Report PDF Writer node Step 5.2. Aggregate the Data IICalculate the Basket Size of each order (number of products inthe order) with the GroupBy node. It can be obtained by countingany column. Retain information about the StoreTypeShow the number of orders for each Basket Size in the OnlineStore and Onsite Store respectively with the Pivot node Step 5.3. Aggregate the Data IIIIn a new branch, repeat step 5.1 with a GroupBy node. This time,for each customer, keepCustomerIDCustomerGroupAgeGroupSum of PriceMean PriceUnique Count of ProductNr Step 6.3. Visualize the Data IIAssign a color to each row according to the CustomerGroupcolumn with the Color Manager nodeIn a Scatter Plot show the Sum(Price) and UniqueCount(ProductNr)Show the count of occurrences for CustomerGroup in a Bar ChartShow a Parallel Coordinates Plot withAgeGroupCustomerGroupSum(Price)Unique Count(ProductNr)Create a title with the Text View nodeNote: apply the color when possible. In the Bar Chart, in the GroupBar option select Frequency Dimension. Learning objectives: Access, clean, transform, merge and aggregate data to report and visualize the results.Workflow description: This workflow accesses customer and transactions data, transforms the data and generates an interactive view with multiple visualizations.You’ll find the use case information in the blue annotations and the instructions to the exercises in the yellow annotations. Use CasePauline from Sales would like to write a report and visualize the insights on the customer transaction data her team collects every month. This includes the total priceper customer and the basket size per store type. Since Pauline receives the data from multiple sources each month, which needs blending, cleaning, andtransformation before it can be put into a report, she has reached out to your data team for help to automate the process and thereby save time.  Your continuous project will be to help Pauline build a workflow to report the monthly insights, by completing each step of the process she repeats every month below. Data AccessPauline receives the data from multiple sources, including files,and data base tables. Your first task is to start building theworkflow by accessing the different data sources. Data CleaningNow that the data access is established, Pauline asks your helpin cleaning the data, since there seems to be duplicates, missingvalues, and some columns that are not needed. Data TransformationNext, you need to merge the cleaned data. To do so, Pauline outlines how she normally transforms some of the data in the rows andreshapes some columns to create the desired form. Data Export, Visualization and ReportingPauline is happy that the data is ready to be exported. Her teamneeds the results from 5.1 as an Excel file, 5.1 as a Bar Chart,and 5.3. in an interactive dashboard that can be exported as a pdf. Data MergingWith the data cleaned and in the correct shape, you can now helpPauline to merge the different data sources, by stacking tablesand matching or joining others to add the necessary columns. Data AggregationYou have now reached the point where the Pauline's data can beaggregated to get the necessary insight concerning the price andbasket sizes for customers and stores. Step 4.1. Merge the DataConcatenate the two customer tables with the Concatenate nodeAppend the StoreType (in Stores.csv) and Price (from theProductNrAndPrice DB table) to each transaction (stored in theTransactions DB table) with two Value Lookup nodesJoin the Customer and Transaction tables on the CustomerIDcolumn with the Joiner node.Note: The first output port of the Joiner shows the rows with amatch in the two tables. Use only these rows in the next steps. Node 1Node 2Node 3Node 7Node 8Node 9Node 10Node 11Node 12Node 13Node 14Node 15Node 16Node 17Connect to SalesDatabaseTransactionsProdcutNrAndPriceNode 296Node 297Calculate BasketSizefor each orderNode 379Node 380Node 381Calculate BasketSizefor each orderNode 383Node 384Node 385Node 386Node 387Node 388Node 389Node 390Node 392 Excel Reader CSV Reader Table Reader Table Cropper DuplicateRow Filter Missing Value Rule Engine String Manipulation Cell Splitter Column Renamer Column Merger Number to String Concatenate Value Lookup SQLite Connector DB Table Selector DB Table Selector DB Reader DB Reader Value Lookup Joiner GroupBy Data Cleaning andTransformation Row Aggregator Pivot GroupBy Excel Writer Number to String Bar Chart Color Manager Scatter Plot Bar Chart ParallelCoordinates Plot Report PDF Writer Visualizations Report TemplateCreator L1 Project - ExcercisesCustomer Transactions Analysis Step 1.1. Access the Data from File(s)Read CustomerInfoSystem1.xlsx with the Excel Reader nodeRead CustomerInfoSystem2.table with the Table Reader nodeRead Stores.csv with the CSV Reader node Step 1.2. Access the Data from a DatabaseConnect to the database with the SQLite Connector nodeWith two DB Table Selector nodes, select the tablesTransactionsProductNrAndPriceRead the tables with two DB Reader nodes Step 2.1. Clean the DataClean the Customer Information System 1 dataExclude the first row and first column with the Table Cropper nodeRemove rows with duplicate CustomerID with the Duplicate RowFilter nodeWhen the age is missing, insert the mean age of the dataset withthe Missing Value node Step 3.1. Transform the DataCreate a new column Age Group with values Adolescent, Adult, Older Adult according to the age value with the Rule Engine node<18 Adolescents, 18-65 Adults, >65 Older AdultsReplace the character "_" with a space "" from the column country Country with the String Manipulation nodeSplit the column CustomerID on the character "_" with the Cell Splitter nodeRename the columns created by the split with the Column Renamer nodeMerge the columns Email and CorporateEmail with the Column Merger node. Keep the Email when both are available.Convert the column Newsletter with the Number to String node Step 3.2.Tidy up the Workflow with a MetanodeRepeat the steps of Data Cleaning and Data Transformation for the Customer Information System 2 dataTidy up the workflow in a Metanode Step 5.1. Aggregate the Data ISum the Price for each CustomerID with the Row Aggregator node Step 6.1. Export the DataWrite the table containing the total price per customer with theExcel Writer node Step 6.2. Visualize the Data IConvert the BasketSize with the Number to String nodeShow in a BarChart the amount of orders for each BasketSize, forboth Online and Onsite Stores Step 6.4. Components and ReportingCreate a component containing the visualization nodesUse the Layout Editor to rearrange the interactive view of thecomponentSelect Enable Reporting in the Layout EditorAdd a Report Template Creator node before the component andexport its view as a PDF report with the Report PDF Writer node Step 5.2. Aggregate the Data IICalculate the Basket Size of each order (number of products inthe order) with the GroupBy node. It can be obtained by countingany column. Retain information about the StoreTypeShow the number of orders for each Basket Size in the OnlineStore and Onsite Store respectively with the Pivot node Step 5.3. Aggregate the Data IIIIn a new branch, repeat step 5.1 with a GroupBy node. This time,for each customer, keepCustomerIDCustomerGroupAgeGroupSum of PriceMean PriceUnique Count of ProductNr Step 6.3. Visualize the Data IIAssign a color to each row according to the CustomerGroupcolumn with the Color Manager nodeIn a Scatter Plot show the Sum(Price) and UniqueCount(ProductNr)Show the count of occurrences for CustomerGroup in a Bar ChartShow a Parallel Coordinates Plot withAgeGroupCustomerGroupSum(Price)Unique Count(ProductNr)Create a title with the Text View nodeNote: apply the color when possible. In the Bar Chart, in the GroupBar option select Frequency Dimension. Learning objectives: Access, clean, transform, merge and aggregate data to report and visualize the results.Workflow description: This workflow accesses customer and transactions data, transforms the data and generates an interactive view with multiple visualizations.You’ll find the use case information in the blue annotations and the instructions to the exercises in the yellow annotations. Use CasePauline from Sales would like to write a report and visualize the insights on the customer transaction data her team collects every month. This includes the total priceper customer and the basket size per store type. Since Pauline receives the data from multiple sources each month, which needs blending, cleaning, andtransformation before it can be put into a report, she has reached out to your data team for help to automate the process and thereby save time.  Your continuous project will be to help Pauline build a workflow to report the monthly insights, by completing each step of the process she repeats every month below. Data AccessPauline receives the data from multiple sources, including files,and data base tables. Your first task is to start building theworkflow by accessing the different data sources. Data CleaningNow that the data access is established, Pauline asks your helpin cleaning the data, since there seems to be duplicates, missingvalues, and some columns that are not needed. Data TransformationNext, you need to merge the cleaned data. To do so, Pauline outlines how she normally transforms some of the data in the rows andreshapes some columns to create the desired form. Data Export, Visualization and ReportingPauline is happy that the data is ready to be exported. Her teamneeds the results from 5.1 as an Excel file, 5.1 as a Bar Chart,and 5.3. in an interactive dashboard that can be exported as a pdf. Data MergingWith the data cleaned and in the correct shape, you can now helpPauline to merge the different data sources, by stacking tablesand matching or joining others to add the necessary columns. Data AggregationYou have now reached the point where the Pauline's data can beaggregated to get the necessary insight concerning the price andbasket sizes for customers and stores. Step 4.1. Merge the DataConcatenate the two customer tables with the Concatenate nodeAppend the StoreType (in Stores.csv) and Price (from theProductNrAndPrice DB table) to each transaction (stored in theTransactions DB table) with two Value Lookup nodesJoin the Customer and Transaction tables on the CustomerIDcolumn with the Joiner node.Note: The first output port of the Joiner shows the rows with amatch in the two tables. Use only these rows in the next steps. Node 1Node 2Node 3Node 7Node 8Node 9Node 10Node 11Node 12Node 13Node 14Node 15Node 16Node 17Connect to SalesDatabaseTransactionsProdcutNrAndPriceNode 296Node 297Calculate BasketSizefor each orderNode 379Node 380Node 381Calculate BasketSizefor each orderNode 383Node 384Node 385Node 386Node 387Node 388Node 389Node 390Node 392Excel Reader CSV Reader Table Reader Table Cropper DuplicateRow Filter Missing Value Rule Engine String Manipulation Cell Splitter Column Renamer Column Merger Number to String Concatenate Value Lookup SQLite Connector DB Table Selector DB Table Selector DB Reader DB Reader Value Lookup Joiner GroupBy Data Cleaning andTransformation Row Aggregator Pivot GroupBy Excel Writer Number to String Bar Chart Color Manager Scatter Plot Bar Chart ParallelCoordinates Plot Report PDF Writer Visualizations Report TemplateCreator

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: Emilio.Silvestri
Created at: 2023-08-15
On NodePit since: 2024-04-11
Last update: 2024-05-01
Created with KNIME version: v5.2.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!