I have the following script in R, which uploads data from Google analytics-API to a BigQuery table. The code worked until a few weeks ago, but without any reason, a warning became to be shown:
#### Autenticaciones ####
if (!require("pacman")) install.packages("pacman")
pacman::p_load(ggplot2, taskscheduleR, miniUI, shiny, tidyr, googlesheets4, googlesheets, sqldf,googleAnalyticsR, writexl, RMySQL, DBI, readr, dplyr,bigQueryR,stringr,lubridate,bigrquery,readr,telegram.bot,googleAuthR,adjust)
#taskscheduleR::taskschedulerAddin()
project_id <- "ace-amplifier-455"
scopes = c("https://www.googleapis.com/auth/bigquery",
"https://www.googleapis.com/auth/bigquery.insertdata",
"https://www.googleapis.com/auth/analytics",
"https://www.googleapis.com/auth/webmasters")
# Autenticación BigQuery read Data
bq_auth(path="bq.json")
# Autenticación BigQuery Upload Data
options(googleAuthR.scopes.selected = scopes)
Sys.setenv(BQ_AUTH_FILE = "bq.json")
bqr_auth("bq.json")
# Autenticación Google Analytics
gar_auth_service("ga.json")
#### Date range ####
maxDate <- paste("SELECT max(date) FROM `ace-amplifier-455.searches.searches_by_platform`
WHERE Date > DATE_SUB(CURRENT_DATE(), INTERVAL 10 DAY)", sep="")
####LLAMADO API DATOS FUNNEL CHILE WEB ####
maxDate<- bq_project_query(project_id, maxDate, use_legacy_sql=FALSE)
maxDate<- bq_table_download(maxDate)
maxDate <-ymd(maxDate$f0_)
## Se definen variables tipo Date, para descargar siempre el día anterior #
today <- maxDate+1
today2 <- Sys.Date()-1
if (Sys.Date() >= maxDate)
{
#### CHILE WEB ####
searchCHweb<- google_analytics(viewId = "89511434",
date_range = c(today,today2),
metrics =c("ga:totalEvents","ga:transactions", "ga:transactionRevenue" ),
dimensions = c("ga:Date", "ga:eventAction","ga:eventLabel"),
filtersExpression = c("ga:eventAction=~Búsqueda;ga:eventCategory==Búsquedas en el sitio"),
anti_sample = TRUE)
searchCHweb$Date<- ymd(searchCHweb$Date)
searchCHweb<- searchCHweb %>% mutate(Country=c("Chile"))%>%
mutate(Platform=c("WEB"))
#### MEXICO WEB ####
searchMXweb<- google_analytics(viewId = "79705518",
date_range = c(today,today2),
metrics =c("ga:totalEvents","ga:transactions", "ga:transactionRevenue" ),
dimensions = c("ga:Date", "ga:eventAction","ga:eventLabel"),
filtersExpression = c("ga:eventAction=~Búsqueda;ga:eventCategory==Búsquedas en el sitio"),
anti_sample = TRUE)
searchMXweb$Date<- ymd(searchMXweb$Date)
searchMXweb<- searchMXweb %>% mutate(Country=c("Mexico"))%>%
mutate(Platform=c("WEB"))
#### COLOMBIA WEB ####
searchCOweb<- google_analytics(viewId = "80392523",
date_range = c(today,today2),
metrics =c("ga:totalEvents","ga:transactions", "ga:transactionRevenue" ),
dimensions = c("ga:Date", "ga:eventAction","ga:eventLabel"),
filtersExpression = c("ga:eventAction=~Búsqueda;ga:eventCategory==Búsquedas en el sitio"),
anti_sample = TRUE)
searchCOweb$Date<- ymd(searchCOweb$Date)
searchCOweb<- searchCOweb %>% mutate(Country=c("Colombia"))%>%
mutate(Platform=c("WEB"))
#### PERU WEB ####
searchPEweb<- google_analytics(viewId = "80948718",
date_range = c(today,today2),
metrics =c("ga:totalEvents","ga:transactions", "ga:transactionRevenue" ),
dimensions = c("ga:Date", "ga:eventAction","ga:eventLabel"),
filtersExpression = c("ga:eventAction=~Búsqueda;ga:eventCategory==Búsquedas en el sitio"),
anti_sample = TRUE)
searchPEweb$Date<- ymd(searchPEweb$Date)
searchPEweb<- searchPEweb %>% mutate(Country=c("Peru"))%>%
mutate(Platform=c("WEB"))
searchesWeb <- rbind(searchCHweb, searchMXweb, searchCOweb, searchPEweb)
searchesWeb <- searchesWeb %>% select(-2)%>%mutate(eventAction=c("SearchButton"))%>%
select(Date,eventAction,eventLabel,transactions,totalEvents,transactions,transactionRevenue,Country,Platform)
#### CHILE Android ####
searchCHAndroid<- google_analytics(viewId = "94361755",
date_range = c(today,today2),
metrics =c("ga:totalEvents","ga:transactions", "ga:transactionRevenue" ),
dimensions = c("ga:Date", "ga:eventAction","ga:eventLabel"),
filtersExpression = c("ga:eventCategory==Search"),
anti_sample = TRUE)
searchCHAndroid$Date<- ymd(searchCHAndroid$Date)
searchCHAndroid<- searchCHAndroid %>% mutate(Country=c("Chile"))%>%
mutate(Platform=c("Android"))
#### MEXICO Android ####
searchMXAndroid<- google_analytics(viewId = "72908168",
date_range = c(today,today2),
metrics =c("ga:totalEvents","ga:transactions", "ga:transactionRevenue" ),
dimensions = c("ga:Date", "ga:eventAction","ga:eventLabel"),
filtersExpression = c("ga:eventCategory==Search"),
anti_sample = TRUE)
searchMXAndroid$Date<- ymd(searchMXAndroid$Date)
searchMXAndroid<- searchMXAndroid %>% mutate(Country=c("Mexico"))%>%
mutate(Platform=c("Android"))
#### COLOMBIA Android ####
searchCOAndroid<- google_analytics(viewId = "72918707",
date_range = c(today,today2),
metrics =c("ga:totalEvents","ga:transactions", "ga:transactionRevenue" ),
dimensions = c("ga:Date", "ga:eventAction","ga:eventLabel"),
filtersExpression = c("ga:eventCategory==Search"),
anti_sample = TRUE)
searchCOAndroid$Date<- ymd(searchCOAndroid$Date)
searchCOAndroid<- searchCOAndroid %>% mutate(Country=c("Colombia"))%>%
mutate(Platform=c("Android"))
#### PERU Android ####
searchPEAndroid<- google_analytics(viewId = "72909649",
date_range = c(today,today2),
metrics =c("ga:totalEvents","ga:transactions", "ga:transactionRevenue" ),
dimensions = c("ga:Date", "ga:eventAction","ga:eventLabel"),
filtersExpression = c("ga:eventCategory==Search"),
anti_sample = TRUE)
searchPEAndroid$Date<- ymd(searchPEAndroid$Date)
searchPEAndroid<- searchPEAndroid %>% mutate(Country=c("Peru"))%>%
mutate(Platform=c("Android"))
searchesAndroid <- rbind(searchCHAndroid, searchMXAndroid, searchCOAndroid, searchPEAndroid)
#### CHILE IOS ####
searchCHIOS<- google_analytics(viewId = "94362277",
date_range = c(today,today2),
metrics =c("ga:totalEvents","ga:transactions", "ga:transactionRevenue" ),
dimensions = c("ga:Date", "ga:eventAction","ga:eventLabel"),
filtersExpression = c("ga:eventCategory==Search"),
anti_sample = TRUE)
searchCHIOS$Date<- ymd(searchCHIOS$Date)
searchCHIOS<- searchCHIOS %>% mutate(Country=c("Chile"))%>%
mutate(Platform=c("IOS"))
#### MEXICO IOS ####
searchMXIOS<- google_analytics(viewId = "81137798",
date_range = c(today,today2),
metrics =c("ga:totalEvents","ga:transactions", "ga:transactionRevenue" ),
dimensions = c("ga:Date", "ga:eventAction","ga:eventLabel"),
filtersExpression = c("ga:eventCategory==Search"),
anti_sample = TRUE)
searchMXIOS$Date<- ymd(searchMXIOS$Date)
searchMXIOS<- searchMXIOS %>% mutate(Country=c("Mexico"))%>%
mutate(Platform=c("IOS"))
#### COLOMBIA IOS ####
searchCOIOS<- google_analytics(viewId = "81143200",
date_range = c(today,today2),
metrics =c("ga:totalEvents","ga:transactions", "ga:transactionRevenue" ),
dimensions = c("ga:Date", "ga:eventAction","ga:eventLabel"),
filtersExpression = c("ga:eventCategory==Search"),
anti_sample = TRUE)
searchCOIOS$Date<- ymd(searchCOIOS$Date)
searchCOIOS<- searchCOIOS %>% mutate(Country=c("Colombia"))%>%
mutate(Platform=c("IOS"))
#### PERU IOS ####
searchPEIOS<- google_analytics(viewId = "81141054",
date_range = c(today,today2),
metrics =c("ga:totalEvents","ga:transactions", "ga:transactionRevenue" ),
dimensions = c("ga:Date", "ga:eventAction","ga:eventLabel"),
filtersExpression = c("ga:eventCategory==Search"),
anti_sample = TRUE)
searchPEIOS$Date<- ymd(searchPEIOS$Date)
searchPEIOS<- searchPEIOS %>% mutate(Country=c("Peru"))%>%
mutate(Platform=c("IOS"))
searchesIOS <- rbind(searchCHIOS, searchMXIOS, searchCOIOS, searchPEIOS)
searchesComplete <- rbind(searchesWeb,searchesAndroid,searchesIOS)
####################################################################
# No se han encontrado resultados KEYWORDS #
#### CHILE WEB ####
searchCHweb<- google_analytics(viewId = "89511434",
date_range = c(today,today2),
metrics =c("ga:searchUniques"),
dimensions = c("ga:Date", "ga:pageTitle","ga:searchKeyword"),
filtersExpression = c("ga:pageTitle=~No se han encontrado resultados"),
anti_sample = TRUE)
searchCHweb$Date<- ymd(searchCHweb$Date)
searchCHweb<- searchCHweb %>% mutate(Country=c("Chile"))%>%
mutate(Platform=c("WEB"))
#### MEXICO WEB ####
searchMXweb<- google_analytics(viewId = "79705518",
date_range = c(today,today2),
metrics =c("ga:searchUniques"),
dimensions = c("ga:Date", "ga:pageTitle","ga:searchKeyword"),
filtersExpression = c("ga:pageTitle=~No se han encontrado resultados"),
anti_sample = TRUE)
searchMXweb$Date<- ymd(searchMXweb$Date)
searchMXweb<- searchMXweb %>% mutate(Country=c("Mexico"))%>%
mutate(Platform=c("WEB"))
#### COLOMBIA WEB ####
searchCOweb<- google_analytics(viewId = "80392523",
date_range = c(today,today2),
metrics =c("ga:searchUniques"),
dimensions = c("ga:Date", "ga:pageTitle","ga:searchKeyword"),
filtersExpression = c("ga:pageTitle=~No se han encontrado resultados"),
anti_sample = TRUE)
searchCOweb$Date<- ymd(searchCOweb$Date)
searchCOweb<- searchCOweb %>% mutate(Country=c("Colombia"))%>%
mutate(Platform=c("WEB"))
#### PERU WEB ####
searchPEweb<- google_analytics(viewId = "80948718",
date_range = c(today,today2),
metrics =c("ga:searchUniques"),
dimensions = c("ga:Date", "ga:pageTitle","ga:searchKeyword"),
filtersExpression = c("ga:pageTitle=~No se han encontrado resultados"),
anti_sample = TRUE)
searchPEweb$Date<- ymd(searchPEweb$Date)
searchPEweb<- searchPEweb %>% mutate(Country=c("Peru"))%>%
mutate(Platform=c("WEB"))
noResults <- rbind(searchCHweb, searchMXweb, searchCOweb, searchPEweb)
noResults <- noResults%>%rename(eventLabel="searchKeyword")
searchesComplete2 <- searchesComplete %>% full_join(noResults, by=c("Date","Country","Platform","eventLabel"))
#### SUBIR A BIGQUERY ####
project_id <- "ace-amplifier-455"
scopes = c("https://www.googleapis.com/auth/bigquery",
"https://www.googleapis.com/auth/bigquery.insertdata")
options(googleAuthR.scopes.selected = scopes)
Sys.setenv(BQ_AUTH_FILE = "bq.json")
bqr_auth("bq.json")
bqr_upload_data(projectId = "ace-amplifier-455", datasetId = "searches",
tableId = "searches_by_platform", upload_data = searchesComplete2,
create = "CREATE_IF_NEEDED",
writeDisposition = "WRITE_APPEND",
schema = schema_fields(searchesComplete2))
} else
{"since es menor"}
This code works, but one day without reason, stops working, showing me the following error:
Error in if (Sys.Date() >= maxDate) { :
valor ausente donde TRUE/FALSE es necesario
Any hints about how to fix it?
Aucun commentaire:
Enregistrer un commentaire