Когда-то давно в 2014ом году для одной из презентаций о рынке e-commerce в Юлмарте мы строили широко известную во всем мире консалтинга диаграмму Waterfall, средствами Excel. В этом материале построим Waterfall chart средствами Python — она наглядно демонстрирует изменения с появлением нового положительного или отрицательного фактора. Для построения диаграммы будем использовать библиотеку plotly.
Для тех, кто пропустил: в цикле материалов о визуализации данных на Python мы уже пробовали строить диаграмму Градусник — она полезна, когда мы хотим сравнить, как соотносятся ожидаемые и реальные данные.

В качестве данных используем сведенную в Юлмарте информацию об изменении объёма рынка e-commerce с 2013 по 2014 год. Данные по оси X — подписи к каждому столбцу, по Y — начальные, итоговые значения и их изменения. Функцией sum() посчитаем итог и добавим его в конец списка. Тег <br> в списке x_list означает перенос строки.

import plotly.graph_objects as go

x_list = ['2013','Макроэкономика РФ','Сокращение численности<br>трудоспобного населения','Проникновение интернета','Развитие трансграничной<br>торговли', 'Федеральные компании', '2014']
y_list = [738.5, 48.7, -7.4, 68.7, 99.7, 48.0]
total = round(sum(y_list))
y_list.append(total)

Создадим список text_list — это те самые значения столбцов. Они берутся из списка y_list, но сперва их нужно немного обработать: переведём все числа в строки и если это столбец с изменением, то есть любой столбец, кроме первого и последнего, добавим к строке знак «плюс» для наглядности. А ещё в случае положительного изменения поменяем цвет на зелёный и на красный в обратном случае. Первому и последнему значению прибавим жирности к шрифту тегом <b>.

text_list = []
for index, item in enumerate(y_list):
    if item > 0 and index != 0 and index != len(y_list) - 1:
        text_list.append(f'+{str(y_list[index])}')
    else:
        text_list.append(str(y_list[index]))
for index, item in enumerate(text_list):
    if item[0] == '+' and index != 0 and index != len(text_list) - 1:
        text_list[index] = '<span style="color:#2ca02c">' + text_list[index] + '</span>'
    elif item[0] == '-' and index != 0 and index != len(text_list) - 1:
        text_list[index] = '<span style="color:#d62728">' + text_list[index] + '</span>'
    if index == 0 or index == len(text_list) - 1:
        text_list[index] = '<b>' + text_list[index] + '</b>'

Для того, чтобы поместить на фон пунктирные линии, необходимо задать их параметры. Сделаем список словарей и положим в него пунктирные линии светло-серого цвета с положением по Y от 0 до 1000 с шагом в 200.

dict_list = []
for i in range(0, 1200, 200):
    dict_list.append(dict(
            type="line",
            line=dict(
                 color="#666666",
                 dash="dot"
            ),
            x0=-0.5,
            y0=i,
            x1=6,
            y1=i,
            line_width=1,
            layer="below"))

Теперь зададим диаграмму — она лежит в методе Waterfall(). У каждого столбца есть тип — total, absolute или relative. Колонки с итоговыми значениями получают тип total или absolute, с промежуточными — relative. Кроме того, задаём цвета: делаем соединяющую линию прозрачной, положительные изменения — зелёными, отрицательные — красными, а итоговые колонки — фиолетовыми. Для текста выберем шрифт Open Sans.

О том, как подобрать хорошие шрифты для своей визуализации данных, можно узнать в материале «Choosing Fonts for Your Data Visualization»

fig = go.Figure(go.Waterfall(
    name = "e-commerce", orientation = "v",
    measure = ["absolute", "relative", "relative", "relative", "relative", "relative", "total"],
    x = x_list,
    y = y_list,
    text = text_list,
    textposition = "outside",
    connector = {"line":{"color":'rgba(0,0,0,0)'}},
    increasing = {"marker":{"color":"#2ca02c"}},
    decreasing = {"marker":{"color":"#d62728"}},
    totals={'marker':{"color":"#9467bd"}},
    textfont={"family":"Open Sans, light",
              "color":"black"
             }
))

Наконец, добавим заголовок и описание графика, уберём легенду, подпишем ось Y и внесём пунктирные линии на график.

fig.update_layout(
    title = 
        {'text':'<b>Waterfall chart</b><br><span style="color:#666666">Изменение объема рынка e-commerce с 2013 по 2014 год</span>'},
    showlegend = False,
    height=650,
    font={
        'family':'Open Sans, light',
        'color':'black',
        'size':14
    },
    plot_bgcolor='rgba(0,0,0,0)',
    yaxis_title="млрд руб.",
    shapes=dict_list
)
fig.update_xaxes(tickangle=-45, tickfont=dict(family='Open Sans, light', color='black', size=14))
fig.update_yaxes(tickangle=0, tickfont=dict(family='Open Sans, light', color='black', size=14))

fig.show()

Получим такую диаграмму:

В материале «Парсим данные, используя Buetiful Soup и Selenium» мы уже рассмотрели, как быть, когда данные на странице динамически подгружаются при скролле страницы. Но бывают ситуации, когда новые данные можно получить, только нажав на кнопку «Показать ещё» — сегодня узнаем, как через Selenium сымитировать нажатие кнопки для полного открытия страницы, соберём идентификаторы пива, оценки к каждому продукту и отправим данные в Clickhouse

Структура страницы

Возьмём случайную пивоварню — у неё 105 чекинов, то есть, отзывов. Страница с чекинами пивоварни показывает не более 25 чекинов и выглядит так:

Если попробуем промотать в самый низ, столкнёмся с той самой кнопкой, мешающей нам взять все 105 за раз:

Мы поступим так: выясним, к какому классу относится элемент кнопки и будем на неё нажимать, пока это возможно. Так как Selenium запускает браузер, следующая кнопка «Показать ещё» может не успеть прогрузиться, поэтому между нажатиями поставим интервал в пару секунд. Как только страница раскроется полностью — мы возьмём её содержимое и распарсим нужные данные из чекинов. Зайдём в код страницы и найдём кнопку — она относится к классу more_checkins.

У кнопки есть свойства стиля, а именно — display. В случае, если кнопка должна отображаться, display принимает значение block. Но когда промотаем страницу до самого конца, кнопку не нужно будет показывать, ведь открывать больше нечего — поэтому display кнопки примет значение none. В случае, если мы запросим у кнопки display и вернётся none будем знать, что открывать больше нечего и можно перестать жать на кнопку.

Пишем код

Начнём с импорта библиотек:

import time
from selenium import webdriver
from bs4 import BeautifulSoup as bs
import re
from datetime import datetime
from clickhouse_driver import Client

Chromedriver, необходимый для запуска браузера через Selenium, можно установить с официальной страницы

Подключимся к базе данных, зададим cookies:

client = Client(host='ec1-23-456-789-10.us-east-2.compute.amazonaws.com', user='', password='', port='9000', database='')
count = 0
cookies = {
    'domain':'untappd.com',
    'expiry':1594072726,
    'httpOnly':True,
    'name':'untappd_user_v3_e',
    'path':'/',
    'secure':False,
    'value':'your_value'
}

О том, как запускать Selenium с cookies можно прочитать в материале «Парсим данные каталога сайта, используя Beautiful Soup и Selenium». Нам нужен параметр untappd_user_v3_e.

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

options = webdriver.ChromeOptions()
prefs = {'profile.default_content_setting_values': {'images': 2, 
                            'plugins': 2, 'fullscreen': 2}}
options.add_experimental_option('prefs', prefs)
options.add_argument("start-maximized")
options.add_argument("disable-infobars")
options.add_argument("--disable-extensions")
driver = webdriver.Chrome(options=options)
driver.get('https://untappd.com/TooSunnyBrewery')
driver.add_cookie(cookies)

Напишем функцию, которая принимает ссылку, переходит по ней, полностью раскрывает страницу и возвращает нам soup, который можно будет распарсить. Получим display кнопки и запишем в переменную more_checkins: пока он не равен none будем нажимать на кнопку и снова получать её display. Сделаем интервал в две секунды между нажатиями, чтобы подождать прогрузку страницы. Как только будет получена вся страница, переведём её в soup библиотекой bs4.

def get_html_page(url):
    driver.get(url)
    driver.maximize_window()
    more_checkins = driver.execute_script("var more_checkins=document.getElementsByClassName('more_checkins_logged')[0].style.display;return more_checkins;")
    print(more_checkins)
    while more_checkins != "none":
        driver.execute_script("document.getElementsByClassName('more_checkins_logged')[0].click()")
        time.sleep(2)
        more_checkins = driver.execute_script("var more_checkins=document.getElementsByClassName('more_checkins_logged')[0].style.display;return more_checkins;")
        print(more_checkins)
    source_data = driver.page_source
    soup = bs(source_data, 'lxml')
    return soup

Напишем следующую функцию: она тоже будет принимать url страницы, передавать его в get_html_page, получать soup и парсить его. Функция вернёт запакованные списки с идентификатором пива и оценкой к нему.

О том, как парсить элементы страницы мы уже говорили в материале «Парсим данные каталога сайта, используя Beautiful Soup».

def parse_html_page(url):
    soup = get_html_page(url)
    brewery_id = soup.find_all('a', {'class':'label',
                                     'href':re.compile('https://untappd.com/brewery/*')})[0]['href'][28:]
    items = soup.find_all('div', {'class':'item',
                                  'id':re.compile('checkin_*')})
    checkin_rating_list = []
    beer_id_list = []
    count = 0
    print('Заполняю списки')
    for checkin in items:
        print(count, '/', len(items))
        try:
            checkin_rating_list.append(float(checkin.find('div', {'class':'caps'})['data-rating']))
        except Exception:
            checkin_rating_list.append('cast(Null as Nullable(Float32))')
        try:
            beer_id_list.append(int(checkin.find('a', {'class':'label'})['href'][-7:]))
        except Exception:
            beer_id_list.append('cast(Null as Nullable(UInt64))')
        count += 1 
    return zip(checkin_rating_list, beer_id_list)

Наконец, напишем вызов функций по пивоварням. В материале «Использование словарей в Clickhouse на примере данных Untappd» мы уже рассмотрели, как получить список идентификаторов российских пивоварен — обратимся к нему через таблицу в Clickhouse

brewery_list = client.execute('SELECT brewery_id FROM brewery_info')

Если посмотрим на brewery_list, то узнаем, что данные вернулись в неудобном формате: это список кортежей.

Небольшое лямбда-выражение позволит его «выпрямить»:

flatten = lambda lst: [item for sublist in lst for item in sublist]
brewery_list = flatten(brewery_list)

Работать с таким списком значительно комфортнее:

Для каждой пивоварни в списке сформируем url — он состоит из стандартной ссылки и идентификатора пивоварни в конце. Отправим url в функцию parse_html_page, которая сама вызовет get_html_page и вернёт списки с beer_id и rating_score. Так как два списка вернутся упакованными можем пройти по ним итератором, сформировав кортеж и отправив его в Clickhouse.

for brewery_id in brewery_list:
    print('Беру пивоварню с id', brewery_id, count, '/', len(brewery_list))
    url = 'https://untappd.com/brewery/' + str(brewery_id)
    returned_checkins = parse_html_page(url)
    for rating, beer_id in returned_checkins:
        tuple_to_insert = (rating, beer_id)
        try:
            client.execute(f'INSERT INTO beer_reviews VALUES {tuple_to_insert}')
        except errors.ServerException as E:
            print(E)
    count += 1

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

В Clickhouse реализована возможность использования внутренних и внешних словарей, которые могут быть альтернативой JOIN (которые, к сожалению, не всегда здорово работают). Словари хранят информацию в памяти и к ним можно обратиться командой dictGet. Рассмотрим как создать словарь в Clickhouse и как его можно использовать в запросах.

Будем изучать функционал на примере данных из API Untappd. Untappd — социальная сеть любителей крафтового пива. Мы сфокусируемся на чекинах российких крафтовых пивоварен, начнем собирать информацию о них, чтобы в следующих постах проанализировать данные и сделать некоторые выводы. В рамках этого поста разберем получение мета-информации о российских пивоварнях на Untappd, а полученные данные сохраним в словаре Clickhouse.

Собираем данные с Untappd

Для обращений к API нужны client_id и  client_secret_key — их можно получить, создав приложение. Для этого переходим в раздел создания приложения в документации и указываем некоторые данные:

После отправления заявки нужно будет подождать некоторое время: от 1 до 3 недель.

import requests
import pandas as pd
import time

Отправлять запросы к API будем через requests, а в pandas посмотрим на результаты и выгрузим в csv, чтобы отправить в словарь Clickhouse. У Untappd строгие ограничения на количество запросов: всего в час можно отправить 100 запросов, поэтому будем библиотекой time ставить скрипт в ожидание на 38 секунд, чтобы число запросов в час не превосходило 100.

client_id = 'ваш_client_id'
client_secret = 'ваш_client_secret'
all_brewery_of_russia = []

Мы хотим собрать всю тысячу российских пивоварен. Один запрос к методу Brewery Search позволяет получить до 50 пивоварен. При поиске вручную на сайте Untappd по слову «Russia» сайт выдаст 3369 пивоварен:

Проверим это: пролистаем страницу до самого низа и откроем код страницы.

Каждая полученная пивоварня в поиске находится в классе beer-item. Значит, можем в поиске посчитать количество упоминаний beer-item:

И выясняем, что на самом деле их здесь ровно 1000, а не 3369. По запросу Russia в выборку попадают и американские пивоварни, а некоторые были удалены. Значит, придётся отправить 20 запросов, будем получать по 50 пивоварен за раз:

for offset in range(0, 1000, 50):
    try:
        print('offset = ', offset)
        print('осталось:', 1000 - offset, '\n')
        response = requests.get(f'https://api.untappd.com/v4/search/brewery?client_id={client_id}&client_secret={client_secret}',
                               params={
                                   'q':'Russia',
                                   'offset':offset,
                                   'limit':50
                               })
        item = response.json()
        print(item, '\n')
        all_brewery_of_russia.append(item)
        time.sleep(37)
    except Exception:
        print(Exception)
        continue

В параметрах метод Brewery Search принимает q — строку, по которой будем осуществлять поиск на сервисе. Укажем в ней «Russia», чтобы получить все пивоварни, связанные с Россией. Другой параметр — offset — отвечает за смещение. Получив первые 50 пивоварен мы смещаемся на 50 строк в поиске, чтобы получить следующие 50 пивоварен. limit отвечает за количество получаемых пивоварен и не может быть больше 50.
Преобразовываем ответ в формат json и добавляем полученные данные в список all_brewery_of_russia. Объект item будет содержать такие данные:

Но в полученных данных могли затесаться и пивоварни других стран. Отфильтруем их: пройдём итератором по всему списку all_brewery_of_russia и добавим в итоговый только те пивоварни, у которых параметр country_name принимает значение Russia.

brew_list = []
for element in all_brewery_of_russia:
    brew = element['response']['brewery']
    for i in range(brew['count']):
        if brew['items'][i]['brewery']['country_name'] == 'Russia':
            brew_list.append(brew['items'][i])

Посмотрим на первый элемент списка brew_list:

print(brew_list[0])

Соберём из списка DataFrame с колонками brewery_id, beer_count, brewery_name, brewery_slug, brewery_page_url, brewery_city, lat и  lng. Получим в отдельные списки данные из  brewery_list:

df = pd.DataFrame()
brewery_id_list = []
beer_count_list = []
brewery_name_list = []
brewery_slug_list = []
brewery_page_url_list = []
brewery_location_city = []
brewery_location_lat = []
brewery_location_lng = []
for brewery in brew_list:
    brewery_id_list.append(brewery['brewery']['brewery_id'])
    beer_count_list.append(brewery['brewery']['beer_count'])
    brewery_name_list.append(brewery['brewery']['brewery_name'])
    brewery_slug_list.append(brewery['brewery']['brewery_slug'])
    brewery_page_url_list.append(brewery['brewery']['brewery_page_url'])
 brewery_location_city.append(brewery['brewery']['location']['brewery_city'])
    brewery_location_lat.append(brewery['brewery']['location']['lat'])
    brewery_location_lng.append(brewery['brewery']['location']['lng'])

И отправим их в DataFrame:

df['brewery_id'] = brewery_id_list
df['beer_count'] = beer_count_list
df['brewery_name'] = brewery_name_list
df['brewery_slug'] = brewery_slug_list
df['brewery_page_url'] = brewery_page_url_list
df['brewery_city'] = brewery_location_city
df['brewery_lat'] = brewery_location_lat
df['brewery_lng'] = brewery_location_lng

Посмотрим, как выглядит наша таблица:

df.head()

Отсортируем значения по  brewery_id и выгрузим таблицу в формате csv без столбца с индексами и заголовков колонок:

df = df.sort_values(by='brewery_id')
df.to_csv('brewery_data.csv', index=False, header=False)

Создаём словарь Clickhouse

