Power BI, R, and Marketing Campaign Analysis

I’m going to talk about casino performance analysis, so I’m feeling a little like Marty McFly from Back to the Future.  One of the biggest struggles we had was with our direct mail offers to our players’ club members.  A proper direct mail offer campaign would be setup with a control group so we could perform analysis against the control group, but try to tell that to the A-tier player that didn’t get an offer or a different amount than the other A-tier players.

Without the control group, we would calculate the difference between the average coin in before and after the direct mail offer was received to judge our incremental revenue and evaluate whether the campaign was successful. This method was very simplistic because it assumed all differences between performance before and after the interaction are due strictly to that direct mail offer.  Google set out to help digital marketers with this problem of no control group in 2014, and we can use their Causal Impact R package to analyze our casino’s direct mail offers as well.

Putting it All to Use

Using the same daily Coin in (gross sales) data from the last blog on “Use Power BI and R to Quickly Identify Business Insights”, we need to complete the following steps:

1. Install the following R packages
   1. packages(“devtools”)
   2. packages(“BoomSpikeSlab”)
   3. packages(“dtw”)
   4. packages(“chron”)
   5. devtools::install_github(“google/CausalImpact”)
2. In Power BI Desktop, select the “R” in the Visualizations section and then drag the Denomination, Date and Revenue fields from the Coin In tables into the Values section to build our dataset for are R code.
3. Add the following code in the R Script Editor
   

   library(CausalImpact)

   library(data.table)

   productsM <- melt(dataset, id=1:2)

   productsC <- dcast(productsM, Date ~ Denomination + variable, sum)

   colnames(productsC) <- make.names(colnames(productsC))

   products <- zoo(productsC[,2:ncol(productsC)],as.Date(productsC[,1]))

   pre.period <- as.Date(c(“2008-01-01”, “2008-10-03”))

   post.period <- as.Date(c(“2008-10-04”, “2008-11-15”))

   impact <- CausalImpact(products, pre.period, post.period, model.args=list(nseasons=7))

   plot(impact)

   #summary(impact)

4. Click the Run button to execute the R script

Understanding the Results

The Casual Impact R package reduces the below three graphs

Original: Solid, black line is observed data before the intervention and dotted, blue line is the predicted coin in values for what would have occurred without the intervention

Pointwise: The net difference between the observed and predicted response on the original scale, or the difference between the solid, black line and the dotted, blue line on the original graph.

Cumulative: Dotted, blue line is the individual causal effects added up in time, day after day.

For all three graphs, the light blue shaded area represents the results in a 95% confidence level. The farther that the graph extends past the beginning of the intervention, the less certain of the causal effect; hence, the larger the shaded area.

Conclusion

Hope this helps you with your marketing campaign analysis in your organization.  Please contact KTL Solutions to kick start your organization into becoming data driven by exploring your Business Intelligence, BI360, Power BI, Microsoft Dynamics GP, and CRM needs.

BARRY CROWELL, MBA | BI/EDW Solution Architect

Barry is a Microsoft SQL Certified Professional with a strong knowledge of the Microsoft’s BI Stack (SSIS, SSRS, SSAS and Power BI). He has architected, developed and deployed clients’ Business Intelligence needs using Microsoft’s BI Stack and/or Solver’s BI360.  His solutions have included SSIS ETL tools, SSRS reports and dashboards, Excel dashboards, Power BI reports and dashboards, and SSAS cubes. He has performed implementations as the lead consultant and/or end-user project manager. Barry has over 20 years of experience working in accounting and the Microsoft Dynamics GP industry, and in various industries such as housing authorities, universities, Tribal governments, and casino hospitality. He possesses a Bachelor’s Degree in Accounting and Business Administration from Black Hills State University and a Master’s in Business Administration from La Salle University.  Barry’s experience in both the IT consultant field and experience as an accountant gives him the ability to understand the issues from both the IT and Finance prospective, and provide a solution that fits the needs for all parties involved.