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

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

Передаём Google client ID в Метрику с помощью GTM

Мне иногда надо «дописывать» в кол-трекинг client_id Метрики когда определился только id гугла.

Создаём в GTM новую переменную ga_cookie:

И новый тег с пользовательским HTML:

Укажите свой счетчик Метрики

Код:

<script>
try {
  var cookie = {{ga_cookie}}.split(".");
  var gacid = cookie[2] + "." + cookie[3] 
} 
catch(e) {
  var gacid = "no_gacid" 
}

window.yaParams = { "ga_cid": gacid };
ym(XXXXXX, 'params', window.yaParams||{});
// console.log("google_cid: " + gacid); 
</script>

Теперь в параметрах визита есть id гугла:

Вуаля

Полезное по теме:
Что такое ClientID в Яндекс Метрике?
5 способов получить cid (Client ID) пользователя Google Analytics 

15 марта  

Парсим объявления в Директе-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’)
Скачать драйвер

2019  

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

Пришло время, и вы вдруг начали подозревать, что ваш любимый канал 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/ВПР.
Гугл-таблица с примером.

2019  
2019   python

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

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

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

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

2019   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  
Ctrl + ↓ Ранее