Google_Analytics_and_Adwords_Performance_Lost_Revenue_by_impression_share_or_rank

####Google Analytics JAzz####   https://netpeak.net/blog/how-to-measure-lost-revenue-in-google-adwords-with-the-help-of-language-r/###
#Credit goes to Alexey Seleznev


#need to do this to authentiate is all 

# In order to access the AdWords API you have to meet the following requirements:
# 
# Valid Google API project (client ID, client secret)
# Valid AdWords MCC (my client center) account
# Valid AdWords API Developer Token
# Do NOT use an Adwords Testaccount!

install.packages("curl")
install.packages("devtools")
install.packages("RAdwords")
install.packages("RGoogleAnalytics")
install.packages("ggplot2")
install.packages("WriteXLS")


library(RGoogleAnalytics)
library(RAdwords)
library(curl)
library(devtools)
library(WriteXLS)

#2. Declaration of variables
clientid <- "InsertCLientID"
secret <- "insertSecret" #Client secret из google console
ga_view <- "Insert_GA_ID" #ID views from Google Analytics - make sure format is "GA:XXXXXXXX"
adwords_id <- "XXX-XXX-XXXX" #ID of Google AdWords account

# Authorize the Google Analytics account.  This does not need to be executed in every session once the token object is created...

#RGoogleAnalytics::Auth('ClientID','Secret') Placeholder

token <- Auth(client.id,client.secret)


save(token,file="./token_file")
load("./token_file")
ValidateToken(token)



#Time period, in which it is needed to determine the amount of lost revenue
start_period  <- c(day = "01",
                   month = "01",
                   year = "2018")
end_period    <- c(day = "26",
                   month = "01",
                   year = "2018")




#4.Requesting data from services.
#4.1. Receiving data from Google Analytics
#4.1.1. Describing the query to Google Analytics
query.list <- Init(start.date = paste(start_period["year"],start_period["month"],start_period["day"],sep = "-"),
                   end.date = paste(end_period["year"],end_period["month"],end_period["day"],sep = "-"),
                   dimensions = "ga:adwordsCampaignID, ga:campaign",
                   metrics = "ga:transactions,ga:transactionRevenue",
                   filters = "ga:source==google,ga:medium==cpc",
                   table.id = ga_view)


#You'll notice VERY SMALL changes about 3-4 on this script vs the article above at the top, because I had to make
#a few adjustments to get this to work right.

#4.1.2. Creating an API query object to Google Analytics
ga.query <- QueryBuilder(query.list)

#4.1.3. Receiving data from Google Analytics in R
gaData <- GetReportData(ga.query, token, split_daywise = FALSE, paginate_query = FALSE)
#if you get max results error and restart pagination - make sure to restart R session and install RGoogleAnalytics and load it again, dont use devtools
#https://github.com/Tatvic/RGoogleAnalytics/blob/master/R/GetReportData.R <- I found out that older version had not declared max results in functions in
#the newer package so I skipped out on the devtools install and did install.packages within R Studio. 

#4.2. Receiving data from Google AdWords
#4.2.1. Describing the API query to Google AdWords.
body <- statement(select=c('CampaignId',
                           'Impressions',
                           'Clicks',
                           'Cost',
                           'Ctr',
                           'SearchBudgetLostImpressionShare',
                           'SearchRankLostImpressionShare  ',
                           'ContentBudgetLostImpressionShare',
                           'ContentRankLostImpressionShare'),
                  report="CAMPAIGN_PERFORMANCE_REPORT",
                  start=paste0(start_period["year"],start_period["month"],start_period["day"]),
                  end=paste0(end_period["year"],end_period["month"],end_period["day"]))

#For Adwords Auth It will ask for data and make you accept permissions as well
adwords_auth <- doAuth(F)

#4.2.2. Sending the query to Google AdWords - Need to remove API Version for this to work like so:
adwordsData <- getData(clientCustomerId = adwords_id,
                       google_auth = google_auth,
                       statement = body,
                       transformation = T)

#in case all else fails-  from the repo https://github.com/jburkhardt/RAdwords
#data <- getData(clientCustomerId='XXX-XXX-XXXX', google_auth=google_auth ,statement=body)

