Анализируем блог на WordPress с помощью Power BI

1 Star2 Stars3 Stars4 Stars5 Stars (Голосов 12, средняя оценка: 5,00)
Загрузка...

Анализируем блог на WordPress с помощью Power BI

Несмотря на большой объем данных, которые Google Analytics при стандартных настройках собирает из сайтов, очень часто их недостаточно для полноценного анализа чего-либо. Мы столкнулись с такой проблемой, когда решили проанализировать публикации на нашем блоге. Но, обо всем по порядку.

  1. Собираем данные о взаимодействии со статьями на блоге.
  2. Настройка сбора данных о категории и авторе статьи.
  3. Собираем и визуализируем данные блога в Power BI.

Итак, приступим.

Несмотря на большой объем данных, которые Google Analytics при стандартных настройках собирает из сайтов


Этап 1 - Собираем данные о взаимодействии со статьями на блоге

Первое, что мы решили сделать, это оценить какие статьи пользователи читают больше и лучше, т.е. читают до конца, проводят больше времени на странице.

какие статьи пользователи читают больше и лучше

Стандартные отчеты в Google Analytics не позволяют получить необходимую информацию в связи с особенностью сбора данных. Поэтому было решено использовать скрипт Анастасии Тимошенко для отслеживания скроллинга и времени на странице. Мы решили использовать вторую, улучшенную версию, благодаря которой можно также собрать информацию по категориям читателей. Получилось примерно вот так:

можно также собрать информацию по категориям читателей

Данные были полезны, только было одно большое НО: время на странице считалось общее: от захода на страницу, до выхода с неё. Все мы знаем, что у большинства пользователей окно браузера выглядит следующим образом:

браузер типичного пользователя

Пользователи очень редко сосредоточены на одной вкладке. В большинстве случаев, как на примере выше, у них открыто множество вкладок. И разница между общим временем на странице и временем, когда вкладка активна и пользователь с ней взаимодействует, очень огромна.

Как видим на скриншоте, в среднем, в примере с блогом, пользователь находится на странице около 45 минут, при этом около 14 минут вкладка с блогом активна. Поэтому мы решили улучшить существующий скрипт. Более подробно о том, как собрать эти данные читайте в статье.

Отчет в google analytics о среднем времени просмотра страницы, и среднем времени проведенном на странице с активной вкладкой

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


Этап 2 - Настройка сбора данных о категории и авторе статьи

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

Собрать значения для этих параметров можно различными способами, например на jQuery это можно сделать с помощью такого кода:

function() {

   var author = jQuery('your_selector').text();

   return author;

 }

где, вместо your_selector нужно указать CSS-селектор нужного элемента.

переменная с java script для получения данных об авторе статьи

С событием передаем clientID, он нам понадобиться чуть позже. Выглядит тег отправки данных в Google Tag Manager примерно вот так:

пример тега для отправки данных о статье в google analytics

В Google Analytics в итоге удалось построить подобного рода отчет:

отчет о категории статей и авторе в google analytics

 

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


Этап 3 - Собираем и визуализируем данные блога в Power BI

Power BI не ограничивается одним источником, поэтому информацию решили объединить со следующих систем:

  1. Google Analytics:
    • количество просмотров страниц;
    • количество уникальных посетителей;
    • clientID посетителей, которые посещали необходимые страницы;
    • тип читателя (информация о скроллинге и времени проведенном на странице);
  2. База данных блога (WordPress):
    • название статьи;
    • категория;
    • автор;
    • количество комментариев;
    • количество оценок;
    • рейтинг статьи;
  3. CRM (Bitrix 24):
    • информация о лидах;
    • информация о продажах;
    • доход.

3.1. Собираем данные с базы данных блога

Начнем с базы данных блога:
Для начала находим данные для авторизации в эту базу данных. Обычно они лежат в файле wp-config.php:

файл wp-config.php

или же в админ панели хостинга.
Получив доступ, стучимся через Power BI в нужную БД. Для наших целей нам необходимы таблицы отмеченные на следующим скриншоте:
Нужные нам таблицы с базы данных wordpress

  • blog_posts - информация о названии статьи и прямой ссылке;
  • blog_ratings - хранится информация о количестве оценок и рейтинге статьи;
  • blog_comments - информация о количестве комментариев;
  • blog_users - информация об авторах блога и статьях, которые они написали;
  • term_relationships, term_taxonomy, terms - в этих БД храниться информация о категориях к которым принадлежат статьи.

Далее связываем загруженные данные между собой так как указано на скриншоте:

связи между таблицами с базы данных wordpress

Важный момент! Связи выделенные на скриншоте обязательно должны быть двунаправленными. Реализовать это можно следующим образом: сделайте двойной щелчок на нужной связи и в открывшемся окне, в пункте “Направление кросс-фильтрации” выберите “Двунаправленная”.

двунаправленная кросфильтрафия в power BI

Если вы соединили все правильно, остается создать меры и можно строить отчет.
Необходимые меры:

  • Рейтинг = CALCULATE(AVERAGE(blog_ratings[rating_rating]))
  • Проголосовали = CALCULATE(COUNT(blog_ratings[rating_rating]))
  • Комментарии = CALCULATE(COUNT(blog_comments[comment_ID]))

пример отчета в power BI по данным wordpress


3.2. Собираем данные с Google Analytics