Словари для Clickhouse можно создавать по-разному. Мы попробуем задать его структуру в xml-файле, настроить конфигурационные файлы сервера и обращаться к нему через клиент. Наш xml будет иметь следующую структуру:

Со всеми способами создания словарей можно ознакомиться в документации

<yandex>
<dictionary>
        <name>breweries</name>
        <source>
                <file>
                        <path>/home/ubuntu/brewery_data.csv</path>
                        <format>CSV</format>
                </file>
        </source>
        <layout>
                <flat />
        </layout>
        <structure>
                <id>
                        <name>brewery_id</name>
                </id>
                <attribute>
                        <name>beer_count</name>
                        <type>UInt64</type>
                        <null_value>Null</null_value>
                </attribute>
                <attribute>
                        <name>brewery_name</name>
                        <type>String</type>
                        <null_value>Null</null_value>
                </attribute>
                <attribute>
                        <name>brewery_slug</name>
                        <type>String</type>
                        <null_value>Null</null_value>
                </attribute>
                <attribute>
                        <name>brewery_page_url</name>
                        <type>String</type>
                        <null_value>Null</null_value>
                </attribute>
                <attribute>
                        <name>brewery_city</name>
                        <type>String</type>
                        <null_value>Null</null_value>
                </attribute>
                <attribute>
                        <name>lat</name>
                        <type>String</type>
                        <null_value>Null</null_value>
                </attribute>
                <attribute>
                        <name>lng</name>
                        <type>String</type>
                        <null_value>Null</null_value>
                </attribute>
        </structure>
        <lifetime>300</lifetime>
</dictionary>
</yandex>

Под  идёт имя словаря. В  указываем свойства колонок. Под тегом идёт ключевое поле, а под тегом укажем путь и формат файла. Скоро мы положим его в папку /home/ubuntu, поэтому так и укажем.

Загрузим нашу csv-таблицу и xml-файл на сервер, это можно сделать, например, по ftp через FileZilla. В одном из материалов мы учились ставить Clickhouse на бесплатную машину от Amazon, в этот раз будем работать там же. В FileZilla заходим в настройки SFTP и добавляем файл с ключом:

И подключаемся к серверу по адресу, который указан в консоли EC2 машины на AWS. Укажем протокол SFTP, свой Host и в качестве User — Ubuntu:

В случае перезагрузки машины через консоль Public DNS мог измениться

После подсоединения мы попадём в папку /home/ubuntu сервера. Положим файлы туда же. Теперь подключимся по SSH через Termius. Чтобы Clickhouse увидел файл со структурой словаря, его нужно положить в папку /etc/clickhouse-server:

О том, как подключаться в серверу на AWS через SSH-клиент мы рассказывали в материале «Устанавливаем Clickhouse на AWS»

sudo mv breweries_dictionary.xml /etc/clickhouse server/

Переходим в конфигурационный файл:

cd /etc/clickhouse-server
sudo nano config.xml

Нам нужен тег  — он указывает путь к файлу, который описывает структуру словарей. Укажем путь к нашему xml:

<dictionaries_config>/etc/clickhouse-server/breweries_dictionary.xml</dictionaries_config>

Сохраняем файл и запускаем клиент Clickhouse:

clickhouse client

Проверим, что наш словарь действительно загрузился:

SELECT * FROM system.dictionaries\G

В случае успеха получим подобное:

Напишем запрос к функции dictGet, чтобы получить название пивоварни под ID 999. Указываем первым аргументом наименование словаря, затем поле, значение которого хотим получить и ID.

SELECT dictGet('breweries', 'brewery_name', toUInt64(999))

Если сделаем всё правильно, то выясним, что под ID 999 находится Балтика:

Аналогичным образом удобно использовать функцию, когда в таблице с фактами хранится только ID измерения для получения понятного наименования.

Есть много разных сканеров для QR, но не всегда изображение обладает хорошим качеством. В компьютерном зрении для этого используется Image Pre-processing: предобработка изображения. Сегодня рассмотрим, как средствами библиотеки scikit-image помочь QR-сканеру найти код на картинке.

from matplotlib import pyplot as plt
import skimage
from skimage import util, exposure, io, measure, feature
from scipy import ndimage as ndi
import numpy as np
import cv2

Проблема

Попробуем просканировать чек из материала «Собираем данные с чеков гипермаркетов на Python». Прочтём картинку методом imread библиотеки matplotlib и покажем его на экране:

img = plt.imread('чек.jpg')
plt.imshow(img)

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

def qr_reader(img):
    detector = cv2.QRCodeDetector()
    data, bbox, _ = detector.detectAndDecode(img)
    if data:
        print(data)
    else:
        print('Ничего не нашлось!')

И обратимся к ней, чтобы просканировать наше изображение:

qr_reader(img)

Ничего не нашлось!

И это можно понять: обилие лишних пикселей мешает сканеру распознать здесь QR-код. Тем не менее, мы можем помочь сканеру, указав где находится искомая область.

Решение

Сделаем так: уберём с картинки всё лишнее, найдём координаты прямоугольника с QR-кодом, чтобы затем передать в функцию qr_reader не исходное изображение, а исключительно QR-код. Первым делом уменьшим шум, используя медианный фильтр и сконвертируем изображение из rgb в gray: QR-код состоит всего из двух цветов, так что работать с остальными нам не нужно.

image = ndi.median_filter(util.img_as_float(img), size=9)
image = skimage.color.rgb2gray(image)
plt.imshow(image, cmap='gray')

Медианный фильтр размыл изображение, и разбросанные одинокие пиксели стали менее отчётливыми, а QR теперь выделяется на их фоне. Попробуем применить adjust_gamma к изображению. Эта функция возводит в степень gamma значение каждого пикселя: чем меньше будет этот параметр — тем меньше будет значение пикселя и тем ближе к белому он будет становиться. Попробуем взять gamma за 0.5.

pores_gamma = exposure.adjust_gamma(image, gamma=0.5)
plt.imshow(pores_gamma, cmap='gray')

Заметно, что QR стал ещё отчетливее прочего на фото. Воспользуемся этим: все пиксели, значение которых меньше 0.3 сделаем 0, а остальных — 1.

thresholded = (pores_gamma <= 0.3)
plt.imshow(thresholded, cmap='gray')

А теперь воспользуемся детектором границ canny для полученного изображения thresholded. Этот оператор сам сглаживает изображение и ищет градиенты: границы находятся там, где градиент принимает максимальное значение. С повышением параметра sigma детектор canny перестает замечать менее отчетливые границы.

edge = feature.canny(thresholded, sigma=6)
plt.imshow(edge)

Наконец, получим координаты границ: для этого нарисуем контуры. Получаем их методом find_contours и рисуем поверх изображения edge. Объекты массива contours — координаты по осям X и Y.

contours = measure.find_contours(edge, 0.5)
plt.imshow(edge)
for contour in contours:
    plt.plot(contour[:,1], contour[:,0], linewidth=2)

Возьмём максимальные и минимальные координаты по X и по Y: это будут границы видимого прямоугольника.

positions = np.concatenate(contours, axis=0)
min_pos_x = int(min(positions[:,1]))
max_pos_x = int(max(positions[:,1]))
min_pos_y = int(min(positions[:,0]))
max_pos_y = int(max(positions[:,0]))

Теперь, имея координаты, можем на исходном изображении обвести область с кодом:

start = (min_pos_x, min_pos_y)
end = (max_pos_x, max_pos_y)
cv2.rectangle(img, start, end, (255, 0, 0), 5)
io.imshow(img)

Попробуем срезать оригинальное изображение по этим координатам:

new_img = img[min_pos_y:max_pos_y, min_pos_x:max_pos_x]
plt.imshow(new_img)

И передадим новое изображение в функцию qr_reader:

qr_reader(new_img)

Получаем в ответе:

t=20190320T2303&s=5803.00&fn=9251440300007971&i=141637&fp=4087570038&n=1

Это то, чего мы и хотели. Конечно, скрипт не будет универсальным, ведь в каждом изображении будут свои недостатки: где-то шума будет больше, где-то фотография размыта, где-то не будет хватать контраста. Поэтому в отдельных случаях потребуется вносить и иные корректировки в изображение. На следующем этапе обработки фотографии мы воспользуемся уже готовой библиотекой.

Перевели увесистую шпаргалку по оконным функциям в SQL от learnsql.com: вспоминаем синтаксис, функции распределения, ранжирования и многое другое.

Версия в  pdf

За cheatsheet спасибо Telegram-каналу DataEng

Эта статья — продолжение серии материалов «Собираем данные с чеков гипермаркетов на Python» и «Парсим данные каталога сайта». В этот раз построим модель, которая обучится на датасете из собранного каталога и классифицирует товарные позиции чека из гипермаркета на продуктовые категории. Суть проблемы: в чеке мы видим данные о каждом товаре отдельно, а иногда хочется быстро понять сколько сегодня потратили денег на «Сладкое».

Предобработка датасета

Импортируем библиотеку pandas и прочитаем csv-файл с каталогом igoods (мы сформировали его, когда парсили каталог). Заодно посмотрим, как он выглядит:

Подробнее о том, как программе эмулировать поведение человека на сайте и собрать датасет из каталога можно прочитать в материале «Парсим данные каталога сайта»

import pandas as pd
sku = pd.read_csv('SKU_igoods.csv',sep=';')
sku.head()

После парсинга в таблице осталось несколько ненужных колонок: например, нам ни к чему знать цену на продукт и его вес, чтобы построить модель предсказания категории товара. Избавляемся от этих колонок методом drop(), а остальные переименуем через rename() и снова смотрим на таблицу:

sku.drop(columns=['Unnamed: 0', 'Weight','Price'],inplace=True)
sku.rename(columns={"SKU": "SKU", "Category": "Group"},inplace=True)
sku.head()

Сгруппируем товары по их категории и посчитаем количество функциями groupby() и agg():

sku.groupby('Group').agg(['count'])

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

Стеммер — программа, которая находит для заданного слова его основу.

import nltk
from nltk.corpus import stopwords
from pymystem3 import Mystem
from string import punctuation
nltk.download('stopwords')

Для стемминга будем использовать стеммер Яндекса из библиотеки pymystem3. Список стоп-слов необходимо расширить — каталог товаров из магазина немного отличается от бытовых ситуаций, в которых базовый набор актуален.

mystem = Mystem() 
russian_stopwords = stopwords.words("russian")
russian_stopwords.extend(['лента','ассорт','разм','арт','что', 'это', 'так', 'вот', 'быть', 'как', 'в', '—', 'к', 'на'])

Опишем функцию подготовки текста. Она приводит текст стеммером к своей основе, убирает из него знаки пунктуации, цифры и стоп-слова. Этот код был найден в одном из kernel на kaggle.

def preprocess_text(text):
    text = str(text)
    tokens = mystem.lemmatize(text.lower())
    tokens = [token for token in tokens if token not in russian_stopwords\
              and token != " " \
              and len(token)>=3 \
              and token.strip() not in punctuation \
              and token.isdigit()==False]
    text = " ".join(tokens)
    return text

Проверим, как работает функция:

preprocess_text("Мой дядя самых честных правил, Когда не в шутку занемог, Он уважать себя заставил И лучше выдумать не мог.")

Получаем:

'дядя самый честный правило шутка занемогать уважать заставлять выдумывать мочь'

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

print(‘Было:’, sku['SKU'][0])
print(‘Стало:’, preprocess_text(sku['SKU'][0]))

Получаем:

Было: Фисташки соленые жареные ТМ 365 дней
Стало: фисташка соленый жареный день

Функция справляется отлично, теперь можем применить её ко всем наименованиям и вынести обработанные названия в новый столбец processed. Посмотрим, как выглядит датасет теперь:

sku['processed']=sku['SKU'].apply(preprocess_text)
sku.head()

Строим модель предсказания категории

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

import pandas as pd
from sklearn.model_selection import train_test_split
from sklearn.feature_extraction.text import CountVectorizer
from sklearn.feature_extraction.text import TfidfTransformer
from sklearn.naive_bayes import MultinomialNB
from imblearn.pipeline import Pipeline

Поделим наш датасет на X — обработанные наименования товаров и на Y — их категории. Разделим на обучающую и тестовую выборку, отдав под тесты 33% от общего числа данных.

x = sku.processed
y = sku.Group
X_train, X_test, y_train, y_test = train_test_split(x, y, test_size=0.33)

Пройдём пайплайном следующие команды:

• CountVectorizer() — вернет матрицу с количеством вхождений каждого токена
• TfidfTransformer() — преобразует эту матрицу в нормализованное представление tf-idf
• MultinomialNB() — наивный байесовский классификатор для предсказания категории товара

text_clf = Pipeline([('vect', CountVectorizer(ngram_range=(1,2))),
                     ('tfidf', TfidfTransformer()), 
                    ('clf', MultinomialNB())])

На выходе получим модель в text_clf, которую затем обучим по обучающей выборке и посчитаем предсказания по тестовой выборке:

text_clf = text_clf.fit(X_train, y_train)
y_pred = text_clf.predict(X_test)

А теперь оценим модель:

print('Score:', text_clf.score(X_test, y_test))

Получим такую точность:

Score: 0.923949864498645

Верификация на реальных данных

Можем проверить, как работает модель на реальных данных из свежего чека. В материале о том, как получить продукты из чека гипермаркета, на выходе мы получали DataFrame с продуктами — возьмём его и применим к названиям товаров функцию preprocess_text.

my_products['processed']=my_products['name'].apply(preprocess_text)
my_products.head()

Заполним новый столбец prediction — он будет предсказывать категорию товара по его названию. Передаем ему колонку с обработанными названиями и создаём новую колонку с предсказаниями.

prediction = text_clf.predict(my_products['processed'])
my_products['prediction']=prediction
my_products[['name', 'prediction']]

DataFrame станет таким:

И посчитаем сумму по каждой категории:

my_products.groupby('prediction').sum()

В целом, модель справляется неплохо с предсказаниями: сосиски уходят в мясную гастрономию, творог — в молочные продукты, багет — в хлеб и выпечку. И всё же заметно, что киви почему-то относится к молочным продуктам, а груши — к эко-продуктам. Проблема в том, что в каталоге в этих разделах много товаров «со вкусом груши» или «со вкусом киви», из-за чего наивный байесовский классификатор отдаёт предпочтение тому классу, экземпляров которого в датасете больше. Это известная проблема несбалансированных классов, которую можно победить ресемплингом исходного датасета или задав нужные веса в модели.

Очень часто диаграммы, построенные стандартными средствами Matplotlib, выглядят некрасиво и неинформативно. В 2011ом году для целей одного из отчетов телеком-компании в Excel мы построили полезную симпатичную диаграмму «Градусник», рецепт которой стал известен из популярного в тот момент блога Chandoo про приемы визуализации в Excel.
Вот как она выглядела в Excel:

Времена меняются, и мы попробуем восстановить знание о построении этой полезной диаграммы, используя штатные средства библиотеки matplotlib в Python.

Для каких случаев подойдет диаграмма «Градусник»?
Лучше всего использовать данный тип для сравнения плановых и фактических значений, таким образом наглядно можно увидеть недовыполнение и перевыполнение показателей. При этом план / факт может быть как в процентах, так и в фактических значениях. Мы рассмотрим пример с фактическими значениями в условных единицах.

В этот раз возьмем данные из excel-файла. Используем типичный состав библиотек для работы с данными (и соответствующие им типичные alias):

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

И считываем в DataFrame таблицу:

df = pd.read_excel('data.xlsx')

Посмотрим, как она выглядит:

Начнем извлекать из таблицы колонки. Первый столбец «Продажи» будет вертикальной подписью к каждому столбцу на графике. «План» — статичный столбец, относительно которого измеряется «Факт». Некоторые данные могут приходить в виде вещественных чисел — такие столбцы будут считаны как тип str, если в данных будет встречена запятая. Чтобы работать с такими числами, будем сначала менять в них запятую на точку, а затем переводить в тип float.

xticks = df.iloc[:,0]
try:
    bars2 = df.iloc[:,1].str.replace(',','.').astype('float')
except AttributeError:
    bars2 = df.iloc[:,1].astype('float')
try:
    bars1 = df.iloc[:,2].str.replace(',','.').astype('float')
except AttributeError:
    bars1 = df.iloc[:,2].astype('float')

Так как мы не знаем наверняка, будут ли в данных такие числа, можем словить AttributeError в случае их отсутствия, ведь будем обращаться к методу str, который есть только у строк. Поэтому напишем обработчик исключений try — except, который будет на всякий случай переводить данные в тип float.

Построим из этого классический barchart — график со столбцами. Зададим массив положения на оси Х для bars1 функцией np.arange и bars2, смещённый на ширину столбца:

barWidth = 0.2
r1 = np.arange(len(bars1))
r2 = [x + barWidth for x in r1]
 
plt.bar(r1, bars1, width=barWidth)
plt.bar(r2, bars2, width=barWidth)

И посмотрим, что получилось:

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

