Как создать 220 000 ключевых слов за 30 секунд, с помощью макросов в Excel

Дата . Автор

vba-excel
Как за полминуты написать 220 000 ключевых фраз для рекламы интернет-магазина? Не знаете? Сейчас я Вам расскажу.

Задача

Запустить контекстную рекламу в Google Adwords для интернета-магазина автомобильных запчастей и аксессуаров. В первоначальном варианте в CSV-таблице файла-выгрузки видим следующие столбцы:

  1. ссылка на страницу сайта,
  2. название товара,
  3. тип товара,
  4. марка автомобиля,
  5. годы выпуска.

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

table-after

На каждой посадочной странице размещены товары для одной модели авто с определёнными годами выпуска. Например, на странице http://site.ru/renault-logan/bamper-peredniy/ размещены передние бамперы для Renault Logan с 2005 по 2015 годы выпуска.

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

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

В итоге у нас вышел список из 48 ключевых фраз для этой страницы:

  • Бампер передний LOGAN
  • Бампер передний ЛОГАН
  • Бампер передний RENAULT LOGAN
  • Бампер передний РЕНО ЛОГАН
  • Бампер передний LOGAN 2005
  • Бампер передний ЛОГАН 2005
  • Бампер передний RENAULT LOGAN 2005
  • Бампер передний РЕНО ЛОГАН 2005
  • Бампер передний LOGAN 2006
  • Бампер передний ЛОГАН 2006
  • Бампер передний RENAULT LOGAN 2006
  • Бампер передний РЕНО ЛОГАН 2006
  • Бампер передний LOGAN 2007
  • Бампер передний ЛОГАН 2007
  • Бампер передний RENAULT LOGAN 2007
  • Бампер передний РЕНО ЛОГАН 2007
  • Бампер передний LOGAN 2008
  • Бампер передний ЛОГАН 2008
  • Бампер передний RENAULT LOGAN 2008
  • Бампер передний РЕНО ЛОГАН 2008
  • Бампер передний LOGAN 2009
  • Бампер передний ЛОГАН 2009
  • Бампер передний RENAULT LOGAN 2009
  • Бампер передний РЕНО ЛОГАН 2009
  • Бампер передний LOGAN 2010
  • Бампер передний ЛОГАН 2010
  • Бампер передний RENAULT LOGAN 2010
  • Бампер передний РЕНО ЛОГАН 2010
  • Бампер передний LOGAN 2011
  • Бампер передний ЛОГАН 2011
  • Бампер передний RENAULT LOGAN 2011
  • Бампер передний РЕНО ЛОГАН 2011
  • Бампер передний LOGAN 2012
  • Бампер передний ЛОГАН 2012
  • Бампер передний RENAULT LOGAN 2012
  • Бампер передний РЕНО ЛОГАН 2012
  • Бампер передний LOGAN 2013
  • Бампер передний ЛОГАН 2013
  • Бампер передний RENAULT LOGAN 2013
  • Бампер передний РЕНО ЛОГАН 2013
  • Бампер передний LOGAN 2014
  • Бампер передний ЛОГАН 2014
  • Бампер передний RENAULT LOGAN 2014
  • Бампер передний РЕНО ЛОГАН 2014
  • Бампер передний LOGAN 2015
  • Бампер передний ЛОГАН 2015
  • Бампер передний RENAULT LOGAN 2015
  • Бампер передний РЕНО ЛОГАН 2015

Проделывать такие операции вручную долго и нудно. На помощь приходит магия, а точнее Visual Basic for Application и Microsoft Excel.

 

Решение задачи

Для решения этой задачи было проделано следующее::

  1. Взяли выгрузку из интернета-магазина, сохранили её в формате XLSM (формат Excel, позволяющий хранить в себе макросы);
  2. Составили блок схему на листе А4, в ней описали, что необходимо умножить и в каком виде сохранить;
  3. Написали макрос (код ниже), протестировали, исправили ошибки;
  4. Запустили готовый макрос на реальных данных.

Мы получили таблицу с ключевыми фразами, целевыми страницами и информацией о товаре. Затем, при помощи макросов мы легко и быстро сгенерируем 220 000 объявлений, которые будут релевантны поисковой фразе и посадочной странице.

 

Код макроса

Сам код достаточно прост:

Sub СТАРТ()

'Переменные
date1 = 0
date2 = 0
TekDate = 0
Str1 = 1
NewStroka = 2

'Заголовки создаваемой таблицы
Cells(1, 13) = "Название группы объявлений для товара"
Cells(1, 14) = "Формируем ключевое слово"
Cells(1, 15) = "Название товара"
Cells(1, 16) = "Год начала выпуска"
Cells(1, 17) = "Год окончания выпуска"
Cells(1, 18) = "Вариант написания марки+модели авто без года"
Cells(1, 19) = "Формируем отображаемый URL, главное затем не забыть заменить пробелы на "
Cells(1, 20) = "Текущий год выпуска, мы создаём ключевое слово под каждый год в диапазоне дат выпуска модели авто"
Cells(1, 21) = "Марка авто по русский"
Cells(1, 22) = "Марка авто по английски пригодится при формировании текстов объявлений"
Cells(1, 23) = "Модель авто по английски пригодится при формировании текстов объявлений"
Cells(1, 24) = "Модель авто по русски пригодится при формировании текстов объявлений"
Cells(1, 25) = "Ссылка на товар"

'Самый главный цикл, бегаем по всему массиву исходных данных
For stroka = 3 To Cells(Rows.Count, 1).End(xlUp).Row

'Запоминаем даты выпуска модели авто
date1 = Cells(stroka, 3) 'Год начала выпуска
date2 = Cells(stroka, 4) 'Год окончания выпуска
TekDate = 0

