Icon

Customer Transactions Analysis - Solution

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 Row FilternodeWhen the age is missing, insert the mean age of the dataset with theMissing 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&lt;18 Adolescents, 18-65 Adults, &gt;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 the ExcelWriter 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, for bothOnline and Onsite Stores Step 6.4. Components and ReportingCreate a component containing the visualization nodesUse the Layout Editor to rearrange the interactive view of the componentSelect Enable Reporting in the Layout EditorAdd a Report Template Creator node before the component and export itsview 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 in the order)with the GroupBy node. It can be obtained by counting any column.Retain information about the StoreTypeShow the number of orders for each Basket Size in the Online Store andOnsite 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 eachcustomer, keepCustomerIDCustomerGroupAgeGroupSum of PriceMean PriceUnique Count of ProductNr Step 6.3. Visualize the Data IIAssign a color to each row according to the CustomerGroup column withthe Color Manager nodeIn a Scatter Plot show the Sum(Price) and Unique Count(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 Group Baroption 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 price per customer andthe basket size per store type. Since Pauline receives the data from multiple sources each month, which needs blending, cleaning, and transformation 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. &nbsp;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 database tables. Your first task is to start building the workflow by accessingthe different data sources. Data CleaningNow that the data access is established, Pauline asks your help incleaning the data, since there seems to be duplicates, missing values,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 and reshapes somecolumns to create the desired form. Data Export, Visualization and ReportingPauline is happy that the data is ready to be exported. Her team needsthe results from 5.1 as an Excel file, 5.1 as a Bar Chart, and 5.3. in aninteractive dashboard that can be exported as a pdf. Data MergingWith the data cleaned and in the correct shape, you can now help Paulineto merge the different data sources, by stacking tables and matching orjoining 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 and basketsizes 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 CustomerID column withthe Joiner node.Note: The first output port of the Joiner shows the rows with a match inthe two tables. Use only these rows in the next steps. Customer InformationSystem 2Pivot Basket Sizeand StoreTypeConnect to SalesDatabaseProduct ID and PriceTransactionsNewsletter to StringMissing(Age)= meanRemove duplicatebased on CustomerIDSplit CustomerIDand CustomerGroupRemove _from CountryRename newcolumnsConcatenate customertablesMerge email andcorporate emailAppend PriceJoin customersand transactionsTotal Priceper CustomerTotal Priceper Customer+ Customer InfoNode 327Calculate BasketSizefor each orderConvert the basket sizeto StringKeep only therelevantpart of the tableCustomer InformationSystem 1Stores.csvAppendStore Type<18 Adolescents18-65 Adults>65 Older AdultsNode 379Export as PDF reportExport total pricetableNode 392 Table Reader Pivot SQLite Connector DB Table Selector DB Table Selector DB Reader DB Reader Number to String Missing Value DuplicateRow Filter Cell Splitter String Manipulation Column Renamer Concatenate Column Merger Value Lookup Joiner Row Aggregator GroupBy Bar Chart GroupBy Number to String Table Cropper Excel Reader CSV Reader Value Lookup Rule Engine Data Cleaning andTransformation Report PDF Writer Excel 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 Row FilternodeWhen the age is missing, insert the mean age of the dataset with theMissing 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&lt;18 Adolescents, 18-65 Adults, &gt;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 the ExcelWriter 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, for bothOnline and Onsite Stores Step 6.4. Components and ReportingCreate a component containing the visualization nodesUse the Layout Editor to rearrange the interactive view of the componentSelect Enable Reporting in the Layout EditorAdd a Report Template Creator node before the component and export itsview 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 in the order)with the GroupBy node. It can be obtained by counting any column.Retain information about the StoreTypeShow the number of orders for each Basket Size in the Online Store andOnsite 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 eachcustomer, keepCustomerIDCustomerGroupAgeGroupSum of PriceMean PriceUnique Count of ProductNr Step 6.3. Visualize the Data IIAssign a color to each row according to the CustomerGroup column withthe Color Manager nodeIn a Scatter Plot show the Sum(Price) and Unique Count(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 Group Baroption 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 price per customer andthe basket size per store type. Since Pauline receives the data from multiple sources each month, which needs blending, cleaning, and transformation 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. &nbsp;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 database tables. Your first task is to start building the workflow by accessingthe different data sources. Data CleaningNow that the data access is established, Pauline asks your help incleaning the data, since there seems to be duplicates, missing values,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 and reshapes somecolumns to create the desired form. Data Export, Visualization and ReportingPauline is happy that the data is ready to be exported. Her team needsthe results from 5.1 as an Excel file, 5.1 as a Bar Chart, and 5.3. in aninteractive dashboard that can be exported as a pdf. Data MergingWith the data cleaned and in the correct shape, you can now help Paulineto merge the different data sources, by stacking tables and matching orjoining 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 and basketsizes 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 CustomerID column withthe Joiner node.Note: The first output port of the Joiner shows the rows with a match inthe two tables. Use only these rows in the next steps. Customer InformationSystem 2Pivot Basket Sizeand StoreTypeConnect to SalesDatabaseProduct ID and PriceTransactionsNewsletter to StringMissing(Age)= meanRemove duplicatebased on CustomerIDSplit CustomerIDand CustomerGroupRemove _from CountryRename newcolumnsConcatenate customertablesMerge email andcorporate emailAppend PriceJoin customersand transactionsTotal Priceper CustomerTotal Priceper Customer+ Customer InfoNode 327Calculate BasketSizefor each orderConvert the basket sizeto StringKeep only therelevantpart of the tableCustomer InformationSystem 1Stores.csvAppendStore Type<18 Adolescents18-65 Adults>65 Older AdultsNode 379Export as PDF reportExport total pricetableNode 392Table Reader Pivot SQLite Connector DB Table Selector DB Table Selector DB Reader DB Reader Number to String Missing Value DuplicateRow Filter Cell Splitter String Manipulation Column Renamer Concatenate Column Merger Value Lookup Joiner Row Aggregator GroupBy Bar Chart GroupBy Number to String Table Cropper Excel Reader CSV Reader Value Lookup Rule Engine Data Cleaning andTransformation Report PDF Writer Excel 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-05-14
Last update: 2024-11-21
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!