R in a digital analytics workflow

Demo linking GA and CRM data

Wunderman

An overview of how to use R in your workflow:

1. Importing Data
   • CRM data sitting on MS SQL Server
   • Web data via Google Analytics API
2. Transform; Analyse; Visualise
3. Presentation

Why not just do it in Excel?

• Reproducability: write a function for one client, repeat for another
• Flexibility: library() includes open source functions for all needs
• Scalability: copes easily beyond Excel limits, can scale up in cloud using same code
• Interportability: Can run (almost) same code on Windows, Linux, Mac

Finding the question to answer

• What is the business case?
• What data is available?
• Do we need to enchance/capture more data?
• What keys can the data be linked on?

Demo with a major Scandinavian retailer

• 1 million+ registered customers
• 200+ retail shops
• Online activation for offline customers Key Point
• Online shop running Google Analytics
• CRM data sitting on Microsoft SQL Server
• Every Purchase Receipt for last 2 years
• Store Location, Customer Loyalty ID, Product Category, Household size, etc.

Ask the business question, then see what data you need.

Demo data set had:

Repeat process for questions that need userID link

As we only have 20mins, slides going over data connection are in the Appendix:

• Importing CRM data from Microsoft SQL Server using library(RODBC)
• Importing GA data using library(rga)

After importing, we have:

• GA: 363,187 rows: date, productSku, itemRevenue
• CRM: 35,250 rows: product_cat, productID (SKU)
• CRM: 26,452 rows: revenue, category, date

We now have a standard dataFrame in R which can be used for all R functions.

• CRM query imported a 10% sample with 4.4 million rows with 24 variables.
• Saves to 120MB on disc, 820MB object size in RAM
• Runs fine on MacBook Air 4GB RAM

R is RAM based, so first place to scale is there.

Run same code on a GCE instance with 54GB RAM for bigger data sets.

Bigger than that, need on disk R libraries such as dplyr(), ff(), RHadoop() etc..

Where R shines, manipulating data

## merge GA SKUs to find category

gaCat <- merge(lookup, GAdata, by.x = "ProductID", by.y = "productSku" , all.y = TRUE)

## aggregate on date / category
library(reshape2)

gaCatMelt <- melt(gaCat, measure.vars="itemRevenue")

gaCatCast <- dcast(gaCatMelt, date + CategoryName ~ variable, sum)

Looks like dirty data, can safely remove

## find outliers
DailyCatRev[DailyCatRev$Revenue > 10000000, c("date","Revenue")]

DailyCatRev[DailyCatRev$Revenue < -10000000, c("date","Revenue")]

## remove outliers
DailyCatRev <- DailyCatRev[DailyCatRev$Revenue < 10000000,]

DailyCatRev <- DailyCatRev[DailyCatRev$Revenue > -10000000,]

Write a function to check cross correlation on all 600 product categories

The 'R way' is to avoid loops, use vectorised functions

## cross correlation function
crossCorRevenue <- function(product){
ccf(GAdataAgg[GAdataAgg$CategoryName == product,"itemRevenue"], DailyCatRev[DailyCatRev$CategoryName == product,"Revenue"], main=paste("CCF for", product) )
}

## apply to all unique Category names
sapply(unique(GAdataAgg$CategoryName), crossCorRevenue)

Not conclusive, but some evidence of lags in online and offline shopping behaviour Next steps:

• Deal with Christmas peak affecting results
• Repeat with each product, not product category (now easy with vectorised formula to repeat on 100,000+ products)
• Get bigger sample size - techniques trained on 10% sample. etc. etc.

Main point is R is quick to iterate over transform, visualise, analyse loop

This presentation is made in R using RStudio's RMarkdown

• Turns markdown and R code into HTML5
• Customise via CSS

Other alternatives include:

• PDF exports of ggplot() graphs
• Interactive web dashboards using Shiny
• Export processed data and visualise in Tableau, Qlikview etc.

This presentation is made in R using RStudio's RMarkdown

* Turns markdown and R code into HTML5
* Customise via CSS

Other alternatives include:
* PDF exports of ggplot() graphs
* Interactive web dashboards using Shiny
* Export processed data and visualise in Tableau, Qlikview etc.

Hotkeys: hit 'o' to navigate, 'f' for fullscreen (when not on RPubs)

Quick tour how R helps in all steps of data analysis:

1. Importing data - rga() RODBC()
2. Data Cleaning/Transforming data - merge() reshape2() dplyr()
3. Analysis and statistics - ccf() glm() forecast()
4. Visualisation - ggplot2() ggvis() shiny()
5. Presentation - knitr()

1. Forecasting / Prediction
2. Neural Nets / Machine Learning
3. Text Analysis
4. Connection with other Web APIs (e.g. twitteR() )
5. Report Automation (via RScript)
6. Creation of data feeds
7. R in the cloud
8. (New Sep 2014!) Google's casualImpact package

• Nytimes.com - Big Data Scientists Hurdle is Janitor Work
• Recommended Google Analytics to R library - rga()
• My GA Forecasting Shiny R app
• R Markdown Presentations like this one
• R Bloggers
• Installing R on Google Compute Engine
• Advanced R Programming by Hadley Wickham
• Say hello on Twitter: @HoloMarkeD

Importing Data

• Importing CRM data from Microsoft SQL Server using library(RODBC)
• Importing GA data using library(rga)

This is (sadly) a lot easier when running R on Windows as you need to setup Active Directory

• Create a user DSN data source
• Should have something like this:

Now load up R/RStudio and connect using the crudentials you've just created

• Connect to Microsoft SQL server, take a look at what Tables you have library("RODBC") channel <- odbcConnect("nameOfUDS", uid="username", pwd='password') Tables <- sqlTables(channel)

Your connection is saved to object channel - use SQL queries to get the data you want

• Pull in top 100 rows

exampleSQL <- sqlQuery(channel, "SELECT TOP 1000 * FROM dbo.purchase)

• More complicated example - pull in 10% sample from Puchase table and join it to Receipt table

purchasesSample <- sqlQuery(channel, "SELECT * FROM dbo.purchase tablesample (10 percent) LEFT OUTER JOIN dbo.purchaseline purline ON dbo.purchase.ReceiptID = purline.ReceiptID")

We now have a standard dataFrame in R which can be used for all R functions.

Similar workflow if using another type of database with ODBC (Open Database Connectivity Overview)

Other data sources may have their own library e.g. RMySQL, rmongodb, bigrquery, redshift-r, etc.

We will now use another library to connect to Google Analytics - skardhamar/rga()

We can merge on Product SKU code, so we pull ga:date, ga:productSku and ga:itemRevenue

## install rga following instructions on github, then....

## pull all the GA profiles
## your profile id is in this table
profiles <- ga$getProfiles() 

GAdata <- ga$getData(ids = "YOUR_PROFILE_ID", start.date = "2013-01-01", end.date = "2014-06-01", metrics = "ga:itemRevenue", dimensions = "ga:date,ga:productSku", batch = TRUE)