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.
To replicate the scenario you will need:
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 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.
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.
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.
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.
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.
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.
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”
Prepare the Data for Microsoft Power BI
1. Use the Formula Tool
Use the Formula tool to create new columns and update existing columns.
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.
© COPYRIGHT 2003 - 2021 DE VILLIERS WALTON LIMITED. ALL RIGHTS RESERVED.
SAP® Business Suite, SAP® BusinessObjects™, SAP® BW, SAP® SuccessFactors are the trademarks or registered trademarks of SAP SE in Germany and in several other countries.