webew
Войти » Регистрация
 
PHP
MySQL/MariaDB
Интернет-маркетинг :: оценка эффективности

Бревно - система интернет-статистики своими руками

3 июля 2008, 1:11
Чем дальше в лес, тем больше дров.

В статье описана система «Бревно» — система сбора статистики посещения сайта, построенная на простых принципах с использованием PHP и MySQL. Сбор и сохранение полной статистики позволяет ответить на многие вопросы, связанные с эффективностью каналов продвижения и юзабилити сайта. Простота кода (100 строк на PHP) позволяет настраивать систему под конкретные задачи и встраивать в существующий код.

Для чего нужно Бревно?

Для качественной оценки эффективности работы сайта требуются ответы на многие вопросы, например:

  • Сколько посетителей приходят в день на сайт, сколько страниц они просматривают?
  • Сколько человек просмотрело заданную страницу за заданный период?
  • С каких сайтов или поисковых систем приходят посетители?

На перечисленные вопросы можно получить развернутый ответ с помощью популярных систем статистики (например, liveinternet и Google Analytics), достаточно удобных и доступных, в основном, бесплатно (будем называть из в дальнешем "счетчики"). Счетчики предлагают интерфейс для просмотра ответов на перечисленные выше и многие подобные вопросы. Внимательный просмотр такой статистики оставляет ощущение недосказанного анекдота и желание задать следующий ряд вопросов с общей темой "А куда они потом пошли?", например:

  1. С каких сайтов посетители, просмотревшие заданную страницу или выполнившие заданное действие, впервые пришли на сайт? Сколько страниц они просмотрели на сайте прежде чем попасть на заданную страницу?
  2. Что еще просматривали посетители, просмотревшие данную страницу?
  3. Какие страницы просмотрели посетители, пришедшие с заданного сайта?
  4. Сколько раз робот Яндекса загружал каждую страницу и когда был последний раз на каждой из страниц?

Счетчики дают ответ лишь на часть вопросов второй ступени. Для получения ответа на некоторые их них требуется предварительно описать целевые страницы и включить сбор статистики по целевым страницам (например, так работает Яндекс.Метрика). Ограничения связаны с тем, что счетчики не в состоянии хранить полную статистику посещаемости по всем обслуживаемым сайтам.

При активном анализе эффективности, новые вопросы могут появляться один за одним уже после того, как события на сайте произошли. Бревно хранит полную статистику посещений сайта в базе MySQL, что в большинстве случаев технически возможно. При ежедневной посещаемости в 10000 хитов, объем базы данных на диске составит примерно 2 Mb за каждый день статистики или 700 Mb за год, что вполне адекватно предложению на рынке хостинг-услуг.

Бревно представляет собой систему сбора статистики. Анализ данных потребует базовых знаний языка запросов SQL.

Статистические сессии

Для того, чтобы идентифицировать пользователя при повторных заходах на сайт будем использовать cookie. При первом посещении пользователя, он получает cookie BREVNOID, в которой сохраняется номер статистической сессии. Одновременно, данные о пользователе, включающие сайт, с которого он перешел, сохраняются в таблице brevno_sessions. Все страницы, загруженные пользователем фиксируются в таблице brevno_access, связанной с brevno_sessions по sessionid.

Обратите внимание, что cookie хранится 2 года (по умолчанию, если пользователь не очищает cookie в браузере) и статистическая сессия никак не связана с сессией PHP или с сессией CMS, используемой для авторизации. Обычно, в рамках одной статистической сессии, пользователь может несколько раз залогиниться/разлогиниться на сайте и в статистике все эти действия будут отражены (так же, как и неудачные попытки логина). В таблице сессий всегда доступен Referer, соответствующий первому посещению пользователя, таким образом можно отслеживать эффективность различных каналов рекламы.

Установка системы «Бревно»

Перед установкой системы убедитесь, что магические кавычки в PHP отключены (magic_quotes=Off) или побеждены средствами CMS или как описано в статье Магические кавычки в PHP.

Для установки Бревна, требуется создать 3 таблицы:

CREATE TABLE `brevno_pages` (
  `id` int(11) NOT NULL auto_increment,
  `host` varchar(50) NOT NULL,
  `uri` varchar(255) NOT NULL,
  `method` varchar(7) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `uri` (`uri`,`host`,`method`)
) DEFAULT CHARSET=latin1;

