Скрипт для экспорта статистики рекламных кампаний

[Man]

Одним из главных преимуществ МСС было, что можно было всю статистику с акков собирать в одном месте. Если же работать с обычных акков, то появляется небольшая проблема, что нужно заходить на каждый акк отдельно. И для этого был написан скрипт, который собирает и передает всю статку в гугл таблицы.

Для удобства работы со скриптом дам несколько советов:

1. Название кампании прописывайте не стандартное, а с нумерацией, которая будет понятная вам, что это статка именно с этого акка.
2. Статистику лучше выгружать или под конец дня, чтобы на след. день посмотреть, как акк открутил, или продублировать скрипт, чтобы он срабатывал несколько раз за день, а то если поставить, чтобы скрипт срабатывал только утром или в обед, то сложно получить объективную статистику.
3. Столбцы, которые вам не нужны, по типу названия группы объявлений и самого объявления можно сгруппировать, чтобы таблица занимала меньше места на экране.
Дальше, чтобы самому не тыкать много букв, то снова попросил чат гпт расписать суть работы скрипта:

1. Инициализация таблицы и листа
- Открывает таблицу по ID, указанному в SPREADSHEET_ID.
- Проверяет, существует ли лист с названием SHEET_NAME. Если его нет, создается новый лист с этим названием.

2. Настройка заголовков и начальной строки
- Проверяет значение ячейки Y1, которая хранит номер следующей строки для записи данных.
- Если в Y1 ничего нет (первый запуск), добавляет заголовки столбцов на первую строку и форматирует их:
- Устанавливает значение nextRow в 2, если это первая запись.

3. Получение текущих данных аккаунта
- Сохраняет текущую дату и время в формате yyyy-MM-dd HH:mm:ss (например, 2024-11-23 10:00:00).
- Получает данные аккаунта: ID клиента и валюту.

4. Получение статистики за все время
- Выполняет запрос AD_PERFORMANCE_REPORT с данными по кампаниям, группам объявлений и объявлениям (показы, клики, конверсии, стоимость) за все время.
- Создает объект lifetimeStats, где ключом является комбинация названия кампании, группы объявлений и заголовка объявления, а значениями — данные по метрикам за все время.

5. Получение статистики за сегодня
- Выполняет запрос AD_PERFORMANCE_REPORT для получения данных за текущий день: показы, клики, конверсии, стоимость и статусы (кампаний, групп объявлений, объявлений).
- Переходит к построчной обработке данных.

