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.

Proyecto L1 - EjerciciosCustomer 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 AgeGroup with values Adolescent, Adult, Older Adult according to the age value: &lt;18 Adolescents, 18-65 Adults,&gt;65 Older AdultsReplace the character "_" with a space "" from the column country CountrySplit 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 nodePerform instructions 1 and 2 with two Expression nodes. Click Ask K-AI and copy paste the text of the instruction in the prompt. 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 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 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. &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 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. Customer InformationSystem 2Pivot Basket Sizeand StoreTypeConnect to SalesDatabaseProduct ID and PriceTransactionsNewsletter to StringMissing(Age)= meanRemove duplicatebased on CustomerIDSplit CustomerIDand CustomerGroupRename 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 TypeNode 379Export as PDF reportExport total pricetableNode 392Remove _from Country<18 Adolescents18-65 Adults>65 Older Adults Table Reader Pivot SQLite Connector DB Table Selector DB Table Selector DB Reader DB Reader Number to String Missing Value DuplicateRow Filter Cell Splitter 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 Data Cleaning andTransformation Report PDF Writer Excel Writer Visualizations Report TemplateCreator Expression Expression Proyecto L1 - EjerciciosCustomer 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 AgeGroup with values Adolescent, Adult, Older Adult according to the age value: &lt;18 Adolescents, 18-65 Adults,&gt;65 Older AdultsReplace the character "_" with a space "" from the column country CountrySplit 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 nodePerform instructions 1 and 2 with two Expression nodes. Click Ask K-AI and copy paste the text of the instruction in the prompt. 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 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 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. &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 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. Customer InformationSystem 2Pivot Basket Sizeand StoreTypeConnect to SalesDatabaseProduct ID and PriceTransactionsNewsletter to StringMissing(Age)= meanRemove duplicatebased on CustomerIDSplit CustomerIDand CustomerGroupRename 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 TypeNode 379Export as PDF reportExport total pricetableNode 392Remove _from Country<18 Adolescents18-65 Adults>65 Older AdultsTable Reader Pivot SQLite Connector DB Table Selector DB Table Selector DB Reader DB Reader Number to String Missing Value DuplicateRow Filter Cell Splitter 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 Data Cleaning andTransformation Report PDF Writer Excel Writer Visualizations Report TemplateCreator Expression Expression

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-09-14
Last update: 2024-11-18
Created with KNIME version: v5.3.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!