predict new customers using alteryx, sap and microsoft power bi

© 2017 De Villiers Walton Limited

PREDICT NEW CUSTOMERS USING ALTERYX, SAP & MICROSOFT POWER BI

CONTENTS

• The Scenario• Method• Getting Started• Data Sources• Format and Blend the Data• Use Predictive tools• Prepare the Data for Microsoft Power BI• Publish to Microsoft Power BI• Use the Data in Microsoft Power BI• Next Steps


THE SCENARIO

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.


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:1. Alteryx Designer

– Request a trial here http://bit.ly/2mTkyxa

2. Alteryx Connector for SAP– Request a trial here http://bit.ly/2nxlsfY

3. Microsoft Power BI– Request a trial here http://bit.ly/2mEXHF4

4. Alteryx Starter Kit for Microsoft– Download it here http://bit.ly/2mkmMSy

5. Access to SAP BW


http://bit.ly/2mTkyxa

http://bit.ly/2nxlsfY

http://bit.ly/2mEXHF4

http://bit.ly/2mkmMSy


DATA SOURCES

DATA SOURCES

This workflow requires two Data Sources:


1

2

1


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.

1. DATA SOURCES – SAP LOGON TOOL


The SAP Logon tool is used to connect to the SAP systems we want to use in the Alteryx workflow.

1. DATA SOURCES –SAP BW INFOPROVIDER


The SAP BW InfoProvider tool is used to connect to the SAP BW Data Source.

The Characteristics and Key Figures required are selected. In this scenario, no Filters are applied.


2. PROSPECTIVE CUSTOMERS FROM EXCEL

The Prospective Customer data is provided in Excel format from the Alteryx Starter Kit for Microsoft.

2. DATA SOURCES – EXCEL SHEET


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 2

4 6 7

5

3

1. FORMAT & BLEND – DATE 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. FORMAT & BLEND – 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. FORMAT & BLEND – 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. FORMAT & BLEND – 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. FORMAT & BLEND – 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. FORMAT & BLEND – UNION TOOL


The Alteryx Union tool merges data and outputs columns contained in both the SAP source and the Excel source. For this workflow the “Auto Config by Name” configuration option has been selected.

The output from the Union tool.

7. FORMAT & BLEND – 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.

Current Customers

Prospective Customers


USE PREDICTIVE TOOLS



1

2 4

3 5

1. USE PREDICTIVE TOOLS –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.

2. USE PREDICTIVE TOOLS –BROWSE TOOL


Use the Browse tool to view the output of the Linear Regression Model tool.

3. USE PREDICTIVE TOOLS –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 PREDICTIVE TOOLS –BROWSE TOOL


Use the Browse tool to view the output of the Stepwise tool.

5. USE PREDICTIVE TOOLS –SCORE TOOL


Use the Score tool apply the model created using the Stepwise too 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 2

1. PREPARE THE DATA FOR POWER BI –FORMULA TOOL


Use the Formula tool to create new columns and update existing columns.

Total Sales: Round PredictedTotalSales from the Score tool

Marital Staus: If MaritalStatus = “M” then convert to “Married” otherwise “Single”

Home Owner Flag: If HouseOwnerFlag = 1 the convert to “Home Owner” else “Renter”

Sales Potential: Convert the Total Sales value into “High”, “Medium” or “Low”

2. PREPARE THE DATA FOR POWER BI –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

1. PUBLISH TO MICROSOFT POWER BI –PUBLISH TO POWER BI TOOL


Use the Publish To Power BI tool.

Choose the Power BI Dataset and Table name. Select the Operation (Create New, Append or Replace).

Decide the Power BI authentication method


USE THE DATA IN MICROSOFT POWER BI



Run the Alteryx workflow. The “New Customer” Dataset is created


NEXT STEPS

www.dvwanalytics.com

http://www.dvwanalytics.com/

http://www.dvwanalytics.com/

predict new customers using alteryx, sap and microsoft power bi

Software