Данные с базы данных блога, это хорошо, но мы же хотели еще увидеть как пользователи взаимодействуют с контентом на сайте, а для этого нужно выгрузить в Power BI данные с Google Analytics. Вот несколько способов:

  • стандартный коннектор Power BI к Google Analytics;
  • PQGoogleAnalytics - коннектор к Google Analytics от Максима Уварова;
  • RGA — пакет R для работы с API Google Analytics от Artem Klevtsov;
  • googleAnalyticsR — еще один пакет R для работы с API Google Analytics от Mark Edmondson.

В своей работе мы решили остановится на последнем варианте — пакете googleAnalyticsR. Пример запроса для получения данных ниже:

## Устанавливаем необходимые пакеты, если они не установлены
if(!"googleAuthR" %in% installed.packages()[,1]){install.packages("googleAuthR")}
if(!"googleAnalyticsR" %in% installed.packages()[,1]){install.packages("googleAnalyticsR")}


## Подключаем необходимые библиотеки
library(googleAuthR)
library(googleAnalyticsR)

## Устанавливаем рабочую папку
setwd("C:/getBlogdata/")

## Авторизация в аккаунт Google
service_token <- gar_auth_service("C:/getBlogdata/service_ga.json")

## Указываем идентификатор представления
id_ga <- "XXXXXXX"

## Указываем диапазон дат.
start_date <- "2018-05-01"
end_date <- as.character.Date(Sys.Date() - 1)

## Создаем необходимые фильтры
df_eventCategory <- dim_filter("eventCategory","REGEXP","/blog/",not = FALSE)
df_eventLabel <- dim_filter("eventLabel","REGEXP","(not set)",not = TRUE)

## Создаем объект общего фильтра
filter_scrolling <- filter_clause_ga4(list(df_eventCategory, 
                                          df_eventLabel_1,
                                          df_eventLabel_2), 
                                     operator = "AND")

## Парсим информацию о взаимодействии со страницей, которую мы собрали на Этапе 1
scrolling_cID_data <- google_analytics_4(id_ga, 
                                        date_range = c(start_date,end_date),                                        dimensions=c('eventCategory','eventAction','eventLabel','date','dimension3'), 
                                        metrics = c('totalEvents','uniqueEvents'), 
                                        dim_filters = filter_scrolling,
                                        anti_sample = TRUE)

Собрав эти данные можно построить следующий отчет:

Здесь мы видим динамику просмотра блога в общем и каждой статьи по отдельности. Естественно, для себя вы можете выбрать другой способ визуализации данных и ограничены вы в этом случае только своей фантазией 😉

Уточнение: на практике при каждом обновлении отчета в Power BI запрашивать весь массив информации через API Google Analytics очень долго, да и можно очень легко попасть на ограничения. Поэтому рекомендуем, с помощью скрипта указанного выше, собирать данные и передавать их в базу данных. А вот уже с неё подгружать данные в Power BI. Пример кода для загрузки данных в базу данных MySQL ниже.

## Устанавливаем необходимые пакеты, если они не установлены
if(!"RMySQL" %in% installed.packages()[,1]){install.packages("RMySQL")}

## Подключаем необходимую библиотеку
library(RMySQL)

## Подключаемся к базе данных
 conMySQL <- dbConnect(MySQL(), dbname = "dbname", user = "user", password = "password", host = "host")

## Записываем информацию  
 dbWriteTable (conMySQL, db_name, data_name, row.names = FALSE, append = TRUE)

  #Закрываем соединение
 dbDisconnect(conMySQL)

Где в соответствующие значения нужно передать свои данные:

  • dbname - название базы данных
  • user - имя пользователя
  • password - пароль
  • host - хост
  • db_name - название таблицы в которую хотим записать данные
  • data_name - массив данных, который хотим записать.

Более подробно об этом мы уже рассказывали в статье Как контролировать весь отдел PPC и видеть полную картину в одном отчёте Power BI 


3.3. Подключаем данные CRM системы

Решено было не останавливаться на достигнутом и проанализировать информацию о лидах, и продажах на которые повлияли статьи с блога (ассоциированные конверсии по блогу). Для этого к уже имеющимся данным добавили информацию из CRM системы Bitrix24. В этот раз не использовались коннекторы Power BI, так как данные с CRM уже давно выгружаем в собственную базу данных. Помимо данных из CRM добавили дополнительные данные с Google Analytics. На выходе получился итоговый отчет:

Пример финального отчета по блогу

В этом отчете, помимо предыдущих показателей ещё рассчитываются следующие:

ПоказательЗначение
Ass.leadsАссоциированные лиды (лид посещал указанную страницу блога)
Ass.salesАссоциированные продажи (лид посещал указанную страницу блога)
Ass.revenueАссоциированный доход (лид посещал указанную страницу блога)
landing.leadsЛид, для которого указанная статья была страницей входа на наш сайт
landing.salesПродажа, для которой указанная статья была страницей входа на наш сайт
landing.revenueДоход, для которого указанная статья была страницей входа на наш сайт
last.leadsЛиды, предыдущим шагом которых перед оформлением заявки было посещение указанной страницы

Помимо показателей в отчете также можно посмотреть информацию о том, какие именно лиды/клиенты читали определенную статью:

какие именно лиды/клиенты читали определенную статью

Верно и обратное, можно посмотреть какие статьи читал определенный лид/клиент и с какого источника он к нам пришел:

какие статьи читал определенный лид/клиент и с какого источника он к нам пришел


Вместо заключения: Хороший контент уже давно стал важным элементом продвижения в онлайне. С помощью построенного отчета мы начали измерять влияние блога на наш бизнес не только по ощущениям, но и с помощью реальных показателей выраженных в $.

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

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