Большие выводы на маленьких выборках

Чтобы начать двигать недвижимость, достаточно сменить систему отсчёта.
Фейсбук
→ Посты за 2018, 2017, 2016

Big Query → DataFrame

Простой способ достать данные из Google Big Query в Jupyter Notebook.

# Установите библиотеки
# !pip install python-gflags
# !pip install httplib2
# !pip install google-api-python-client
# !pip install pandas-gbq

# Включите API: https://console.developers.google.com/apis/dashboard


import pandas as pd
from pandas.io import gbq

#  Ваш запрос в BQ
Query = """
SELECT
 *
FROM 
[your_dataset_name]
"""

# Укажите project_id и диалект
data = gbq.read_gbq(Query, project_id='your_BQ_project_id', dialect = 'standard')


data.head()

Источник

7 сентября   big query   pandas   python

Начинаем работать с Google Sheets API v4

Статья будет полезна тем, кто хочет создавать Гугл-таблицы, вносить или забирать данные через API.

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

Понадобится anaconda, jupyter notebook и python 3.

Создаём аккаунт и авторизируемся

Нужна почта на gmail.
Заходим на Google Developers Console и создаём новый проект.
Включаем Drive API и Sheets API.
Сохраняем учётные данные и закрытый ключ:

После всех манипуляций вы скачаете json-файл. Он понадобится вот тут:

!pip install --upgrade google-api-python-client

from datetime import datetime
import calendar
import locale
locale.setlocale(locale.LC_TIME, 'ru_RU.UTF-8') 
from pprint import pprint
import httplib2
import apiclient.discovery
from oauth2client.service_account import ServiceAccountCredentials
CREDENTIALS_FILE = 'XXX.json'  #  ← имя скаченного файла с закрытым ключом
credentials = ServiceAccountCredentials.from_json_keyfile_name(CREDENTIALS_FILE, ['https://www.googleapis.com/auth/spreadsheets',                                                                               'https://www.googleapis.com/auth/drive'])
httpAuth = credentials.authorize(httplib2.Http())
service = apiclient.discovery.build('sheets', 'v4', http = httpAuth)

Достаём данные

Создайте новый Googlesheet, дайте разрешение на редактирование всем у кого есть ссылка:

Скопируйте id из адресной строки:

Заполните ячейку A1 чем-нибудь неожиданным:

И попробуйте что-то вроде:

spreadsheetId = '1vR7sByPDE10wZSyz7I8hqBS8j5DgTNerFH2X_1sbVu8'
range_name = 'Лист1!A:G'
table = service.spreadsheets().values().get(spreadsheetId=spreadsheetId, range=range_name).execute()
table

Ура, забрали данные:

Пусть в исходной табличке будет табличка вида:

Ставим pandas, делаем dataframe:

import pandas as pd

campaign_list = []
visits_list = []
cost_list= []

for i in table['values'][1:]:

    campaign_list.append( i[0] )
    visits_list.append( i[1] )
    cost_list.append( i[2] )

right = pd.DataFrame( list(zip(campaign_list, visits_list, cost_list) ),columns=[ 
                                                table['values'][0][0],
                                                table['values'][0][1],
                                                table['values'][0][2]])
right

Вуаля ;-)

Отправляем данные

Создаём новый spreadsheet:

title = 'Hello, world'
list_title = '1'

spreadsheet = service.spreadsheets().create(body = {
    'properties': {'title': title, 'locale': 'ru_RU'},
    'sheets': [{'properties': {'sheetType': 'GRID',
                               'sheetId': 0,
                               'title': list_title,
                               'gridProperties': {'hideGridlines': True, 'rowCount': 5, 'columnCount': 5}}}]
}).execute()

Файл создан — смотрим, что получилось:

Если перейти по ссылке, увидим «Доступ закрыт» — всё верно, файл принадлежит сервисному аккаунту. Даём себе разрешение:

email = 'xxx@gmail.com' # ← ваша почта