6. Обработка данных и запись в таблицу
- Для каждой строки отчета за сегодня:
1. Формирует ключ (комбинация названий кампании, группы и объявления) для сопоставления с данными за все время.
2. Определяет статусы кампании, группы объявлений и объявления. Если статус включен (enabled), но показы равны 0, статус меняется на not eligible.
3. Рассчитывает CPA (стоимость за конверсию) для данных за сегодня и за все время.
4. Записывает данные в таблицу в формате:
- Общие данные: дата обновления, названия и статусы (кампании, группы, объявления), валюта.
- Метрики за сегодня: показы, клики, конверсии, CPA, стоимость.
- Метрики за все время: показы, клики, конверсии, CPA, стоимость.
- ID аккаунта.
5. Подсвечивает статусы:
- Зеленый фон (#34a853) — если статус включен (enabled).
- Красный фон (#ea4335) — если статус отключен или недоступен.
- Увеличивает номер строки для следующей записи.

7. Форматирование новых данных
- Если были добавлены новые строки, форматирует их:
- Добавляет границы, устанавливает выравнивание по центру.

8. Обновление номера следующей строки
- Обновляет ячейку Y1 значением номера следующей строки (currentRow).

9. Создание или обновление фильтра
- Проверяет, установлен ли фильтр в таблице. Если нет, добавляет фильтр на весь диапазон данных.

10. Логирование результата
- Записывает в лог:
- Сколько строк добавлено.
- Номер следующей строки для записи.

var SPREADSHEET_ID = 'ИД_ГУГЛ_ТАБЛИЦЫ';
var SHEET_NAME = 'Account Statistics';

function main() {
  var spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID);
  var sheet = spreadsheet.getSheetByName(SHEET_NAME) || spreadsheet.insertSheet(SHEET_NAME);
 
  // Получаем или инициализируем значение следующей строки из Y1
  var nextRowCell = sheet.getRange('Y1');
  var nextRow = nextRowCell.getValue();
 
  // Если значение пустое или некорректное, начинаем с создания заголовков - канал tg @OOGLinks
  if (!nextRow || isNaN(nextRow)) {
    // Создаем заголовки только если это первый запуск
    sheet.getRange(1, 1, 1, 19).setValues([[
      'Дата обновления',
      'Название кампании',
      'Статус кампании',
      'Название группы объявлений',
      'Статус группы объявлений',
      'Название объявления',
      'Статус объявления',
      'Валюта аккаунта',
      'Количество показов за сегодня',
      'Количество кликов за сегодня',
      'Количество конверсий на аккаунте за сегодня',
      'Цена конверсии за сегодня',
      'Расход на аккаунте за сегодня',
      'Количество показов за все время',
      'Количество кликов за все время',
      'Количество конверсий на аккаунте за все время',
      'Цена конверсии за все время',
      'Расход на аккаунте за все время',
      'Account ID'
    ]]);
    
    // Форматируем заголовки
    sheet.getRange(1, 1, 1, 19)
      .setBackground('#4285f4')
      .setFontColor('#ffffff')
      .setFontWeight('bold');
    
    // Автоматически подгоняем ширину столбцов ТОЛЬКО при первом создании
    sheet.autoResizeColumns(1, 19);
    
    nextRow = 2;
  }

  var today = new Date();
  var formattedDateTime = Utilities.formatDate(today, 'Europe/Kiev', 'yyyy-MM-dd HH:mm:ss');
 
  var account = AdWordsApp.currentAccount();
  var accountId = account.getCustomerId();
  var currency = account.getCurrencyCode();

  var lifetimeStats = {};

  var reportLifetime = AdWordsApp.report(
    "SELECT CampaignName, AdGroupName, Headline, Impressions, Clicks, Conversions, Cost " +
    "FROM AD_PERFORMANCE_REPORT " +
    "DURING ALL_TIME");
    
  var lifetimeRows = reportLifetime.rows();
  while (lifetimeRows.hasNext()) {
    var row = lifetimeRows.next();
    var key = row['CampaignName'] + '|' + row['AdGroupName'] + '|' + (row['Headline'] || 'N/A');
    lifetimeStats[key] = {
      impressions: parseInt(row['Impressions']) || 0,
      clicks: parseInt(row['Clicks']) || 0,
      conversions: parseFloat(row['Conversions']) || 0,
      cost: parseFloat(row['Cost']) || 0
    };
  }

  // Получаем статистику за сегодня - канал tg @OOGLinks
  var reportToday = AdWordsApp.report(
    "SELECT CampaignName, CampaignStatus, AdGroupName, AdGroupStatus, " +
    "Headline, Status, Impressions, Clicks, Conversions, Cost " +
    "FROM AD_PERFORMANCE_REPORT " +
    "DURING TODAY");

  var currentRow = nextRow;
  var rowsToday = reportToday.rows();

  while (rowsToday.hasNext()) {
    var row = rowsToday.next();
    var key = row['CampaignName'] + '|' + row['AdGroupName'] + '|' + (row['Headline'] || 'N/A');
    
    // Обработка статусов
    var campaignStatus = row['CampaignStatus'] ? row['CampaignStatus'].toLowerCase() : 'not eligible';
    var adGroupStatus = row['AdGroupStatus'] ? row['AdGroupStatus'].toLowerCase() : 'not eligible';
    var adStatus = row['Status'] ? row['Status'].toLowerCase() : 'not eligible';
    
    // Если статус enabled, но показы 0, меняем на not eligible
    if (campaignStatus === 'enabled' && parseInt(row['Impressions']) === 0) {
      campaignStatus = 'not eligible';
    }
    if (adGroupStatus === 'enabled' && parseInt(row['Impressions']) === 0) {
      adGroupStatus = 'not eligible';
    }
    if (adStatus === 'enabled' && parseInt(row['Impressions']) === 0) {
      adStatus = 'not eligible';
    }
    
    var impressionsToday = parseInt(row['Impressions']) || 0;
    var clicksToday = parseInt(row['Clicks']) || 0;
    var conversionsToday = parseFloat(row['Conversions']) || 0;
    var costToday = parseFloat(row['Cost']) || 0;
    var cpaToday = conversionsToday > 0 ? (costToday / conversionsToday).toFixed(2) : '-';

    var lifetimeData = lifetimeStats[key] || {
      impressions: 0,
      clicks: 0,
      conversions: 0,
      cost: 0
    };
    var cpaLifetime = lifetimeData.conversions > 0 ?
      (lifetimeData.cost / lifetimeData.conversions).toFixed(2) : '-';
    
    sheet.getRange(currentRow, 1, 1, 19).setValues([[
      formattedDateTime,
      row['CampaignName'],
      campaignStatus,
      row['AdGroupName'],
      adGroupStatus,
      row['Headline'] || 'N/A',
      adStatus,
      currency,
      impressionsToday,
      clicksToday,
      conversionsToday,
      cpaToday,
      costToday.toFixed(2),
      lifetimeData.impressions,
      lifetimeData.clicks,
      lifetimeData.conversions,
      cpaLifetime,
      lifetimeData.cost.toFixed(2),
      accountId
    ]]);
    
    // Подсвечиваем статусы
    if (campaignStatus === 'enabled') {
      sheet.getRange(currentRow, 3).setBackground('#34a853');
    } else {
      sheet.getRange(currentRow, 3).setBackground('#ea4335');
    }
    
    if (adGroupStatus === 'enabled') {
      sheet.getRange(currentRow, 5).setBackground('#34a853');
    } else {
      sheet.getRange(currentRow, 5).setBackground('#ea4335');
    }
    
    if (adStatus === 'enabled') {
      sheet.getRange(currentRow, 7).setBackground('#34a853');
    } else {
      sheet.getRange(currentRow, 7).setBackground('#ea4335');
    }
    
    currentRow++;
  }

  // Форматируем только новые строки (без изменения размера колонок)
  if (currentRow > nextRow) {
    var newDataRange = sheet.getRange(nextRow, 1, currentRow - nextRow, 19);
    newDataRange.setBorder(true, true, true, true, true, true, 'black', SpreadsheetApp.BorderStyle.SOLID);
    newDataRange.setHorizontalAlignment('center');
  }

  // Обновляем номер следующей строки в Y1
  nextRowCell.setValue(currentRow);

  // Добавляем или обновляем фильтр для всей таблицы
  if (!sheet.getFilter()) {
    sheet.getRange(1, 1, sheet.getLastRow(), 19).createFilter();
  }

  Logger.log('Экспорт завершен. Добавлено строк: ' + (currentRow - nextRow));
  Logger.log('Следующая строка будет: ' + currentRow);
}