CREATE TABLE `brevno_sessions` (
  `id` int(11) NOT NULL auto_increment,
  `uuid` char(36) NOT NULL,
  `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `ipnum` int(10) unsigned default NULL,
  `useragent` varchar(255) NOT NULL,
  `referer` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `uuid` (`uuid`),
  KEY `referer` (`referer`(16))
) DEFAULT CHARSET=latin1;

CREATE TABLE `brevno_access` (
  `id` int(11) NOT NULL auto_increment,
  `sessionid` int(11) NOT NULL,
  `pageid` int(11) NOT NULL,
  `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `ipnum` int(10) unsigned default NULL,
  `userid` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `userid` (`userid`),
  KEY `pageid` (`pageid`,`date`),
  KEY `sessionid` (`sessionid`,`date`)
) DEFAULT CHARSET=latin1;

Таблица brevno_pages хранит перечень страниц сайта, к которым происходят обращения. При этом учитывается метод HTTP (GET, POST, HEAD или другие) и GET-параметры, перечисленные в массиве $GET_PARAMS. brevno_sessions хранит статистические сесиии, описанные выше, а brevno_access - обращения к страницам. Для сокращения объема данных brevno_access не хранит useragent и referer, поэтому факт обновления браузера пользователем останется незамеченным, так же как и повторный переход с внешнего сайта (эти параметры записываются один раз для каждой сессии). Поле userid в таблице brevno_access хранит id пользователя, если он залогинен в CMS или на форуме сайта (о том, как связать CMS и Бревно напишем далее).

Сохраните файл brevno.php в каталоге, где хранятся php-файлы сайта.

Изменяя значения переменных, заданных в начале определения класса brevno, задайте перечень GET-параметров, которые требуется отслеживать, домен для cookie ('.example.ru' будет работать для example.ru, www.example.ru и других поддоменов) и префикс для таблиц (brevno_ по умолчанию).

Внутри php-файла, который вызывается каждой страницей сайта, поместите код создания объекта класса brevno. Вызов необходимо поместить после установления соединения с базой данных, так как Бревно не открывает собственное соединение. Вызов, тем не менее должен быть выполнен до отправки какого либо html-текста пользователь, чтобы Бревно имело возможность задать значение cookie.

 global $mybrevno;
  if(!isset($mybrevno)) {
    require_once "brevno.php";
    $mybrevno = new brevno();
  }

Код brevno упрощен, поэтому сейчас при создании объекта происходит и запись в базу данных статистической информации. Если Вы хотите отслеживать id пользователя CMS с помощью системы brevno, то необходимо перед созданием объекта, поместить id пользователя в переменную сессии MySQL @userid. Например, для интеграции с punbb следующий код следует поместить в header.php после всех include:

if(isset($pun_user['id'])) {
  mysql_query("SET @userid = " . intval($pun_user['id']));
}
// затем код создания объекта $mybrevno

Объект $mybrevno может быть полезен в процессе выполнения программы, так как в $mybrevno->referer содержится адрес страницы, с которой в первый раз пришел текущий пользователь. Например, если включите информацию из $mybrevno->referer в текст формы заказа, отправляемой по email, то сотрудник, обрабатывающий заказы, сможет отслеживать эффективность каналов рекламы (если $mybrevno->referer не вошла в форму, то вы сможете получить ее впоследствии с помощью запроса к базе данных).

Примеры использование Бревна

Бревно может ответить на широкий класс вопросов, сформулированных на языке SQL. Приведем примеры SQL-запросов, отвечающих на некоторые из вопросов, сформулированных в начале статьи.

Отметим, что не все запросы будут обрабатываться одинаково быстро. Скорость выполнения каждого запроса будет зависеть от сложности запроса и объема данных. Структура базы данных включает ключевые поля, ускоряющие наиболее распространенные запросы. Если обработка запросов происходит часто и на том же сервере, на котором происходит сбор данных, то целесообразно использовать таблицы типа Innodb, в остальных случаях подойдут таблицы MyISAM, создаваемые в MySQL по умолчанию.

С каких внешних страниц пришли посетители заданной страницы?

