Думаю всем привычен вид отчета Auction insights в Google Ads.

Годами он выглядел вот так скудно, и давал лишь общее представление о том в каком положении относительно конкурентов вы находитесь.

Важно помнить что отчет именно относительный. Он показывает данные из тех аукционов в которых вы участвовали. Если по каким-то причинам ваша реклама была остановлена и за показ вы не конкурировали — то и данные о тех аукционах в вашем отчете не будет.

Однако, некоторое время назад Гугл добавил возможность просматривать данные Auction insights в стандартном конструкторе отчетов. И оказалось что данных там намного больше:

Этот отчет построен на уровне MCC аккаунта, для обычного рекламодателя нужно всё то же самое, за исключением первой колонки.

Теперь данных у нас много, но с такой таблицей работать не очень удобно. Нужно организовать хранение данных, построение по ним отчетов, и их обновление. Я решил сделать это в Google Data Studio.

Отчет к сожалению не получается выгружать по API, поэтому я написал скрипт в Google Apps, который и будет актуализировать данные в отчете.

Сбор данных

На Google Drive была создана папка с общим доступом. Любой из трафик менеджеров имеющих доступ к отчёту и к папке может выгрузить в интерфейсе Google Ads .csv-файл отчета и закинуть его в эту общую папку. Раз в час папку проверяет скрипт, и если находит в ней файл отчета — загружает его в Google BigQuery.

Напрямую файл загрузить не получится, его надо привести к нужному формату. Вот функция обработки файла:

function main() {
  var date = Utilities.formatDate(new Date(), 'GMT', 'yyyyMMdd');

  // папка для отчётов
  var folderCSV = DriveApp.getFolderById('*********************************');
  var files = folderCSV.getFilesByType(MimeType.CSV);
  while (files.hasNext()) {
    var file = files.next();
    var filename = file.getName();

    // обработанные файлы будем переименовывать и удалять, это фильтр чтобы не запутаться
    if (filename.indexOf('__') == -1) {
      var data = file.getBlob().getDataAsString();
      var arr = Utilities.parseCsv(data);
      arr.splice(0, 3);

      // отрезаем заголовки и логируем кол-во строк
      Logger.log(arr.length);
      var uniqueArr = [];
      for (var i = 0; i < arr.length; i++) {
        uniqueArr.push([arr[i][1], 0]);
      }
      uniqueArr = unique(uniqueArr);
      var filterArr = [];

      // а это мы посчитали кол-во уникальных доменов в отчёте
      Logger.log(uniqueArr.length);
      for (var d = 0; d < uniqueArr.length; d++) {
        var line = uniqueArr[d];
        for (var l = 0; l < arr.length; l++) {
          if (uniqueArr[d][0] == arr[l][1]) {
            line[1] = +line[1] + +1;
          }
        }

        // в BigQuery мы будем складывать данные только по доменам которые встречаются в отчете более 100 раз
        if (line[1] >= 100) {
          filterArr.push(line[0]);
        }
      }
      var filteredArr = [];
      for (var f = 0; f < filterArr.length; f++) {
        for (var l = 0; l < arr.length; l++) {
          if (filterArr[f] == arr[l][1]) {
            try {

              // все красиво форматируем и складываем в массив
              arr[l][2] = Utilities.formatDate(new Date(arr[l][2]), "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'");
              arr[l][3] = arr[l][3].replace(/\%/i, '').replace(/<10/i, '1');
              arr[l][4] = arr[l][4].replace(/\%/i, '');
              arr[l][5] = arr[l][5].replace(/\%/i, '');
              arr[l][6] = arr[l][6].trim().replace(/\-\-/i, '0');
              filteredArr.push(arr[l].join(','));
            } catch (e) {
              Logger.log(e);
            }
          }
        }
      }

      // Собираем из массива csv-файл для загрузки в BigQuery
      var newCSVstring = filteredArr.join('\r\n');

      var newFile = folderCSV.createFile('__AU_' + date + '.csv', newCSVstring);
      if (newFile) {
        try {
          file.setName('___DEL__' + date + '.csv');
          file.setTrashed(true);
        } catch(e) {
          Logger.log(e);
        }
        var fileId = newFile.getId();

        // удаляем оригинальный файл, а отформатированный и очищенный отправляем в BigQuery
        loadCsv(fileId);
      }
    } else {
        try {
          file.setName('___DEL__' + date + '.csv');
          file.setTrashed(true);
        } catch(e) {
          Logger.log(e);
        }
    }
    break;
  }
}
function unique(arr) { // убираем повторы
    var tmp = {};
    return arr.filter(function (a) {
        return a in tmp ? 0 : tmp[a] = 1;
    });
}