driveService = apiclient.discovery.build('drive', 'v3', http = httpAuth)
shareRes = driveService.permissions().create(
    fileId = spreadsheet['spreadsheetId'],
    #body = {'type': 'anyone', 'role': 'writer'},  # доступ на редактирование кому угодно
    body = {'type': 'user', 'role': 'writer', 'emailAddress': email},
    fields = 'id'
).execute()

Теперь ссылка открывается:

Вставляем значение в выбранную ячейку:

cell = 'A1'
value = '1,618'

service.spreadsheets().values().batchUpdate(spreadsheetId = spreadsheet['spreadsheetId'], 
body = {
"valueInputOption": "USER_ENTERED",
"data": [
    {"range": list_title + "!" + cell,
     "majorDimension": "ROWS",
     "values": [[value]]}

]
}).execute()

Жирный шрифт:

DATA = {'requests': [{'repeatCell':
        {'range': 
            { 
              'startRowIndex': 0, # номер строки (нумерация с 0) с которой включительно будет применено форматирование
              'endRowIndex': 1, # номер строки до которой будет применено форматирование, не включительно
                
              'startColumnIndex': 0, # номер столбца (нумерация с 0)…
              'endColumnIndex': 1
            },
        
        'cell':  {'userEnteredFormat': {'textFormat': {'bold': True}}},

        'fields': 'userEnteredFormat', # другие параметры форматирования ячейки будут сброшены
    }}
]}

service.spreadsheets().batchUpdate(spreadsheetId = spreadsheet['spreadsheetId'], body=DATA).execute()

Число с тремя знаками после запятой, горизонтальное выравнивание влево, вертикальное по верху, 9-й шрифт, отменить жирность, перенос по словам:

DATA = {'requests': [{'repeatCell':
        {'range': 
            { 
              'startRowIndex': 0, # номер строки (нумерация с 0) с которой включительно будет применено форматирование
              'endRowIndex': 1, # номер строки до которой будет применено форматирование, не включительно
                
              'startColumnIndex': 0, # номер столбца (нумерация с 0)…
              'endColumnIndex': 1
            },
        
        'cell':  {'userEnteredFormat': {"numberFormat": { "type": "NUMBER", "pattern": "#,##0.000"}, 
                                        "verticalAlignment": "TOP", 
                                        "horizontalAlignment": "LEFT", 
                                        "wrapStrategy": "WRAP", 
                                        "textFormat": {"fontSize": 9,'bold': False}
                                              }},

        'fields': 'userEnteredFormat', # другие параметры форматирования ячейки будут сброшены
    }}
]}

service.spreadsheets().batchUpdate(spreadsheetId = spreadsheet['spreadsheetId'], body=DATA).execute()

Другие возможности — в описании метода:

В правой можно потренироваться в составлении запроса

Задаём ширину столбца или высоту строки:

service.spreadsheets().batchUpdate(spreadsheetId = spreadsheet['spreadsheetId'], body = {
  "requests": [


    {
      "updateDimensionProperties": {
        "range": {
          "sheetId": 0,
          "dimension": "COLUMNS",  # COLUMNS — столбцы, ROWS — строки
          "startIndex": 0,         # Нумеруются с нуля
          "endIndex": 1            # startIndex берётся включительно, endIndex — не включительно
        },
        "properties": {
          "pixelSize": 200     # Например, 200 пикселей
        },
        "fields": "pixelSize" 
      }
    }
  ]
}).execute()

That’s it!

Ещё по теме:
Эта статья на 50% — упрощенный копипаст вот этого поста на Хабре
Описание метода batchUpdate на сайте Google

Спарсить Циан

Пока сам не понял зачем, сделал парсер Циана (python + selenium). Например, находить и сохранять телефоны собственников-продавцов квартир и показывать им рекламу. Или глубоко анализировать влияние этажа на площадь кухни стоимость.

На выходе вот такая таблица:

Кому надо — пишите — отдам даром.

Парсим объявления конкурентов в Директе

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

Что делает скрипт

Берет файл со списком запросов, вот такой, например:

Каждый запрос вбивает в Яндекс и записывает заголовки, тексты и отображаемые ссылки рекламных объявлений в CSV-файл.

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

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

И ещё вот такую, со сводными данными:

