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

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

Парсим объявления в Директе-2 и делаем скриншоты

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

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

CSV-файл
Скриншоты в папке

Как сделать

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

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

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

Задайте регион. Наример, Москва и Московская область — 1, а Санкт-Петербург — 2.

region = '1'

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

https://www.yandex.ru/search/ads?text=дилеры%20ауди%20+в%20москве&lr=1&p=0
Запрос 1 из 6
...
...
...
https://www.yandex.ru/search/ads?text=ауди%20официальный%20дилер%20+в%20москве&lr=1&p=0
Запрос 2 из 6
...
...
...
https://www.yandex.ru/search/ads?text=дилер%20ауди%20север%20москва&lr=1&p=0
Запрос 3 из 6
...
...
...
https://www.yandex.ru/search/ads?text=ауди%20север%20официальный%20дилер%20+в%20москве&lr=1&p=0
Запрос 4 из 6
...
...
...
https://www.yandex.ru/search/ads?text=купить%20ауди%20москва%20дилер&lr=1&p=0
Запрос 5 из 6
...
...
...
https://www.yandex.ru/search/ads?text=купить%20ауди%20+у%20официального%20дилера%20+в%20москве&lr=1&p=0
Запрос 6 из 6
...
...
...

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

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

P. S. ChromeDriver нужно устанавливать. Путь в скрипте указывается в строчке browser = webdriver.Chrome(chrome_options=options, executable_path=r’/Users/vitalybakhvalov/Documents/chromedriver’)
Скачать драйвер

12 февраля  

Как склеить выгрузку из колтрекинга с данными о визитах в гугл-таблицах

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

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

Пример выгрузки из CRM с контактами тех, кто приехал в офис продаж

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

Приводим номера телефонов в единый вид

Удалим все не-цифры, возьмем 10 цифр справа и добавим к ним «7»
=REGEXREPLACE(REGEXREPLACE(ТЕКСТ(F9;"#");"\D";"");".*(\d{10})$";"7$1")

Избавляемся от столбцов

Конечно, проще сделать split в SQL, но иногда так быстрее
=QUERY({D18:E21\F18:F21;D18:E21\G18:G21};"select Col1,Col2,Col3 where Col3 != '' order by Col1 asc, Col2 asc label Col1 'id', Col2 'name', Col3 'phone'";0)

Итого

Теперь эти данные можно корректно подтянуть к выгрузке колтрекинга с помощью VLOOKUP/ВПР.
Гугл-таблица с примером.

10 февраля  
1 февраля   python

Подсчёт количества символов для Директа в Гугл-таблицах

При подсчёте количества символов в текстах объявлений Директа не учитываются некоторые символы.
Для верного подсчёта в Гугл-таблицах подойдет формула:

=ДЛСТР(REGEXREPLACE(REGEXREPLACE(A1;СИМВОЛ(34);"");"[«»,.:;!]";""))

Где A1 — ячейка с текстом.

15 января   google_sheets

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()

Источник

2018   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). Например, находить и сохранять телефоны собственников-продавцов квартир и показывать им рекламу. Или глубоко анализировать влияние этажа на площадь кухни стоимость.

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

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

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

UPD: Новая версия

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

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

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

Каждый запрос вбивает в Яндекс и записывает заголовки, тексты и отображаемые ссылки рекламных объявлений в 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  
Ctrl + ↓ Ранее