База рекламных объявлений содержит 43 984 091 уникальных рекламных объявлений Яндекс.Директ, 406 379 уникальных доменов рекламодателей, 23 798 137 уникальных заголовков и 14 045 562 уникальных текстов рекламных объявлений по регионам Россия, Украина, Беларусь, Казахстан и крупным городам этих стран. База собиралась в апреле - мае 2017 года.
При запросах рекламных объявлений ни один рекламодатель не пострадал, по ссылкам программа не переходила.
Эта база будет полезна тем, кто хочет составить рекламные объявления для Яндекс.Директ на основе имеющихся объявлений конкурентов. Как правило, веб-сервисы, анализирующие конкурентов, показывают объявления конкурентов по запросам, что, конечно же, полезно, но мы преследуем более простую и утилитарную цель и хотим помочь быстро составить рекламные объявления на основе уже существующих. Это делается благодаря возможности поиска объявлений (а также их частей) по вхождениям ключевых слов в заголовки и тексты рекламных объявлений.
База рекламных объявлений Яндекс.Директ предлагается в двух вариантах: 1) в виде дампа csv, который рекомендуется тем, кто знаком с работой приложений для БД и 2) сконвертированной в формат sqlite, готовой к импортированию и дальнейшей работы с ней. Этот вариант базы будет предпочтителен для тех, кто не знаком с базами и хочет работать с готовой оболочкой.
Также остаются доступными для скачивания прежние базы рекламных объявлений, собранные 1) с апреля по октябрь 2016 и 2) с октября 2016 г. по март 2017 г. включительно.
Мы выбрали формат sqlite как наиболее популярный в работе с базами данных. Для sqlite есть много бесплатных оболочек, предлагаем вам две достаточно популярные бесплатные оболочки DB Browser for SQLite и SQLiteStudio.
Обе программы кросс-платформенные, т.е. имеют версии для Windows, MacOS X и Linux. Обе поддерживают русский язык. Некоторым недостатком, который мы заметили у DB Browser for SQLite, является то, что она при импорте базы подвисает (вычитывает все данные базы) и процедура импорта занимает больше времени. Как отключить первоначальное чтение базы при открытии, мы не нашли, но это ее единственный заметный недостаток.
В итоге решили для объяснений использовать SQLiteStudio. Скриншоты ниже сделаны с использованием SQLiteStudio.
1. Скачайте нужный дистрибутив с http://sqlitestudio.pl/?act=download и распакуйте архив (как такового процесса инсталляции нет).
2. В папке программы найдите файл SQLiteStudio.exe и нажмите его. При первом запуске выберите язык:
3. В меню «База данных» выберите пункт «Добавить базу данных»:
4. Выберите скачанную с сайта программы Букварикс и распакованную базу рекламных объявлений Яндекс Директ (YandexAds.db) и дважды кликните на названии базы, чтобы открыть:
Если база не открывается, обратите внимание на путь к файлу базы данных. В синтаксисе пути не должно быть пробелов.
5. Откройте окно редактора SQL:
6. Введите в окно запроса синтаксис запроса и нажмите кнопку старта:
Сразу отметим, что в ходе работы появляется сообщение об ошибке:
Could not find Android Debug Bridge application. Click here to point out the location of the ADB application, otherwise the Android SQLite plugin will not support USB cable connections, only the network connection...
и предупреждение:
SQLiteStudio не удалось извлечь метаданные из запроса. Результаты нельзя будет редактировать.
Не обращайте на них внимания, редактировать базу на андроид-устройстве мы не собираемся, да и редактировать базу вообще тоже не будем :)
По умолчанию программа выводит 1000 результатов на страницу, есть возможность перехода по страницам. Экспортируется полная выборка.
Поиск в базе осуществляется по трем полям: заголовку рекламного объявления, тексту рекламного объявления и домену рекламодателя. В синтаксисе запросов используется *, заменяющая символы в полнотекстовом поиске (используем при поиске в текстах и заголовках объявлений), %, заменяющий символы при обычном неиндексированном поиске (используем при поиске доменов). Регистр букв не влияет на результат.
Для поиска словоформ нужно использовать основу слова (без окончаний) или начальную часть слова (без суффиксов или даже без чередующихся букв корня) со звездочкой, заменяющей буквы в конце слова. Обращаем ваше внимание на то, что звездочку можно использовать для замещения букв только в конце слова, для замещения начальных букв слова этот синтаксис в данном случае не работает.
Приведем пример. Просмотрим рекламные объявления, предлагающие организацию проведения детских праздников.
1. Выборка полных рекламных объявлений по одному слову, которое может находиться либо в заголовке, либо в тексте объявления.
SELECT * FROM ads WHERE ads MATCH 'праздни*';
В результатах выборки будут полные объявления, содержащие слова «праздник» и «праздничный» в разных словоформах.
2. Выборка полных рекламных объявлений по двум словам, которые могут находиться либо в заголовке, либо в тексте объявления:
SELECT * FROM ads WHERE ads MATCH 'дет* праздни*';
В результатах выборки будут полные объявления, которые одновременно содержат формы слов «праздник» или «праздничный» в разных и формы слов «дети» или «детский». По аналогии в запрос можно добавить еще слова или их формы, например, ...'дет* праздни* спорт*'.
Можно выбрать рекламные объявления, в которых искомое слово находится только в заголовке.
3. Выборка полных рекламных объявлений по словам, которые находится в заголовке:
SELECT * FROM ads WHERE ads MATCH 'atitle:дет* atitle:праздни*';
4. Выборка полных рекламных объявлений по словам, которые находится в тексте объявления:
SELECT * FROM ads WHERE ads MATCH 'atext:спорт*' atext:праздни*;
Можно найти объявления, у которых в заголове одно слово, а в тексте другое (условия выполняются одновременно).
5. Выборка полных рекламных объявлений по разным словам, находящимся в заголовке и в тексте объявления:
SELECT * FROM ads WHERE ads MATCH 'atitle:дет* atitle:праздни* atext:школ*';
Для того, чтобы исключить вхождения с каким-либо словом из выборки, перед словом поставим знак минус "-".
6. Выборка полных рекламных объявлений с условием невхождения слов в заголовок и/или текст рекламного объявления:
SELECT * FROM ads WHERE ads MATCH 'atitle:дет* atitle:праздни* atext:-школ* atext:-сад*'
Например, при в результате выполнения вышеприведенного запроса выберутся объявления, в заголовках которых присутствуют формы слов «дети», «детский» и «праздник», «праздничный» и, в то же время, в текстах не встречается формы слов «школа», «школьный» и «сад».
Во всех вышеприведенных примерах в результатах выборки выводились все три поля базы. Однако, можно вывести только заголовки или только тексты.
7. Выборка заголовков с условием вхождения слов в заголовок рекламного объявления:
SELECT atitle FROM ads WHERE ads MATCH 'atitle:дет* atitle:праздни*';
В этом запросе выведены заголовки со словоформами «праздник» и «праздничный». В этом случае заголовки могут повторяться, поскольку один и тот же заголовок может входить в состав разных рекламных объявлений.
8. Выборка уникальных заголовков с условием вхождения слова в заголовок рекламного объявления:
SELECT distinct (atitle) FROM ads WHERE ads MATCH 'atitle:дет* atitle:праздни*';
Как вы видите, в случае запроса неуникальных и уникальных заголовков получается разница в количестве таких заголовков. Аналогичным образом можно выбрать и тексты рекламных объявлений с условием вхождения слов в такие тексты.
9. Выборка объявлений по домену:
SELECT * FROM ads WHERE adomain = 'alexgrim.ru';
Примечание: при выборке по домену тратится больше времени, поскольку анализируется вся база. Ускорить этот процесс можно, но поскольку наша задача - выбор объявлений по словам в тексте и/или в заголовке, то в данной статье мы этот вопрос не рассматриваем. Просто подождите 1-2 минуты.
Домены могут быть записаны по-разному, например, corp.123event.ru или svadba.123event.ru, поэтому при запросе имеет смысл предусмотреть возможные варианты написания:
SELECT * FROM ads WHERE (adomain like '%.123event.ru') or (adomain = '123event.ru');
Для дальнейшей работы с объявлениями вам, возможно, потребуется экспортировать выборку рекламных объявлений и далее открывать ее в Excel.
10. Экспорт выборки рекламных объявлений.
В основном меню Инструменты выберите пункт «Экспорт» и далее следуйте по шагам.
В качестве экспортиуемого содержания выберите «Результат запроса»:
Вставьте код нужного запроса:
Настройте формат экспорта в csv (при необходимости можно выбрать и другие форматы). На этом шаге нужно обратить внимание на выбор пути к файлу выборки, на выбор кодировки - выберите UTF-8 и на опции разделителя столбцов - выберите точку с запятой:
Мы описали основные возможности работы с базой рекламных объявлений, полагаем, что для большинства случаев этого достаточно.
Однако, если вы хотите подробнее узнать о возможностях полнотекстового поиска в SQLite, смотрите https://www.sqlite.org/fts3.html.
А если вы хотите больше информации о SQL запросах в SQLite, смотрите https://www.sqlite.org/lang_select.html
© 2014 - 2018 Букварикс