Как сделать

Скачайте скрипт и файл с запросами. Сохраните в отдельную папку.

Будем использовать специальный дистрибутив Питона, который называется «Анаконда». Качаем версию Питона 3.6, устанавливаем, запускаем Anaconda Navigator, в нём жмем launch Jupiter Notebook.

Там выберите папку со скачанными файлами и файл .ipynb:

В коде задайте минус-слова:

# Список минус-слов для запросов
minus_list = '-крым -море -booking -отдых -домен -отель -аренда -хостел -краснодар -сеть'

Регион:

# Задаём регион
# Список идентификаторов российских регионов https://tech.yandex.ru/xml/doc/dg/reference/regions-docpage/
# Москва и Московская область – 1
# Санкт-Петербург – 2
region = 1

Количество рекламных блоков:

# Задаём сколько позиций рекламной выдачи сохраняем.
maxPos = 6

Жмём Shift + Enter (или Run из меню) для каждой ячейки — фишка Jupiter Notebook в том, что код выполняется пошагово. Когда дойдете до шага «Основной цикл» — придется подождать. Скрипт будет писать какой запрос он сейчас обрабатывает:

Далее формируем датафрейм и записываем в CSV-файл. Как всё будет готово, появится табличка с первыми пятью строками результата и файл в папке со скриптом.

Первые пять строчек для первого запроса из файла. Все данные — в CSV-файле

Для получения сводной таблицы запустите последние ячейки

В табличке легко, например, найти конкурентов кто активно пользуется вашими бренд-запросами
2018  

Находим минус-слова с помощью API Директа

Понадобятся Renta и Google BigQuery.

В Ренте выбираем интеграцию с Директом
Выбираем логин; Report type: SEARCH_QUERY_PERFORMANCE_REPORT; Параметры: CampaignId, Query, AdGroupId, AdId; Период и время обновления отчёта

Данные отправим в BigQuery.
Создаём запрос — замените [Renta_dataset.yd_XXXXX] на название вашей таблицы в BigQuery, а в CampaignId = ’YYYYY’ укажите ваш ID кампании в Директе:

SELECT
  '!' + REGEXP_REPLACE(Word, '[^а-яА-Яa-zA-Z0-9_]', '') AS Words
FROM (
  SELECT
    SPLIT(Query, ' ') AS Word
  FROM
    [Renta_dataset.yd_XXXXX]
  WHERE
    CampaignId = 'YYYYY')
GROUP BY
  Words
ORDER BY
  Words

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

Там наверняка будет что отминусовать ;-)

P. S. Можно не разделять поисковые фразы по словам, а получить просто все поисковые фразы:


SELECT
  Query
FROM
  [Renta_dataset.yd_XXXXX]
WHERE
  CampaignId = 'YYYYY'
GROUP BY
  Query
ORDER BY
  Query
2017  

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

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

Чтобы оценить, как долго клиент принимает решение о звонке, предлагаю покопаться в сырых данных Метрики и найти, с какого по счёту визита клиент таки звонит. Статья будет полезна тем, кто хочет начать работать с Logs API Метрики и Google BigQuery.

Сначала достанем нужные данные из Logs API: будем использовать новый сервис Renta. Данные застройщика с обычными рекламными каналами: контекст, медийка, таргет, фб, вк.

В Ренте легко доставать данные из разных систем и отправлять в свою базу данных

Выбираете номер счётчика;
источник Visits;
поля: ym:s:visitID, ym:s:clientID, ym:s:goalsID;
период наблюдения и время обновления данных

Данные отправим в BigQuery. Исходные данные выглядят так:

Первый столбец — уникальный ID визита, второй — ID пользователя, третий — какие цели, заданные в Метрике, достиг пользователь за этот визит. (Для отслеживания звонков нужен динамический колтрекинг и интеграция с Метрикой.)

Создадим первый запрос: оставим только пользователей кто звонил (в моём случае достиг цели с ID 34341129) и пронумеруем их визиты по дате. Поскольку Метрика присваивает номера визитам по порядку, поле «Дата и время» нам не понадобится — достаточно отсортировать от возрастанию visitID.
Внимание! Дальше будет быдло-код — знатоки, не ругайтесь.

