Бревно - система интернет-статистики своими руками
В статье описана система «Бревно» — система сбора статистики посещения сайта, построенная на простых принципах с использованием PHP и MySQL. Сбор и сохранение полной статистики позволяет ответить на многие вопросы, связанные с эффективностью каналов продвижения и юзабилити сайта. Простота кода (100 строк на PHP) позволяет настраивать систему под конкретные задачи и встраивать в существующий код.
Для чего нужно Бревно?
Для качественной оценки эффективности работы сайта требуются ответы на многие вопросы, например:
- Сколько посетителей приходят в день на сайт, сколько страниц они просматривают?
- Сколько человек просмотрело заданную страницу за заданный период?
- С каких сайтов или поисковых систем приходят посетители?
На перечисленные вопросы можно получить развернутый ответ с помощью популярных систем статистики (например, liveinternet и Google Analytics), достаточно удобных и доступных, в основном, бесплатно (будем называть из в дальнешем "счетчики"). Счетчики предлагают интерфейс для просмотра ответов на перечисленные выше и многие подобные вопросы. Внимательный просмотр такой статистики оставляет ощущение недосказанного анекдота и желание задать следующий ряд вопросов с общей темой "А куда они потом пошли?", например:
- С каких сайтов посетители, просмотревшие заданную страницу или выполнившие заданное действие, впервые пришли на сайт? Сколько страниц они просмотрели на сайте прежде чем попасть на заданную страницу?
- Что еще просматривали посетители, просмотревшие данную страницу?
- Какие страницы просмотрели посетители, пришедшие с заданного сайта?
- Сколько раз робот Яндекса загружал каждую страницу и когда был последний раз на каждой из страниц?
Счетчики дают ответ лишь на часть вопросов второй ступени. Для получения ответа на некоторые их них требуется предварительно описать целевые страницы и включить сбор статистики по целевым страницам (например, так работает Яндекс.Метрика). Ограничения связаны с тем, что счетчики не в состоянии хранить полную статистику посещаемости по всем обслуживаемым сайтам.
При активном анализе эффективности, новые вопросы могут появляться один за одним уже после того, как события на сайте произошли. Бревно хранит полную статистику посещений сайта в базе 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 таблицы:
`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.
if(!isset($mybrevno)) {
require_once "brevno.php";
$mybrevno = new brevno();
}
Код brevno упрощен, поэтому сейчас при создании объекта происходит и запись в базу данных статистической информации. Если Вы хотите отслеживать id пользователя CMS с помощью системы brevno, то необходимо перед созданием объекта, поместить id пользователя в переменную сессии MySQL @userid. Например, для интеграции с punbb следующий код следует поместить в header.php после всех include:
mysql_query("SET @userid = " . intval($pun_user['id']));
}
// затем код создания объекта $mybrevno
Объект $mybrevno может быть полезен в процессе выполнения программы, так как в $mybrevno->referer содержится адрес страницы, с которой в первый раз пришел текущий пользователь. Например, если включите информацию из $mybrevno->referer в текст формы заказа, отправляемой по email, то сотрудник, обрабатывающий заказы, сможет отслеживать эффективность каналов рекламы (если $mybrevno->referer не вошла в форму, то вы сможете получить ее впоследствии с помощью запроса к базе данных).
Примеры использование Бревна
Бревно может ответить на широкий класс вопросов, сформулированных на языке SQL. Приведем примеры SQL-запросов, отвечающих на некоторые из вопросов, сформулированных в начале статьи.
Отметим, что не все запросы будут обрабатываться одинаково быстро. Скорость выполнения каждого запроса будет зависеть от сложности запроса и объема данных. Структура базы данных включает ключевые поля, ускоряющие наиболее распространенные запросы. Если обработка запросов происходит часто и на том же сервере, на котором происходит сбор данных, то целесообразно использовать таблицы типа Innodb, в остальных случаях подойдут таблицы MyISAM, создаваемые в MySQL по умолчанию.
С каких внешних страниц пришли посетители заданной страницы?
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)
Какие страницы просматривали посетители, пришедшие с заданного сайта?
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)
Какие страницы Яндекс проиндексировал последними, сколько раз была загружена каждая страница?
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)
Какие страницы просматривали пользователи, просмотревшие данную страницу?
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. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в печатных изданиях допускается только с разрешения редакции.