SELECT s.referer, count(*) cnt
    FROM brevno_sessions s
    JOIN brevno_access a ON a.sessionid = s.id
    JOIN brevno_pages p ON p.id = a.pageid
    WHERE p.uri like '%414.webew%'
    GROUP BY s.referer
    ORDER BY cnt DESC LIMIT 6;
+------------------------------------------------+-----+
| referer                                        | cnt |
+------------------------------------------------+-----+
|                                                | 484 |
| http://sqlinfo.ru/forum/viewtopic.php?id=679   |  36 |
| http://abrdev.com/?p=332                       |  12 |
| http://habrahabr.ru/blog/javascript/43946.html |  11 |
| http://www.google.com/reader/view/             |  10 |
| http://habrahabr.ru/blog/webdev/42276.html     |  10 |
+------------------------------------------------+-----+
6 rows in set (0.65 sec)

Какие страницы просматривали посетители, пришедшие с заданного сайта?

SELECT p.uri,count(*) c
    FROM brevno_sessions s
    JOIN brevno_access a ON s.id=a.sessionid
    JOIN brevno_pages p ON a.pageid=p.id
    WHERE s.referer LIKE 'http://davydov%'
    GROUP BY p.uri
    ORDER BY c DESC LIMIT 10;
+---------------------+----+
| uri                 | c  |
+---------------------+----+
| /articles/189.webew | 41 |
| /articles/187.webew | 34 |
| /articles/275.webew | 33 |
| /articles/179.webew | 16 |
| /                   | 15 |
| /js/                |  7 |
| /articles/216.webew |  6 |
| /cms/               |  6 |
| /php/               |  5 |
| /html/              |  4 |
+---------------------+----+
10 rows in set (0.01 sec)

Какие страницы Яндекс проиндексировал последними, сколько раз была загружена каждая страница?

SELECT p.uri,count(*) cnt, max(a.date)
    FROM brevno_sessions s
    JOIN brevno_access a ON s.id=a.sessionid
    JOIN brevno_pages p ON a.pageid=p.id
    WHERE s.useragent LIKE 'Yandex%'
    GROUP BY p.uri
    ORDER BY max(a.date) DESC LIMIT 10;
+---------------------+------+---------------------+
| uri                 | cnt  | max(a.date)         |
+---------------------+------+---------------------+
| /                   |  217 | 2008-07-03 03:09:28 |
| /robots.txt         | 1128 | 2008-07-03 03:09:28 |
| /webew.rss          | 1626 | 2008-07-03 03:05:45 |
| /articles/650.webew |    8 | 2008-07-02 17:29:54 |
| /articles/189.webew |   46 | 2008-07-02 17:29:53 |
| /articles/187.webew |   36 | 2008-07-02 17:29:53 |
| /posts/514.webew    |   19 | 2008-07-02 17:29:22 |
| /users/652.webew    |    3 | 2008-07-02 17:29:21 |
| /users/674.webew    |    3 | 2008-07-02 17:29:21 |
| /articles/182.webew |   16 | 2008-07-02 17:29:20 |
+---------------------+------+---------------------+
10 rows in set (0.16 sec)

Какие страницы просматривали пользователи, просмотревшие данную страницу?

SELECT p2.uri, count(DISTINCT a1.sessionid) cnt
    FROM brevno_pages p1
    JOIN brevno_access a1 ON p1.id = a1.pageid
    JOIN brevno_access a2 USING(sessionid)
    JOIN brevno_pages p2 ON a2.pageid = p2.id
    WHERE p1.uri LIKE '/articles/411.webew%'
      AND p2.uri NOT LIKE '/articles/411.webew%'
    GROUP BY p2.uri
    HAVING cnt>10
    ORDER BY cnt DESC LIMIT 10;
+---------------------+-----+
| uri                 | cnt |
+---------------------+-----+
| /                   | 374 |
| /66/                | 285 |
| /html/              | 166 |
| /js/                | 160 |
| /articles/406.webew | 144 |
| /php/               | 135 |
| /articles/455.webew | 100 |
| /articles/378.webew |  98 |
| /articles/387.webew |  94 |
| /cms/               |  92 |
+---------------------+-----+
10 rows in set (1.22 sec)

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

Заключение

Мы рассмотрели принципы построения системы сбора статистики «Бревно» и примеры SQL-запросов для получения ответов на актуальные вопросы. Изложенных принципов достаточно для качественного анализа посещаемости многих сайтов. Код Бревна занимает 100 строчек, поэтому система легко модифицируется под требования конкретного проекта. Код Бревна передан в общественное достояние (public domain), модифицируйте и используйте на здоровье в любых целях.


