Google_Store_Keyword_Heatmap.R

#This script outlines how to heatmap PPC keyword data for analysis & optimization of your PPC campaigns

#load libraries
library(sqldf)

## Loading required package: gsubfn

## Loading required package: proto

## Loading required package: RSQLite

library(readxl)
if (!require("RColorBrewer")) {
  install.packages("RColorBrewer", dependencies = TRUE)
  library(RColorBrewer)
}

## Loading required package: RColorBrewer

#the dataset used here was downloaded from the Google Merchandise Store Dataset Masterview
#Google Analytics > Google Merchandise Store > 1 Master View > Adwords > Keywords > Goal 1: Purchase Completed
#substitute with your own data
#this data was cleaned in Excel prior to import
keywords<-read_excel("~/Dropbox/Google Merch Store AdWords Keywords 20170413-20170512.xlsx")

#inspect your data
head(keywords,2)

##                    keyword clicks   cost        CPC users sessions
## 1       dynamic search ads    592 110.27 0.18626689   315      380
## 2 google merchandise store    324  26.03 0.08033951   271      373
##   bounce_rate pagesSession conversion_rate purchase_completed
## 1   0.3447368     5.731579      0.02368421                  9
## 2   0.2546917     5.769437      0.04289544                 16

names(keywords)

##  [1] "keyword"            "clicks"             "cost"              
##  [4] "CPC"                "users"              "sessions"          
##  [7] "bounce_rate"        "pagesSession"       "conversion_rate"   
## [10] "purchase_completed"

#I want to limit data to just keywords with at least 1 conversion
keywords<-sqldf('SELECT * 
            FROM keywords
            WHERE purchase_completed >= 1
            ORDER BY conversion_rate DESC')

## Loading required package: tcltk

## Warning: Quoted identifiers should have class SQL, use DBI::SQL() if the
## caller performs the quoting.

#turn keywords into row names
row.names(keywords) <- keywords$keyword

#drop the first column
keywords <- keywords[,2:10]

#inspect data
head(keywords,2)

##                          clicks  cost        CPC users sessions
## '+google +merch               3  0.78 0.26000000     4        5
## google merchandise store    324 26.03 0.08033951   271      373
##                          bounce_rate pagesSession conversion_rate
## '+google +merch            0.6000000     6.000000      0.20000000
## google merchandise store   0.2546917     5.769437      0.04289544
##                          purchase_completed
## '+google +merch                           1
## google merchandise store                 16

#turn dataframe into matrix
keywords_matrix<-data.matrix(keywords)

#display all colour schemes
display.brewer.all()

#create heatmap colors using Rcolorbrewer palette
keywords_matrix<-heatmap(keywords_matrix, Rowv=NA, Colv=NA, col = brewer.pal(8,"Oranges"),
                         scale="column",  main=" keyword heatmap")

#now you have your keyword data heatmapped!