SELECT
      all.clientid AS clientid,
      all.visitid,
      goalsid,
      ROW_NUMBER() OVER (PARTITION BY all.clientid ORDER BY all.visitid) AS number_of_visit
    FROM (
      SELECT
        *
      FROM
        [Renta_dataset.ym_logs_XXXXX]) all
    INNER JOIN (
      SELECT
        clientid
      FROM
        [Renta_dataset.ym_logs_XXXXX]
      WHERE
        goalsID LIKE '%34341129%'
      GROUP BY
        clientid) clients_with_leads
    ON
      all.clientid = clients_with_leads.clientid
    ORDER BY
      all.clientid,
      number_of_visit

Замените [Renta_dataset.ym_logs_XXXXX] на название вашей таблицы в BigQuery, а в goalsID LIKE ’%34341129%’ укажите ваш ID цели в Метрике.

Получим такую таблицу:

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

Теперь из предыдущего запроса нам надо найти номер визита, когда клиент позвонил впервые:

SELECT
    clientid,
    MIN(number_of_visit) as FirstCallVisitNumber
  FROM ( /*предыдущий запрос*/)
  WHERE
    goalsID LIKE '%34341129%'
  GROUP BY
    clientid

Получаем табличку: ID пользователя с номером визита его первого звонка:

Почти всё — считаем количество пользователей для каждого Номера визита пользователя, когда он первый раз позвонил

SELECT
  FirstCallVisitNumber,
  COUNT(clientid) AS clients
FROM (/*предыдущий запрос*/)
GROUP BY
  FirstCallVisitNumber
ORDER BY
  FirstCallVisitNumber
Результат запроса

Из BigQuery легко отправить данные в Google Data Studio, чтобы сделать простенькую визуализацию:

В моём примере — на данных московского застройщика — 70% пользователей звонит после первого визита на сайт, почти 90% — не позже второго. Я повторил упражнение для нескольких других застройщиков — результаты похожи.

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

2017  

Упражняемся с формулой Пуассона в медиапланировании

В медиапланах недвижимости принято писать прогноз количества кликов, коэффициента конверсии и количества лидов. Прогноз лидов считается так: клики × конверсия. Вроде всё правильно, но нет.

Эксперимент с монеткой

Вы менеджер по подкидыванию монетки. К вам приходит начальник и говорит: «Хочу в этом месяце пять решек, готовь медиаплан».
Вы знаете конверсию (50%) и считаете сколько раз надо покинуть монетку, чтоб выполнить задачку начальника:
N = 5/50% = 10. Вписываете в медиаплан затраты на 10 бросков и вроде готово.

Но вот что получится.
Берём монетку и подкидываем её 10 000 раз по 10 Эксель и моделируем ситуацию:

С помощью генератора случайных чисел — функция СЛУЧМЕЖДУ() или RANDBETWEEN() — генерируем десять столбцов, где 0 — решка, 1 — орёл.
Повторяем для, например, 10 000 строчек. Больше строчек — точнее результат. Получаем табличку с нулями и единицами.
Добавляем столбец «Результат», где считаем для каждой строчки количество нулей-решек с помощью формулы СЧЁТЕСЛИ(Диапазон;0) или COUNTIF().

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

Видно, что чаще всего из 10 попыток выпало 5 решек, реже всего — ни одной или все 10.
Считаем процент от суммы по столбцу и получаем вероятность того или иного исхода.
Теперь сложим вероятности для неудовлетворяющих начальника результатов: от нуля до четырёх решек (он просил минимум пять).

38%. Почти сорок процентов, что план мы не выполним.

Давайте «подкинем» 15 раз и посмотрим результат:

6% что план не выполним, но это уже терпимо.

Кидаем 15 раз, чтобы с вероятностью 94% не профакапить план.
Теперь попробуем более реальную ситуацию.

Планируем Директ

