User Tools

Site Tools


adwords_api_integration
# Developer token: hFv3VyAOV58aNVONg5_6dw 
# Client ID: http://386386895776-30vds3ltg52g85odf0f05pr7u2rhp97s.apps.googleusercontent.com/
# Client secret: ALG95T4hlVk2guhLICVe-ncj
# Client token: 4/UNDDhSZ4a38jYmz0BesQwmb-laKDf69LmGioHq-73qE
 
########
# Data #
########
 
library(RAdwords)
library(reshape2)
library(ggplot2)
library(dplyr)
 
# Authentication
google_auth <- doAuth()
 
# Specify date range, i.e. last two weeks until yesterday
yesterday <- gsub("-","",format(Sys.Date()-1,"%Y-%m-%d"))
thirtydays<- gsub("-","",format(Sys.Date()-29,"%Y-%m-%d"))
 
# Create statement
body <- statement(select=c("AccountDescriptiveName","Criteria","Clicks","Impressions", "CampaignName", "Cost",
                           "AdGroupName", "ConvertedClicks","CostPerConvertedClick",
                           "Date"),
                  report="KEYWORDS_PERFORMANCE_REPORT",
                  where="ConvertedClicks>0",
                  start=thirtydays,
                  end=yesterday)
 
data <- getData(clientCustomerId='530-931-7736', google_auth=google_auth ,statement=body)
 
write.csv(data, file = "data_ph.csv")
data <- read.csv('data_ph.csv')
data$Day <- as.Date(data$Day,"%Y-%m-%d") # tabulate all the options here
 
 
 
 
data$Cluster=gsub("_","",gsub("\\d","", gsub("]([^/]*)$","]",data$Adgroup)))
data$CPO <- round(data$CPO,2)
data <- data[order(-data$CPO),] 
row.names(data) = NULL
 
#######################
# Account Performance # 
###################### 
 
# CPO and loess smoothing
data_cpo <- data %>% group_by(Day) %>% summarize(CPO=mean(CPO), Impressions=sum(Impressions))
ggplot(aes(x = data_cpo$Day, y = data_cpo$CPO), data = data_cpo) + geom_line() + stat_smooth(method = "loess", formula = y ~ x, size = 1, colour="red") + theme(legend.position="none")+ ggtitle("CPO Last 30 days")+ ylab("CPL") + xlab("")
 
# Multiple lines
data1 = data
data1$Account = NULL
data1$Impressions = NULL
data1$Keyword = NULL
data1$Campaign = NULL
data1$Adgroup = NULL
data1$Dayofweek = NULL
data1$Cluster = NULL
data1$SearchImpr.share = NULL
data1$Position = NULL
data1$Max.CPC = NULL
data1$CPO = NULL
 
data1_piv <- data1 %>% group_by(Day) %>% summarize( Clicks=sum(Clicks), Conversions=sum(Conversions), Cost=sum(Cost))
data1_piv_long <- melt(data1_piv, id="Day")
ggplot(data1_piv_long, aes(x=Day, y=value, color=variable)) + geom_line() +   ylab("KPI") +
  ggtitle("Overall Account Performance")
 
 
 
######
# QS #   
######
 
 
# Create statement
body <- statement(select=c("AccountDescriptiveName","CampaignName","AdGroupName","Criteria","KeywordMatchType","QualityScore","Impressions","Clicks","Ctr", "ConvertedClicks","AverageCpc"),
                  report="KEYWORDS_PERFORMANCE_REPORT",
                  where="Impressions >1 AND AdNetworkType1 = SEARCH",
                  start=thirtydays,
                  end=yesterday)
data <- getData(clientCustomerId='537-890-7161', google_auth=google_auth ,statement=body)
 
#Change Quality Score to numeric
data$Qualityscore = as.numeric(as.character(data$Qualityscore))
 
#Histogram of Quality Score
hist(data$Qualityscore, main="Quality Score Frequency", xlab="", col = "gray") 
 