#5. Preparing the summary table.
#5.1. Combining data from Google Analytics and Google AdWords in one table
totalData <- merge(gaData, adwordsData, by.x = "adwordsCampaignID", by.y = "CampaignID", all.x = TRUE)

#5.2. Replacing missed values with zeros - Need to remove this, already have it below...
for (i in 1:length(totalData)){
  totalData[which(is.na(totalData[i])),i] <- 0
}

#5.3. Final calculations of the number of lost transactions and revenue.
totalData$lostImpressionByBudgetSearch  <- round(totalData$Impressions / (1-totalData$`SearchLostIS(budget)`) - totalData$Impressions,0)
totalData$lostImpressionByRankSearch    <- round(totalData$Impressions / (1-totalData$`SearchLostIS(rank)`) - totalData$Impressions,0)
totalData$lostImpressionByBudgetDisplay <- round(totalData$Impressions / (1-totalData$`ContentLostIS(budget)`) - totalData$Impressions,0)
totalData$lostImpressionByRankDisplay   <- round(totalData$Impressions / (1-totalData$`ContentLostIS(rank)`) - totalData$Impressions,0)
totalData$lostImpressionByBudget        <- totalData$lostImpressionByBudgetSearch + totalData$lostImpressionByBudgetDisplay
totalData$lostImpressionByRank          <- totalData$lostImpressionByRankSearch  + totalData$lostImpressionByRankDisplay
totalData$lostClicksByBudget            <- round(totalData$lostImpressionByBudget * (totalData$CTR),0)
totalData$lostClicksByRank              <- round(totalData$lostImpressionByRank * (totalData$CTR),0)
totalData$lostTransactionsByBudget      <- round(totalData$lostClicksByBudget * (totalData$transactions / totalData$Clicks),0)
totalData$lostTransactionsByRank        <- round(totalData$lostClicksByRank * (totalData$transactions / totalData$Clicks),0)
totalData$lostTransactions              <- totalData$lostTransactionsByBudget + totalData$lostTransactionsByRank
totalData$lostRevenueByBudget           <- round(totalData$lostTransactionsByBudget * (totalData$transactionRevenue / totalData$transactions),0)
totalData$lostRevenueByRank             <- round(totalData$lostTransactionsByRank * (totalData$transactionRevenue / totalData$transactions),0)
totalData$lostRevenue                   <- totalData$lostRevenueByBudget + totalData$lostRevenueByRank



#6. Unloading the calculated table in csv file
#install.packages("WriteXLS")
#library("WriteXLS")

write.csv(totalData, file="Adwords_GA_Analysis_CampaignsExample.csv", row.names=FALSE)

#7. Visualization in the form of a pie chart
lost_revenue <- c('received revenue' = sum(totalData$transactionRevenue), 'lost by budget' =sum(totalData$lostRevenueByBudget), 'lost by rank' = sum(totalData$lostRevenueByRank))
pie(lost_revenue,col = c("green", "red", "yellow"))


#Not Working?  Go back up to step 5.2 to Remove the NA values! Then Run Pie chart and the bind again in #7



#Cant figure this out, or how to fix this yet... 

require("ggplot2")
HistData <- rbind(data.frame(subset(totalData, select = c("Campaign", "transactionRevenue")), Type = "GottenRevenue"),
                  setNames(data.frame(subset(totalData, select = c("Campaign", "lostRevenueByBudget")), Type = "LostByBudget"), c("Campaign", "transactionRevenue","Type")),
                  setNames(data.frame(subset(totalData, select = c("Campaign", "lostRevenueByRank")), Type = "LostByRank"), c("Campaign", "transactionRevenue","Type")))
HistData <- HistData[!is.nan(HistData$transactionRevenue),]


ggplot(HistData, aes(x = Campaign, y = transactionRevenue, fill = Type))+
  geom_bar(stat = "identity", position = "fill")+
  scale_fill_manual(values=c("forestgreen", "firebrick1", "tan1"))+
  theme(axis.text.x = element_text(angle = 90, hjust = 1, size = 7))+
  ggtitle("Lost Conversion rate")