© Все права на данную статью принадлежат порталу webew.ru. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в печатных изданиях допускается только с разрешения редакции.
Добавить комментарий
Отображение комментариев: Древовидное | Плоское

bur

ЗдОрово!
До кучи кину еще одну систему для сбора статистики: awstats.
Только она перловая, что не совсем по теме метки.
09.07.2008, 18:12
Ответить
NO USERPIC

rgbeast

awstats - это лог-анализатор, то есть система другого типа, чем Бревно. Работа основана на обработке логов http-сервера, у этого подхода есть и плюсы и минусы. Плюс в том, что логи ведутся в любом случае и не требуют дополнительных ресурсов. Минусы: по умолчанию в лог не записывается Referer, невозможно отслеживать userid в CMS и статистические сессии (повторные заходы одного и того же пользователя), статистика не доступна онлайн для движка сайта.

Надо отметить, что лог-анализаторов достаточно много, в том числе коммерческие системы для профессионального использования.
09.07.2008, 21:28
Ответить

1234ru

В предолженной реализации не очень удовлетворительно может быть решена задача определения путей движения пользователя по сайту:

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

2. Нельзя определить границы фактической сесси просмотра сайта (т.е. одного сенса сёрфинга: пользователь открыл в браузере сайт, полазил по сайту, закрыл сайт).

Решением этой проблемы, насколько я понимаю, могло бы быть хранение referef для каждой записи brevno_access, т.е. на каждую просмотренную страницу. Видимо, это была бы действительно исчерпывающая информация об активности на сайте (сложно придумать что-то еще дополнительное). Следует отметить, что при такой реализации объем сохраняемой информации увеличился бы где-то в 2-4 раза (хотя может быть какое угодно соотношение). В таком случае, возможно, следовало бы отдельно хранить host и относительные пути страниц. Также можно было бы сделать флаг, показывающий, является ли страница-referer внешней. При этом таблица brevno_sessions становится лишней:

CREATE TABLE `brevno_access` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `pageid` INT(11) NOT NULL,
  `DATE` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ipnum` INT(10) UNSIGNED DEFAULT NULL,
  `userid` INT(11) DEFAULT NULL,
  `useragent` VARCHAR(255) NOT NULL,
  `referer_host` VARCHAR(255) NOT NULL, /* в случае c переходом со своей же страницы ничего сюда не записываем - для этого есть флаг referer_isexternal*/
  `referer_isexternal` TINYINT NOT NULL DEFAULT 0,
  `referer_ruri` VARCHAR(255), /* NULL оставить, иначе невозможно будет отличить переход с главной страницы своего сайта от, например, прямого перехода */
  PRIMARY KEY  (`id`),
  KEY `userid` (`userid`),
  KEY `pageid` (`pageid`,`DATE`),
  KEY `sessionid` (`sessionid`,`DATE`)
) DEFAULT CHARSET=latin1;



Хочу отметить, однако, что предложенная модификация значительно повышает требования к месту для хранения информации, не повышая при этом серьёзно ее ценность: не настолько важнее бывает каждый раз знать источник перехода на сайт и точный путь посетителей по сайту, чем знать исходный (он же - наиболее значимый) источник посетителей и их путь с некоторыми ошибками, чтобы выделять под статистику значительно больше мощностей. По соотношению эффективности и требовательности к ресурсам предложенная в статье схема является более предпочтительной.
То, что не убивает нас, делает нас инвалидами.
29.10.2008, 15:02
Ответить
NO USERPIC

rgbeast

У сессий есть преимущество в том, что сессия хранится для одного пользователя при его заходе в разные дни. Таким образом можно отследить заказ, сделанный не в тот же день, в который пользователь пришел с рекламы. На этом сделан акцент. Referer можно хранить в таблице access, но таблицу sessions это не отменяет.

Пути по сайту, ИМХО, довольно сложная для анализа информация. Наиболее интересно на какую ссылку пользователь кликнул на каждой странице. Для этого достаточно одной таблицы с полями referer, url, count, которая позволит быстро извлекать распределение по выбранным ссылкам на каждой странице. Чтобы отслеживать еще и внешние ссылки, можно на них навесить Javascript.
29.10.2008, 15:54
Ответить

1234ru

Цитата:
Чтобы отслеживать еще и внешние ссылки, можно на них навесить Javascript

Я так понимаю, JavaScript нужен, если речь идет о сборе статистики со стороннего сайта через счетчик. Ведь если скрипт отрабатывает на том же сайте, для которого собирается статистика, то можно обратиться к referer напрямую.
То, что не убивает нас, делает нас инвалидами.
29.10.2008, 17:22
Ответить
NO USERPIC

rgbeast

Я не совсем корректно выразился. Имел в виду исходящие ссылки. При анализе путей это важнейщая компонента.
29.10.2008, 17:32
Ответить

1234ru

А, ясно. Ну, т.е., это на каждую исходящую ссылку придется навесить событие по onclick?
Ваще это довольно пробематично имхо :о...
Как это делать цивильно? придется, видимо, уже видимо перед самой выдачей страницы пользователю проходиться по всему конечному HTML страницы и всовывать внутрь тегов <a> соотв. функцию, которая при нажатии на ссылку пишет в базу.
То, что не убивает нас, делает нас инвалидами.
29.10.2008, 20:17
Ответить
NO USERPIC

rgbeast

Можно добавить обработчик автоматически на JS, не меняя HTML-код. См. статью: Обработчики событий - добавляем/удаляем
29.10.2008, 20:24
Ответить

stopkran

О-очень полезная вещь. Давно мечтал у кого-нибудь украсть счётчик. Первое, что сделал, - добавил в shepka_access поле referer. Для статистики посещаемости нужно ещё отличать роботов от людей (что, кстати, делает awstats). А как, например, считать доступы к robots.txt - что, добавлять в .htaccess инструкцию AddHandler application/x-httpd-php .txt? Или RewriteRule ^robots\.txt$ robots.php [L]?
19.06.2009, 13:35
Ответить
NO USERPIC

rgbeast

Роботов от людей можно отличить по User-Agent. Из названных Вами способов обработки robots.txt мне больше всего нравится последний. Точнее мы обычно обрабатываем все URL в одном скрипте, см. статью http://webew.ru/articles/2291.webew
19.06.2009, 15:00
Ответить

stopkran

Не, Дядя Фёдор, неправильный это бутерброд. Посмотрел я sql-таблицы для своего небольшого сайта за 4 дня: _access - 7000, _session - 6000! Это говорит о том, что роботы не хранят куки. И надо, видимо, роботов (поимённо) не пускать в таблицы. Или не пускать в таблицы всех, кто не принимает куки. Только как это узнать - что агент (если это не известный робот из списка) не принимает куки?
24.06.2009, 09:27
Ответить
NO USERPIC

rgbeast

Если Вы не хотите перечислять роботов поименно, то следуюет записывать все обращения к robots.txt в отдельную таблицу (date, ipnum, user-agent). Затем при обращении к странице проверять был ли запрос к robots.txt и если был, то обрабатывать особым образом.
24.06.2009, 10:59
Ответить

stopkran

Это если нужно анализировать именно трафик роботов. А я как раз хотел бы от него избавиться. И поэтому создал список из основных, самых прожорливых (Яндекс, Гугль, Яху...), и если строка user-agent содержит робота из списка, функции log_event() и create_session() в моей версии Вашей программы ничего не делают. А для надёжности добавил ещё в список роботов слова robot, crawl.
24.06.2009, 12:49
Ответить

stopkran

ну и как поживает бревно после кризиса перепроизводства (переполнения таблицы access)? :-) Расскажете, как такую ошибку исправить?
10.07.2009, 05:26
Ответить
NO USERPIC

rgbeast

В MySQL таблица переполняется, если кончилось место на диске (как было на webew) или по достижению 2 млрд строк. Второе пока далеко. Можно сделать ротацию логов - раз в месяц переименовывать таблицу access в access_200907 и то же делать с таблицей sessions, но это приведет к разрыву статистических сессий.
10.07.2009, 11:38
Ответить
Добавить комментарий
Отображение комментариев: Древовидное | Плоское
© 2007—2016 webew.ru, связаться: x собака webew.ru
Сайт использует Flede и соответствует стандартам WAI-WCAG 1.0 на уровне A.
Rambler's Top100