Campaign Optimization - Predicting How and When to Contact Leads

PowerShell Instructions

  • Setup
  • Execute PowerShell Script
  • Review Data
  • Visualizing Results
  • Other Steps
    • If you have deployed a VM through the 'Deploy to Azure' button on the Quick start page, all the steps below have already been performed and your database on that machine has all the resulting tables and stored procedures. Skip to the Typical Workflow for a description of how these files were first created in R by a Data Scientist and then deployed to SQL stored procedures.

    If you are configuring your own server, or if you want to reset your VM to its initial state, continue with the steps below to run the PowerShell script.

    Setup

    First, make sure you have set up your SQL Server by following these instructions. Then proceed with the steps below to run the solution template using the automated PowerShell file.

    Execute PowerShell Script

    Running this PowerShell script will create the data tables and stored procedures for the the operationalization of this solution in R in the Campaign_R database. It will also execute these procedures to create full database with results of the steps – dataset creation, modeling, and scoring as described here.

    1. Log onto the computer that contains the SQL Server you wish to use.

    2. Install Git if it is not already present. During the install, check the box to add LFS support.

    3. Download CampaignSetup.ps1 to your computer. The best way of downloading the file is to clone the repository using git clone https://github.com/Microsoft/r-server-campaign-optimization-of-stay. You will find CampaignSetup.ps1 in the Resources/ActionScripts folder.

    4. Open a command or PowerShell window as Administrator.

    5. CD to the directory where you downloaded the above .ps1 file and execute the command:

      .\CampaignSetup.ps1

    6. Make sure to accept installation of NuGet if prompted.

    7. This will make the following modification to your SQL Server:

      • Installs the SQL Server PowerShell module. If this is already installed, it will update it if necessary.
      • Creates the SLQRUserGroup for running R and Python code.
      • Reconfigures SQL Server to allow running of external scripts.
      • Clones the solution code and data into the c:\Solutions\Campaign directory
      • Creates the solution database Campaign_R and configures an ODBC connection to the database.
      • Executes the stored procedure Initial_Run_Once_R to run the entire workflow for this solution.

    Review Data

    Once the PowerShell script has completed successfully, log into the SQL Server Management Studio to view all the datasets that have been created in the Campaign database.
    Hit Refresh if necessary.

    Click here to view more information about each of these tables.

    Right click on dbo.Recommendations and select View Top 1000 Rows to preview the scored data.

    Click here to view the details all tables created in this solution.

    Visualizing Results

    You’ve now created and processed data, created models, picked the best one and used the model to recommend a combination of Channel/Time/Day as described here. This PowerShell script also created the stored procedures that can be used to score new data for the next campaign.

    Let’s look at our current results. Proceed to Visualizing Results with PowerBI.

    Other Steps

    You’ve just completed the fully automated solution that simulates the data, trains and scores the models, and provide recommendations by executing PowerShell scripts.

    See the Typical Workflow for a description of how these files were first created in R by a Data Scientist and then incorporated into the SQL stored procedures that you just deployed.