Как скачать данные из GA c помощью R и сохранить в MySQL на своей машине

План прост:

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, то нужно создать дб в этой же кодировки.

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 минут 

 

Про DAX

http://moonexcel.com.ua/uroki-dax2-calculate_ru

Каким образом мы получили в расчетном поле Count_All_Product цифры 16? Объяснение в том, что мы сняли для этого поля все фильтры, которые были применены в сводной таблице (каждая строка в сводной таблице это отдельный фильтр), с помощью функции ALL(). То есть, другими словами, функция ALL(Demo) дает команду снять все фильтры для расчетного поля Count_All_Product, которые применяются к таблице Demo.

Теперь давайте внесем незначительные изменения в нашу формулу и посмотрим на результат.

=CALCULATE(COUNTROWS(Demo);ALL(Demo[Product]))

Мы изменили аргумент для функции ALL(), заменив Demo на Demo [Product]. Теперь фильтры будут сняты не для всей таблицы Demo, а только для ее столбца Demo[Product].

———————-

https://habrahabr.ru/post/245631/—

Шаг 2. Усложняем: Посчитаем долю каждой записи в общих продажах.

Здесь, как можно заметить окно открывается через все записи датасэта, попробуем аналогичную вещь в PowerPivot:

=[Отгрузка шт]/CALCULATE(SUM([Отгрузка шт]);ALL('Таблица1'))

Основное внимание обратим к знаменателю: Я уже упоминал выше что основное отличие вычисляемого поля от меры заключается в том что в поле формулы считают по горизонтали ( в рамках одной записи) а меры – по вертикали ( в рамках одного атрибута). Типо ALL свнимает всю фильтрацию, используем если нужны статичные данные.

 

Дошборды Гугл Таблицы

Пример: есть много гуглтаблиц (разные книги) с данными из GA. Например, нам нужно сводить сумму по сеансам при условии (например вхождение бренда в URL, или любые другие условия — даты, каналы-источники, названия рекламных кампаний и т.п.).

Допустим — это исходные данные (мы уже выгрузили из GA урлы, сеансы за нужные даты и по нужным каналам-источникам и получили такую табличку): https://docs.google.com/…/1O7LqnwrZkzUZs36KYdrctu3pWP…/edit…

А вот здесь (в другой книге) суммируем все сеансы при условии вхождения «asus» (или что вам там нужно) в URL:

https://docs.google.com/…/1CfAfIrdJFaQVhTp4B-VcVkM13M…/edit…

Здесь очень удобно использовать сочетание QUERY и IMPORTRANGE 🙂

=QUERY(IMPORTRANGE(«1O7LqnwrZkzUZs36KYdrctu3pWPL3R0ToDNi3YnNyp-o», «url!A:B»),»select sum(Col2) where Col1 contains ‘asus’ label sum(Col2) ‘СУММА'»)

Таким способом очень удобно, например делать сводки по (например) брендовым рекламным кампаниям. Т.е. есть РК с вхождениями «бренд» по разным клиентам, нужно сделать сводку по CPO (по всем клиентам). Ну и т.п.