Сравнение данных из BigQuery и Google Analytics с помощью Jupyter notebook
Опубликовано: 01 окт 2019
Данная статья показывает на примере, как можно сравнить графически два набора данных в Jupyter notebook. В качестве примера, мы будем рассматривать данные о пользователях нашего сайта из BigQuery и Google Analytics. Под данными из BigQuery в данном случае понимаются данные стриминга, который реализован для Realtime аналитики.
Данный алгоритм можно легко перенести на любые другие наборы данных с одним временным рядом.
Подготовим среду для работы с BigQuery в Jupyter notebook – импортируем необходимые библиотеки и ключ сервис аккаунта для автоматической авторизации:
import os
from google.cloud.bigquery.client import Client
os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = 'datawhbq.json'
bq_client = Client()
assert bq_client is not None
%load_ext google.cloud.bigquery
Импортируем необходимые библиотеки для обработки и визуализации данных с Python Pandas:
import pandas as pd
from datetime import datetime, timedelta
import matplotlib as mpl
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
import numpy as np
И немного форматирования, для более удобного отображения графиков:
mpl.style.use('ggplot')
Эти функции нам пригодятся для автоматического конвертирования строковых данных в формат дат в наших графиках:
from pandas.plotting import register_matplotlib_converters
register_matplotlib_converters()
Получаем данные из BigQuery
Получим вначале данные о пользователях из BigQuery за вчерашний день и занесем их в таблицу bqusers:
%%bigquery bqusers
SELECT COUNT(DISTINCT clientId) bqusers,
DATETIME_TRUNC(DATETIME(TIMESTAMP_SECONDS(timestamp),"Europe/Moscow"), HOUR) hour
FROM `gastreaming.hits`
WHERE _PARTITIONDATE BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY)
AND CURRENT_DATE()
AND DATE(TIMESTAMP_SECONDS(timestamp), "Europe/Moscow") = DATE_SUB(CURRENT_DATE("Europe/Moscow"), INTERVAL 1 DAY)
GROUP BY hour
ORDER BY hour
Проиндексируем датафрейм по колонке, содержащей данные о часе (временной шкале) для удобства:
bqusers.set_index('hour', inplace=True)
bqusers.head()
На основе данного датафрейма можно построить первый график, в котором по оси Х будут часы, по оси Y пользователи (количесвто пользователей в час) по данным из BigQuery:
fig = plt.figure(figsize=(20,5))
ax = fig.add_subplot(1,1,1)
plt.plot(bqusers, color='blue')
plt.ylabel('Количество пользователей в час')
plt.ylim(bottom=0)
ax.xaxis.set_major_locator(mdates.HourLocator(interval = 1)) #чтобы по x шаг был 1 час
ax.xaxis.set_major_formatter(mdates.DateFormatter('%H:%M')) #опционно - отфарматируем значения оси x
Получим те же данные с помощью Reporting API
Загрузим необходимые библиотеки и сервисный ключ для автоматической авторизации:
from googleapiclient.discovery import build
from oauth2client.service_account import ServiceAccountCredentials
from datetime import datetime, timedelta
import pandas as pd
credentials = ServiceAccountCredentials.from_json_keyfile_name('client_secrets.json')
api_client = build('analyticsreporting', 'v4', credentials=credentials)
Будем использовать следующую функцию для обработки результата запроса с последующей записью его в Pandas DataFrame:
from pandas.io.json import json_normalize
def parse_response(response):
report = response['reports'][0]
report_data = report.get('data', {})
print ('samplesReadCounts: '+report_data.get('samplesReadCounts', ['no'])[0])
print ('samplingSpaceSizes: '+report_data.get('samplingSpaceSizes', ['no'])[0])
column_headers = report.get('columnHeader', {})
dimension_headers = column_headers.get('dimensions', {})
metric_headers = []
mheaders = column_headers.get('metricHeader', {}).get('metricHeaderEntries', [])
for mheader in mheaders:
metric_headers.append(mheader['name'])
data = json_normalize(report['data']['rows'])
if 'dimensions' in data:
data_dimensions = pd.DataFrame(data['dimensions'].tolist(), columns=dimension_headers)
else:
data_dimensions = pd.DataFrame([])
data_metrics = pd.DataFrame(data['metrics'].tolist())
data_metrics = data_metrics.applymap(lambda x: x['values'])
data_metrics = pd.DataFrame(data_metrics[0].tolist(), columns=metric_headers)
result = pd.concat([data_dimensions, data_metrics], axis=1)
return result
Построим наш запрос к API:
request = {
'viewId': '111111111',
'dateRanges': {
'startDate': datetime.strftime(datetime.now() - timedelta(days = 1),'%Y-%m-%d'),
'endDate': 'yesterday'
},
'metrics': [
{'expression': 'ga:users','alias': 'users'},
],
'dimensions': [
{
'name':'ga:dateHour'
}],
'pageSize': 100000,
'samplingLevel': 'LARGE'
}
И вместе с обращением к API получим ответ:
response = api_client.reports().batchGet(
body={
'reportRequests': request
}).execute()
С помощью выше определенной функции обработаем результат и немного переформатируем датафрейм, чтобы проще было вывести график:
gausers = parse_response(response)
gausers['ga:dateHour'] = pd.to_datetime(gausers['ga:dateHour'], format='%Y%m%d%H', errors='ignore')
gausers.set_index('ga:dateHour', inplace=True)
gausers.index.name = 'hour'
gausers['users'] = pd.to_numeric(gausers['users']) #переведем строковые значения в числовые переменные
gausers.head()
Построим график по аналогии с предыдущим:
fig = plt.figure(figsize=(20,5))
ax = fig.add_subplot(1,1,1)
plt.plot(gausers)
plt.ylabel('Количество пользователей в час')
plt.ylim(bottom=0)
ax.xaxis.set_major_locator(mdates.HourLocator(interval = 1))
ax.xaxis.set_major_formatter(mdates.DateFormatter('%H:%M'))
Объединение двух графиков
Индекс наших двух датафреймов совпадает, и соответственно мы можем объединить их на одном графике, где ось X будет соответствовать значениям индекса. При этом давайте добавим небольшую аннотацию, указывающую на начало расхождения кривых в графиках.
fig = plt.figure(figsize=(20,5))
ax = fig.add_subplot(1,1,1)
plt.plot(gausers)
plt.plot(bqusers, color='blue')
plt.ylabel('Количество пользователей в час')
plt.ylim(bottom=0)
ax.xaxis.set_major_locator(mdates.HourLocator(interval = 1))
ax.xaxis.set_major_formatter(mdates.DateFormatter('%H:%M'))
#аннотация:
plt.annotate('Начало расхождения', # если текст не нужен значение должно быть пустым
xy=('2019-09-30 07:00:00', 3500), # координаты стрелки
xytext=('2019-09-30 04:00:00', 1000), # координаты хвостика
xycoords='data', # для координат будем использовать значения координат графика
arrowprops=dict(arrowstyle='->', connectionstyle='arc3', color='green', lw=2)
);
Таким образом задача решена — мы видим, что данные из BigQuery несколько отличаются от данных из аналитики, где пользователей меньше. Возможно это связано с заданными фильтрами в аккаунте аналитики и отфильтровкой части ботового трафика.
Для галочки напомню, что данный график в интерфейсе Google Analytics можно посмотреть в разделе Audience -> Overview
