Вивантаження даних з ки та Google Analytics за API
Матеріал складний і розкриває лише базові підходи до автоматизації, щоб задати вам вектор розвитку.
Необхідний стек знань для комфортного читання: ви легко можете скласти кастомний звіт у Google Analytics і Метриці; парсили та фільтрували ядра в Key Collector; знаєте, що таке середньозважена; користуєтеся зведеними таблицями в Excel; знаєте Python, Pandas або маєте невеликий досвід програмування; знаєте, чому в деяких тематиках слова «лапають», а в інших немає
Тож почнемо! У цій статті ми вивантажимо пошукові запити з конверсіями з Яндекс Метрики, які згодом розширюватимемо шляхом кластеризації, відфільтруємо їх та приведемо до леми. Також ми вивантажимо дані для розрахунку ставки Google Analytics.
На поточному етапі складно пояснити, як конкретно використовуватимуться ці дані, але наберіться терпіння, поступово до вас прийде розуміння.
Вивантажуємо дані із Яндекс Метрики за допомогою Excel Power Query
Створюємо новий запит
Переходимо в режим редагування
Вставляємо код функції запиту
Копіювати код можна за посиланням. Автор коду Максим Уваров, дякуємо йому!
Вставляємо параметри запиту
Якщо ви хочете скласти свій власний звіт, призначити фільтри, то вам знадобиться ознайомитися з документацією API Метрикиrel="nofollow">>>
У нашому запиті ми використали такі параметри:
Після натискання Invoke ви побачите прев'ю ваших даних. Якщо при запиті вийшла помилка, то можемо редагувати запит, натиснувши на шестерню Source
Якщо все добре, то натискаємо Close and load і завантажуємо всі данітаблиці.
Яндекс для нас є основним джерелом трафіку, тому пошук Google в рамках статті розглядати не будемо, щоб не ускладнювати
Нормалізація та фільтрація семантичного ядра
Нормалізація, це приведення всіх слів в однину називний відмінок і т.д. Для цього використовуємо сервіс K50
Копіюємо дані з файлу lemmas.csv до нашого основного файлу у вкладку Lemmas. За допомогою функції vlookup (в українському Excel ВПР) підтягуємо лематизовані значення ключових слів із таблиці lemmas.
Все, завдання виконане!
Фільтрування семантичного ядра, чищення
Тепер у нас є лематизований список фраз і нам потрібно його очистити від фраз, які не відповідають вимогам Яндекс Директ. Для цього додаємо всі фрази до Key Collector і натискаємо на іконку фільтра у стовпчику «Фраза»
Яндекс Директ не приймає як фрази слова зі складом більше 7 слів і фрази зі спеціальними символами, тому видаляємо їх.
Далі фільтруємо слова через список стоп-слів, тобто, видаляємо з нашого списку фрази, які містять стоп-слова. Хороша збірка стоп-слів знаходиться за посиланням
Вивантаження з Google Analytics середнього чека та конверсії з URL
Теорія
Відповідно до властивостей аукціону Яндекс Директ і Google Adwords, для максимізації прибутку нам необхідно як ставку встановити цінність кліку ключового слова
Ценность клика = Середній чек * Частка маржі в чеку * Конверсія сайта
Є ще портфельна теорія призначення ставок, вона дозволяє підняти прибуток на 10-20%, але в рамках статті її не розглядаємо, щоби не ускладнювати.
Що це для нас означає? — Нам потрібно зібрати історичні дані конверсії тасереднього чека в розрізі URL сайту та ключових фраз. Ці дані ми використовуватимемо для призначення ставок.
Не можете зіштовхувати, що до чого? Так, це важко, але ви все зрозумієте, коли ми у фінальній статті з'єднаємо всі дані в одній формулі. Тож про все по порядку.
Спочатку зберемо середні чеки та конверсію по всіх URL сайту, це просто. Ці дані можна взяти з історії Google Analytics. Для цього знадобиться Google Spread Sheets та Addon Google Analytics, який ви можете встановити в магазині додатків.
Створюємо новий звіт
Вводимо test, вибираємо свій лічильник та представлення Google Analytic, та натискаємо «Create report»
Вводимо конфігурацію звітів, як на зображенні, і натискаємо Run reports. Розумію, що пояснень до заданих параметрів недостатньо, але це може надто далеко відвести нас від статті. Детальну інформацію ви зможете знайти у документації
У нашому запиті ми використовували такі метрики та параметри:
Тепер копіюємо звіти на нові вкладки та вставляємо лише значення. Тепер нам потрібно змінити крапки на коми, щоб потім відкрити документ в Excel – змінюємо.
Для числових значень задаємо числовий формат.
Оскільки параметр ga:sourceMedium дублює деякі URL, то будуємо зведену таблицю. Принагідно чистимо від небажаних значень та дублів.
Додаємо нове поле, що розраховується = 'ga:transactionRevenue' / 'ga:transactions' , це середній чек.
На виході маємо акуратну таблицю з URL та середніми чеками.
Аналогічні операції проводимо з таблицею конверсії по URL.
Весь документ можна завантажити в Excel.
Вивантаження данихЯндекс Директ з Google Analytics
Вивантажуємо з Google Analytics, як ми робили кілька кроків раніше. На скрині приклад конфігурації звіту. У полі Filters ми використовуємо регулярні вирази.
На виході отримуємо наступне
Все ми зібрали дані, з якими працюватимемо в наступних етапах.