barWidth1 = 0.065
barWidth2 = 0.032
x_range = np.arange(len(bars1) / 8, step=0.125)

А теперь отобразим столбцы на графике, задав им положение, ширину, значения, цвет, легенду и подписи к диаграммам bars2:

plt.bar(x_range, bars1, color='#dce6f2', width=barWidth1/2, edgecolor='#c3d5e8', label='План')
plt.bar(x_range, bars2, color='#ffc001', width=barWidth2/2, edgecolor='#c3d5e8', label='Факт')
for i, bar in enumerate(bars2):
    plt.text(i / 8 - 0.015, bar + 1, bar, fontsize=14)

Наконец, сделаем несколько визуальных штрихов — уберём лишние рамки, чёрточки, добавим серую линию под столбцами, поправим размер и шрифт легенде, сделаем диаграмму шире, выведем её на экран и сохраним как plt.png в директории скрипта:

plt.xticks(x_range, xticks)
plt.tick_params(
    bottom=False,
    left=False,
    labelsize=15
)
plt.rcParams['figure.figsize'] = [25, 7]
plt.axhline(y=0, color='gray')
plt.legend(frameon=False, loc='lower center', bbox_to_anchor=(0.25, -0.3, 0.5, 0.5), prop={'size':20})
plt.box(False)
plt.savefig('plt', bbox_inches = "tight")
plt.show()

Получили такую диаграмму:

В пятничном лонгриде проделаем большую работу: возьмём информацию по рекламным кампаниям ВКонтакте и сопоставим их с данными Google Analytics в Redash. Чтобы снова не поднимать сервер, будем передавать данные через Google Docs, используя Spreadsheet API.

Получение access token
Для получение пользовательского ключа ВКонтакте нужно создать приложение. Идём в раздел «Разработчики» по https://vk.com/apps?act=manage, жмём на кнопку «Создать приложение». В поле «Тип приложения» выбираем «Standalone-приложение» и даём любое название. После этого в меню слева идём в настройки и сохраняем себе ID приложения.

Актуальную информацию о ключах можно посмотреть в статье «Получение ключа доступа»

Теперь копируем себе эту ссылку:

https://oauth.vk.com/authorize?client_id=YourClientID&scope=ads&response_type=token

Но вместо YourClientID вставляем ID своего созданного приложения. В scope у этой ссылки только ads, так что с этим ключом можно будет получать только информацию о рекламном кабинете. Вставляем её в браузер и нас скидывает на другую страницу — в адресе этой странице будет указан ваш сгенерированный access token.

Срок жизни токена — 86400 секунд: ровно сутки. Чтобы получить токен без временных ограничений можно добавить в scope параметр offline. Если токен понадобилось отозвать — смените пароль от страницы или в настройках безопасности завершите активные сессии.

Ещё для запросов к API нам пригодится ID рекламного кабинета — проходим по https://vk.com/ads?act=settings и копируем «номер кабинета».

Сбор данных через запросы к API
Напишем скрипт, который обращается к серверу ВКонтакте с нашим access token и номером рекламного кабинета и берёт информацию о всех кампаниях пользователя: количество просмотров на рекламах, кликов и затрат. Затем скрипт будет формировать из него DataFrame и отправлять в Google Docs.

from oauth2client.service_account import ServiceAccountCredentials
from pandas import DataFrame
import requests
import gspread
import time

Зададим несколько константных значений: access token, ID рекламного кабинета и версию API ВКонтакте, которую будем использовать. Актуальной является версия 5.103.

token = 'fa258683fd418fafcab1fb1d41da4ec6cc62f60e152a63140c130a730829b1e0bc'
version = 5.103
id_rk = 123456789

За получение статистики по рекламе отвечает метод ads.getStatistics, но один из обязательных параметров при его вызове — ’ids’, ID рекламного объявления, статистику по которому мы хотим получить. Так как ID у нас пока нет, придётся сначала воспользоваться методов ads.getAds, который возвращает ID объявлений и кампаний.

Подробнее со всеми методами ВКонтакте API можно ознакомиться в документации

Библиотекой requests отправляем запрос к серверу и передаём свои параметры. Полученный ответ сразу переведём в формат json

campaign_ids = []
ads_ids = []
r = requests.get('https://api.vk.com/method/ads.getAds', params={
    'access_token': token,
    'v': version,
    'account_id': id_rk
})
data = r.json()['response']

Вот, как выглядит объект data: нам вернулся обычный список словарей, с которым мы уже имели дело в материале “Передаём и анализируем собранные данные по рекламным капманиям в Redash”.

Заполняем словарь ad_campaign_dict. Ключом будет ID объявления, а значением — ID кампании, к которой принадлежит объявление. Так будет удобнее присваивать к объявлению ID кампании, к которой оно принадлежало.

ad_campaign_dict = {}
for i in range(len(data)):
    ad_campaign_dict[data[i]['id']] = data[i]['campaign_id']

Теперь, имея ID каждого нужного объявления, можно обратиться к методу ads.getStatistics. Мы будем собирать количество просмотров, кликов, затрат и даты начала и конца объявления, поэтому заблаговременно заведём пустые списки.

ads_campaign_list = []
ads_id_list = []
ads_impressions_list = []
ads_clicks_list = []
ads_spent_list = []
ads_day_start_list = []
ads_day_end_list = []

Вызывать getStatistics нужно отдельно для каждого объявления — будем делать это в итераторе по ad_campaign_dict. Отправляем запрос, передавая в ‘period’ значение ‘overall’ — берём данные за всё время. У некоторых объявлений могут отсутствовать данные по полю «Просмотры» или «Клики» если они не были запущены, и, потребовав их, мы словим KeyError — во избежание этого добавим обработчик try — except, который заставит скрипт не обращать внимания на эту ошибку.

for ad_id in ad_campaign_dict:
        r = requests.get('https://api.vk.com/method/ads.getStatistics', params={
            'access_token': token,
            'v': version,
            'account_id': id_rk,
            'ids_type': 'ad',
            'ids': ad_id,
            'period': 'overall',
            'date_from': '0',
            'date_to': '0'
        })
        try:
            data_stats = r.json()['response']
            for i in range(len(data_stats)):
                for j in range(len(data_stats[i]['stats'])):
                    ads_impressions_list.append(data_stats[i]['stats'][j]['impressions'])
                    ads_clicks_list.append(data_stats[i]['stats'][j]['clicks'])
                    ads_spent_list.append(data_stats[i]['stats'][j]['spent'])
                    ads_day_start_list.append(data_stats[i]['stats'][j]['day_from'])
                    ads_day_end_list.append(data_stats[i]['stats'][j]['day_to'])
                    ads_id_list.append(data_stats[i]['id'])
                    ads_campaign_list.append(ad_campaign_dict[ad_id])
        except KeyError:
            continue

Теперь сформируем из списков DataFrame и выведем первые 5 элементов:

df = DataFrame()
df['campaign_id'] = ads_campaign_list
df['ad_id'] = ads_id_list
df['impressions'] = ads_impressions_list
df['clicks'] = ads_clicks_list
df['spent'] = ads_spent_list
df['day_start'] = ads_day_start_list
df['day_end'] = ads_day_end_list
print(df.head())

Экспорт данных в Google Docs
Для экспорта DataFrame в таблицу Google Sheets необходим ключ доступа Google API. Пройдём по https://console.developers.google.com и создадим новый проект. Даём ему любое имя и в Dashboard жмём на кнопку “Подключить API и сервисы”. Нужно включить два API — Google Drive API и Google Sheets API. Ищем первый в поиске, нажимаем на “Включить API”, затем ищем второй и проделываем то же самое.

После включения нас отправят на панель управления API. Жмём на «Создать учётные данные» — по ним будем проводить авторизацию в скрипте. Отмечаем, что используем Google Sheets API из веб-сервера и обращаемся к данным пользователя. Нажимаем на «Выбрать тип учётных данных» и создаем сервисный аккаунт. В поле «Роль» выбираем Проект — Редактор, а тип ключа оставим JSON.

После этого нам отправят файл в формате JSON с нашими учетными данными — назовём его «credentials.json» — и перенаправят на страницу с сервисными аккаунтами. Ниже будет поле с почтой — копируем её себе.

Переходим по https://docs.google.com/spreadsheets и создаем пустой файл с названием data, в который будут отправляться данные из DataFrame. В настройках доступа даём доступ по почте, скопированной ранее из сервисных аккаунтов — от неё будут приходить данные из скрипта.

Закинем файл credentials.json в директорию со скриптом и продолжим писать код. Перечисляем область видимости в виде ссылок:

scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']

И при помощи библиотек oauth2client и gspread проводим авторизацию методами ServiceAccountCredentials.from_json_keyfile_name и gspread.authorize, указывая в параметрах первого наш файл и переменную scope. Через переменную sheet будем обращаться к нашему файлу в Google Docs.

creds = ServiceAccountCredentials.from_json_keyfile_name('credentials.json', scope)
client = gspread.authorize(creds)
sheet = client.open('data').sheet1

Для ввода значений в ячейку таблички есть метод update_cell. Важно: нумерация индексов ячеек при обращении начинается не с нуля, а с единицы. Первым циклом пройдём по первой строке и перенесем туда заголовки нашего DataFrame. Во втором будем идти по каждой ячейке и вставлять соответствующие значения DataFrame. По умолчанию стоит ограничение — 100 запросов в 100 секунд. Это ограничение может остановить наш скрипт на полпути: чтобы избежать ошибки пропишем time.sleep, чтобы после каждой вставки скрипт секунду выжидал.

count_of_rows = len(df)
count_of_columns = len(df.columns)
for i in range(count_of_columns):
    sheet.update_cell(1, i + 1, list(df.columns)[i])
for i in range(1, count_of_rows + 1):
    for j in range(count_of_columns):
        sheet.update_cell(i + 1, j + 1, str(df.iloc[i, j]))
        time.sleep(1)

Если всё сделаем правильно — получим таблицу такого вида:

Экспорт данных в Redash

Подключение Google Analytics к Redash описано в статье «Как подключить Google Analytics как Redash?».

Имея в Redash таблицу с Google Analytics и рекламным кампаниям ВКонтакте, можем сопоставить их друг другу. Напишем такой запрос:

SELECT
    query_50.day_start,
    CASE WHEN ga_source LIKE '%vk%' THEN 'vk.com' END AS source,
    query_50.spent,
    query_50.impressions,
    query_50.clicks,
    SUM(query_49.ga_sessions) AS sessions,
    SUM(query_49.ga_newUsers) AS users
FROM query_49
JOIN query_50
ON query_49.ga_date = query_50.day_start
WHERE query_49.ga_source LIKE '%vk%' AND DATE(query_49.ga_date) BETWEEN '2020-05-16' AND '2020-05-20'
GROUP BY query_49.ga_date, source

ga_source — источник, с которого человек пришел на сайт. Всё, что похоже на vk оператором CASE объединяем в столбец «vk.com». Оператором JOIN добавляем таблицу с данными из ВКонтакте, объединяя по полю даты. Отсеиваем данные — возьмём день последней рекламной кампании и посмотрим на несколько дней после него. На выходе получим таблицу такого вида:

Итоги
Получилась таблица, сообщающая, сколько всего было затрачено на объявления в этот день, сколько человек его посмотрели, зашли к нам на сайт и стали нашими новыми пользователями.

В этот раз разберёмся, как с помощью Python передавать в Clickhouse данные по рекламным кампаниям и построим агрегат, используя материализованное представление.
Для чего нам материализованные представления? Часто Clickhouse используется для работы с огромными объемами данных, а время получения ответа на запрос к таблице с сырыми данными постоянно растёт. Стандартно, чтобы решить такую задачу эффективным способом, чаще всего используют ETL-процессы или создают таблицы агрегатов, что не очень удобно, ведь их необходимо регулярно пересчитывать. Clickhouse обладает встроенной и эффективной возможностью для решения задачи — материализованными представлениями.
Материализованные представления физически хранят и обновляют данные на диске в соответствии с запросом SELECT, на основе которого представление было создано. При вставке данных в искомую таблицу SELECT преобразовывает данные и вставляет их в представление.

Настройка машины
Наш скрипт на Python из предыдущих материалов необходимо подключить к Clickhouse — он будет отправлять запросы, поэтому нужно открыть несколько портов. В Dashboard AWS переходим в Network & Security — Security Groups. Наша машина входит в группу launch-wizard-1. Переходим в неё и смотрим на Inbound rules: нам нужно добавить правила как на скриншоте.

Настройка Clickhouse
Теперь настроим Clickhouse. Отредактируем файл config.xml в редакторе nano:

cd /etc/clickhouse-server
sudo nano config.xml

Воспользуйтесь мануалом по горячим клавишам, если тоже не сразу поняли, как выйти из nano.

Раскоментируем строку

<listen_host>0.0.0.0</listen_host>

чтобы доступ к базе данных был с любого IP-адреса:

Создание таблицы и материализованного представления
Зайдём в клиент и создадим нашу базу данных, в которой впоследствии создадим таблицы:

CREATE DATABASE db1
USE db1

Мы проиллюстрируем всё тот же пример сбора данных с Facebook. Информация по кампаниям может часто обновляться, и мы, в целях упражнения, хотим создать материализованное представление, которое будет автоматически пересчитывать агрегаты на основе собранных данных по затратам. Таблица в Clickhouse будет практически такой же, как DataFrame из прошлого материала. В качестве движка таблицы используем CollapsingMergeTree: он будет удалять дубликаты по ключу сортировки:

CREATE TABLE facebook_insights(
	campaign_id UInt64,
	clicks UInt32,
	spend Float32,
	impressions UInt32,
	date_start Date,
	date_stop	 Date,
	sign Int8
) ENGINE = CollapsingMergeTree
ORDER BY (date_start, date_stop)

И сразу создадим материализованное представление:

CREATE MATERIALIZED VIEW fb_aggregated
ENGINE = SummingMergeTree()
ORDER BY date_start
	AS
	SELECT campaign_id,
		      date_start,
		      sum(spend * sign) as spent,
		      sum(impressions * sign) as impressions,
		      sum(clicks * sign) as clicks
	FROM facebook_insights
	GROUP BY date_start, campaign_id

Подробности рецепта можно посмотреть в блоге Clickhouse.

К сожалению, в Clickhouse UPDATE отсутствует, поэтому необходимо придумывать некоторые ухищрения. Мы воспользовались рецептом от команды Яндекса для обходного пути команды UPDATE. Идея состоит в том, чтобы в начале вставить строки, которые уже были в таблице с отрицательным Sign, а затем использовать Sign для сторнирования. Следуя этому рецепту старые данные не будут учитываться при суммировании.

Скрипт
Начнём писать скрипт. Понадобится новая библиотека — clickhouse_driver, позволяющая отправлять запросы к Clickhouse из скрипта на Python:

В материале приведена только доработка скрипта, описанного в статье «Собираем данные по рекламным кампаниям в Facebook». Всё будет работать, если вы просто вставите код из текущего материала в скрипт предыдущего.

from datetime import datetime, timedelta
from clickhouse_driver import Client
from clickhouse_driver import errors

Объект класса Client позволит отправлять запросы методом execute(). В host вводим свой public dns, user ставим default, port — 9000 и в database базу данных для подключения.

client = Client(host='ec1-2-34-56-78.us-east-2.compute.amazonaws.com', user='default', password=' ', port='9000', database='db1')

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

client.execute('SHOW DATABASES')

В случае успеха получим на экране такой список:

[('_temporary_and_external_tables',), ('db1',), ('default',), ('system',)]

Пусть, например, мы хотим рассматривать данные за последние три дня. Получим эти даты библиотекой datetime и переведём в нужный формат методом strftime():

date_start = datetime.now() - timedelta(days=3)
date_end = datetime.now() - timedelta(days=1)
date_start_str = date_start.strftime("%Y-%m-%d")
date_end_str = date_end.strftime("%Y-%m-%d")

Напишем вот такой запрос, получающий все колонки таблицы за это время:

SQL_select = f"select campaign_id, clicks, spend, impressions, date_start, date_stop, sign from facebook_insights where date_start > '{date_start_str}' AND date_start < '{date_end_str}'"

И выполним запрос, поместив информацию в список old_data_list. А затем поменяем всем sign на -1 и добавим в new_data_list:

new_data_list = []
old_data_list = []
old_data_list = client.execute(SQL_select)

for elem in old_data_list:
    elem = list(elem)
    elem[len(elem) - 1] = -1
    new_data_list.append(elem)

Наконец, напишем наш алгоритм: вставляем те же самые данные с sign = −1, оптимизируем для удаления дубликатов движком CollapsingMergeTree и выполняем INSERT новых данных со знаком sign = 1.

