Рассчитать премию сотруднику с помощью «Google Таблиц» и Google Analytics

Инструкция по созданию автоматизированной системы расчёта мотивации сотрудников отдела электронной коммерции.

Для начала необходимо создать файл в «Google Таблицах». Все дальнейшие действия будут производиться в нём. На первом листе сделаем таблицу, в которой будем в дальнейшем получать данные по размеру премии для выплаты каждому из сотрудников по месяцам. В примере данные будут в процентах, в реальных условиях можно использовать данные в деньгах.

Для каждого из сотрудников в файле необходимо создать дополнительный лист, на котором и будут производиться расчёты премии. На каждом из этих листов лучше прописать все условия мотивации, чтобы в дальнейшем их использовать в формулах, а в случае изменения каких-либо показателей мотивации их можно будет исправить в одном месте, а не переделывать все формулы.

Например, у email-маркетолога мотивация будет содержать следующие условия:

  • при увеличении дохода с email-канала на 10% и выше относительно предыдущего месяца — премия 100%;

  • если при этом email-база не выросла на 2000 и более подписчиков относительно предыдущего месяца, премия сокращается на 30%;

  • план максимум: при увеличении дохода с email-канала на 30% и выше и росте базы на 2000 и более подписчиков относительно предыдущего месяца — премия 150%.

Также условия можно дополнить другими параметрами — в зависимости от особенностей разных компаний. К примеру, можно исключить доход с каких-то крупных акций, анонсируемых через email, так как они могут быть нерегулярными и будут портить статистику по доходу с канала. Чтобы в дальнейшем исключить доход с акций, необходимо в метки utm_campaign добавлять определённый параметр.

Далее на этом же листе создаём таблицу, которая потребуется для расчёта премии email-маркетолога.

Но требуемые для расчёта данные будем добавлять не вручную из Google Analytics, а автоматизируем с помощью дополнения, которое можно установить из меню «Дополнения», пункт «Установить дополнения».

После установки необходимо снова зайти в меню «Дополнения», выбрать Google Analytics и нажать Create a new report.

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

Мотивация сотрудника основана на доходе с канала, поэтому в Metrics указываем ga:transactionRevenue — это «Доход» в Google Analytics. Также потребуются данные о канале, кампаниях и временные показатели. Поэтому в Dimensions указываем ga:campaign, ga:source, ga:medium, ga:yearMonth. Таким образом в отчёте получим данные по месяцу, источнику, каналу, кампании и доходу.

Конфигуратор отчёта позволяет дополнительно указать сегмент, по которому будет строиться отчёт. Чтобы упростить дальнейшие вычисления и не выгружать в файл «Google Таблиц» всю информацию, можно в Google Analytics создать сегменты под каждого сотрудника, указав в фильтре соответствующий канал.

После заполнения всех полей в файле будет создан лист с конфигурацией будущего отчёта.

Теперь необходимо снова зайти в меню «Дополнения» — Google Analytics — Run reports. После этого будет создан дополнительный лист с данными из Google Analytics.

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

Теперь добавим два дополнительных столбца, в которых с помощью формул получим данные по месяцам и доходу в нужном формате.

В расчёте премии email-маркетолога помимо дохода участвуют данные по приросту базы подписчиков. Для получения этой информации необходимо создать дополнительный отчёт через дополнение Google Analytics. Для этого во вкладке «Дополнения» выбираем Google Analytics — Great new report и заполняем параметры отчёта, как в примере выше.

Но в этот раз метриками будут уникальные события, а в качестве фильтра выбираем категории события и указываем все категории событий форм, где пользователь может оставить электронный адрес на сайте. В результате на вкладке Report Configuration появятся данные, по которым будет строиться новый отчёт, а также добавится лист с заданным названием, на который будут выгружаться данные по количеству подписок.

Аналогично предыдущему отчёту забираем данные на лист Data и с помощью формул приводим их к виду, который можно будет использовать в дальнейших расчётах.

Теперь, имея все необходимые данные на листе Data, можно рассчитать выполнение ежемесячного плана на листе email-маркетолога, используя начальные условия. В столбце «Доход с акций» считаем только доход с кампаний, у которых метка utm-campaign начинается с bigsale.

