google_trends_API.R

require(gtrendsR)
require(data.table)
require(RODBC) 

#GTRENDS ACCOUNT INFORMATION 
usr <- "USERNAME"
psw <- "PASSWORD"

#PULL STUDIO DATA

dbhandle <- odbcDriverConnect('driver={SQL Server};server=SERVER,PORT;database=DATABASE;trusted_connection=true')

df <- sqlQuery(dbhandle, 
               "
               QUERY TO GET DATA USED TO DETERMINE WHICH ITEMS TO SEARCH
               
               ")

#SORT BY QTY
#EXTRACT TOP STUDIOS IN CHARACTER LIST
df            <- df[order(-df$Qty),]
search.term1  <- as.character(df$Studio[1])
search.term2  <- as.character(df$Studio[2])
search.term3  <- as.character(df$Studio[3])
search.term4  <- as.character(df$Studio[4])
search.term5  <- as.character(df$Studio[5])

#FUNCTION TO LOCATE ALL BREAKOUT TEXT
find.breakout.terms <- function(subject, value) {
#browser()  
  
  output <- data.frame()
  breakout.pct  <- vector()
  breakout.pct  <- as.character(output)
  breakout.term <- vector()
  
  for (i in 1:length(subject)) { 
   if(grepl("%", subject[i])) {
     
     breakout.pct[i]  <- subject[i]
     breakout.term[i] <- value[i]
     
   }else{
     
     next
    }
    
  }
  breakout.pct  <- breakout.pct[!is.na(breakout.pct)]
  breakout.term <- breakout.term[!is.na(breakout.term)]
  
  output <- data.frame(breakout.pct, breakout.term)
  return(output)  
}

#GET HITS DATA FOR TOP GROSSING STUDIOS
trend.data1   <- gtrends(search.term1, time = "today 12-m")
subj1 <- trend.data1$related_topics$subject
val1 <- trend.data1$related_topics$value
trend.data1.breakouts <- find.breakout.terms(subj1, val1)

column.name1 <- paste('Breakout Terms', toString(search.term1))
column.name2 <- paste('Search Terms', toString(search.term1))
colnames(trend.data1.breakouts) <- c(column.name1, column.name2)


trend.data2   <- gtrends(search.term2, time = "today 12-m")
subj2 <- trend.data2$related_topics$subject
val2 <- trend.data2$related_topics$value
trend.data2.breakouts <- find.breakout.terms(subj2, val2)

column.name1 <- paste('Breakout Terms', toString(search.term2))
column.name2 <- paste('Search Terms', toString(search.term2))
colnames(trend.data2.breakouts) <- c(column.name1, column.name2)

trend.data3   <- gtrends(search.term3, time = "today 12-m")
subj3 <- trend.data3$related_topics$subject
val3 <- trend.data3$related_topics$value
trend.data3.breakouts <- find.breakout.terms(subj3, val3)

column.name1 <- paste('Breakout Terms', toString(search.term3))
column.name2 <- paste('Search Terms', toString(search.term3))
colnames(trend.data3.breakouts) <- c(column.name1, column.name2)

trend.data4   <- gtrends(search.term4, time = "today 12-m")
subj4 <- trend.data4$related_topics$subject
val4 <- trend.data4$related_topics$value
trend.data4.breakouts <- find.breakout.terms(subj4, val4)

column.name1 <- paste('Breakout Terms', toString(search.term4))
column.name2 <- paste('Search Terms', toString(search.term4))
colnames(trend.data4.breakouts) <- c(column.name1, column.name2)

trend.data5   <- gtrends(search.term5, time = "today 12-m")
subj5 <- trend.data5$related_topics$subject
val5 <- trend.data5$related_topics$value
trend.data5.breakouts <- find.breakout.terms(subj5, val5)

column.name1 <- paste('Breakout Terms', toString(search.term5))
column.name2 <- paste('Search Terms', toString(search.term5))
colnames(trend.data5.breakouts) <- c(column.name1, column.name2)