Оперативный контроль рекламы в Яндекс Директе

# Загружаем необходимые библиотеки
library(RMariaDB)     # Подключение к базе данных MariaDB (MySQL)
library(dplyr)        # Для удобной работы с таблицами
library(timeperiodsR) # Для работы с временными периодами (не используется явно, но может пригодиться)
library(lubridate)    # Для удобной работы с датами
library(stringr)      # Для работы со строками
library(tidyr)        # Для заполнения пропущенных значений

# Устанавливаем рабочую директорию
TokenPath <- "C:/Scripts_serv/R/script/bijobs/"
setwd(TokenPath)

# Загружаем переменные окружения из .env файла
readRenviron("C:/Scripts_serv/.env")
bd_login <- Sys.getenv("CONN_BJ") 
bd_pass <- Sys.getenv("CONN_BJ_PASS") 
bd_ip <- Sys.getenv("CONN_HOST") 

# Функция для подключения к базе данных
connection_fn <- function(usr = NULL, pas = NULL){
  dbConnect(drv = MariaDB(), 
            user = usr,
            password = pas,
            host = bd_ip,
            dbname = usr)  # Используем логин как имя базы
}

# Подключаемся к базе и загружаем данные
con <- connection_fn(usr = bd_login, pas = bd_pass)

# Загружаем все данные начиная с 2025 года (можно поменять)
database <- dbGetQuery(con, "
  SELECT * FROM vf_direct 
  WHERE Date >= '2025-01-01'
")
dbDisconnect(con)

# Приводим колонку с датами к формату Date
database <- database %>% mutate(Date = as.Date(Date))

# Определяем с какого месяца начинаем анализ (последние 4 месяца)
date_cutoff <- floor_date(Sys.Date() %m-% months(4), "month")

# Агрегируем данные по IdYandex, Директологу и месяцу
monthly_data <- database %>%
  filter(Date >= date_cutoff) %>%
  mutate(
    year = year(Date),
    month = month(Date)
  ) %>%
  group_by(IdYandex, Директолог, year, month) %>%
  summarise(
    total_cost = sum(Cost, na.rm = TRUE),
    total_conversions = sum(Konversii, na.rm = TRUE),
    .groups = "drop"
  )

# Определяем последний доступный месяц
latest_month <- monthly_data %>%
  arrange(desc(year), desc(month)) %>%
  slice(1) %>%
  select(year, month)

# Текущий месяц — данные для прогноза
current_month <- monthly_data %>%
  inner_join(latest_month, by = c("year", "month")) %>%
  mutate(
    total_cost = as.numeric(total_cost),
    total_conversions = as.numeric(total_conversions),
    cpa_current = ifelse(total_conversions > 0, total_cost / total_conversions, 0)
  ) %>%
  select(IdYandex, Директолог, 
         budget_current = total_cost, 
         conversions_current = total_conversions, 
         cpa_current)

# Рассчитываем средние значения за предыдущие 3 месяца
last_3_months <- monthly_data %>%
  anti_join(latest_month, by = c("year", "month")) %>%
  group_by(IdYandex) %>%
  summarise(
    budget_avg_3m = mean(total_cost, na.rm = TRUE),
    conversions_avg_3m = mean(total_conversions, na.rm = TRUE),
    cpa_avg_3m = ifelse(sum(total_conversions) > 0, sum(total_cost) / sum(total_conversions), 0),
    .groups = "drop"
  )

# Объединяем фактические и средние данные
final_data <- current_month %>%
  filter(!is.na(Директолог)) %>%
  left_join(last_3_months, by = "IdYandex") %>%
  mutate(across(everything(), ~replace_na(.x, 0)))  # Заменяем NA на 0

# Рассчитываем прогнозы и отклонения

# Определяем текущую дату и сколько дней уже прошло в месяце
yesterday <- Sys.Date() - 1
days_passed <- day(yesterday)
total_days <- days_in_month(yesterday)

# Добавляем прогнозы и отклонения от среднего
final_data_t <- final_data %>%
  mutate(
    budget_forecast = (budget_current / days_passed) * total_days,
    conversions_forecast = (conversions_current / days_passed) * total_days,
    
    budget_deviation = ((budget_forecast / budget_avg_3m) - 1) * 100,
    conversions_deviation = ((conversions_forecast / conversions_avg_3m) - 1) * 100,
    cpa_deviation = (cpa_current / cpa_avg_3m - 1) * 100
  ) %>%
  select(IdYandex, Директолог,
         budget_current, budget_forecast, budget_deviation,
         conversions_current, conversions_forecast, conversions_deviation,
         cpa_current, cpa_deviation)

# Анализируем проблемные аккаунты
problematic_accounts <- final_data_t %>%
  mutate(
    alert_level = case_when(
      budget_deviation < -40 | budget_deviation > 40 ~ "CRITICAL",
      conversions_deviation < -50 ~ "CRITICAL",
      cpa_deviation > 50 ~ "CRITICAL",
      budget_deviation < -30 | budget_deviation > 30 ~ "WARNING",
      conversions_deviation < -30 ~ "WARNING",
      cpa_deviation > 30 ~ "WARNING",
      TRUE ~ "NORMAL"
    ),
    # Формируем описание проблемы на основе отклонений
    reason = str_trim(str_c(
      ifelse(budget_deviation < -40, "Резкое сокращение бюджета, ", ""),
      ifelse(budget_deviation > 40, "Сильный рост бюджета, ", ""),
      ifelse(conversions_deviation < -50, "Резкое падение конверсий, ", ""),
      ifelse(cpa_deviation > 50, "Существенное увеличение CPA, ", ""),
      ifelse(budget_deviation < -30 & budget_deviation >= -40, "Заметное сокращение бюджета, ", ""),
      ifelse(budget_deviation > 30 & budget_deviation <= 40, "Заметный рост бюджета, ", ""),
      ifelse(conversions_deviation < -30 & conversions_deviation >= -50, "Падение конверсий, ", ""),
      ifelse(cpa_deviation > 30 & cpa_deviation <= 50, "Рост CPA, ", "")
    )) %>%
    str_remove(", $") %>%
    na_if("") %>%
    replace_na("Нет проблем")
  )

# Теперь у тебя есть:
# 1. `final_data_t` — таблица с метриками по всем аккаунтам
# 2. `problematic_accounts` — таблица с "вниманием" и уровнями отклонений

🛠 Что потребуется дальше?