Icon

Customer Transactions Analysis - Solution - v4.7

There has been no title set for this workflow's metadata.

There has been no description set for this workflow's metadata.

L1 Project - ExcercisesCustomer Transactions Analysis Step 1.1. Access the Data from File(s)Read CustomerInfoSystem1.xlsx with the Excel Readernode.Read CustomerInfoSystem2.table with the TableReader node.Read Stores.csv with the CSV Reader node. Step 1.2. Access the Data from a DatabaseConnect to the database with the SQLite Connectornode.With two Table Selector nodes, select the tablesTransactions and ProductNrAndPrice.Read the tables with two DB Reader nodes. Step 2.1. Clean the DataClean the Customer Information System 1 data.Exclude the first row and first column with a Row Filter node anda Column Filter node.Remove rows with duplicate CustomerID with the DuplicateRow Filter node.When 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 valuewith 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 Manipulationnode.Split the column CustomerID on the character "_" with the Cell Splitter node.Rename the columns created by the split with the Column Rename node.Merge the columns Email and CorporateEmail with the Column Merger node. Keep the Email when bothare 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 data.Tidy up the workflow in a Metanode. Step 5.1. Aggregate the Data ISum the Price for each CustomerID with the GroupBynode. Step 6.1. Export the DataWrite the table containing the total price per customer withthe Excel Writer node. Step 6.2. Visualize the Data IConvert the BasketSize with the Number to String node.Show in a BarChart the amount of orders for eachBasketSize, for both Online and Onsite Stores. Step 6.4. Components and ReportingCreate a component containing the visualization nodes.Use the Layout Editor to rearrange the interactive view ofthe component.Enable the report output of the component. Export thecomponent interactive view as a PDF report with theReport PDF Writer node (only available in KNIMEAnalytics Platform v5.1 or higher). Step 5.2. Aggregate the Data IICalculate the Basket Size of each order (number ofproducts in the order) with the GroupBy node. It can beobtained by counting any column. Retain informationabout the StoreType.Show the number of orders for each Basket Size in theOnline Store and Onsite Store respectively with thePivoting node. Step 5.3. Aggregate the Data IIIIn a new branch, repeat step 5.1 with a GroupBy node.This time, for each customer, keep: CustomerID,CustomerGroup, AgeGroup, Sum of Price, MeanPrice& Unique Count of ProductNr. Step 6.3. Visualize the Data IIAssign a color to each row according to theCustomerGroup column with the Color Manager node.In a Scatter Plot show the Sum(Price) and UniqueCount(ProductNr).Show the count of occurrences for each AgeGroup in aBar Chart.Show a Parallel Coordinates Plot with: AgeGroup,CustomerGroup, Sum(Price)& UniqueCount(ProductNr).Create a title with the Text Output Widget node.Note: Select the color column when possible. 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 withmultiple visualizations.You’ll find the use case information in the blue annotations and the instructions to the exercises in the yellow annotations. Use Case:Pauline 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 and the basket size per store type. Since Pauline receives the data from multiple sources eachmonth, which needs blending, cleaning, and transformation before it can be put into a report, she has reached out to your data team for helpto 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 sherepeats every month below. Data AccessPauline receives the data from multiple sources,including files, and data base tables. Your first task is tostart building the workflow by accessing the different datasources. Data CleaningNow that the data access is established, Pauline asks your help incleaning 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 ofthe data in the rows and reshapes some columns to create the desired form. Data Export, Visualization and ReportingPauline is happy that the data is ready to be exported. Herteam needs the results from 5.1 as an Excel file, 5.1 as aBar Chart, and 5.3. in an interactive dashboard that can beexported as a pdf. Data MergingWith the data cleaned and in the correct shape, youcan now help Pauline to merge the different datasources, by stacking tables and matching or joiningothers to add the necessary columns. Data AggregationYou have now reached the point where the Pauline's datacan be aggregated to get the necessary insightconcerning the price and basket sizes for customers andstores. Step 4.1. Merge the DataConcatenate the two customer tables with theConcatenate node.Append the StoreType (in Stores.csv) and Price(from the ProductNrAndPrice DB table) to eachtransaction (stored in the Transactions DB table)with two Joiner nodes.Join the Customer and Transaction tables on theCustomerID column with the Joiner node.Note: The first output port of the Joiner shows therows with a match in the two tables. Use only theserows in the next steps. Customer InformationSystem 1Customer InformationSystem 2Stores.csvConnect to SalesDatabaseTransactionsProduct ID and PriceKeep only therelevant rowsKeep only therelevant columnsRemove duplicatebased on CustomerIDMissing(Age)= mean<18 Adolescents18-65 Adults>65 Older AdultsRemove _from CountrySplit CustomerIDand CustomerGroupRename newcolumnsMerge email andcorporate emailNewsletter to StringConcatenate customertablesAppendStore TypeAppend PriceJoin customersand transactionsTotal Priceper CustomerCalculate BasketSizefor each orderPivot Basket Sizeand StoreTypeTotal Priceper Customer+ Customer InfoExport total pricetableConvert the basket sizeto String Excel Reader Table Reader CSV Reader SQLite Connector DB Table Selector DB Table Selector DB Reader DB Reader Row Filter Column Filter DuplicateRow Filter Missing Value Rule Engine String Manipulation Cell Splitter Column Rename Column Merger Number To String Concatenate Data Cleaning andTransformation Joiner Joiner Joiner GroupBy GroupBy Pivoting GroupBy Excel Writer Number To String Bar Chart Visualizations L1 Project - ExcercisesCustomer Transactions Analysis Step 1.1. Access the Data from File(s)Read CustomerInfoSystem1.xlsx with the Excel Readernode.Read CustomerInfoSystem2.table with the TableReader node.Read Stores.csv with the CSV Reader node. Step 1.2. Access the Data from a DatabaseConnect to the database with the SQLite Connectornode.With two Table Selector nodes, select the tablesTransactions and ProductNrAndPrice.Read the tables with two DB Reader nodes. Step 2.1. Clean the DataClean the Customer Information System 1 data.Exclude the first row and first column with a Row Filter node anda Column Filter node.Remove rows with duplicate CustomerID with the DuplicateRow Filter node.When 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 valuewith 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 Manipulationnode.Split the column CustomerID on the character "_" with the Cell Splitter node.Rename the columns created by the split with the Column Rename node.Merge the columns Email and CorporateEmail with the Column Merger node. Keep the Email when bothare 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 data.Tidy up the workflow in a Metanode. Step 5.1. Aggregate the Data ISum the Price for each CustomerID with the GroupBynode. Step 6.1. Export the DataWrite the table containing the total price per customer withthe Excel Writer node. Step 6.2. Visualize the Data IConvert the BasketSize with the Number to String node.Show in a BarChart the amount of orders for eachBasketSize, for both Online and Onsite Stores. Step 6.4. Components and ReportingCreate a component containing the visualization nodes.Use the Layout Editor to rearrange the interactive view ofthe component.Enable the report output of the component. Export thecomponent interactive view as a PDF report with theReport PDF Writer node (only available in KNIMEAnalytics Platform v5.1 or higher). Step 5.2. Aggregate the Data IICalculate the Basket Size of each order (number ofproducts in the order) with the GroupBy node. It can beobtained by counting any column. Retain informationabout the StoreType.Show the number of orders for each Basket Size in theOnline Store and Onsite Store respectively with thePivoting node. Step 5.3. Aggregate the Data IIIIn a new branch, repeat step 5.1 with a GroupBy node.This time, for each customer, keep: CustomerID,CustomerGroup, AgeGroup, Sum of Price, MeanPrice& Unique Count of ProductNr. Step 6.3. Visualize the Data IIAssign a color to each row according to theCustomerGroup column with the Color Manager node.In a Scatter Plot show the Sum(Price) and UniqueCount(ProductNr).Show the count of occurrences for each AgeGroup in aBar Chart.Show a Parallel Coordinates Plot with: AgeGroup,CustomerGroup, Sum(Price)& UniqueCount(ProductNr).Create a title with the Text Output Widget node.Note: Select the color column when possible. 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 withmultiple visualizations.You’ll find the use case information in the blue annotations and the instructions to the exercises in the yellow annotations. Use Case:Pauline 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 and the basket size per store type. Since Pauline receives the data from multiple sources eachmonth, which needs blending, cleaning, and transformation before it can be put into a report, she has reached out to your data team for helpto 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 sherepeats every month below. Data AccessPauline receives the data from multiple sources,including files, and data base tables. Your first task is tostart building the workflow by accessing the different datasources. Data CleaningNow that the data access is established, Pauline asks your help incleaning 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 ofthe data in the rows and reshapes some columns to create the desired form. Data Export, Visualization and ReportingPauline is happy that the data is ready to be exported. Herteam needs the results from 5.1 as an Excel file, 5.1 as aBar Chart, and 5.3. in an interactive dashboard that can beexported as a pdf. Data MergingWith the data cleaned and in the correct shape, youcan now help Pauline to merge the different datasources, by stacking tables and matching or joiningothers to add the necessary columns. Data AggregationYou have now reached the point where the Pauline's datacan be aggregated to get the necessary insightconcerning the price and basket sizes for customers andstores. Step 4.1. Merge the DataConcatenate the two customer tables with theConcatenate node.Append the StoreType (in Stores.csv) and Price(from the ProductNrAndPrice DB table) to eachtransaction (stored in the Transactions DB table)with two Joiner nodes.Join the Customer and Transaction tables on theCustomerID column with the Joiner node.Note: The first output port of the Joiner shows therows with a match in the two tables. Use only theserows in the next steps. Customer InformationSystem 1Customer InformationSystem 2Stores.csvConnect to SalesDatabaseTransactionsProduct ID and PriceKeep only therelevant rowsKeep only therelevant columnsRemove duplicatebased on CustomerIDMissing(Age)= mean<18 Adolescents18-65 Adults>65 Older AdultsRemove _from CountrySplit CustomerIDand CustomerGroupRename newcolumnsMerge email andcorporate emailNewsletter to StringConcatenate customertablesAppendStore TypeAppend PriceJoin customersand transactionsTotal Priceper CustomerCalculate BasketSizefor each orderPivot Basket Sizeand StoreTypeTotal Priceper Customer+ Customer InfoExport total pricetableConvert the basket sizeto String Excel Reader Table Reader CSV Reader SQLite Connector DB Table Selector DB Table Selector DB Reader DB Reader Row Filter Column Filter DuplicateRow Filter Missing Value Rule Engine String Manipulation Cell Splitter Column Rename Column Merger Number To String Concatenate Data Cleaning andTransformation Joiner Joiner Joiner GroupBy GroupBy Pivoting GroupBy Excel Writer Number To String Bar Chart Visualizations

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: elisabeth.richter
Created at: 2023-10-31
On NodePit since: 2024-03-02
Last update: 2024-04-28
Created with KNIME version: v5.1.2

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

Try NodePit Runner!