SQL_query = 'INSERT INTO facebook_insights VALUES'
client.execute(SQL_query, new_data_list)
SQL_optimize = "OPTIMIZE TABLE facebook_insights"
client.execute(SQL_optimize)
for i in range(len(insight_campaign_id_list)):
    client.execute(SQL_query, [[insight_campaign_id_list[i],
                                insight_clicks_list[i],
                                insight_spend_list[i],
                                insight_impressions_list[i],
                                datetime.strptime(insight_date_start_list[i], '%Y-%m-%d').date(),
                                datetime.strptime(insight_date_start_list[i], '%Y-%m-%d').date(),
                                1]])
    client.execute(SQL_optimize)

Вернёмся в Clickhouse. Выполним SELECT * FROM facebook_insights LIMIT 20, чтобы посмотреть первые 20 строк таблицы:

И SELECT * FROM fb_aggregated LIMIT 20, чтобы проверить наше представление:

Отлично! Мы сделали материализованное представление — теперь новые данные, поступающие в таблицу facebook_insights будут поступать и в материализованное представление fb_aggregated и каждый раз пересчитываться благодаря SummingMergeTree. При этом трюк с sign позволяет отлавливать уже обработанные записи и не допускать их суммирования, а CollapsingMergeTree — чистить дубликаты.

Сегодня поработаем с Clickhouse — поставим его на личную бесплатную машину с Amazon Web Services.

Аккаунт на AWS и машина на Ubuntu
Проще всего Clickhouse установить из deb пакетов на машину под управлением Ubuntu. Сегодня вовсе необязательно иметь такую под рукой — достаточно создать годовой бесплатный аккаунт на Amazon Web Services, который выделит нам нужную машину. Переходим на https://aws.amazon.com и создаём аккаунт и попадаем в Dashboard. В разделе «Build a solution» идём в «Launch a virtual machine» и подбираем виртуальную машину. Нам подойдёт та, на которой установлен Ubuntu Server.

Заодно создаём key pair — пара состоит из публичного ключа AWS и приватного ключа-файла. Последний нужно сохранить у себя на компьютере для подключения к машине.

После откроется консоль EC2, где уже будет запущен наш Instance виртуальной машины. У него есть public dns — сохраняем его.

Подключаемся через Termius
Подключаться к виртуальной машине будем через протокол удалённого доступа ssh. Есть много клиентов, поддерживающих этот протокол — я буду использовать Termius. Жмём на «+ NEW HOST» и заполняем информацию.
В поле address вводим наш public dns, который заранее сохранили. В Username вводим «ubuntu», поле пароля оставляем пустым. Чтобы заполнить Key нужно добавить новый ключ — то есть, указать путь к файлу с расширением .pem, который мы получили при создании новой виртуальной машины. Должно получиться что-то такое:

Сразу после авторизации подключаемся к машине и получаем новый экран с консолью:

Для начала установим Clickhouse — это быстро. Выполним следующую команду, чтобы добавить репозиторий Clickhouse к нашим APT репозиториям:

Подробнее со всеми способами установки Clickhouse можно ознакомиться в документации

echo "deb http://repo.yandex.ru/clickhouse/deb/stable/ main/" | sudo tee /etc/apt/sources.list.d/clickhouse.list

Обязательно обновляем пакеты:

sudo apt-get update

Наконец, установим клиент и сервер командой:

sudo apt-get install -y clickhouse-server clickhouse-client

Готово! Клиент и сервер Clickhouse установлены на виртуальной машине. Запустим сервер, чтобы подсоединиться позже через клиент:

sudo service clickhouse-server start

И проверим успешность запуска командой:

sudo service clickhouse-server status

Получим примерно такой результат:

Clickhouse установлен! Для подключения к клиенту необходимо ввести:

clickhouse-client

Чтобы убедиться, что всё работает как надо, документация предлагает ввести запрос

SELECT 1

Вот, что мы должны получить:

Готово! А в следующем материале поработаем в связке Python + Clickhouse: вернёмся к нашему скрипту, который получает информацию по рекламным кампаниям и сделаем так, чтобы эти данные собирались в таблицу и материализованное представление.

В прошлый раз мы узнали, как получить полную информацию по рекламным кампаниям Facebook. А для анализа этой информации можно загрузить данные в Redash. Чтобы наш скрипт передавал данные в Redash, загрузим его в облако AWS и поднимем сервер на aiohttp. Но сперва необходимо немного улучшить наш скрипт.

from facebook_business.api import FacebookAdsApi
from facebook_business.adobjects.adaccount import AdAccount
from facebook_business.adobjects.adreportrun import AdReportRun
from facebook_business.adobjects.adsinsights import AdsInsights
from facebook_business.adobjects.adaccountuser import AdAccountUser as AdUser
from facebook_business.exceptions import FacebookRequestError
import time

Redash, к примеру, принимает данные в формате json. Ранее мы не разбирали этот источник данных, сегодня изучим и его. Файл формата json выглядит как список словарей — его наш новый скрипт и будет передавать в Redash, предварительно переведя в json. Из прошлого скрипта нам понадобятся переменная с полями, access token, app id, app secret и две функции: wait_for_async_job() и get_insights(). Вторая функция принимает все параметры аккаунта и асинхронно собирает информацию по рекламным кампаниям — из них мы выбираем поля клики, просмотры, затраты и даты.

В материале используется практически весь скрипт из статьи «Собираем данные по рекламным кампаниям Facebook»

Получение данных обернём в функцию return_json() — она будет вызывать get_insights(), заполняя список insights_lists. Так как рекламных кампаний в аккаунте может быть несколько, наш результат окажется не списком словарей, а списком списков словарей. Для этого используем простое лямбда-выражение, «сглаживающее» список списков в список. Затем возвращаем наш insights_lists_flatten.

def return_json():
   insights_lists = []
   date_preset = 'last_year'
   for elem in my_accounts:
       elem_insights = get_insights(elem, date_preset)
       insights_lists.append(elem_insights)
   flatten = lambda lst: [item for sublist in lst for item in sublist]
   insights_lists_flatten = flatten(insights_lists)
   return insights_lists_flatten

Теперь наш скрипт возвращает список словарей с информацией. Но ещё нам нужен сервер на aiohttp, который будет собирать список и возвращать его в формате json. Создадим новый файл, импортируем библиотеку aiohttp и функцию get_json() из нашего прошлого скрипта. Напишем простой обработчик запросов — данные из Facebook скрипт получает асинхронно, так что асинхронная функция handler будет «спать», пока вся информация не будет собрана и передана. Возвращает функция данные через json_response, чтобы информация передавалась в формате json.

from aiohttp import web
from get_json import return_json
 
async def handler(request):
   data = return_json()
   return web.json_response(data)

Далее инициализируем и запускаем наше приложение.

app = web.Application()
app.add_routes([web.get('/json', handler)])
web.run_app(app)

Теперь идём на AWS, создаём папку и через sftp кидаем туда оба скрипта. Проверим, что в ACL на AWS нужный порт открыт. Заходим в console — network & security — security groups — default.

Запускаем файл с сервером. После запуска можно проверить, работает ли скрипт, обратившись к нему по ip сервера с портом 0880 на route, указанный в скрипте с сервером — там мы указали /json.

Теперь обратимся через Redash к URL и получим ту самую таблицу, которую вернул скрипт:

url: ip сервера

Сформировав query results, можем написать запрос следующего вида:

select date_start, sum(clicks) as clicks, sum(impressions) as impressions, sum(spend) as spend from query_45
group by date_start

Получаем такую таблицу, сгруппированную по колонке date_start:

Теперь по этой таблице можно отобразить данные на графике — посмотрим, как количество затрат на рекламу влияет на количество кликов по ней:

Готово! В следующий раз поработаем с ВКонтакте — получим информацию по рекламным кампаниям уже оттуда.

Давайте узнаем, как получить информацию о затратах, кликах и показах рекламных кампаний из Facebook.

from facebook_business.api import FacebookAdsApi
from facebook_business.exceptions import FacebookRequestError
from facebook_business.adobjects.adaccount import AdAccount
from facebook_business.adobjects.adreportrun import AdReportRun
from facebook_business.adobjects.adsinsights import AdsInsights
from facebook_business.adobjects.campaign import Campaign
from facebook_business.adobjects.adset import AdSet
from facebook_business.adobjects.adaccountuser import AdAccountUser as AdUser
from facebook_business import adobjects
from matplotlib import pyplot as plt
from pandas import DataFrame
import time

Получение ключа

Первое, что необходимо сделать для работы с Facebook API — создать приложение. Для этого заходим на https://developers.facebook.com — My Apps — Create App. Заполняем название приложения, контактный Email и жмём на «Create App ID»

Перед нами открывается Dashboard приложения. Переходим в Settings — Basic и записываем себе App ID и App Secret. Эти данные понадобятся для авторизации.

Теперь переходим в Tools — Graph API Explorer и оказываемся в меню для создания нового access token.

Токены выдаются под разные нужды, и нам нужно задать определенные права для нашего. Понадобится только на ads_managment — это право позволяет получать информацию о рекламных кампаниях вашего аккаунта Facebook. Добавляем его и нажимаем на «Generate Access Token».

Осторожно — user access token изначально выдается как short-lived, уже через 1-2 часа он перестанет действовать. Для получения long-lived Token можно нажать на синюю кнопку, чтобы открыть Access Token Info, а затем на «Open in Access Token Tool». Откроется новая страница, внизу которой появится синяя кнопка «Extend Access Token». После этого сгенерируется новый long-lived Access Token, действующий 60 дней.

Написание скрипта

Создадим три переменных, в которые запишем access token, App ID и App Secret. Авторизуемся через метод init() класса FacebooksAdsApi и добавляем пользователя. Метод get_ad_accounts() вернёт нам данные по всем нашим рекламным аккаунтам в виде словаря. По этим же данным можем получить информацию о кампаниях методом get_campaigns().

my_access_token = ''
my_app_id = ''
my_app_secret = ''
FacebookAdsApi.init(my_app_id, my_app_secret, my_access_token)

me = AdUser(fbid='me')
my_accounts = list(me.get_ad_accounts())
my_accounts

my_account = my_accounts[0]
campaigns = my_account.get_campaigns()
print(campaigns)

Попробуем получить amount spent через my_account. Для этого воспользуемся методом api_get(), передав в параметр fields поле AdAccount.Field.amount_spent. Теперь, чтобы получить желаемые данные, выведем поле у переменной my_account, поделив на 100, чтобы обрубить копейки. Расходы получаем в валюте аккаунта, в нашем случае это RUB. То, ради чего мы всё это затеваем — получить данные о расходах на рекламные кампании для последующего анализа.

my_account.api_get(fields=[AdAccount.Field.amount_spent])
print(int(my_account[AdAccount.Field.amount_spent])/100)

Объявим переменную fields — в этом списке будут храниться поля, которые мы хотим получать: id кампании, количество кликов, затрат и просмотров. Теперь опишем две функции. Первая будет асинхронно отправлять запросы к Facebook и возвращать результаты. Вторая — формирует эти запросы и передает в первую функцию. В результате будем получать список словарей.

fields = [
    AdsInsights.Field.campaign_id,
    AdsInsights.Field.clicks,
    AdsInsights.Field.spend,
    AdsInsights.Field.impressions]

count = 0

def wait_for_async_job(async_job):
    global count
    async_job = async_job.api_get()
    while async_job[AdReportRun.Field.async_status] != 'Job Completed' or async_job[
        AdReportRun.Field.async_percent_completion] < 100:
        time.sleep(2)
        async_job = async_job.api_get()
    else:
        print("Job " + str(count) + " completed")
        count += 1
    return async_job.get_result(params={"limit": 1000})

def get_insights(account, date_preset='last_3d'):
    account = AdAccount(account["id"])
    i_async_job = account.get_insights(
        params={
            'level': 'ad',
            'date_preset': date_preset,
            'time_increment': 1},
            fields=fields,
            is_async=True)
    results = [dict(item) for item in wait_for_async_job(i_async_job)]
    return results

Следующий шаг — получение искомых данных о затратах. Будем собирать данные за всё время, поэтому заведём переменную date_preset, значение которой поставим lifetime. И для каждого аккаунта вызовем функцию get_insights(), а список, который она возвращает, положим в insights_lists.
Создадим DataFrame и вытащим из insights_lists интересующие данные — это id кампании, количество кликов, затраты и просмотры.

elem_insights = []
insights_lists = []
date_preset = 'last_year'
for elem in my_accounts:
            elem_insights = get_insights(elem, date_preset)
            insights_lists.append(elem_insights)

insight_campaign_id_list = []
insight_clicks_list = []
insight_spend_list = []
insight_impressions_list = []
insight_date_start_list = []
insight_date_stop_list = []
for elem1 in insights_lists:
    for elem2 in elem1:
        insight_campaign_id_list.append(int(elem2['campaign_id']))
        insight_clicks_list.append(int(elem2['clicks']))
        insight_spend_list.append(float(elem2['spend']))
        insight_impressions_list.append(int(elem2['impressions']))
        insight_date_start_list.append(elem2['date_start'])
        insight_date_stop_list.append(elem2['date_stop'])

df = DataFrame()
df['campaign_id'] = insight_campaign_id_list
df['clicks'] = insight_clicks_list
df['spend'] = insight_spend_list
df['impressions'] = insight_impressions_list
df['date_start'] = insight_date_start_list
df['date_stop'] = insight_date_stop_list

Получим такой DataFrame:

Давайте подытожим эти данные — сгруппируем по кампаниям и посчитаем суммы каждой колонки для групп. Для этого в pandas реализованы методы groupby() и sum() — достаточно указать, по какой колонке проводить группировку.

df.groupby(['campaign_id']).sum()

Теперь построим два графика — по количеству кликов и просмотров относительно дат. Воспользуемся атрибутом rcParams, чтобы задать размеры графиков.

plt.rcParams['figure.figsize'] = [20, 5]
plt.plot(df.date_start.str.replace('2019-', ''), df.clicks)

plt.rcParams['figure.figsize'] = [20, 5]
plt.plot(df.date_start.str.replace('2019-', ''), df.impressions)

Вот и всё! Получили всю информацию о затратах, просмотрах и кликах. В следующем материале посмотрим, как выгрузить её в формате json и передать в Redash для последующего анализа и визуализации.

Пост написан при участии команды Valiotti Analytics: Елисей Рыков (младший аналитик), Георгий Ушаков (технический консультант)

В одной из прошлых заметок мы рассматривали построение Retention-отчета и в нем частично затрагивали понятие когорт.
Под когортой обычно подразумевают группу пользователей продукта или компании. Чаще всего группы выделяют на основе времени установки приложения / появления пользователя в системе.
Выходит, что используя когортный анализ, можно отследить как повлияли изменения в продукте на поведение пользователей (например, на старых и новых пользователей).

Вместе с этим когорты можно определить исходя и из других параметров: география пользователя, источник трафика, платформа устройства и другие важные параметры вашего продукта.

Мы разберемся с тем, как сравнить Retention пользователей недельных когорт в Redash, поскольку у Redash имеется специальный тип визуализации для построения такого отчета.
Определимся для начала c SQL-запросом. У нас как и прежде две таблицы — user (id пользователя и время установки приложения) и client_session — таймстемпы (created_at) активности каждого пользователя (user_id). Будем считать Retention первых семи дней для недельных когорт за последние 60 дней.
Запрос написан в Cloudera Impala, рассмотрим его.

Для начала построим общую численность когорт:

select trunc(from_unixtime(user.installed_at), "WW") AS cohort_week, 
	ndv(distinct user.id) as cohort_size //считаем количество пользователей в когорте
	from user 
	where from_unixtime(user.installed_at) between date_add(now(), -60) and now() //берем зарегистрированных за последние 60 дней
group by trunc(from_unixtime(user.installed_at), "WW")

Вторая часть запроса поможет посчитать количество активных пользователей на каждый день в течение первых тридцати:

select trunc(from_unixtime(user.installed_at), "WW") AS cohort_week, 
        datediff(cast(cs.created_at as timestamp),cast(user.installed_at as timestamp)) as days,
	ndv(distinct user.id) as value  //считаем количество активных пользователей на каждый день
		from user 
		left join client_session cs on user.id=cs.user_id
where from_unixtime(user.installed_at) between date_add(now(), -60) and now()
and from_unixtime(cs.created_at) >= date_add(now(), -60) //берем сессии за последние 60 дней
and datediff(cast(cs.created_at as timestamp),cast(user.installed_at as timestamp)) between 0 and 30 //отрезаем только первые 30 дней активности
group by 1,2

Итого запрос целиком:

select size.cohort_week, size.cohort_size, ret.days, ret.value from
(select trunc(from_unixtime(user.installed_at), "WW") AS cohort_week, 
		ndv(distinct user.id) as cohort_size 
	from user 
	where from_unixtime(user.installed_at) between date_add(now(), -60) and now()
group by trunc(from_unixtime(user.installed_at), "WW")) size
left join (select trunc(from_unixtime(user.installed_at), "WW") AS cohort_week, 
        datediff(cast(cs.created_at as timestamp),cast(user.installed_at as timestamp)) as days,
		ndv(distinct user.id) as value 
		from user 
		left join client_session cs on user.id=cs.user_id
where from_unixtime(user.installed_at) between date_add(now(), -60) and now()
and from_unixtime(cs.created_at) >= date_add(now(), -60)
and datediff(cast(cs.created_at as timestamp),cast(user.installed_at as timestamp)) between 0 and 30
group by 1,2) ret on size.cohort_week=ret.cohort_week