'Формируем ключи без дат
For Str1 = 5 To 8
If Cells(stroka, Str1) <> 0 Then
Cells(NewStroka, 13) = CStr(Cells(stroka, 2)) + CStr(Cells(stroka, Str1)) + " (" + CStr(date1) + "-" + CStr(date2) + ") " 'Название группы объявлений для товара
Cells(NewStroka, 14) = CStr(Cells(stroka, 2)) + " " + CStr(Cells(stroka, Str1)) 'Формируем ключевое слово
Cells(NewStroka, 15) = Cells(stroka, 2) 'Название товара
Cells(NewStroka, 16) = Cells(stroka, 3) 'Год начала выпуска
Cells(NewStroka, 17) = Cells(stroka, 4) 'Год окончания выпуска
If Str1 = 5 Then Cells(NewStroka, 18) = Cells(stroka, 7) 'Вариант написания марки+модели авто без года
If Str1 = 6 Then Cells(NewStroka, 18) = Cells(stroka, 8) 'Вариант написания марки+модели авто без года
If Str1 = 7 Then Cells(NewStroka, 18) = Cells(stroka, 7) 'Вариант написания марки+модели авто без года
If Str1 = 8 Then Cells(NewStroka, 18) = Cells(stroka, 8) 'Вариант написания марки+модели авто без года
Cells(NewStroka, 19) = "site.ru/" + CStr(Cells(stroka, Str1)) + "_" + CStr(date1) + "_" + CStr(date2) 'Формируем отображаемый URL, главное затем не забыть заменить пробелы на "_"
Cells(NewStroka, 20) = CStr(TekDate) 'Текущий год выпуска, мы создаём ключевое слово под каждый год в диапазоне дат выпуска модели авто
Cells(NewStroka, 21) = Cells(stroka, 9) 'Марка авто по русский
Cells(NewStroka, 22) = Cells(stroka, 10) 'Марка авто по английски пригодится при формировании текстов объявлений
Cells(NewStroka, 23) = Cells(stroka, 5) 'Модель авто по английски пригодится при формировании текстов объявлений
Cells(NewStroka, 24) = Cells(stroka, 6) 'Модель авто по русски пригодится при формировании текстов объявлений
Cells(NewStroka, 25) = Cells(stroka, 1) 'Ссылка на товар

NewStroka = NewStroka + 1
End If
Next Str1

'Фиксируем текущую дату в цикле
TekDate = date1

'Формируем ключи с датами
For TekDate = date1 To date2
For Str1 = 5 To 8
If Cells(stroka, Str1) <> 0 Then

Cells(NewStroka, 13) = CStr(Cells(stroka, 2)) + " " + CStr(Cells(stroka, Str1)) + " (" + CStr(date1) + "-" + CStr(date2) + ") " + CStr(TekDate) 'Название группы объявлений для товара
Cells(NewStroka, 14) = CStr(Cells(stroka, 2)) + " " + CStr(Cells(stroka, Str1)) + " " + CStr(TekDate) 'Формируем ключевое слово
Cells(NewStroka, 15) = Cells(stroka, 2) 'Название товара
Cells(NewStroka, 16) = Cells(stroka, 3) 'Год начала выпуска
Cells(NewStroka, 17) = Cells(stroka, 4) 'Год окончания выпуска
If Str1 = 5 Then Cells(NewStroka, 18) = Cells(stroka, 7) 'Вариант написания марки+модели авто без года
If Str1 = 6 Then Cells(NewStroka, 18) = Cells(stroka, 8) 'Вариант написания марки+модели авто без года
If Str1 = 7 Then Cells(NewStroka, 18) = Cells(stroka, 7) 'Вариант написания марки+модели авто без года
If Str1 = 8 Then Cells(NewStroka, 18) = Cells(stroka, 8) 'Вариант написания марки+модели авто без года
Cells(NewStroka, 19) = "site.ru/" + CStr(Cells(stroka, Str1)) + "_" + CStr(TekDate) 'Формируем отображаемый URL, главное затем не забыть заменить пробелы на "_"
Cells(NewStroka, 20) = CStr(TekDate) 'Текущий год выпуска, мы создаём ключевое слово под каждый год в диапазоне дат выпуска модели авто
Cells(NewStroka, 21) = Cells(stroka, 9) 'Марка авто по русский
Cells(NewStroka, 22) = Cells(stroka, 10) 'Марка авто по английски пригодится при формировании текстов объявлений
Cells(NewStroka, 23) = Cells(stroka, 5) 'Модель авто по английски пригодится при формировании текстов объявлений
Cells(NewStroka, 24) = Cells(stroka, 6) 'Модель авто по русски пригодится при формировании текстов объявлений
Cells(NewStroka, 25) = Cells(stroka, 1) 'Ссылка на товар

NewStroka = NewStroka + 1
End If
Next Str1
Next TekDate

Next stroka
End Sub

Здесь вы можете скачать сам Excel-файл и посмотреть, как он работает.

 

Выводы и советы

  1. Если Вы специалист по контекстной рекламе и часто запускаете рекламные кампании на сотни тысяч ключевых слов и объявлений, то вам не обойтись без умения работы с макросами;
  2. Для программирования достаточно знаний основ программирования, которые мы изучали еще в школе и 1-2 дня тренировки. В сети вы найдете множество решений, посвящённых макросам в Excel, поэтому ответы на интересующие вопросы находятся за считанные минуты;
  3. Начните с простых макросов, например, склеивания значений в ячейках. И постепенно переходите к сложным задачам.

А какие вы решаете задачи в контекстной рекламе при помощи VBA?

Если у вас возникли вопросы, задавайте их в комментариях к посту.

Рустам Рустам Гизатуллин младший специалист по контекстной рекламе