Приходит начальник, говорит хочу 100 лидов с Директа, сколько нужно денег.
Пусть вы знаете: клики всегда стоят 50 рублей, а конверсия ровно 1%.
Бюджет = 50 ₽ × Клики = 50 ₽ × 100/1% = 500 000 ₽?
Продолжим мучать Эксель.

Делаем табличку 10 000 × 2 000, генерим случайное число от 0 до 99, считаем что получилось:

Не пытайтесь повторить дома ;-) Нужен весьма шустрый компьютер для такого извращения.
В процентах. В нашем моделировании получилось, что вероятность не выполнить план (меньше 100 лидов) = 50,2%.

Вероятность получить менее 90 лидов — 16%.

Как считать формулой

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

В Экселе считается с помощью формулы ПУАССОН.РАСП

Чтобы с вероятностью 95% получить при заданной конверсии 100 и более конверсий, нужно больше кликов:

Если кликов 11 700, то 100+ конверсий будет с вероятностью 95%, что весьма хорошо.

Таким образом нужен бюджет 50 ₽ × 11 700 кликов = 585 000 ₽.

Без Экселя сумму можно посчитать в WolframAlpha:

Оценка рентабельности

Пусть в прошлом месяце у вас было медийное размещение на площадке X.
Вы потратили 200 000 ₽ и получили 4000 кликов и 16 лидов. Стоимость лида 12 500 ₽. Конверсия 0,4%.
У вас есть ограничение на стоимость лида 10 000 ₽.
Что делать с площадкой — убирать или ей просто не повезло?

Найдем коэффициент конверсии при которой она давала бы конверсии по 10 000 ₽, то есть с неё было бы 20 лидов: 20 / 4000 = 0,5%.
Теперь посчитаем вероятность получить не более 16 лидов при такой конверсии:

С вероятностью 22% при «рентабельной» конверсии 0,5% можно получить результат 16 и менее лидов. Вполне вероятно! Площадку можно оставить — возможно, ей просто «не повезло».

Оставили ещё на два месяца и результат повторился: 600 000 ₽, 12 000 кликов, 48 лида. А надо 60, чтобы не превысить ограничение. Считаем:

6,5% шанс получить такой результат при «рентабельности». Стоит убрать из МП или договориться о скидке.

Вывод

Кэп предупреждает: чтобы точно выполнить план надо заложить дельту в медиаплане. Размер дельты можно оценить вышеуказанным способом.

Ещё по теме:

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

2017  

Делаем свою модель атрибуции с помощью Power Query и Logs API Яндекс.Метрики

Статья для тех, кто не умеет в ClickHouse (как, например, я), а поковыряться в сырых данных Метрики всё-таки хочется. Будем строить модель атрибуции взвешивая источники по длине сессии пользователя.

Видео: Logs API Яндекс.Метрики и ClickHouse: что полезного можно получить из сырых данных

Выгружайте сырые данные из Метрики через Logs API Блог Яндекс.Метрики

У нас в недвижке бывает так:

  • клиент пришел с РСЯ, провёл на сайте 60 секунд и ушёл,
  • вернулся через день с ремаркетинга в GDN, провёл на сайте 120 секунд и ушёл,
  • снова вернулся через брендовый Директ, провёл на сайте 20 секунд и позвонил.

Модель по ласт клику даст всю конверсию бренд-Директу, по первому — РСЯ. Мы же хотим учесть все касания пользователя с сайтом и проранжировать их по времени, проведенному на сайте — тогда каждый канал получит свой кусок конверсии:

  • РСЯ — 60/200 = 0,3,
  • GND — 120/200 = 0,6,
  • бренд — 20/200 = 0,1.

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

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

Например,

  • цепочка РСЯ — Бренд Директ станет просто РСЯ
  • Медийка — Эдвордс — Органика → Медийка — Эдводрс
  • РСЯ — Директ — Медийка — Эдвордс Бренд → РСЯ — Директ — Медийка

Любая модель атрибуции — это от слова модель, поэтому она не претендует на абсолютную точность или истину в последней инстанции.

Вытаскиваем сырые данные из Метрики

Сырые, не агрегированные данные — это записи об отдельных визитах, вот например так

