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

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

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

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

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

Также условия можно дополнить другими параметрами — в зависимости от особенностей разных компаний. К примеру, можно исключить доход с каких-то крупных акций, анонсируемых через 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-маркетолога. Мотивация этого сотрудника будет содержать следующие условия:

Теперь необходимо проделать те же действия, что описаны выше. На вкладке 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, обновляя их в автоматическом режиме.

#инструменты