План прост:

1. Поставить Mysql на машину
2. Создать там нужное бд и таблицы
3. Загрузить данные за позавчерашний день через R
4. Настроить скрипт загрузки с динамическими датами http://sotnik.biz.ua/blog/ppc_in_power_bi/
5. Запустить по крону его через 5 минут https://2steps.pro/r-script-launching-with-task-scheduler.html
6. Если все ок, то ставим крон на 9.30

 

 

1. Поставить Mysql на машину

Скачиваем mysql-installer-web-community-5.7.19.0 и следуем установщику. Если что, то гуглим.

Чтобы войти в мускул:

mysql -u root -p

2. Создать там нужное бд и таблицы

Так как кодировка R и винды cp1251, то нужно создать дб в этой же кодировки.

Важно чтобы кодировка в скрипте и в БД была одинаковая. Проверить кодировку в R можно так Sys.getlocale()

CREATE DATABASE organic_data1 CHARACTER SET cp1251 COLLATE cp1251_general_ci;

А затем нужные таблицы:

CREATE TABLE ga_organic_data(
 date DATE,
 sourceMedium VARCHAR(256),
 landingPagePath VARCHAR(256),
 deviceCategory VARCHAR(256),
 hostname VARCHAR(256),
 sessions INTEGER,
 transactions INTEGER,
 transactionRevenue DECIMAL
 );

3. Загрузить данные за позавчерашний день через R

Коннектимся к бд и загружаем:

conMySQL1 <- dbConnect(MySQL(), dbname = "organic_data1", user = "root", password = "makarovs", host = "localhost",port=3306)

dbWriteTable (conMySQL1, "ga_organic_data", ga_organic_data, row.names = FALSE, append = TRUE)

Сложность была в определении hostname и имени пользователя. Помогла статья: https://stackoverflow.com/questions/4093603/how-do-i-find-out-my-mysql-url-host-port-and-username 

4. Настроить скрипт загрузки с динамическими датами

library(RMySQL)
library("googleAuthR")
library("googleAnalyticsR")
library("jsonlite")


conMySQL1 <- dbConnect(MySQL(), dbname = "organic_data1", user = "root", password = "makarovs", host = "localhost",port=3306)

old_data <- dbReadTable(conMySQL1, "ga_organic_data")
last_date <- max(old_data$date)
start_date <- as.character.Date(as.Date(last_date)+1)
end_date <- as.character.Date(Sys.Date() - 1)


# Сюда вставить то, что в json-файле. Обязательно заменить обратный слеш \ на двойной \\
json <- '{

}
'

# функция для авторизации
gar_auth_service <- function(json, scope = getOption("googleAuthR.scopes.selected")){
 
 endpoint <- httr::oauth_endpoints("google")
 
 secrets <- jsonlite::fromJSON(json)
 scope <- paste(scope, collapse=" ")
 
 if(is.null(secrets$private_key)){
 stop("$private_key not found in JSON - have you downloaded the correct JSON file? 
 (Service Account Keys, not service account client)")
 }
 
 google_token <- httr::oauth_service_token(endpoint, secrets, scope)
 
 Authentication$set("public", "token", google_token, overwrite=TRUE)
 Authentication$set("public", "method", "service_json", overwrite=TRUE)
 
 return(invisible(Authentication$public_fields$token))
 
 }

# авторизируемся
gar_auth_service(
 json = json,
 scope = "https://www.googleapis.com/auth/analytics"
)

# id представления GA
ga_view <- '46195197'


df <- dim_filter("sourceMedium","REGEXP","(google / organic|yandex / organic)")
fc <- filter_clause_ga4(list(df))


# Выгружаем данные
ga_organic_data <- google_analytics_4(ga_view, 
 date_range = c(start_date,end_date),
 dimensions=c('date','sourceMedium','landingPagePath','deviceCategory','hostname'), 
 metrics = c('sessions','transactions','transactionRevenue'),
 dim_filters = fc,
 anti_sample = TRUE)


dbWriteTable (conMySQL1, "ga_organic_data", ga_organic_data, row.names = FALSE, append = TRUE)

dbDisconnect(conMySQL1)

5. Запустить по крону его через 5 минут