#Broad vs. Exact
data2.copy <- data %>% group_by(Matchtype) %>% summarize(AverageQS=mean(Qualityscore), Clicks=sum(Clicks))
data2.copy
 
## Geolocation
 
###
#1# Data Munging and Preparation
###
 
# https://developers.google.com/adwords/api/docs/appendix/geotargeting
 
body <- statement(select=c("AccountDescriptiveName","BidModifier","Id","CostPerConvertedClick","Clicks", "ConvertedClicks"),
                  report="CAMPAIGN_LOCATION_TARGET_REPORT",
                  where="Clicks > 0",
                  start=thirtydays,
                  end=yesterday)
data <- getData(clientCustomerId='530-931-7736', google_auth=google_auth ,statement=body)
 
Alocation <- read.csv("C:/Users/Vincenzo Grasso/Desktop/AdWords API Location Criteria 2015-05-29 (2).csv")
data.copy <- data %>% group_by(Location) %>% summarize(AverageCPL=mean(CPO), Clicks=sum(Clicks),Conversions=sum(Conversions))
data.copy[order(-data.copy$AverageCPL),]
 
# vlookup
 
join <- merge(Alocation, data.copy, by.x = 'Criteria.ID', by.y='Location') 
join_ord <- join[order(-join$Clicks),]
 
###
#2# Get coordinates
###
 
# Geocode function
 
library(RCurl)
library(RJSONIO)
 
construct.geocode.url <- function(address, return.call = "json", sensor = "false") {
  root <- "http://maps.google.com/maps/api/geocode/"
  u <- paste(root, return.call, "?address=", address, "&sensor=", sensor, sep = "")
  return(URLencode(u))
}
 
gGeoCode <- function(address,verbose=FALSE) {
  require("plyr")
  if(verbose) cat(address,"\n")
  u <- aaply(address,1,construct.geocode.url)
  doc <- aaply(u,1,getURL)
  json <- alply(doc,1,fromJSON,simplify = FALSE)
  coord = laply(json,function(x) {
    if(x$status=="OK") {
      lat <- x$results[[1]]$geometry$location$lat
      lng <- x$results[[1]]$geometry$location$lng
      return(c(lat, lng))
    } else {
      return(c(NA,NA))
    }
  })
  if(length(address)>1) colnames(coord)=c("lat","lng")
  else names(coord)=c("lat","lng")
  return(data.frame(address,coord))
}
 
# extract the column and save it as a vector
 
locations <- as.vector(join_ord[,'Name'])
 
# Geocode function over the vector and attach it on the original dataframe
gps <- gGeoCode(locations)
gps$Conversions <- join_ord$Conversions
gps$AverageCPL <- join_ord$AverageCPL
 
# Plot the map
library(ggmap)
library(mapproj)
map <- get_map(location = 'Philippines', zoom = 6)
ggmap(map, legend = "left") + geom_point(aes(x = gps$lng, y = gps$lat, size = log(gps$AverageCPL),color=total_len), data = gps, alpha = 0.5) + ylab("") + ggtitle("Clicks per Location") +  xlab("")  + theme(legend.position = "none") + 
  scale_color_gradient(low="blue", high="red")  
 
 
 
 
# Geocode function over the vector and attach it on the original dataframe
gps <- gGeoCode(locations)
gps$Clicks <- join_ord$Clicks
gps$AverageCPL <- join_ord$AverageCPL
 
# Plot the map
library(ggmap)
library(mapproj)
map <- get_map(location = 'Philippines', zoom = 5)
ggmap(map, legend = "left") + geom_point(aes(x = gps$lng, y = gps$lat, size = sqrt(gps$Clicks)), data = gps, alpha = 0.5) + ylab("") + ggtitle("Clicks per Location") +  xlab("")  + theme(legend.position = "none")
adwords_api_integration.txt · Last modified: 2015/09/21 15:19 by vincenzo