Отлично, теперь нам доступны правильно посчитанные данные.

Данные когорт в табличном виде

Создадим новую визуализацию в Redash и правильно укажем параметры:

Важно правильно указать параметры — им соответствуют колонки результирующего запроса

Обязательно отметим, что у нас недельные когорты:

Вуа-ля, наша визуализация когорт готова:

К ней можно добавить фильтры и параметры и использовать в дашборде

Материалы по теме

• Когортный анализ. Метрики продукта vs метрики роста
• A beginners guide to cohort analysis

Disclaimer: заметка носит технический характер, поэтому может быть интересна меньшему числу лиц с бизнес-бэкграундом.

В этом блоге еще ни разу не затрагивалась тема Clickhouse, одной из самых быстрых баз данных от компании Яндекс. Краткая справка без деталей: Clickhouse — наиболее эффективно написанная СУБД колоночного типа с точки зрения программного кода, информация о СУБД довольно подробно описана в документации и во множестве видео на Youtube (раз, два, три).

В своей практике последние четыре года я использовал Clickhouse в качестве аналитика и эксперта по построению аналитической отчетности. В основном для решения задачи визуализации отчетности / отчетов с параметрами / дашбордов использовался Redash как наиболее удобный интерфейс для доступа к данным Clickhouse.
Однако совсем недавно в Looker, о котором я рассказывал ранее, появилась возможность подключить Clickhouse в качестве источника данных. Следует заметить, что подключение к Clickhouse в Tableau существует уже довольно давно.

Архитектура аналитического сервиса, в основе которого лежит Clickhouse, в основном облачная. И в рассматриваемой задаче было именно так. Предположим, у вас существует выделенный instance EC2 в Amazon (на который вы установили Clickhouse) и отдельный Kafka-кластер (решение Amazon MSK).

Задача: подключить Clickhouse в качестве consumer для получения информации с брокеров вашего кластера Kafka. На самом деле, в документации на сайте Amazon MSK довольно подробно описано как именно подключаться к кластеру Kafka, не буду дублировать эту информацию. В моем случае гайд помог: топики создавались продюсером с машины, на которой установлен Clickhouse и с неё читались консюмером.

Но возникла проблема: при подключении Clickhouse к Kafka в качестве консюмера, происходила следующая ошибка:

020.02.02 18:01:56.209132 [ 46 ] {e7124cd5-2144-4b1d-bd49-8a410cdbd607} <Error> executeQuery: std::exception. Code: 1001, type: cppkafka::HandleException, e.what() = Local: Timed out, version = 20.1.2.4 (official build) (from 127.0.0.1:46586) (in query: SELECT * FROM events), Stack trace (when copying this message, always include the lines below):

Продолжительное время я искал информацию в документации Clickhouse о том, что может вызывать эту ошибку, но не смог ничего найти. Следующей мыслью стала проверка работы локального брокера Kafka с той же машины. Установил клиент Kafka, подключил Clickhouse, отправил данные в топик и Clickhouse с легкостью их прочитал, т. е. консюмер Clickhouse работает с локальным брокером, а значит и вовсе работает.

Поговорив со всеми своими знакомыми экспертами в области инфраструктуры и Clickhouse (Вася, Макс, привет!), с ходу мы не смогли определить в чем проблема. Проверили firewall, настройки сети, все было открыто. Подтверждалось также тем, что с локальной машины можно было отправить в топик удаленного брокера Kafka сообщения командой bin/kafka-console-producer.sh и прочитать оттуда же bin/kafka-console-consumer.sh.

Затем мне пришла в голову мысль обратиться к главному гуру и разработчику Clickhouse — Алексею Миловидову. Алексей с радостью постарался ответить на возникшие вопросы и предложил ряд гипотез, которые мы проверили (трассировку сетевых подключений и т. п.), однако и после более низкоуровневого аудита проблему локализовать не удалось. Тогда Алексей посоветовал обратиться к Михаилу Филимонову из компании Altinity. Михаил оказался очень отзывчивым экспертом и одну за другой предлагал гипотезы для проверки (параллельно подсказывая как именно будет лучше их проверить).

В итоге в ходе совместных усилий мы обнаружили, что проблема возникает у библиотеки librdkafka, так как другой пакет kafkacat, который использует эту же библиотеку отваливается от подключения к брокеру с той же проблемой (Local: timed out).

После изучения подключения через bin/kafka-console-consumer.sh и параметров подключения, Михаил посоветовал добавить такую строку в /etc/clickhouse-server/config.xml:

<kafka><security_protocol>ssl</security_protocol></kafka>

И, о чудо! Clickhouse подключился к кластеру и вытянул необходимые данные с брокера.

Надеюсь, этот рецепт и мой опыт позволит вам сэкономить время и силы на изучение похожей проблемы :)

Самое удобное и полезное в Redash — параметры. Параметры могут быть в отчете и дашбордах.
Параметр — элемент интерфейса, которым управляет пользователь при запуске отчета.

Параметр в отчете может быть следующих типов:

Про каждый тип по отдельности:

• Text — текстовое поле свободного ввода, может использоваться в конструкциях типа LIKE, параметр такого типа не может использоваться в публичных дашбордах
• Number — число, которое вводит пользователь
• Dropdown list — список значений, из которых пользователь сможет выбрать одно или несколько (недавно появилась возможность мульти-ввода параметров в выпадающим списке)
• Query dropdown list — аналогично предыдущему, однако значения будут браться из имеющегося запроса
• Date / Date and Time / Date and Time (with seconds) — поля ввода даты
• Date Range / Date and Time Range / Date and Time Range (with seconds) — поля ввода интервалов дат. Удобно использовать для конструкции вида

between '{{parameter.start}}' and '{{parameter.end}}'

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

Из неочевидных, но крайне полезных решений: как сделать параметр отключаемым?
Предположим, что параметр типа «выпадающий список» называется parameter и мы хотим его настроить для столбца geo таблицы, тогда в коде запроса будет что-то типа:

WHERE
    ('{{parameter}}' = 'Disabled' or geo = '{{parameter}}')

При этом, разумеется, в значения выпадающего списка нужно добавить ’Disabled’.

Вчера мне удалось выступить на крупнейшей маркетинговой конференции в Казахстане: Kazakhstan Marketing Conference 2020.

Город Алматы произвел на меня положительное впечатление, а сама конференция оказалась высокопрофессиональным мероприятием, наполненным массой умных разносторонних и доброжелательных людей.

Приятный бонус для участников конференции: презентация моего выступления доступна на slideshare (осторожно, VPN!), так можно будет вспомнить о чем шла речь.

Помимо выступления в основной секции форума я проводил мастер-класс на тему: «Как построить понятное техническое задание на аналитику?».
И в рамках работы с аудиторией нам удалось сформулировать тезисы для шаблона технического задания.

Делюсь шаблоном, он окажется полезным для всех, кто сталкивается со сложностями в переводе задачи с бизнес-языка на технический.

С недавних пор блог Left Join стал доступен на английском языке. Пока готова только треть текущего контента и английский блог постепенно наполняется.

Соответствующая ссылка появилась в правом верхнем углу этого блога.
Буду благодарен вашим комментариям по текстам на английском языке :)

Сегодня поговорим о BI-платформе Looker, над которой мне удалось поработать в течение 2019-го года.

Представляю краткое содержание статьи для удобной и быстрой навигации:

1. Что такое Looker?
2. Как и к каким СУБД можно подключиться через Looker?
3. Построение Looker ML модели данных
4. Режим Explore (исследование данных на построенной модели)
5. Построение отчетов и сохранение их в Look
6. Примеры дашбордов в Looker

Что такое Looker?

Создатели Looker позиционируют его как программное обеспечение класса business intelligence и платформу big data аналитики, которая помогает исследовать, анализировать и делиться аналитикой бизнеса в режиме реального времени.
Looker — это действительно удобный инструмент и один из немногих продуктов класса BI, который позволяет в режиме онлайн работать с преднастроенными кубами данных (на самом деле, реляционными таблицами, которые описаны в Look ML-модели).
Инженеру, работающему над Looker, требуется описать модель данных на языке Look ML (что-то среднее между CSS и SQL), опубликовать эту модель данных и далее настроить отчетность и дашборды.
Сам Look ML достаточно прост, взаимосвязи между объектами данных задаются data-инженером, что впоследствии позволяет использовать данные без знания языка SQL (если быть точным: движок Looker сам за пользователя генерирует код на языке SQL).

Буквально недавно, в июне 2019-го года Google объявил о покупке платформы Looker за $2.6 млрд.

Как и к каким СУБД можно подключиться через Looker?

Выбор СУБД, с которыми работает Looker довольно богатый. На скриншоте ниже перечислены всевозможные подключения на Октябрь 2019 г.:

Доступные СУБД для подключения

Настроить подключение к базе данных несложно через веб-интерфейс:

Веб-интерфейс подключения к СУБД

В вопросе соединений с базами данных отдельно хочется отметить два факта: к сожалению, на текущий момент и в ближайшем будущем отсутствует поддержка Clickhouse от Яндекса. Скорее всего поддержка не появится, учитывая тот факт, что Looker был приобретен конкурирующей компанией Google.
Второй досадный факт состоит в том, что построить одну модель данных, которая бы обращалась в разные СУБД нельзя. В Looker нет встроенного хранилища, которое могло бы объединять результаты запроса (кстати, в отличии даже от того же Redash).
Это означает, что аналитическая архитектура данных должна быть построена в рамках одной СУБД (желательно с высоким быстродействием или на агрегированных данных).

Построение Looker ML модели данных

Для того чтобы построить отчет или дашборд в Looker, предварительно необходимо настроить модель данных. Синтаксис языка Look ML достаточно подробно описан в документации. От себя могу добавить, что описание модели не требует долгого погружения для специалиста со знанием SQL. Скорее, необходимо перестроить подход к подготовке модели данных. Язык Look ML очень похож на CSS:

Консоль создания Look ML модели

В модели данных прописываются связи с таблицами, ключи, гранулярность, информация о том, какие поля являются фактами, какие измерениями. Для фактов прописывается агрегация. Разумеется, при создании модели можно использовать различные IF / CASE выражения.

Режим Explore

Наверное, это самая главная киллер-фича Looker, поскольку позволяет любым бизнес-поздразделениям самостоятельно получать данные без привлечения аналитиков / инженеров данных. И, видимо, поэтому использование аккаунтов с использованием режиме Explore тарифицируется отдельно.

Фактически, режим Explore это интерфейс, который позволяет использовать настроенную Look ML модель данных, выбрать необходимые метрики и измерения и построить кастомный отчет / визуализацию.
К примеру, мы хотим понять сколько каких действий в интерфейсе Лукера было совершено на прошлой неделе. Для этого, используя режим Explore, мы выберем поле Date и поставим на него фильтр: прошлая неделя (Looker в этом смысле достаточно умный и в фильтре будет достаточно написать ‘Last week’), затем из измерений выберем «Категорию» и в качестве метрики — Количество. После нажатия кнопки Run сформируется готовый отчет.

Построение отчета в Looker

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

Применение визуализации к отчету

Построение отчетов и сохранение их в Look

Полученный набор данных / визуализацию в режиме Explore иногда хочется сохранить и поделиться с коллегами, для этого в Looker существует отдельная сущность — Look. Это готовый построенный отчет с выбранными фильтрами / измерениями / фактами.

Пример сохраненного Look

Примеры дашбордов в Looker

Систематизируя склад созданных Look зачастую хочется получить готовую композицию / overview ключевых метрик, которые было бы видно на одном листе.
Для этих целей отлично подходит создание дашборда. Дашборд создается либо на лету, либо используя ранее созданные Look. Одной из «фишек» дашборда является установка параметров, которые меняются на всем дашборде и могут быть применены ко всем Look сразу.

Интересные фишки одной строкой

• В Looker можно ссылаться на другие отчеты и, используя данных функционал, можно создать динамический параметр, который передается по ссылке.
  Например, построили отчет с разделениям выручки по странам и в этот отчете можем ссылаться на дашборд по отдельно взятой стране. Переходя по ссылке, пользователь видит дашборд по конкретной стране, на которую перешел
• На каждой странице Looker существует чат, в котором оперативно отвечает поддержка
• Looker не умеет работать с merge данных на уровне разных СУБД, однако может объединить данные на уровне готовых Look (в нашем случае эта функциональность работает очень странно).
• В рамках работы с различными моделями данных, я обнаружил крайне нетривиальное использование SQL для подсчета уникальных значений в ненормализованный таблице данных, Looker называет это симметричными агрегатами.
  SQL действительно выглядит очень нетривиально:

SELECT 
 order_items.order_id AS "order_items.order_id",
 order_items.sale_price AS "order_items.sale_price",
 (COALESCE(CAST( ( SUM(DISTINCT (CAST(FLOOR(COALESCE(users.age ,0)
 *(1000000*1.0)) AS DECIMAL(38,0))) + 
 CAST(STRTOL(LEFT(MD5(CONVERT(VARCHAR,users.id )),15),16) AS DECIMAL(38,0))
 * 1.0e8 + CAST(STRTOL(RIGHT(MD5(CONVERT(VARCHAR,users.id )),15),16) AS DECIMAL(38,0)) ) 
 - SUM(DISTINCT CAST(STRTOL(LEFT(MD5(CONVERT(VARCHAR,users.id )),15),16) AS DECIMAL(38,0))
 * 1.0e8 + CAST(STRTOL(RIGHT(MD5(CONVERT(VARCHAR,users.id )),15),16) AS DECIMAL(38,0))) ) 
 AS DOUBLE PRECISION) 
 / CAST((1000000*1.0) AS DOUBLE PRECISION), 0) 
 / NULLIF(COUNT(DISTINCT CASE WHEN users.age IS NOT NULL THEN users.id 
 ELSE NULL END), 0)) AS "users.average_age"
FROM order_items AS order_items
LEFT JOIN users AS users ON order_items.user_id = users.id

GROUP BY 1,2
ORDER BY 3 DESC
LIMIT 500

• При внедрении Looker к покупке обязателен JumpStart Kit, который стоит не менее $6k, в рамках которого вы получаете поддержку и консультации от Looker при внедрении инструмента.

Введение

• Глоссарий

  • Revenue / Income / Sales — выручка, доход (руб. / $ / евро)
  • GMV (Margin) — маржа (% / руб. )
  • MAU (monthly active users) — уникальное число активных пользователей за месяц (шт.)
  • WAU (weekly active users) — уникальное число активных пользователей за неделю (шт.)
  • DAU (daily active users) — уникальное число активных пользователей за день (шт.)
  • Requests — запросы (за рекламой) (шт.)
  • Impressions — показы (рекламы) (шт.)
  • Clicks — клики (на рекламу) (шт.)
  • FR (fill rate) — заполняемость ( =Impressions / Requests) (%)
  • CTR (click through rate) — кликабельность ( =Clicks / Impressions) (%)
  • С1 (conversion first purchase) — конверсия в первую покупку (%)
  • R, R1, R3, R7 (retention) — удержание (1го, 3го, 7го дня) (%)
  • RR (rolling retention) (%)
  • Churn — отток (%)
  • ARPU (average revenue per user) — средняя выручка на пользователя (руб. / $ / евро)
  • ARPPU (average revenue per paying user) — средняя выручка на платящего пользователя (руб. / $ / евро)
  • cARPU (cumulative average revenue per users) — накопленная средняя выручка на пользователя (руб. / $ / евро)
  • LTV (lifetime value) / CLV (customer lifetime value) — пожизненная ценность клиента
  • ROI (return of investment) — возврат инвестиций (%)
  • ROAS (return on advertising spend) — окупаемость расходов на рекламу (%)
  • ROMI (return of marketing investment) — возврат маркетинговых инвестиций (%)
  • CPA (cost per action) — стоимость действия (напр., покупка или установка приложения) (руб. / $ / евро)
  • CPC (cost per click) — стоимость клика (руб. / $ / евро)
  • CPO (cost per order) — стоимость заказа (руб. / $ / евро)
  • CPS (cost per sale) — стоимость продажи (руб. / $ / евро)
  • CPM (cost per mille) — стоимость тысячи рекламных показов (руб. / $ / евро)
  • CAC (customer acquisition cost) — стоимость привлечения клиента (руб. / $ / евро)
  • CARC (customer acquision and retention cost) — стоимость привлечения и удержания клиента (руб. / $ / евро)
  • ДРР — доля рекламных расходов (%)

Для чего нужна маркетинговая аналитика?