Нам нужна таблица со следующими столбцами:

  • ID клиента (ym:s:clientID),
  • ID визита (ym:s:visitID),
  • время на сайте (ym:s:visitDuration),
  • дата визита (ym:s:date),
  • ID целей, достигнутых за данный визит,
  • источник трафика, метки UTM Source и Campaign (ym:s:lastTrafficSource,ym:s:UTMSource,ym:s:UTMCampaign).

Все параметры визитов в руководстве Яндекса

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

Статья Алексея Макарова, где подробно всё описано.

Идёте по ссылке и копируете токен для Метрики.

Скачиваете приложение Postman для Хрома и запускаете.

Выбираем тип запроса POST, а в поле ввода запроса вставляете следующий запрос:

https://api-metrika.yandex.ru/management/v1/counter/XXXXXXXX/
logrequests?date1=2017-07-01&date2=2017-09-24
&fields=ym:s:isNewUser,ym:s:clientID,ym:s:visitID,ym:s:pageViews,
ym:s:visitDuration,ym:s:date,ym:s:dateTime,ym:s:goalsID,
ym:s:lastTrafficSource,ym:s:UTMSource,ym:s:UTMCampaign&source=visits
&oauth_token=XXXXXXXXXXXXXXXXXXXXXX

counter/XXXXXXXX/ — подставляете номер счётчика Метрики

date1=2017-07-01&date2=2017-09-24 — нужные даты

oauth_token=XXXXXXXX — полученный токен

В ответ придёт такая штука:

Нам нужен request_id — это идентификатор, созданного запроса на получение данных из Logs API — копируем его номер для следующего шага.

Выбираем тип запроса GET, в поле ввода запроса вставлям следующий запрос:

https://api-metrika.yandex.ru/management/v1/counter/XXXXXXX/logrequest/248496?oauth_token=XXXXXXXXXXXXXXXXXXXXXX

counter/XXXXXXXX/ — подставляете номер счётчика Метрики

logrequest/248496 — request_id из предыдущего шага

oauth_token=XXXXXXXX — полученный токен

В ответ получим:

Ответ на GET-запрос

Нас интересует параметр status — у нас значение processed — запрос лога обработан и лог готов к скачиванию.

Параметр status может принимать несколько значений,
подробнее в помощи Яндекса

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

Третий запрос — выбираем тип запроса GET, а в поле ввода запроса вставляете следующий запрос:

https://api-metrika.yandex.ru/management/v1/counter/XXXXXXXX/logrequest/248496/part/0/download?oauth_token=XXXXXXXXXXXXX

Только теперь нажимаем не Send, а Send and Download

Сохраняете файл как .csv
Всё, логи скачали, идём в Эксель.

Строим модель атрибуции

В Экселе (у меня Excel 2016) идёте в раздел «Данные», выбираете сохраненный csv-файл

Ждетё загрузку

Нажимаете «Изменить»

Попадаете в редактор запросов

Каждая строчка — это отдельный визит.

Для построения модели атрибуции по длине сессии, нам потребуется к этой таблице добавить ещё несколько столбцов:

  • сумма длительности всех визитов пользователя,
  • вес визита,
  • повершил ли пользователь конверсию.

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

Нам надо сделать сводную таблицу по ID клиента (ym:s:clientID) и просуммировать ym:s:visitDuration.
Если в модели мы не хотим учитывать прямые, органические или даже бренд-контекстные запросы — присваиваем им длительность «0». Копируем исходный запрос и делаем несколько преобразований.

Шаг 0. Копируем исходный запрос.
Шаг 1. Удаляем все столбцы кроме ym:s:clientID, ym:s:visitDuration, ym:s:lastTrafficSource, ym:s:UTMCampaign
Шаг 2. Добавляем условный столбец — если источник «лишний» ставите «0», иначе = ym:s:visitDuration; меняете тип данных полученного столбца на целое число.
Шаг 3. Удаляем лишние столбцы и делаем сводную таблицу суммируя продолжительность визитов для каждого уникального пользователя. (Комп очень тупил, поэтому скринкаст подвисает.)

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

