Записываем данные из Google analytics в БД Postgresql при помощи Python

Сразу скажу, что решение не идеальное, но оно работает, если у вас есть рекомендации по улучшению, то добро пожаловать в комментарии.

В одной из прошлых статей мы рассмотрели как получить данные из Google analytics при помощи Python, если вы не знакомы с этой статьей, то лучше начните с неё. Сегодня мы продолжим работу с этими данными. Наша задача настроить ежедневную загрузку данных из Google analytics за прошедший день в базу данных.

Выгружать будем следующие данные:

Date
Client id
Session id
Source/medium
Sessions
Goal

По умолчанию из GA нельзя выгрузить Client id и Session id, нужно настроить custom dimension для сбора этих данных, после этого данные начнут собираться в GA. О том как настроить Client id и Session id можно прочитать у Simo Ahava. Либо можете настроить выгрузку каких-то других данных, которые по умолчанию доступны в API GA, посмотреть можно здесь.

База данных будет установлена локально на моём компьютере под управлением windows 10, скрипт будет размещен там же. Конечно это все можно разместить на linux сервере или воспользоваться google cloud, но в этой статье будет мой комп с windows.

План действий

Установить postgresql, настроить БД
Настроить загрузку данных в БД из GA
Настроить ежедневные выгрузки/загрузки в планировщике windows

1 Установка postgresql, настроить БД

Установка

Переходим на страницу загрузки postgresql, выбираем нужную операционную систему и последнюю версию программы. У меня windows. Выбираем последнюю версию и жмем download.

После того как файл скачается, запускаем его. В появившемся окне жмем next

Выбираем директорию для установки

В окне выбора компонентов выбираем все компоненты и жмем next

Придумайте пароль

Выберите локализацию. Лучше Америку.

Далее еще несколько раз жмем next и ждем пока пройдет установка.

Настройка

Далее в меню пуск находим программу pgadmin и запускаем её. Запустится локальный сервер postgres, а в браузере откроется вкладка для работы с postgresql.

Вводим пароль

По умолчанию у вас уже будет создана БД под названием Postgres, нажмите на неё, а затем на кнопку отмеченную на картинке номером 2 (query tool)

У вас откроется окно для ввода SQL запросов — query editor. Теперь нам надо создать таблицу в которую будем записывать данные из google analytics. Для этого пишем запрос и жмем кнопку f5 чтобы выполнить его.

Теперь у вас должна появиться таблица в разделе schemas → tables, у меня там 21 таблица, но у вас должна быть одна под названием ga.

В этой таблице должны быть 6 столбцов, которые мы создали через SQL запрос.

2 Настройка загрузки данных из GA в БД

В прошлой статье мы рассмотрели, как выгрузить данные из GA Мы возьмем скрипт из этого файла, модифицируем его и дополним блоком для загрузки в БД. Но предварительно нужно установить пару библиотек.

Вам понадобятся библиотеки gaapi4py, её установку мы рассмотрели в статье про GA аналогично нужно установить библиотеку psycopg2. Для этого в командной строке windows и выполняем команду

pip install psycopg2

1	pip install psycopg2

Также нам понадобятся библиотеки numpy, pandas, datetime, но их устанавливать не надо если вы установили Python при помощи пакета Anaconda, они уже есть у вас.

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

import datetime # библиотека для работы с датами
from gaapi4py import GAClient # библиотека для работы с API GA
import numpy # библиотека для работы с массивами
import psycopg2 # библиотека для подключения к БД postgresql
# подключаем файл-ключ, через который получаем доступ к api GA
c = GAClient(json_keyfile='etl1.json')

# получаем вчерашнюю дату и записываем в переменную yesterday
yesterday = datetime.date.fromordinal(datetime.date.today().toordinal()-1).strftime("%Y-%m-%d")

# тело запроса отправляемого в GA
request_body = {
    'view_id': '123304920',
    'start_date': yesterday, # ставим переменную со вчерашней датой
    'end_date': yesterday, # ставим переменную со вчерашней датой
    'dimensions': {
        'ga:date',
        'ga:dimension2', #  в этой переменной у меня записан client id
        'ga:dimension3', #  в этой переменной у меня записан session id
        'ga:sourceMedium'
    },
    'metrics': {
        'ga:sessions',
        'ga:goal5Completions',          
        
    },

}

response = c.get_all_data(request_body)

## Записываем ответ GA в dataframe
df=response['data']


# Преобразуем порядок столбцов, потому что из GA каждый раз получаем столбцы в разном порядке, 
# а нам нужны данные одного порядка для передачи в базу
df = df[['date', 'dimension2', 'dimension3', 'sourceMedium', 'sessions', 'goal5Completions']]


# конвертируем pandas df в список кортежей
sessions1 = df.to_records(index=False)


# преобразование данных из массива numpy в список
sessions=numpy.recarray.tolist(sessions1)

# Функция для подключения к БД
def create_connection(db_name, db_user, db_password):
    connection = None
    try:
        connection = psycopg2.connect(
            database=db_name,
            user=db_user,
            password=db_password,

        )
        print("Connection to PostgreSQL DB successful")
    except OperationalError as e:
        print(f"The error '{e}' occurred")
    return connection
	
	
# создаем подключение в качестве аргументов функции create_connection указываем название БД, имя пользователя и пароль
connection = create_connection(
    "postgres", "postgres", "password"
)