И данные из столбца «Размер премии» подгружаем на первый лист «Премии сотрудников».

Рассчитав премию для email-маркетолога, сделаем аналогичное для CPC-маркетолога. Мотивация этого сотрудника будет содержать следующие условия:

  • при увеличении дохода с CPC-канала на 10% и выше относительно предыдущего месяца — премия 100%;

  • если при этом средняя стоимость привлечённого заказа выше 700 рублей — премия сокращается на 30%;

  • план максимум: при увеличении дохода с CPC-канала на 30% и выше относительно предыдущего месяца, и средняя стоимость заказа не превышает 700 рублей — премия 150%.

Теперь необходимо проделать те же действия, что описаны выше. На вкладке Report Configuration добавляем условия нового отчёта. Тут необходимы метрики ga:adCost, ga:transactions, ga:transactionRevenue и показатели ga:campaign, ga:source, ga:medium, ga:yearMonth. В качестве фильтра следует указать ga:medium==cpc.

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

Далее с помощью формул преобразовать данные из столбцов Month of Year, Cost, Revenue.

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

Теперь данные с этого листа можно подтянуть на основной лист со всеми премиями.

На основе двух описанных примеров можно рассчитать мотивацию остальных сотрудников, используя разные данные при формировании отчётов из Google Analytics.

Но пока данные о премиях сотрудников хоть и получены, но не обновляются. Чтобы эти данные подгружались в отчёт автоматически, необходимо зайти на вкладку «Дополнения», выбрать Google Analytics — Schedule Reports. В появившемся окне следует установить галку Enable reports to run automatically, указать периодичность обновления и желаемое время, когда отчёт будет обновляться, и нажать на кнопку Save.

В результате всех этих действий получился отчёт, который рассчитывает мотивацию каждого сотрудника отдела электронной коммерции на основе данных Google Analytics, обновляя их в автоматическом режиме.

0
11 комментариев
Написать комментарий...
Booomer-Tuning.Ru

Идея хорошая, только мотивация завязана на каких-то странных показателях. Количество подписчиков на Email рассылку можно накрутить: получится, что на отчётный период их стало 100 000, пофиг, что к следующей дате отчёта больше половины отвалится... А если баловаться моделями атрибуции, то не понятно кто в итоге продал: email, cpc, cpa маркетолог?

Ответить
Развернуть ветку
Дмитрий

Спасибо, хороший урок.

Ответить
Развернуть ветку
Booomer-Tuning.Ru

Нужно завязывать на прямую прибыль или оборот... Все эти CTR, стоимость заказа... ничего не значат для бизнеса в итоге. Бизнесу важен объём продаж, прибыль... Ну стоит заказ на 1000 рублей дороже, так он может и прибыли приносит больше, чем дешевый китайски порнотрафик с стоимостью заказа 1 рубль...

Ответить
Развернуть ветку
Алексей Брязгин

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

Ответить
Развернуть ветку
Booomer-Tuning.Ru

При наличии CRM мне кажется это решается ещё быстрее. По М-протоколу можно передавать в Google Analytics эти данные (реальные продажи из CRM)?

Ответить
Развернуть ветку
Константин Фанки

По м-протоколы просто прокидывать отказы от покупок, как возвраты - правильно?
В расширенной торговле ведь нет именно выкупа? Только возвраты?

Ответить
Развернуть ветку
Booomer-Tuning.Ru

Я уже не помню, что там есть. В расширенной электронной торговле точно есть отказ от покупки. А по м-протоколу можно передавать что угодно.

Ответить
Развернуть ветку
Константин Фанки

Я и говорю, что можно отказы, а не «реальные продажи»
Правильно?

Ответить
Развернуть ветку
Booomer-Tuning.Ru

По идее да. Нужно лезть в документацию Google.

Ответить
Развернуть ветку
Booomer-Tuning.Ru
Ответить
Развернуть ветку
Booomer-Tuning.Ru
Ответить
Развернуть ветку

Комментарий удален модератором

Развернуть ветку
8 комментариев
Раскрывать всегда