Для загрузки в BigQuery заведём отдельную функцию. В ней надо будет указать заранее подготовленные проект и датасет в которых скрипт будет создавать таблицы с отчетами.

function loadCsv(csvFileId) {
  var projectId = '***********'; // название проекта
  var datasetId = '***********'; // название датасета
  var date = Utilities.formatDate(new Date(), 'GMT', 'yyyyMMdd');
  var tableId = 'AU_IS_' + date; // название таблицы
  var table = {
    tableReference: {
      projectId: projectId,
      datasetId: datasetId,
      tableId: tableId
    },
    schema: {
      fields: [
        {
          name: 'account', // если у вас не MCC отчёт - это поле вам не понадобится
          type: 'STRING'
        },
        {
          name: 'domain',
          type: 'STRING'
        },
        {
          name: 'keyword',
          type: 'STRING'
        },
        {
          name: 'date',
          type: 'TIMESTAMP'
        },
        {
          name: 'search_impr_share',
          type: 'FLOAT'
        },
        {
          name: 'top_of_page_rate',
          type: 'FLOAT'
        },
        {
          name: 'abs_top_of_page_rate',
          type: 'FLOAT'
        },
        {
          name: 'position_above_rate',
          type: 'FLOAT'
        }
      ]
    }
  };
  table = BigQuery.Tables.insert(table, projectId, datasetId);
  Logger.log('Table created: %s', table.id);

  var file = DriveApp.getFileById(csvFileId);
  var data = file.getBlob().setContentType('application/octet-stream');
  // Создаем задачу на загрузку данных
  var job = {
    configuration: {
      load: {
        destinationTable: {
          projectId: projectId,
          datasetId: datasetId,
          tableId: tableId
        },
        skipLeadingRows: 1
      }
    }
  };
  job = BigQuery.Jobs.insert(job, projectId, data);
  Logger.log('Load job started. Check on the status of it here: ' + 'https://bigquery.cloud.google.com/jobs/%s', projectId);
}

Всё, функцию main () надо поставить на запуск каждый час с помощью триггера, данные из папки будут автоматически попадать в BigQuery.

Визуализация данных

Визуализировать отчёт я решил в Data Studio.

Для начала нам надо создать источник данных. В списке предложенных коннекторов надо выбрать BigQuery, а дальше создать запрос через custom query:

SELECT * FROM `project-name.dataset_name.AU_IS_*`

Такой запрос будет собирать данные для отчёта из нескольких таблиц, позволяя вам добавлять таблицы с данными «на лету».

Переходим к настройке полей:

Для полей с параметрами конкурентов выставляем агрегацию на усреднение.

И строим отображение отчета:

Удобнее всего использовать сочетание сглаженного графика и вывода данных по-недельно.

Дополнительно легко построить отчет вида «конкретны по ключу»:

Отчет с фильтром по ключевому слову

Где можно получить четкую картину конкуренции по ключевой фразе (или всем фразам с вхождением слова), за интересующий вас период.

Или же отчёт «ключи по конкуренту», где можно увидеть насколько обширно конкурент вторгся на вашу территорию, а насколько сильно старается вас победить:

Это, конечно, выдает не всё семантическое ядро конкурента, а только ту его часть в которой вы пересеклись в ваших аукционах. Тем не менее, по составу фраз и их позициям зачастую можно сделать достаточно полезные выводы о стратегии биддинга на интересующим вас домене.

Чуть позже, в отдельной статье расскажу что можно еще узнать о конкурентах имея такие исходные данные.

Реклама

Ещё интересное


Добавить комментарий