## Запись полученных строк в БД
sessions_records = ", ".join(["%s"] * len(sessions))

insert_query = (
    f"INSERT INTO ga (date_sessions, client_id, session_id, source_medium, sessions, goal) VALUES {sessions_records}"
)
connection.autocommit = True
cursor = connection.cursor()
cursor.execute(insert_query, sessions)

import datetime # библиотека для работы с датами

from gaapi4py import GAClient # библиотека для работы с API GA

import numpy # библиотека для работы с массивами

import psycopg2 # библиотека для подключения к БД postgresql

# подключаем файл-ключ, через который получаем доступ к api GA

c = GAClient(json_keyfile='etl1.json')

# получаем вчерашнюю дату и записываем в переменную yesterday

yesterday = datetime.date.fromordinal(datetime.date.today().toordinal()-1).strftime("%Y-%m-%d")

# тело запроса отправляемого в GA

request_body = {

'view_id': '123304920',

'start_date': yesterday, # ставим переменную со вчерашней датой

'end_date': yesterday, # ставим переменную со вчерашней датой

'dimensions': {

'ga:date',

'ga:dimension2', # в этой переменной у меня записан client id

'ga:dimension3', # в этой переменной у меня записан session id

'ga:sourceMedium'

'metrics': {

'ga:sessions',

'ga:goal5Completions',

}

response = c.get_all_data(request_body)

## Записываем ответ GA в dataframe

df=response['data']

# Преобразуем порядок столбцов, потому что из GA каждый раз получаем столбцы в разном порядке,

# а нам нужны данные одного порядка для передачи в базу

df = df[['date', 'dimension2', 'dimension3', 'sourceMedium', 'sessions', 'goal5Completions']]

# конвертируем pandas df в список кортежей

sessions1 = df.to_records(index=False)

# преобразование данных из массива numpy в список

sessions=numpy.recarray.tolist(sessions1)

# Функция для подключения к БД

def create_connection(db_name, db_user, db_password):

connection = None

try:

connection = psycopg2.connect(

database=db_name,

user=db_user,

password=db_password,

)

print("Connection to PostgreSQL DB successful")

except OperationalError as e:

print(f"The error '{e}' occurred")

return connection

# создаем подключение в качестве аргументов функции create_connection указываем название БД, имя пользователя и пароль

connection = create_connection(

"postgres", "postgres", "password"

)

## Запись полученных строк в БД

sessions_records = ", ".join(["%s"] * len(sessions))

insert_query = (

f"INSERT INTO ga (date_sessions, client_id, session_id, source_medium, sessions, goal) VALUES {sessions_records}"

)

connection.autocommit = True

cursor = connection.cursor()

cursor.execute(insert_query, sessions)

В будущем этот скрипт необходимо улучшить, добавить обработку ошибок, например если при загрузке данных в БД произойдет ошибка, то нужно сделать так чтобы скрипт инициировал новую загрузку, чтобы мы не потеряли данные из-за ошибки. Но в данной статье обойдемся без этого.

Теперь этот код нужно сохранить в формате файла python, т.е. c расширением py и положить в одну папку с JSON ключом, с помощью которого наш скрипт будет получать доступ к GA. Об этом файле мы подробно говорили в статье как выгрузить данные из GA. Я назвал свой файл connector-ga.py

Для тех кто хочет более подробно изучить как работает модуль psycopg2 и другие способы коннекта с различными СУБД, могу порекомендовать статью.

3 Настройка ежедневной выгрузки/загрузки данных в планировщике windows

Находим в пуске планировщик заданий и запускаем его.

Выбираем пункт создать задачу

На вкладке “Общие” Указываем произвольное название задачи.

На вкладке “Действия” жмем “Создать”

Вводим данные

В пункте программа или сценарий пишем просто python (если python добавлен в переменную среды path). Если вы устанавливали python по моей инструкции, то вам не надо думать о path.

Далее указываем название файла с нашим скриптом и указываем путь к нему. Жмем ок.

Переходим на вкладку “триггеры” и создаем новый.С помощью триггера обозначим время когда должен запускаться наш скрипт.

Выбираем ежедневно, выбираем дату когда начать выгрузку и время, повторять каждый 1 день. Сохраняем все изменения.

Поздравляю мы настроили ежедневную выгрузку данных из GA в базу данных.

После того как произойдет выгрузка, мы можем зайти в БД и посмотреть данные.

Вот пример данных у меня в БД, которые загрузил скрипт.

Теперь к этой БД можно подключиться например через power bi и сделать отчеты/визуализации.

Записываем данные из Google analytics в БД Postgresql при помощи Python

План действий

1 Установка postgresql, настроить БД

Настройка

2 Настройка загрузки данных из GA в БД

3 Настройка ежедневной выгрузки/загрузки данных в планировщике windows

Автор: Юрий Борзило

Рубрики

Свежие записи

Записываем данные из Google analytics в БД Postgresql при помощи Python

План действий

1 Установка postgresql, настроить БД

Настройка

2 Настройка загрузки данных из GA в БД

3 Настройка ежедневной выгрузки/загрузки данных в планировщике windows

Автор: Юрий Борзило

Похожие записи

10 ошибок в настройках систем аналитики интернет-магазина, из-за которых вы принимаете неверные решения

Как google analytics обманывает нас?

Рубрики

Свежие записи