Чтобы разобраться с аналитическими метриками, для начала следует разобраться зачем вообще нужна маркетинговая аналитика и на какие вопросы она может дать ответ. В целом, маркетинговая аналитика — это изучение и измерение в количественных показателях маркетинговой деятельности. При этом чаще всего цель данных действий — оценить эффективность маркетинга, посчитать окупаемость маркетинговых инвестиций в компанию.
Маркетинговая аналитика помогает найти ответы на следующие вопросы:

• Насколько эффективно расходуется маркетинговый бюджет?
• Какой ROMI дают разные маркетинговые каналы?
• Какая целевая аудитория наиболее эффективно конвертируется?
• Какие каналы коммуникаций наиболее / наименее прибыльны?
• Что является наибольшим источником дохода компании?

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

Ключевые принципы построения правильной аналитики в маркетинге

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

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

Объективность
Необходимо, решая задачу, рассматривать данные, которые влияют на проблему, с разных сторон. Разные показатели, разная агрегация данных позволит взглянуть на проблему объективно, желательно, чтобы один и тот же вывод из данных, повторялся как минимум дважды.

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

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

Подобные принципы однозначно помогут нанять компетентных аналитиков для построения корректной отчетности.

Как структурировать показатели?

Систему метрик, помогающих оценивать эффективность маркетинга, можно построить исходя из нескольких соображений. Один из ключевых подходов к структуризации — жизненный цикл клиента. Постараемся разобраться в нем и поговорим об одном из интересных фреймворков для работы над такой системой метрик. В жизненном цикле клиента можно выделить основные этапы:
1) Охват аудитории — работа маркетолога начинается еще до того момента, как потенциальная аудитория становится клиентами компании
2) Вовлечение — этап конверсии зашедших пользователей на сайт / в мобильное приложение в зарегистрированных клиентов
3) Монетизация — этап формирования платящих пользователей (из зарегистрированных)
4) Удержание / Отток — мероприятия направленные на развитие и удержание привлеченной аудитории, снижение уровня оттока

Метод AARRR / Pirate Metrics

В 2007-ом году Дейвом МакКлюром был разработан и предложен метод AARRR — система метрик, помогающая стартапам разобраться в бизнес-показателях. Другое название метода, которое также можно встретить, — "пиратские метрики" из-за того, что название произносится на пиратский лад: "ааррр!".
Итак, разберемся в подходе и поговорим о метриках, соответствующих каждому этапу "воронки". Аббревиатура состоит из 5-ти ключевых маркетинговых этапов:

• Аcquisition — привлечение (соответствует п. 1 выше)
• Аctivation — активация (соответствует п. 2 выше)
• Retention — удержание (соответствует п. 4 выше)
• Revenue — доход / монетизация (соответствует п. 3 выше)
• Referral — рекомендации (нововведенный этап)

На входе в воронку располагается целевая аудитория, которую мы хотим привлечь. Затем, всеми силами мы стараемся зарегистрировать потенциального покупателя и превратить его в зарегистрированного клиента (к этому моменту человек, зашедший к нам на сайт / в приложение должен осознавать ценность нашего продукта). После, клиент совершает покупки и возвращается к нам снова и снова. В конечном итоге, если ему очень нравится наш продукт, то он порекомендует его своим друзьям / знакомым.

AARRR-воронка, пиратские метрики (источник изображения)

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

Привлечение

Охват потенциальных покупателей — ключевой этап формирования новой аудитории. Изучим этот важный этап на примере мобильного приложения Grow Food и каналов привлечения трафика. Зачастую, аудитория попадают в приложение Grow Food из нескольких разных источников:

1. Органический трафик: поиск в Google, Yandex, Bing, etc
2. Органический мобильный трафик: поиск в Apple Store / Google Play
3. Коммерческий трафик: реклама в Facebook / Instagram, контекстная реклама (Adwords), мобильные рекламные сети.

Обсудим на примере рекламы в Facebook. Каждое рекламное объявление таргетируется на потенциальную рекламную аудиторию, которая в терминах Facebook называется "Охват". При этом мы можем оптимизировать показы рекламного объявления по кликам / конверсиям / etc. Наша задача получить максимально эффективную аудиторию за минимальные деньги. Следовательно, нужно выбрать метрики, которые помогут нам оценить эффективность, изучим их:

• Impressions — количество показов рекламного объявления, сам показатель мало о чем скажет и очень тесно связан с объемом потенциальной аудитории, однако потребуется нам для понимания остальных метрик.
• Clicks — число кликов на рекламное объявление, в абсолютном выражении, опять же зависит от числа показов.
• Installs — количество клиентов, установивших мобильное приложение
• CTR — кликабельность, рассчитывается как отношение Clicks / Impressions и показывает насколько эффективно наше объявление с точки зрения заинтересованности аудитории, другими словами, какова кликабельность нашего объявления
• CR (conversion rate) (= Installs / Clicks) — уровень конверсии, показывает какой процент пользователей установили приложение из тех, кто кликнул на рекламное объявление
• Spend — число денег, которые мы потратили на данное рекламное объявление
• CPC (= Spend / Clicks) — показывает нам стоимость одного клика, оперировать показателем следует в сравнении с другими объявлениями / рыночными бенчмарками
• CPM (= Spend / Impressions * 1000 ) — показывает нам стоимость тысячи показов рекламного объявления, используется для сравненияи с другими объявлениями / бенчмарками
• CPI (= Spend / Installs) — удельная стоимость одного инсталла
• Revenue — итоговый доход, который мы получили с данного рекламного объявления / кампании (необходимо иметь инструменты для правильной атрибуции)
• ROAS (= Revenue / Spend) — возврат инвестиций в рекламу, валовый доход с потраченного доллара, метрика показывает эффективность рекламной кампании с точки зрения вложенных в нее денег. К примеру, ROAS равный 300% говорит о том, что на каждый потраченный 1$ заработано 3$, а ROAS равный 30%, говорит о том, что на вложенный доллар заработано 30 центов.

Итого, мы уже имеем неплохую палитру метрик, с которыми можно работать — изучать их динамику, сравнивать объявления между собой, между разными источниками трафика. Например, простая табличка, содержащая эти показатели уже будет первым приближением к пониманию эффективности рекламы.

Facebook Campaign Efficiency

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

Резюме: мы можем измерять CTR разных баннеров и понимать какой из них интереснее для аудитории. Этот показатель можно использовать при A/B тестировании одного и того же баннера, выбирая наиболее эффективный. При подсчете эффективности помимо CTR следует иметь в виду CPC для того, чтобы выбрать не только наиболее кликабельный баннер, но и не самый дорогой.

Ключевые KPI, показатели эффективности с точки зрения денег — CPI / ROAS, первый показывает насколько дешево / дорого мы покупаем трафик, а второй — насколько хорошо купленный трафик монетизируется.

Активация

Предположим, что мы разрабатываем мобильную игру. Подумаем о том, что может являться активацией пользователя в этом случае? Мы привлекли пользователей, которые установили игру себе на смартфон. Наша следующая задача — зарегистрировать пользователя (сделать его игроком), предложить вводный тур для прохождения.
На этом этапе две метрики можно считать ключевыми: конверсия в зарегистрированного пользователя (= Registrations / Installs), конверсия в прошедших обучение (=Tutorial Users / Installs).

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

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

Удержание

Любая организация хотела бы, чтобы у нее существовала активная база лояльных клиентов, которые регулярно делают повторные заказы. В этой связи, очень важно отслеживать несколько ключевых метрик: Retention rate (или Rolling retention), Churn. Подробно я разбирал построение retention и rolling retention отчетов в одном из прошлых выпусков блога.

Другой важной фундаментальной метрикой можно считать Sticky Factor — степень вовлеченности пользователей. Sticky Factor за неделю достаточно просто рассчитывается: DAU / WAU * 100%. Разберем, более подробно на прошлом примере. У нас как и прежде есть таблица — client_session, в которой по каждому user_id хранятся таймстемпы активности created_at. Тогда расчет Sticky-фактора довольно несложно выполняется следующим SQL-запросом:

SELECT daily.dau/weekly.wau*100 AS sticky
FROM
-- Считаем среднее DAU за неделю
( SELECT avg(dau) AS dau
FROM
(SELECT from_unixtime(cs.created_at, "yyyy-MM-dd") AS event_date,
ndv(cs.user_id) AS dau
FROM client_session cs
WHERE 1=1
AND from_unixtime(cs.created_at)>=date_add(now(), -7)
AND from_unixtime(cs.created_at)<=now()
GROUP BY 1) d) daily,
-- Считаем WAU за неделю
( SELECT ndv(cs.user_id) AS wau
FROM client_session cs
WHERE 1=1
AND from_unixtime(cs.created_at)>=date_add(now(), -7)
AND from_unixtime(cs.created_at)<=now() ) weekly

Вместе с фундаментальными метриками следует рассматривать метрики, связанные с инструментами удержания клиентской базы. Такими могут являться инструменты директ-маркетинга: sms, email, push-уведомления. У каждого из инструментов обычно бывают такие описательные метрики: число отправленных сообщений / число доставленных сообщений / количество вернувшихся пользователей. Они показывают эффективность каждого из инструментов.

Монетизация

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

Чаще всего оперируют рядом относительных метрик, которые описывают поведение пользователей:
ARPU ( = Revenue / Users )— средняя выручка на одного пользователя
cARPU( = cumulative Revenue / Users ) — накопленная средняя выручка на одного пользователя
ARPPU ( = Revenue / Payers ) — средняя выручка на платящего пользователя
Avg Receipt (= Revenue / Purchases ) — средний чек
LTV / CLV — совокупный доход на одного пользователя (жизненная ценность клиента)

Вопросу LTV я планирую посвятить отдельный пост, поскольку это достаточно обширная тема. В данном посте разберем ARPU, накопленный ARPU и связь c LTV. Метрика ARPU покажет нам сколько мы в среднем зарабатываем с пользователя за какой-то период времени (обычно день или неделя). Это полезная информация, но ее может быть недостаточно. Задача эффективного маркетинга — привлекать таких пользователей, которые приносят компании больше денег, чем затрачивается на их привлечение. Таким образом, если мы модифицируем показатель ARPU и рассмотрим накопленный ARPU, например, за 30, 60, 90, 180 дней, то получим неплохое приближение к LTV пользователя. Еще лучше если мы построим кривую накопленного ARPU по дням.

Кривая накопленного ARPU

Добавив горизонтальной линией CPI, мы получим крайне полезный для понимания график. В точке пересечения двух линий мы получим день, начиная с которого доход от пользователя становится выше, чем затраты на его привлечение (привлечение пользователя становится эффективным). В рассматриваемом выше примере это 56-ой день жизни клиента. Решение этой задачи похоже на поиск точки безубыточности, однако надо помнить, что компания также несет и другие косвенные затраты, которые необходимо заложить, чтобы корректно посчитать точку безубыточности.

Рекомендации

Наилучшим сценарием взаимодействия с клиентами и высшей степенью награды для компании можно считать рекомендацию продуктов компании друзьям, родственникам, знакомым. С точки зрения метрик можно выделить: количество активированных приглашенных новых пользователей на одного клиента и NPS.

Количество активированных рекомендаций позволяет увеличить CAC / CPI. К примеру, мы привлекаем пользователя за $1 и хотим сохранить такую тенденцию. Мы разработали механику реферальных ссылок и выявили, что теперь после внедрения средний пользователь приглашает двух других. Получается, что в таком случае стоимость привлечения пользователя составит $1 / 3 = $0.33. Соответственно, мы можем позволить себе привлекать пользователей за $3, сохранив приемлемое для нас значение CAC.

NPS (Net Promote Score) — метрика, которая показывает уровень потребительской лояльности. Механика расчета подробна описана на Википедии, не будем на ней останавливаться. Скажем лишь о том, что рекомендуем регулярно замерять NPS, используя директ-маркетинговые каналы коммуникаций.

Иерархия метрик в организации

Мы достаточно подробно изучили важные метрики каждого этапа AARRR, осталось разобраться каким образом можно структурировать показатели, чтобы получить идеальный дашборд.

Для решения этой задачи имеет смысл декомпозировать цели компании и соответствующие им метрики на разные уровни. Зачастую каждый следующий уровень соответствует отделу компании и является KPI этого отдела. Упрощенно, мы можем представить главную верхнеуровневую цель компании — Прибыль и декомпозировать ее на составные части: Выручка, Расходы.

Иерархия метрик организации

Хороший пример — школа английского языка SkyEng, на видео можно ознакомиться с детально проработанной структурой метрик SkyEng.

Другой альтернативой может стать построение структуры дашборда на основании разобранного выше фреймворка AARRR. Схематично такой дашборд мог бы выглядеть таким образом:

Заключение

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

Ссылки по теме:

• AARRR: Pirate Metrics for SaaS
• The effectiveness of the marketing funnel AARRR
• Продвижение по методу AARRR
• Аналитика в маркетинге: особенности digital, инструменты и чек-лист
• Привлечь пользователя и заработать на нём: эффективность маркетинговой воронки AARRR
• Важность маркетинговой аналитики

В Redash реализован удобный функционал построения алертов. Под алертами подразумеваются оповещения, возникающие при изменении какого-то конкретного показателя. При этом степень изменения пользователь задает самостоятельно вручную в интерфейсе. Алерты можно настроить на почту или в канал / личные сообщения Slack.

Вспомним как собрать данные, используя Google Analytics, и для этих данных настроим оповещения, используя внутреннюю БД Redash (query-results). В качестве основания для оповещения нас будет интересовать падение числа пользователей на сайте за вчерашний день более, чем на 30%.

Построение запроса к Google Analytics

{
    "ids": "ga:128886640",
    "start_date": "30daysAgo",
    "end_date": "yesterday",
    "metrics": "ga:users", 
    "dimensions": "ga:date"
}

В результате выполнения данного запроса мы получим число пользователей за последние 30 дней.

Включаем хранение query results

Одной из основных фишек redash считается возможность обращения к результатам выполнения запроса, которые хранятся во внутренней СУБД SQLite.
Для того, чтобы включить хранение результатов необходимо перейти в Data Sources и включить query-results (beta).

Теперь простой командой:

select * from query_37

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

Строим запрос для оценки изменений в числе пользователей

Для того, чтобы настроить оповещение необходимо в начале написать запрос, которые выдаст целевой показатель для проверки, в нашем случае — рост или падение числа пользователей на сайте.
Напишем запрос, который обращается ко внутренней СУБД Redash:

SELECT sum(CASE WHEN date(ga_date)=DATE('now', '-1 day') THEN ga_users ELSE NULL END) AS yesterday,
sum(CASE WHEN date(ga_date)=DATE('now', '-2 day') THEN ga_users ELSE NULL END) AS before_yesterday,
(sum(CASE WHEN date(ga_date)=DATE('now', '-1 day') THEN ga_users ELSE NULL END)*1.0/
sum(CASE WHEN date(ga_date)=DATE('now', '-2 day') THEN ga_users ELSE NULL END)*1.0-1)*100 AS difference
FROM query_37

В запросе выше мы считаем число пользователей за вчера и за позавчера, а также оцениваем изменение числа пользователей в процентах.
В текущем примере получили следующую таблицу данных, которую дальше будем использовать при настройке алерта:

Теперь, чтобы мы могли получать алерты, необходимо настроить выполнение запросом по расписанию (регулярное обновление результатов / scheduled query в терминах redash).

Поставим обновление на 10 утра каждого дня:

Настраиваем оповещение

Переходим в меню Create — Alert. Вводим название запроса, в моем случае — это «Alert on users».
Далее, можем поменять отображаемое название нотификации или оставить то, что предлагается системой.

Выбираем целевую метрику, в рассматриваемом примере — difference. Ниже в операторе сравненения (Op) выбираем less then и устанавливаем значение -30.

В блоке справа на странице необходимо выбрать куда именно будет отправляться алерт. Подробно о настройке пути следования алерта можно прочитать на официальном сайте Redash.

Теперь алерт появился на странице со списков алертов и по умолчанию находится в статусе OK.

Как только показатель превысит заданный нами уровень статус изменится на TRIGGERED, а оповещение отправится на почту / в Slack.

Больше по теме

• Настройка алертов на странице Redash
• Добавление Slack в качестве адреса для уведомлений

Две недели назад произошел финальный релиз Redash версии 8. Полный список усовершенствований представлен на странице релиза beta версии v8.

В своем обзоре новшеств остановлюсь только на тех, которые максимально значимо меняют пользовательский опыт использования Redash, то есть наиболее наглядны для нас с вами:

• Support for multi-select in dropdown (and query dropdown) parameters.
• Support for dynamic values in date and date-range parameters.
• Search dropdown parameter values.
• Pivot Table: support hiding totals.
• New Visualization: Details View.

Support for multi-select in dropdown (and query dropdown) parameters.

В 8-ой версии Redash появилась долгожданная поддержка выбора нескольких значений в параметре типа «выпадающий список» или «выпадающий список на основе запроса»:

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

Для нас это означает возможность использования в запросах условия типа IN с параметром:

При использовании таких значений параметра, мы получим следующий URL в строке браузера:

Support for dynamic values in date and date-range parameters.

Максимально удобная и полезная фича для выбора дат, которая часто используется в Tableau, но до сих пор не была реализована в Redash: использование динамических значений для дат:

При нажатии на молнию, можно выбрать произвольный период и Redash самостоятельно подставит нужные значения в запрос. Так, например, можно быстро посмотреть статистику за последнюю неделю или последний месяц.

Search dropdown parameter values.

Допустим, мы используем параметр типа «выпадающий список на основе запроса», и в выбранном запросе более 300 результирующих строчек, по которым нам надо найти интересующее нас значение. В 8-ой версии эта проблема решена автокомплитом и поиском по значениям параметра. В примере ниже я ввожу строку «Orga» и получаю все значения, в которых данная строка встречается, удобно.

Pivot Table: support hiding totals.

Достаточно долгожданная фича, позволяющая убрать итоги по строкам / столбцам.

New Visualization: Details View

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

На днях получил в подарок американский аналитический мерч, фотографией которого делюсь с вами:

Мерч от американской софтверной компании Looker

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

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

Разбавлю блог интересным отчетом, который в свое время был построен для компании Yota в ноябре 2011го года. Построить данный отчет нас вдохновила матрица BCG.

У нас было: один пакет Excel, 75 VBA макросов, ODBC подключение к Oracle, SQL-запросы к БД всех сортов и расцветок. На таком стеке и рассмотрим построение отчета, но в начале немного о самой идее отчета.

Матрица BCG — это матрица размером 2х2, на которой сегменты клиентов изображаются окружностями с центрами на пересечении координат, образуемых соответствующими темпами двух выбранных показателей.

Если упростить, то нам надо было поделить всех клиентов компании на 4 сегмента: ARPU выше среднего / ниже среднего, потребление трафика (основной услуги) выше среднего / ниже среднего. Таким образом получалось, что возникает 4 квадранта, в каждый из которых необходимо поместить пузырьковую диаграмму, где размер пузырька обозначает общее количество пользователей в сегменте. Дополнительно к этому добавляется еще один пузырек в каждом квадранте (более мелкий), который показывает отток в каждом сегменте (авторское усовершенствование).

Что хотелось получить на выходе?
График подобного вида:

Представление матрицы BCG на данных компании Yota

Постановка задачи более-менее ясна, перейдем к реализации отчёта.
Предположим, что мы уже собрали нужные данные (то есть научились определять средний ARPU и среднее потребление трафика, в данном посте не будем разбирать SQL-запрос), тогда первостепенная основная задача — понять как отобразить средствами Excel пузырьки в нужных местах.

Для этого на помощь приходит базовая пузырьковая диаграмма:

Вставка — Диаграмма — Пузырьковая

Идем в меню Выбор источника данных и оцениваем, что необходимо подготовить для построения диаграммы в нужном нам виде: координаты X, координаты Y, значения размеров пузырьков.

Отлично, выходит, если предположить, что наша диаграмма будет расположена в координатах по X от -1 до 1, а по Y от -1 до 1, то центр правого верхнего пузырька это точка (0.5; 0.5) на диаграмме. Аналогичным образом, расположим все остальные основные пузырьки.

Отдельно следует подумать о пузырьках типа Churn (для отображения оттока), они расположены правее и ниже основного пузырька и могут с ним пересекаться, поэтому правый верхний пузырек разместим в эмпирически полученных координатах (0.65; 0.35).

Таким образом, для четырех основных и четырех дополнительных пузырьков мы можем организовать данные в следующем виде:

Рассмотрим подробнее, как будем их использовать:

Итак, мы задаем по X — горизонтальные координаты центра наших пузырьков, которые лежат в ячейках A9:A12, по Y — вертикальные координаты центра наших пузырьков, которые лежат в ячейках B9:B12, а размеры пузырьков мы храним в ячейках E9:E12.
Далее, добавляем еще один ряд данных для Оттока и снова указываем все необходимые параметры.

Мы получим следующий график:

Дальше наводим красоту: меняем цвета, убираем оси и получаем красивый результат.

Добавив необходимые подписи данных, получим то, что требовалось в задаче.

Делитесь в комментариях — приходилось ли строить подобные графики, каким образом решали задачу?

В этой заметке разберём как правильно построить отчет по Retention с использованием Redash и языка SQL.
Для начала, в двух словах, что это за метрика Retention rate, почему она важна, какой бывает и каким образом считается.

Retention rate

Метрика Retention rate довольно широко распространена и особенно известна в мобильной индустрии, поскольку позволяет понять насколько хорошо продукт вовлекает пользователей в ежедневное использование. Вспомним (или узнаем) как рассчитывается Retention:
Retention дня X — это N% процентов пользователей, которые вернутся к продукту в день X. Иными словами, если в какой-то конкретный день (день 0) пришло 100 новых пользователей, а на 1-ый день вернулось 15, то Retention 1-го дня составит 15 / 100 = 15%.
Чаще всего выделяют Retention дней 1, 3, 7 и 30 как наиболее описательные метрики продукта, однако полезно в целом рассматривать кривую Retention и делать выводы исходя из нее.

Кривая retention

В конечном итоге нас интересует построение такой кривой, которая показывает удержание пользователей с 0-го дня до 30-го.

Кривая Retention rate c 0-го по 30-ый день

Rolling Retention (RR)

Кроме классического Retention rate выделяют также Rolling Retention (далее RR). При расчете Rolling Retention помимо дня X учитываются также все последующие дни. Таким образом RR 1-го дня — количество пользователей, которые вернулись в 1-ый и последующие дни.

Сравним Retention и Rolling Retention 10-го дня:
Retention₁₀ — количество пользователей, вернувшихся в 10-ый день / количество пользователей, установивших приложение 10 дней назад * 100%.
Rolling Retention₁₀ — количество пользователей, вернувшихся в 10-ый день или позже / количество пользователей, установивших приложение 10 дней назад * 100%.

Гранулярность (retention временных отрезков)

В некоторых отраслях и соответствующих задачах полезно понимать Retention конкретного дня (чаще всего в мобильной индустрии), в других случаях полезно понимать удержание пользователя на разных временных интервалах: например, недельные отрезки или месячные (часто полезно в e-commerce, ретейле).

Пример когорт по месяцам и соответствующий им месячный Retention

Как построить Retention отчет на языке SQL?

Выше мы разобрали как посчитать Retention в формулах. Теперь приложим это к языку SQL.
Допустим, что у нас есть две таблицы: user — храним данные об идентификаторах пользователей и мета-информацию, client_session — информация о посещениях пользователями мобильного приложения.
В запросе будут фигурировать только две эти таблицы, поэтому вы с легкостью сможете адаптировать запрос под себя.
примечание: в рамках данного кода я использую Impala в качестве СУБД.

Собираем размер когорт

SELECT from_unixtime(user.installed_at, "yyyy-MM-dd") AS reg_date,
          ndv(user.id) AS users
   FROM USER
   WHERE from_unixtime(user.installed_at)>=date_add(now(), -60)
     AND from_unixtime(user.installed_at)<=date_add(now(), -31)
   GROUP BY 1

Разберем этот довольно несложный запрос: по каждому дню мы считаем количество уникальных пользователей для отрезка [60 дней назад; 31 день назад].
Чтобы не лезть в документацию: команда ndv() в Impala аналог команды count(distinct).

Считаем количество вернувшихся пользователей по каждой когорте

SELECT from_unixtime(user.installed_at, "yyyy-MM-dd") AS reg_date,
          datediff(cast(cs.created_at AS TIMESTAMP), cast(installed_at AS TIMESTAMP)) AS date_diff,
          ndv(user.id) AS ret_base
   FROM USER
   LEFT JOIN client_session cs ON cs.user_id=user.id
   WHERE 1=1
     AND datediff(cast(cs.created_at AS TIMESTAMP), cast(installed_at AS TIMESTAMP)) between 0 and 30
     AND from_unixtime(user.installed_at)>=date_add(now(), -60)
     AND from_unixtime(user.installed_at)<=date_add(now(), -31)
   GROUP BY 1, 2

В этом запросе ключевая часть содержится в команде datediff: теперь мы считаем для каждой когорты и для каждого datediff количество уникальных пользователей все той же командой ndv() (фактически, количество пользователей которые вернулись в дни от 0-го до 30-го).

Отлично, теперь у нас есть размер когорт и количество вернувшихся пользователей

Собираем все вместе

SELECT reg.reg_date AS date_registration,
       reg.users AS cohort_size,
       cohort.date_diff AS day_difference,
       cohort.ret_base AS retention_base,
       cohort.ret_base/reg.users AS retention_rate
FROM
  (SELECT from_unixtime(user.installed_at, "yyyy-MM-dd") AS reg_date,
          ndv(user.id) AS users
   FROM USER
   WHERE from_unixtime(user.installed_at)>=date_add(now(), -60)
     AND from_unixtime(user.installed_at)<=date_add(now(), -31)
   GROUP BY 1) reg
LEFT JOIN
  (SELECT from_unixtime(user.installed_at, "yyyy-MM-dd") AS reg_date,
          datediff(cast(cs.created_at AS TIMESTAMP), cast(installed_at AS TIMESTAMP)) AS date_diff,
          ndv(user.id) AS ret_base
   FROM USER
   LEFT JOIN client_session cs ON cs.user_id=user.id
   WHERE 1=1
     AND datediff(cast(cs.created_at AS TIMESTAMP), cast(installed_at AS TIMESTAMP)) between 0 and 30
     AND from_unixtime(user.installed_at)>=date_add(now(), -60)
     AND from_unixtime(user.installed_at)<=date_add(now(), -31)
   GROUP BY 1, 2) cohort ON reg.reg_date=cohort.reg_date
    ORDER BY 1,3

Мы получили запрос, который для каждой когорты считает Retention, в итоге результат можно отобразить в таком виде:

Retention rate, посчитанный для каждой когорты пользователей

Построение единственной кривой Retention

Несколько модифицируем наш запрос и получим требуемые данные для построения одной кривой Retention:

SELECT 
       cohort.date_diff AS day_difference,
       avg(reg.users) AS cohort_size,
       avg(cohort.ret_base) AS retention_base,
       avg(cohort.ret_base)/avg(reg.users)*100 AS retention_rate
FROM
  (SELECT from_unixtime(user.installed_at, "yyyy-MM-dd") AS reg_date,
          ndv(user.id) AS users
   FROM USER
   WHERE from_unixtime(user.installed_at)>=date_add(now(), -60)
     AND from_unixtime(user.installed_at)<=date_add(now(), -31)
   GROUP BY 1) reg
LEFT JOIN
  (SELECT from_unixtime(user.installed_at, "yyyy-MM-dd") AS reg_date,
          datediff(cast(cs.created_at AS TIMESTAMP), cast(installed_at AS TIMESTAMP)) AS date_diff,
          ndv(user.id) AS ret_base
   FROM USER
   LEFT JOIN client_session cs ON cs.user_id=user.id
   WHERE 1=1
     AND datediff(cast(cs.created_at AS TIMESTAMP), cast(installed_at AS TIMESTAMP)) between 0 and 30
     AND from_unixtime(user.installed_at)>=date_add(now(), -60)
     AND from_unixtime(user.installed_at)<=date_add(now(), -31)
   GROUP BY 1,2) cohort ON reg.reg_date=cohort.reg_date
    GROUP BY 1        
    ORDER BY 1

Теперь у нас для каждого дня посчитан средний по всем когортам Retention rate.

Больше по теме

• Как создавать продукты, формирующие привычки?
• Top 3 Ways To Calculate User Retention Rate With Formulas

Продолжение предыдущей статьи о сборе данных с известного онлайн каталога товаров.

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

Динамическая подгрузка товаров на странице

Для таких случае в python также имеется решение — библиотека Selenium, она запускает движок браузера и эмулирует поведение человека.

В первой части скрипта мы соберем дерево категорий аналогично прошлой статье, но уже с использованием Selenium.

import time
from selenium import webdriver
from bs4 import BeautifulSoup as bs

browser = webdriver.Chrome()
browser.get("https://i****.ru/products?category_id=1-ovoschi-frukty-griby-yagody&from_category=true")
cookies_1= {'domain': '.i****.ru', 'expiry': 1962580137, 'httpOnly': False, 'name': '_igooods_session_cross_domain', 'path': '/', 'secure': False, 'value': 'WWJFaU8wMTBMSE9uVlR2YnRLKzlvdHE3MVgyTjVlS1JKVm1qMjVNK2JSbEYxcVZNQk9OR3A4VU1LUzZwY1lCeVlTNDVsSkFmUFNSRWt3cXdUYytxQlhnYk5BbnVoZktTMUJLRWQyaWxFeXRsR1ZCVzVnSGJRU0tLVVR0MjRYR2hXbXpaZnRnYWRzV0VnbmpjdjA5T1RzZEFkallmMEVySVA3ZkV3cjU5dVVaZjBmajU5bDIxVkEwbUQvSUVyWGdqaTc5WEJyT2tvNTVsWWx1TEZhQXB1L3dKUXl5aWpOQllEV245VStIajFDdXphWFQxVGVpeGJDV3JseU9lbE1vQmxhRklLa3BsRm9XUkNTakIrWXlDc3I5ZjdZOGgwYmplMFpGRGRxKzg3QTJFSGpkNWh5RmdxZzhpTXVvTUV5SFZnM2dzNHVqWkJRaTlwdmhkclEyNVNDSHJsVkZzeVpBaGc1ZmQ0NlhlSG43YnVHRUVDL0ZmUHVIelNhRkRZSVFYLS05UkJqM24yM0d4bjFBRWFVQjlYSzJnPT0%3D--e17089851778bedd374f240c353f399027fe0fb1'}
cookies_2= {'domain': '.i****.ru', 'expiry': 1962580137, 'httpOnly': False, 'name': 'sa_current_city_coordinates_cross_domain', 'path': '/', 'secure': False, 'value': '%5B59.91815364%2C30.305578%5D'}
cookies_3= {'domain': '.i****.ru', 'expiry': 1962580137, 'httpOnly': False, 'name': 'sa_current_city_cross_domain', 'path': '/', 'secure': False, 'value': '%D0%A1%D0%B0%D0%BD%D0%BA%D1%82-%D0%9F%D0%B5%D1%82%D0%B5%D1%80%D0%B1%D1%83%D1%80%D0%B3'}
browser.add_cookie(cookies_1)
browser.add_cookie(cookies_2)
browser.add_cookie(cookies_3)
browser.get("https://i****.ru/products?category_id=1-ovoschi-frukty-griby-yagody&from_category=true")
source_data = browser.page_source
soup = bs(source_data)
categories=soup.find_all('div', {'class':['with-children']})
tree = {}
for x in categories:
    tree[x.findNext('span').text]=x.findNext('a').get('href')

В этом сниппете мы как и раньше get-запросом с параметрами вызываем желаемую страницу браузера и скачиваем данные, затем у нас появляется объект класса bs, с которым мы проделываем аналогичные операции. Таким образом, мы получили словарь tree, в котором для каждой категории хранится URL страницы для данной категории, в дальнейшем этот словарь нам пригодится для перебора в цикле.

Приступим к сбору данных о товарах. Для этого импортируем библиотеку pandas и создадим новый dataframe с четырьмя колонками.

import pandas as pd
df = pd.DataFrame(columns=['SKU', 'Weight', 'Price','Category'])

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

for cat, link in tree.items():
    browser.maximize_window()
    browser.get('https://i****.ru'+link)
    cookies_1= {'domain': '.i****.ru', 'expiry': 1962580137, 'httpOnly': False, 'name': '_i****_session_cross_domain', 'path': '/', 'secure': False, 'value': 'WWJFaU8wMTBMSE9uVlR2YnRLKzlvdHE3MVgyTjVlS1JKVm1qMjVNK2JSbEYxcVZNQk9OR3A4VU1LUzZwY1lCeVlTNDVsSkFmUFNSRWt3cXdUYytxQlhnYk5BbnVoZktTMUJLRWQyaWxFeXRsR1ZCVzVnSGJRU0tLVVR0MjRYR2hXbXpaZnRnYWRzV0VnbmpjdjA5T1RzZEFkallmMEVySVA3ZkV3cjU5dVVaZjBmajU5bDIxVkEwbUQvSUVyWGdqaTc5WEJyT2tvNTVsWWx1TEZhQXB1L3dKUXl5aWpOQllEV245VStIajFDdXphWFQxVGVpeGJDV3JseU9lbE1vQmxhRklLa3BsRm9XUkNTakIrWXlDc3I5ZjdZOGgwYmplMFpGRGRxKzg3QTJFSGpkNWh5RmdxZzhpTXVvTUV5SFZnM2dzNHVqWkJRaTlwdmhkclEyNVNDSHJsVkZzeVpBaGc1ZmQ0NlhlSG43YnVHRUVDL0ZmUHVIelNhRkRZSVFYLS05UkJqM24yM0d4bjFBRWFVQjlYSzJnPT0%3D--e17089851778bedd374f240c353f399027fe0fb1'}
    cookies_2= {'domain': '.i****.ru', 'expiry': 1962580137, 'httpOnly': False, 'name': 'sa_current_city_coordinates_cross_domain', 'path': '/', 'secure': False, 'value': '%5B59.91815364%2C30.305578%5D'}
    cookies_3= {'domain': '.i****.ru', 'expiry': 1962580137, 'httpOnly': False, 'name': 'sa_current_city_cross_domain', 'path': '/', 'secure': False, 'value': '%D0%A1%D0%B0%D0%BD%D0%BA%D1%82-%D0%9F%D0%B5%D1%82%D0%B5%D1%80%D0%B1%D1%83%D1%80%D0%B3'}
    browser.add_cookie(cookies_1)
    browser.add_cookie(cookies_2)
    browser.add_cookie(cookies_3)
    browser.get('https://i****.ru'+link)
    
    # Скрипт, который через каждые 3 секунды ищет конец страницы и выполняется пока не закончится получение свежих данных
    lenOfPage = browser.execute_script("window.scrollTo(0, document.body.scrollHeight);var lenOfPage=document.body.scrollHeight;return lenOfPage;")
    match=False
    while(match==False):
        lastCount = lenOfPage
        time.sleep(3)
        lenOfPage = browser.execute_script("window.scrollTo(0, document.body.scrollHeight);var lenOfPage=document.body.scrollHeight;return lenOfPage;")
        if lastCount==lenOfPage:
             match=True