Находим пользователей, совершивших конверсию в любой из визитов

Копируем исходный запрос, затем находим нужных пользователей.

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

Объединяем запросы в один

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

Нужно добавить данные из второго и третьего запроса в первый.

В Power Query есть аналог функции JOIN — в русской версии он ожидаемо называется «Слияние запросов».

Используем Слияние запросов для объединения данных.
Обнуляем длину сессии для каналов которые не хотим учитывать (органика, прямые заходы, бренд-трафик).
Фильтруем тех, у кого общее время равно 0 (клиенты с каналов, которые мы не хотим учитывать). Оставляем только тех, кто совершил конверсию, и находим вес визита — делим продолжительность сессии на общую продолжительность сессий для пользователя.
Удаляем все столбцы кроме ym:s:UTMCampaign и веса, делаем сводную таблицу, где суммируем конверсии для каждой UTMCampaign.

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

2017  

Настройка счётчика top.mail.ru для сайтов с AJAX

Если вы размещаете рекламу в Таргет Мэйле и ваш сайт использует AJAX — статья для вас. Например, застройщики с сайтами от студии ART3D.

Что случилось

Если ваши счётчики Метрики, Аналитикса и Мэйла показывают глубину просмотра 1—1,5 (и у вас не одностраничник), то, скорее всего, они не понимают, что пользователь перешёл на новую страницу — он будет как бы находиться на странице приземления.

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

Что делать

Для Метрики и Аналитикса проблема давно решена, а для счётчика Мэйла фикс появился только недавно, ловите. Мэйл — классные, быстро решили проблему и сделали мануал.

Проверяем звонки с помощью «Вконтакте» и «Яндекс.Аудиторий»

Ещё один метод найти фейковые звонки в сфере недвижимости подсказал мне Боря Константинов. Я проверил двенадцать популярных сайтов и делюсь результатами.

Спойлер: не всё так плохо.

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

Как проверить номера?

«Вконтакте» позволяет собирать аудитории ремаркетинга по номерам телефонов:

Загружать можно любое количество телефонов, в ответ система показывает сколько пользователей соцсети нашлось:

Какая доля клиентов должна находиться?

Я проверил выборку из 400 мобильных номеров с «чистого» канала. Нашёл 62% абонентов. Поскольку выборка небольшая, у этого значения есть погрешность — реальная доля пользователей «Вконтакте» лежит в диапазоне 57—67% (с вероятностью 95%).

Результаты

Доля найденных пользователей «Вконтакте» со звонков с рекламных площадок

Только две площадки из двенадцати показали статистически значимые плохие результаты. Если вы там размещаетесь, прозвоните контакты или посмотрите в ЦРМ были ли с них показы и сделки. Остальные сайты — в норме.

Аналогичный способ в «Яндекс.Аудиториях»

Яндекс с чистого канала находит 45±3% пользователей. Подозрительные по результатам проверки «Вконтакте» площадки и здесь показали худшие результаты: 18±13% и 13±12%.

Ок, пара странных площадок, но обычно звонят реальные люди?

Не знаю. Я исхожу из предположения, что левые звонки делают с новых симок. Если номер новый, его нет в Яндексе, Сбербанке, Авито, Авто.ру, Фейсбуке, «Вконтакте» и других базах.

Организовать действительно большой поток звонков реальных людей (которые будут находиться) с Youdo и подобных сервисов сложно. Поэтому, предлагаю пока снизить градус подозрительности — возможно, не всё так плохо на самом деле. Я продолжаю проверять источники — как будут результаты, поделюсь.

Паранойя 2.0

Что если накручивают звонки не площадки, а те, кто эти размещения продаёт — агентства. Тогда подобные исследования дадут разные результаты в каждом конкретном случае.

Если вы сомневаетесь в своих звонках — пишите.

Ещё по теме:

Как рассчитать размер выборки
Другие способы выявления фрода от Макса Жучкова
Ещё несколько способов: HLR-запросы, Ping-SMS и слитые базы данных
Ищём среди клиентов Сбербанка
Выступление Алексея Липскерова

2017  
Ctrl + ↓ Ранее