In this blog we will use Alteryx to build a model that produces a prioritized list of prospective customers for targeted marketing campaigns.
The Alteryx workflow builds a linear regression model using current customer data from SAP which is scored against prospective customer data from an Excel Sheet to produce a prioritized list of prospects.
The data is then published to Microsoft Power BI.
The Alteryx workflow builds a linear regression model using current customer data from SAP which is scored against prospective customer data from an Excel Sheet to produce a prioritized list of prospects.
The data is then published to Microsoft Power BI.
Method
- Open the, “Predicting New Customers” workflow provided with the Alteryx Starter Kit for Microsoft.
- Adapt the workflow by replacing the Current Customers Excel Sheet data input source with an SAP data source.
Getting Started
To replicate the scenario you will need:
- Alteryx Designer –Request a trial here http://bit.ly/2mTkyxa
- Alteryx Connector for SAP –Request a trial here http://bit.ly/2nxlsfY
- Microsoft Power BI –Request a trial here http://bit.ly/2mEXHF4
- Alteryx Starter Kit for Microsoft –Download it here http://bit.ly/2mkmMSy
- Access to SAP BW
Data Sources
This workflow requires two data sources
1. Current Customers from SAP BW
There are multiple ways for us to access the data from SAP. In this example we are using a custom SAP BW InfoProvider. The data was previously loaded into SAP BW using the Alteryx Data Loader for SAP.
The SAP BW data is shown below.
The SAP BW data is shown below.
The SAP Logon tool is used to connect to the SAP systems we want to use in the Alteryx workflow.
The SAP BW InfoProvider tool is used to connect to the SAP BW Data Source.
2. Prospective Customers from Excel
The Prospective Customer data is provided in Excel format from the Alteryx Starter Kit for Microsoft.
The Excel data is shown below.
The Excel data is shown below.
Use the Input tool to connect to the Excel Sheet of Prospective Customers (This is provided in the Starter Kit).
Format and Blend the Data
1. Use the Data Time Tool
The Alteryx Date Time tool is used to convert the format of the SAP Customer Date of Birth(“ACSUSTDOB”) from a String Field to a new Date/Time Field, “DateTime_Out”
2. Use the Select Tool
Use the Select tool on the SAP data to (1) select Fields relevant for the workflow, (2) change the Type of fields to “Double” so they can be used as Predictor Variables later in the workflow and (3) Rename fields so that they are consistent across the two data sources.
3. Use the Select Tool
Use the Select tool on the Excel Sheet data to select the Fields required for the workflow. In this scenario no changes to Field Type or Description are necessary.
4. Use the Formula Tool
Use the Formula tool to determine the source of data and calculate the age of the customer.
- Source: Identify the SAP Current Customer data as “Current Customer”
- Age: Calculate the Age of the customer based on the Date of Birth and Current Date
5. Use the Formula Tool
Use the Formula tool to determine the source of data, calculate the age of the customer and provide a Total Sales value.
- Source: Identify the Prospective Customer data as “Prospective Customer”
- Age: Calculate the Age of the customer based on the Date of Birth and Current Date
- TotalSales: Set the Total Sales value to NULL
6. Use the Union Tool
Use the Alteryx Union tool to merge data contained in both the SAP source and the Excel source. For this workflow the “Auto Config by Name” configuration option has been selected.
7. Use the Filter Tool
The Alteryx Filter tool is used to split the data into two streams. One stream for Current Customers and one for Prospective Customers.
Based on the Basic Filter applied in the configuration, data for Current Customers comes out of the “T” or True side of the tool. Data for Prospective Customers from the “F” or False side of the tool.
Based on the Basic Filter applied in the configuration, data for Current Customers comes out of the “T” or True side of the tool. Data for Prospective Customers from the “F” or False side of the tool.
Use Alteryx Predictive Tools
1. Use the Linear Regression Model Tool
Use the Linear Regression Model tool on the Current Customer data from SAP to test the statistical significance of selected predictors.
Select the Predictor Variables for the Linear Regression Model tool. In this scenario, we are interested in using Martial Status, Gender, Number of Children, Number of Children Living at Home and Home Ownership as Predictor Variables.
Select the Predictor Variables for the Linear Regression Model tool. In this scenario, we are interested in using Martial Status, Gender, Number of Children, Number of Children Living at Home and Home Ownership as Predictor Variables.
2. Use the Browse Tool
Use the Browse tool to view the output of the Linear Regression Model tool.
3. Use the Stepwise Tool
Use the Stepwise tool to analyse the selected predictors in the linear regression model and create a model using the most significant predictors.
The Stepwise tool uses the output from the Linear Regression Model tool and the output from the Filter tool.
The Stepwise tool uses the output from the Linear Regression Model tool and the output from the Filter tool.
4. Use the Browse Tool
Use the Browse tool to view the output of the Stepwise tool.
5. Use the Score Tool
Use the Score tool apply the model created using the Stepwise tool to the Prospective Customer data. A Total Sales value is predicted and assigned to each Prospective Customer record.
Create a new Total Sales value field, “PredictedTotal Sales”
Create a new Total Sales value field, “PredictedTotal Sales”
Prepare the Data for Microsoft Power BI
1. Use the Formula Tool
Use the Formula tool to create new columns and update existing columns.
- Total Sales: Round PredictedTotalSales from the Score tool
- Sales Potential: Convert the Total Sales value into “High”, “Medium” or “Low”
- Marital Staus: If MaritalStatus = “M” then convert to “Married” otherwise “Single”
- Home Owner Flag: If HouseOwnerFlag = 1 the convert to “Home Owner” else “Renter
2. Use the Select Tool
Use the Select tool to prepare the output for Microsoft Power BI by renaming columns, updating data types, and removing columns.
Publish to Microsoft Power BI
1. Publish to Microsoft Power BI Tool
Use the Publish To Power BI tool.
Use the Data in Microsoft Power BI
Run the Alteryx workflow. The Dataset is created in Power BI.