Теперь мы дошли до конца страницы и можем собрать данные для работы библиотеки beautifulsoup.

# Собираем данные со страницы
    source_data = browser.page_source
    soup = bs(source_data)
    skus=soup.find_all('div', {'class':['b-product-small-card']})
    last_value=len(df)+1 if len(df)>0 else 0
    for i,x in enumerate(skus):
        df.loc[last_value+i]=[x.findNext('a').contents[0],\
                   x.findNext('div',{'class':'product-weight'}).contents[0],\
                   x.findNext('div',{'class':'g-cart-action small'})['data-price'],\
                   cat]
browser.close()

В приведенном выше фрагменте кода мы ищем все элементы <div>, у которых класс — b-product-small-card, а далее для каждого найденного товара собираем значения полей веса и цены.

Исходный код сайта продуктовой карточки

Ставим выполняться скрипт и пьем кофе. Вуа-ля, теперь у нас есть pandas dataframe с данными всех товаров:

DataFrame с товарами, собранными с сайта

Теперь, у нас есть отличные данные для обучения NLP модели — наименования товаров и их принадлежность к различным категориям.

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

Ключевая «боль» / сложность — нам требуется так называемый сет для обучения (training set). Забегая вперед скажу, что успел протестировать такой сет для обучения на реальных данных из одной продуктовой сети, результаты, к сожалению, оказались не самые радужные, поэтому попробуем собрать другой честный сет для обучения, используя открытые источники в интернете.

Для работы возьмем популярный сайт по доставке еды из гипермаркетов. Сегодня будем использовать простую, удобную и функциональную библиотеку в Python для парсинга данных с .html страниц — Beatiful Soup.

Целевая страница, на которой интересующие нас данные имеет следующий вид.

Страница каталога сайта по доставке еды из гипермаркетов

Однако если мы впервые зайдем на сайт нас перебросит на главную, поскольку мы не выбрали ближайший гипермаркет.

Главная страница сайта по доставке еды из гипермаркетов

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

Теперь важно определить порядок действий и структуру сбора данных.

Основная задача:

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

Сегодня будем разбирать первую часть. Приступим, в теории нам должно хватить таких команд:

from bs4 import BeautifulSoup #импортируем модуль BeautifulSoup
import requests
r = requests.get('https://i****.ru/products?category_id=1-ovoschi-frukty-griby-yagody&from_category=true')
soup = BeautifulSoup(r.text)

Однако если мы посмотрим содержимое soup, то обнаружим, что мы получили исходный код главной страницы, а не той, что интересует нас. Главная страница не подходит для целей нашего анализа и сбора информации.

Получили исходный код главной страницы сайта

Поэтому воспользуемся методом Session библиотеки requests, которым можно передать cookies в качестве параметра. Итого наш код выглядит так:

import requests
s = requests.Session()
r = s.get('https://i****.ru/products?category_id=1-ovoschi-frukty-griby-yagody&from_category=true', \
       cookies = {'_igooods_session_cross_domain':'WWJFaU8wMTBMSE9uVlR2YnRLKzlvdHE3MVgyTjVlS1JKVm1qMjVNK2JSbEYxcVZNQk9OR3A4VU1LUzZwY1lCeVlTNDVsSkFmUFNSRWt3cXdUYytxQlhnYk5BbnVoZktTMUJLRWQyaWxFeXRsR1ZCVzVnSGJRU0tLVVR0MjRYR2hXbXpaZnRnYWRzV0VnbmpjdjA5T1RzZEFkallmMEVySVA3ZkV3cjU5dVVaZjBmajU5bDIxVkEwbUQvSUVyWGdqaTc5WEJyT2tvNTVsWWx1TEZhQXB1L3dKUXl5aWpOQllEV245VStIajFDdXphWFQxVGVpeGJDV3JseU9lbE1vQmxhRklLa3BsRm9XUkNTakIrWXlDc3I5ZjdZOGgwYmplMFpGRGRxKzg3QTJFSGpkNWh5RmdxZzhpTXVvTUV5SFZnM2dzNHVqWkJRaTlwdmhkclEyNVNDSHJsVkZzeVpBaGc1ZmQ0NlhlSG43YnVHRUVDL0ZmUHVIelNhRkRZSVFYLS05UkJqM24yM0d4bjFBRWFVQjlYSzJnPT0%3D--e17089851778bedd374f240c353f399027fe0fb1', \
               'sa_current_city_coordinates_cross_domain' : '%5B59.91815364%2C30.305578%5D', \
                  'sa_current_city_cross_domain' : '%D0%A1%D0%B0%D0%BD%D0%BA%D1%82-%D0%9F%D0%B5%D1%82%D0%B5%D1%80%D0%B1%D1%83%D1%80%D0%B3',\
                 'lazy_loader_last_url' : '/products?category_id=1-ovoschi-frukty-griby-yagody&from_category=true'})
soup = BeautifulSoup(r.text)

Мы устанавливаем 4 cookies, которые эмулируют поведение человека и выбранный гипермаркет (их мы установили эмпирическим путем из cookies, установленных браузером, при выборе соответствующего гипермаркета):

Cookies, влияющие на отображение главной страницы сайта

Прекрасно, осталось лишь собрать нужные нам категории и ссылки на них. Для этого напишем следующий код:

categories=soup.find_all('div', {'class':['with-children']})
tree = {}
for x in categories:
    tree[x.findNext('span').text]=x.findNext('a').get('href')

В этом сниппете мы как и раньше GET-запросом с параметрами (cookies) вызываем желаемую страницу браузера и скачиваем данные, затем у нас появляется объект класса BeautifulSoup.
Мы используем команду:

categories=soup.find_all('div', {'class':['with-children']})

И находим все элементы <div>, у которых имеется класс with-children, вот то же самое в коде сайта:

Элементы, которые содержат название категории.

Далее, объявляем пустой объект класса dict и для каждого найденного выше элемента в цикле собираем:

tree[x.findNext('span').text]=x.findNext('a').get('href’)

Что фактически означает: мы берем текст следующего, за найденным <div> элемента <span> и адрес ссылки, следующей за найденным <div>.
Именно это нам и требовалось получить. Таким образом мы получили словарь вида {Категория: URL сайта}:

Справочник категорий и соответствующих им URL

В следующей статье — продолжение о сборе информации по карточкам товаров каталога.

На днях попалась замечательная карта machine learning алгоритмов от scikit-learn

Временно отвлечемся от проекта по сбору чековых данных. О нескольких следующих шагах этого проекта поговорим чуть позже.

Сегодня обсудим новый сервис от Яндекса — DataLens (доступ к демо мне любезно предоставил мой большой друг Василий Озеров и команда Fevlake / Rebrain). Сервис находится в режиме Preview и по сути является облачным BI. Ключевая фишка сервиса в том, что он легко и удобно работает с кликхаусом (Yandex Clickhouse).

Подключение источников данных

Рассмотрим основные вещи: подключение источника данных и настройку датасета.
Выбор СУБД не велик, но некоторые основные вещи присутствуют. Для целей нашего тестирования возьмем MySQL.

Выбор источников данных DataLens

На основе созданного подключения предлагается создать датасет:

Интерфейс настройки датасета, определение измерений и метрик

На этом этапе определяется какие атрибуты таблицы станут измерениями, а какие метриками. Можно выбрать тип агрегации данных для метрик.
К сожалению, мне не удалось найти каким образом возможно вместо одной таблицы указать несколько связанных таблиц (например, присоединить справочник для измерений). Вероятно, на данном этапе разработчики предлагают решать данный вопрос созданием нужных view.

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

Что касается самого интерфейса для визуализации — все довольно легко и удобно. Напоминает облачную версию Tableau. А в сравнении с Redash, который чаще всего используется в связке с Clickhouse, возможности визаулизации — просто потрясают.
Чего стоят сводные таблицы, в которых можно использовать Measure Names в качестве названия столбцов:

Настройка сводных таблиц в DataLens

Разумеется, в DataLens от Яндекса есть возможность собрать и базовые графики:

Построение линейного графика в DataLens

Есть и диаграммы с областями:

Построение диаграммы с областями в DataLens

Однако мне не удалось обнаружить каким образом осуществляется группировка дат по месяцам / кварталам / неделям. Судя по примеру данных, доступному в пробной версии, разработчики пока решают этот вопрос созданием дополнительных атрибутов (DayMonth, DayWeek, etc).

Дашборды

Пока интерфейс создания блоков дашборда выглядит громоздко и не всегда понятны интерфейсные окна. Вот, например, окно позволяющее указать параметр:

Не совсем очевидное окно настройки параметров дашборда

Однако в галерее примеров имеются очень функциональные и удобные дашборды с селекторами, вкладками и параметрами:

Пример работающего дашборда с параметрами и вкладками в DataLens

Ждем исправления интерфейсных недочетов, улучшения DataLens и готовимся к использованию в паре с Clickhouse!

Update: к сожалению, информация в данном посте устарела. Рекомендуем изучить наш новый пост.

Недавно, покупая в очередной раз продукты в гипермаркете, вспомнил, что согласно ФЗ-54 любой оператор торговли, который пробивает кассовый чек, обязан отправлять данные чека в налоговую.

Чек из гипермаркета «Лента», QR-код, который нас интересует, обведен

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

Попробуем в рамках серии постов собрать небольшой прототип приложения, которое позволит строить динамику своих покупок. Итак, начнем с того, что в каждом чеке есть QR-code, если его распознать, то мы получим следующую строку:

t=20190320T2303&s=5803.00&fn=9251440300007971&i=141637&fp=4087570038&n=1

В данной строке содержатся:

t — timestamp, время, когда вы осуществили покупку
s — сумма чека
fn — кодовый номер fss, потребуется далее в запросе к API
i — номер чека, он нам потребуется далее в запросе к API
fp — параметр fiscalsign, потребуется далее в запросе к API

В рамках решения первого шага нашей задачи мы будем парсить данные чека и собирать их в pandas dataframe, используя модули Python.

Мы воспользуемся API, который отдает данные по чеку с сайта налоговой.

В начале получим аутентификационные данные:

import requests
your_phone = '+7XXXYYYZZZZ' #нужно указать ваш телефон, на него придет СМС с паролем
r = requests.post('https://proverkacheka.nalog.ru:9999/v1/mobile/users/signup', json = {"email":"email@email.com","name":"USERNAME","phone":your_phone})

В результате выполнения POST-запроса мы получим пароль в виде SMS на указанный мобильный телефон. Далее, мы будем использовать его в переменной pwd

Теперь распарсим нашу строку со значениями из QR-кода:

import re
qr_string='t=20190320T2303&s=5803.00&fn=9251440300007971&i=141637&fp=4087570038&n=1'
t=re.findall(r't=(\w+)', qr_string)[0]
s=re.findall(r's=(\w+)', qr_string)[0]
fn=re.findall(r'fn=(\w+)', qr_string)[0]
i=re.findall(r'i=(\w+)', qr_string)[0]
fp=re.findall(r'fp=(\w+)', qr_string)[0]

Будем использовать полученные переменные для извлечения данных.
В посте на Хабре довольно подробно изучены статусы ошибок при формировании запроса к API, не буду повторять эту информацию.

В начале необходимо проверить, что по данному чеку есть данные, формируем GET-запрос.

headers = {'Device-Id':'', 'Device-OS':''}
payload = {'fiscalSign': fp, 'date': t,'sum':s}
check_request=requests.get('https://proverkacheka.nalog.ru:9999/v1/ofds/*/inns/*/fss/'+fn+'/operations/1/tickets/'+i,params=payload, headers=headers,auth=(your_phone, pwd))
print(check_request.status_code)

В запросе необходимо указать headers, хотя бы пустые. В моем случае GET-запрос возвращает ошибку 406, из чего я понимаю, что такой чек находится (почему GET-запрос возвращает 406 для меня загадка, буду рад подсказкам в комментариях). Если не указать сумму или дату, то GET-запрос вернет ошибку 400 — bad request.

Переходим к самому интересному, получаем данные чека:

request_info=requests.get('https://proverkacheka.nalog.ru:9999/v1/inns/*/kkts/*/fss/'+fn+'/tickets/'+i+'?fiscalSign='+fp+'&sendToEmail=no',headers=headers,auth=(your_phone, pwd))
print(request_info.status_code)
products=request_info.json()

Должны получить код 200 (успешное выполнение GET-запроса), а в переменной products — все, что относится к нашему чеку.

Чтобы работать с этими данными воспользуемся pandas и преобразуем все в dataframe.

import pandas as pd
from datetime import datetime
my_products=pd.DataFrame(products['document']['receipt']['items'])
my_products['price']=my_products['price']/100
my_products['sum']=my_products['sum']/100
datetime_check = datetime.strptime(t, '%Y%m%dT%H%M') #((https://docs.python.org/3/library/datetime.html#strftime-and-strptime-behavior отформатируем дату))
my_products['date']=datetime_check
my_products.set_index('date',inplace=True)

Теперь мы имеем рабочий pandas.dataframe с чеками, визуально это выглядит так:

«Шапка» чековых данных

Можно построить гистограмму покупок или посмотреть на все в виде «ящика с усами»:

import matplotlib.pyplot as plt
%matplotlib inline
my_products['sum'].plot(kind='hist', bins=20)
plt.show()
my_products['sum'].plot(kind='box')
plt.show()

В завершение элементарно получим описательные статистики в текстовом виде командой .describe():

my_products.describe()

Данные удобно записать в .csv-файл, чтобы в следующий раз дополнить статистику:

with open('hyper_receipts.csv', 'a') as f:
             my_products.to_csv(f, header=True)

Итак, мы планировали разобрать Funnel-визуализацию отчета в redash.
В первую очередь, построим запрос к подключенному нами источнику данных — google analytics.

Прямо вот такой текст необходимо положить в консоль запроса:

{
    "ids": "ga:128886640",
    "start_date": "30daysAgo",
    "end_date": "yesterday",
    "metrics": "ga:users,ga:goal1Completions,ga:goal2Completions,ga:goal3Completions"
}

В данном запросе мы просим API Google Analytics отдать данные за последние 30 дней по аккаунту GA: 128886640. Мы хотим увидеть число пользователей и число выполнения целей 1, 2 и 3.

В итоге получаем таблицу следующего вида:

Отлично, это именно то, что нам нужно для построения воронки.
Расскажу об одной очень полезной фиче Redash: query-results. Чтобы подключить таблицы с результатами выполнения запросов, идем в Data Sources и ищем query-results (beta). Подключаем новый источник данных.
Теперь у нас появилась возможность обращаться к результатам запросов redash. Так, например, мы можем воспользоваться результатами запроса к Google Analytics.

Как это сделать?
Необходимо выбрать слева источник данных query-results:

Выпадающее меню с выбором источников данных (в консоли — слева)

Теперь научимся делать funnel-визуализацию. Для этого пишем следующий SQL-запрос:

select 'Добавление товара в корзину' as step_name, ga_goal1Completions as goalCompletion from query_8
union all
select 'Просмотр корзины' as step_name, ga_goal2Completions from query_8
union all
select 'Оформление заказа' as step_name, ga_goal3Completions from query_8

В данном случае query_8 — это как раз таблица с результатами запроса к источнику данных Google Analytics.

Настроим визуализацию:

Аккуратно выбираем параметры, чтобы получить желаемый результат

В итоге мы получаем воронку конверсий из одной цели в другую:

Данную воронку можно отобразить в дашборде и добавить к ней фильтры / параметры