Создаем статистику для сайта своими руками на ASP.NET
Введение
Практически у каждого владельца сайта в какой-то момент возникает желание узнать какие-то статистические данные о своих посетителях. Сколько посетителей заглядывает на сайт, как долго они находятся на сайте, какие страницы смотрят, откуда приходят и т.д. И, в принципе, для получения подобной информации хватает разнообразных бесплатных (и не очень) сервисов типа top.mail.ru или www.spylog.com, а так же парсеров логов веб серверов. Но все эти службы и сервисы имеют немало недостатков – например потеря информации из-за недоступности онлайн службы или блокировки пользователем изображений в своем браузере или же избыточность лог файлов веб сервера и невозможность более-менее однозначно с помощью парсинга лог файлов определить сессию пользователя. А кроме того они совершенно неспособны дать ответы на какие-то специфические вопросы, например, на вопрос «показать список пользователей, пришедших с поисковых систем и зарегистрировавшихся в сессию прихода». Или же «показать все сессии конкретного пользователя за весь период времени». Что приводит постепенно к мысли о создании собственной системы логирования и набора отчетов для получения нужной статистики.
В какой-то момент встала подобная задача и передо мной. После серьезного редизайна сайта заказчик потребовал статистику, связанную с учетом посетителей с поисковых систем в разрезе их регистраций на сайте и совершения, допустим, покупок. А, кроме того, заказчика очень интересовали моменты как хорошо и быстро поисковые системы пройдутся по сайту и положат его страницы в свой индекс. Соответственно пришлось мне заняться созданием системы логирования посещений сайта.
База данных
На самом деле сам по себе процесс логирования захода посетителя на сайт ничего сложного из себя не представляет. Есть страницы (таблица Page), идентифицируемые их виртуальным адресом и сайтом, есть просмотры пользователями этих страниц (таблица Request) и есть сессии пользователей (совокупность просмотренных пользователем страниц сайта). Соответственно для сохранения всех этих данных у меня получилась вот такая база:
Небольшое пояснение – как я раньше уже упоминал в списке отчетов по посетителям у меня немаловажную роль играли зарегистрированные пользователи. Что и побудило меня добавить столбец UserID, куда будет записываться имя пользователя если логируется аутентифицированный запрос. И хотя я, например, предпочитаю использовать для идентификации пользователя целое число (identity ключ соотв. таблицы базы) в таблице Session это поле имеет тип varchar для максимальной совместимости. Кроме того в сессии сохраняется информация о первой и последней страницах сессии, а так же о дате начала и окончания сессии для удобства построения отчетов по точкам входа/выхода и времени. Ну а с каждым запросом страницы я сохраняю информацию о том, аутентифицирован ли пользователь и является ли этот запрос постбеком.
Для записи запосов в БД используются две не самые сложные процедуры:
create procedure p_SessionStart @UserID int = null, @IPAddress varchar(15), @BrowserString varchar(1024), @ReferralURL varchar(4096) = null, @Site varchar(100) = null, @PageUrl varchar(255) as begin tran declare @PageID int select @PageID = PageID from Page where PageURL = @PageUrl and (@Site is null and Site = @Site or Site is null) if @PageID is null begin insert into Page (PageURL, Site) values (@PageUrl, @Site) select @PageID = @@IDENTITY end insert into Session (UserID, DateStart, FirstPageID, LastPageID, DateEnd, IPAddress, BrowserString, ReferralURL) values (@UserID, getDate(), @PageID, @PageID, getDate(), @IPAddress, @BrowserString, @ReferralURL) commit return @@identity go create procedure p_DoRequest @SessionID int, @UserID varchar(100) = null, @Site varchar(100) = null, @PageUrl varchar(255), @QueryString varchar(1024), @IsPostBack bit, @IsAuthenticated bit as begin tran declare @PageID int select @PageID = PageID from Page where PageURL = @PageUrl and (@Site is null and Site = @Site or Site is null) if @PageID is null begin insert into Page (PageURL, Site) values (@PageUrl, @Site) select @PageID = @@IDENTITY end insert into Request (PageID, SessionID, RequestDate, QueryString, IsPostBack, IsAuthenticated) values (@PageID, @SessionID, getDate(), @QueryString, @IsPostBack, @IsAuthenticated) update Session set LastPageID = @PageID, DateEnd = getdate() where SessionID = @SessionID if @UserID is not null update Session set UserID = @UserID where SessionID = @SessionID commit go
Процедура p_SessionStart, как мне кажется, не требует никаких дополнительных пояснений, а для процедуры p_DoRequest сделаю короткое замечание, что кроме логирования самого запроса к странице она так же изменяет некоторые значения сессии.
На этом пока что работа по созданию базы данных завершена и можно переходить к написанию программного кода для логирования заходов пользователей на сайт.
Модуль логирования
Программную часть системы логирования посещений сайтов я решил сделать в виде HTTP модуля. Предпосылки очевидны – система логирования должна работать с сайтами без изменения их кода, просто настраиваться и обрабатывать все asp.net запросы. Опять таки код этого модуля прост до безобразия и в сумме занимает едва за сотню строк кода.
Так как вся настройка работы модуля будет вестись с помощью конфигурационного файла, то самым оптимальным решением для этого будет создание своей секции в конфигурационного файла для модуля. Модулю для своей работы нужна строка подключения к БД, имя сайта (необязательно) и место хранения идентификатора сессии пользователя (asp.net сессия или куки, по умолчанию куки). Соответственно код структуры параметров модуля:
public enum PersistIDPlace { Session, Cookie } public class SiteStatsSettings { public string ConnectionString; public string Site; public PersistIDPlace SessionIDPlace; }
Сама же секция конфигурационного файла суть класс, реализующий интерфейс System.Configuration.IConfigurationSectionHandler. Этот интерфейс содержит единственный метод Create, который должен вернуть объект. Я не буду долго рассказывать что и как здесь необходимо сделать (это можно прочитать и в MSDN) и просто приведу код класса:
public class SiteStatsConfigHandler : IConfigurationSectionHandler { public SiteStatsConfigHandler(){} public object Create(object parent, object configContext, System.Xml.XmlNode section) { SiteStatsSettings ret = new SiteStatsSettings(); ret.ConnectionString = section.SelectSingleNode(ConnectionString).InnerText; ret.Site = section.SelectSingleNode(Site) != null ? section.SelectSingleNode(Site).InnerText : ; if(section.SelectSingleNode(SessionIDPlace) != null) ret.SessionIDPlace = (PersistIDPlace) Enum.Parse(typeof(PersistIDPlace), section.SelectSingleNode(SessionIDPlace).InnerText); else ret.SessionIDPlace = PersistIDPlace.Cookie; return ret; } }
Как видите ничего сложного в вышеприведенном коде нет – он всего лишь читает параметры из XmlNode и заполняет класс параметров модуля. Для того, чтобы добавить созданную выше секцию в конфигурационном файле в секции <configuration> теперь достаточно добавить вот такие строки:
<configSections> <section name=siteStats type=SiteStats.SiteStatsConfigHandler, SiteStats/> </configSections> И теперь можно использовать секцию <siteStats> для задания параметров модуля: <siteStats> <ConnectionString>server=localhost;uid=sa;pwd=;database=SiteStats</ConnectionString> <Site>Mania</Site> <SessionIDPlace>Cookie</ SessionIDPlace> </siteStats>
А получить эти параметры в коде класса можно с помощью следующей строки кода:
Теперь осталось всего ничего – реализовать класс для записи в БД и собственно сам HTTP модуль. Код класса для работы с БД настолько банален, что их можно привести даже без комментариев:
public class SiteStatsBLL { private static int SessionStart(HttpContext context) { SiteStatsSettings settings = (SiteStatsSettings) ConfigurationSettings.GetConfig(siteStats); SqlConnection myConn = new SqlConnection(settings.ConnectionString); SqlCommand myCmd = new SqlCommand(p_SessionStart, myConn); myCmd.CommandType = CommandType.StoredProcedure; if (context.User.Identity.IsAuthenticated) myCmd.Parameters.Add(@UserID, Int32.Parse(context.User.Identity.Name)); myCmd.Parameters.Add(@IPAddress, context.Request.UserHostAddress); myCmd.Parameters.Add(@BrowserString, context.Request.UserAgent == null ? : context.Request.UserAgent); if (context.Request.UrlReferrer != null) myCmd.Parameters.Add(@ReferralURL, context.Server.UrlDecode(context.Request.UrlReferrer.ToString())); if (settings.Site != ) myCmd.Parameters.Add(@Site, settings.Site); myCmd.Parameters.Add(@PageURL, context.Request.FilePath); myCmd.Parameters.Add(RETURN_VALUE, SqlDbType.Int); myCmd.Parameters[RETURN_VALUE].Direction = ParameterDirection.ReturnValue; myConn.Open(); myCmd.ExecuteNonQuery(); myConn.Close(); return (int) myCmd.Parameters[RETURN_VALUE].Value; } public static void Request(HttpContext context) { SiteStatsSettings settings = (SiteStatsSettings) ConfigurationSettings.GetConfig(siteStats); int SessionID; switch(settings.SessionIDPlace) { case PersistIDPlace.Session: if (context.Session[SessionID] != null) SessionID = (int) context.Session[SessionID]; else { SessionID = SessionStart(context); context.Session[SessionID] = SessionID; } break; default: if (context.Request.Cookies[SessionID] != null) SessionID = Int32.Parse(context.Request.Cookies[SessionID].Value); else { SessionID = SessionStart(context); context.Response.Cookies.Add(new HttpCookie(SessionID, SessionID.ToString())); } break; } SqlConnection myConn = new SqlConnection(settings.ConnectionString); SqlCommand myCmd = new SqlCommand(p_DoRequest, myConn); myCmd.CommandType = CommandType.StoredProcedure; myCmd.Parameters.Add(@SessionID, SessionID); if (context.User.Identity.IsAuthenticated) myCmd.Parameters.Add(@UserID, context.User.Identity.Name); if (settings.Site != ) myCmd.Parameters.Add(@Site, settings.Site); myCmd.Parameters.Add(@PageURL, context.Request.RawUrl.IndexOf(?) != -1 ? context.Request.RawUrl.Substring(0, context.Request.RawUrl.IndexOf(?)) : context.Request.RawUrl); myCmd.Parameters.Add(@QueryString, context.Request.QueryString.ToString()); myCmd.Parameters.Add(@IsAuthenticated, context.Request.IsAuthenticated); myCmd.Parameters.Add(@IsPostBack, context.Request.HttpMethod == POST); myConn.Open(); myCmd.ExecuteNonQuery(); myConn.Close(); } }
Для логирования запроса пользователя используется метод SiteStatsBLL.Request(), который в свою очередь при необходимости вызывает метод SiteStatsBLL.SessionStart() для старта новой сессии. Оба метода всего лишь вызывают соответствующие хранимые процедуры.
Метод SiteStatsBLL.Request() я вызываю в обработчике события HttpApplication. PostRequestHandlerExecute. В момент срабатывания этого события обработка стнаницы завершена и страница готова к отправке клиенту, но сессия еще сущетвует (у нас же есть опция сохранения SessionID в сессии). И вся задача HTTP модуля состоит в том, чтобы при наступлении этого события вызвать метод логирования запроса:
public class SiteStatsModule : IHttpModule { void IHttpModule.Init(HttpApplication context) { context.PostRequestHandlerExecute += new EventHandler(context_PostRequestHandlerExecute); } void IHttpModule.Dispose() { } void context_PostRequestHandlerExecute(object sender, EventArgs e) { SiteStatsBLL.Request(HttpContext.Current); } }
Все... процесс создания модуля логирования запросов к сайту завершен. Теперь для того, чтобы начать вести логи для какого-то сайта достаточно переписать получившуюся сборку в подкаталог bin этого сайта и внести в файл web.config следуюшие изменения.
1. Добавить описание секции в раздел <configuration>:
2. В тот же раздел добавить саму секцию siteStats:
<siteStats> <ConnectionString>строка подключения к БД</ConnectionString> <Site>имя сайта</Site> <SessionIDPlace>Место хранения идентификатора сессии (Cookie или Session)</SessionIDPlace> </siteStats>
3. В секцию <system.web> добавить описание модуля:
Работа сделана и теперь все asp.net запросы к сайту будут логироваться. Но прежде, чем я закончу эту статью, я хотел бы еще рассказать о некоторых дополнительных манипуляциях над сохраняемыми данными.
Поисковые роботы и реферралы
Написанный выше модуль в отличие от бесплатных систем интернет статистики логирует любые заходы на страницу – как заходы обычных пользователей, так и заходы всяких поисковых роботов или систем автоматического сохранения сайтов. И естесственно если не делать дополнительной фильтрации для выявления ненужных заходов, то можно получить цифры, которые будут весьма и весьма далеки от настоящих. Например для сайта aspnetmania.com 1 декабря 2005 года количество залогированных модулем посетителей (сессий) было порядка 26 тысяч, при этом статистика на top.mail.ru показывает 1545 посетителей. Остальные запросы – активная работа поисковых роботов :). И хочешь – не хочешь, но их нужно будет каким-то образом для себя отмечать (или вообще убирать в случае, если статистика по заходам роботов не интересна). Для этого во первых нужно сделать поддержку списка исключаемых юзерагентов ну и, во вторых, добавить статусное поле в таблицу Session.
Дабы не утруждать читателя долгими рассуждениями о том, какие существуют роботы и какой робот кому служит я приведу сразу небольшой список масок учитываемых мной юзерагентов роботов:
Источник
Маска
Google
Googlebot% Mediapartners-Google%
Yahoo
Yahoo% % Yahoo! Slurp%
MSN
msnbot%
Yandex
Yandex%
Rambler
StackRambler%
Aport
Aport%
Teleport Pro
Teleport Pro%
Прочие
%bot%
Последняя строка в этом перечне конечно довольно таки радикальная, но этих роботов столько человеческая мысль наплодила – благо, что хоть они зачастую признаются честно, что они боты :).
Кроме обработки «непользовательских» заходов нужно также сделать обработку реферралов – с какого сайта/поисковой системы пришел посетитель и какие поисковые слова он использовал в случае прихода с поисковой системы. И тут опять не обойтись без дополнительных таблиц – списка поисковых систем и для списка реферральных сайтов. Ну а кроме того необходима таблица для хранения поисковых фраз. И, естесственно, все эти таблицы будут связаны с таблицей Session.
Таблицы для сайтов и поисковых фраз ничего из себя особенного не представляют – это обычные справочники, в которые по необходимости будут добавляться новые значения.
Более интересна таблица поисковых систем – в ней кроме названия поисковой системы понадобится также сохранять информацию о маске имени сайта, а так же информацию о том, где в строке реферрала хранится поисковая фраза. Обычно поисковая фраза содержится в каком-то конкретном параметре URL, но бывают и особо продвинутые индивидуумы, засовывающие ее в структуру каталогов URL. Но и на их фоне выделается AOL, в случае поиска с главной страницы передающий поисковую фразу в строке запроса в зашифрованном виде. Благо хоть в другом параметре, что позволяет упростить учет поисковых фраз.
Ниже представлен примерный справочник поисковых сайтов для создаваемой системы статистики.
Поисковик
Маска URL
Параметр поисковой фразы
Google
%google%
q=
Yahoo
%yahoo%
p=
MSN Search
%search.msn%
q=
Altavista
%altavista%
q=
AOL Search
%search.aol%
query=
Alexa
%alexa.com%
q=
AllTheWeb
%alltheweb.com%
q=
AskJeeves
%ask.com%
q=
HotBot
%hotbot.com%
query=
Jayde
%jayde.com%
query=
LookSmart
%looksmart.com%
qt=
Lycos
%lycos.com%
query=
Netscape
%netscape.com%
query=
Overture
%overture.com%
Keywords=
Teoma
%teoma.com%
q=
WiseNut
%wisenut.com%
q=
A9
%a9.com%
/
WebCrawler
%webcrawler.com%
/
Business
%business.com%
query=
DogPie
%dogpile.com%
/
EntireWeb
%entireweb.com%
q=
Excite
%excite.com%
/
Gigablast
%gigablast.com%
q=
Infospace
%infospace.com%
/
Mamma
%mamma.com%
query=
Metacrawler
%metacrawler.com%
/
SplatSearch
%splatsearch.com%
searchstring=
Yandex
%yandex.ru/yandsearch%
text=
Aport
%sm.aport.ru%
r=
Rambler
%search.rambler.ru%
words=
Ввиду того, что основная масса «неправильных» поисковиков редко используется даже в США (откуда они все родом) и приход на русскоязычный сайт с этого поисковика стремится к нулю, я поленился делать разбор подобных адресов дабы не усложнять логику работы программы.
Вооружившись всем вышеизложенным можно приступать к реализации учета заходов различных роботов и предварительной обработке реферральных заходов.
Расширение модуля
Кроме упомянутых мною выше 4-х новых таблиц (Sites, Bots, SearchEngines и Keywords) меняется только таблица Session, в которой добавляются 4 поля, ссылающиеся на добавленные таблицы:
Ну и соответствующим образом меняется процедура старта сессии:
ALTER procedure p_SessionStart @UserID int = null, @IPAddress varchar(15), @BrowserString varchar(1024), @ReferralURL varchar(4096) = null, @Site varchar(100) = null, @PageUrl varchar(255), @BotID int = null, @SiteName varchar(255) = null, @Keyword varchar(1000) = null, @SearchEngineID int = null as begin tran declare @PageID int select @PageID = PageID from Page where PageURL = @PageUrl and (@Site is null and Site = @Site or Site is null) if @PageID is null begin insert into Page (PageURL, Site) values (@PageUrl, @Site) select @PageID = @@IDENTITY end declare @SiteID int if @SiteName is not null begin select @SiteID = SiteID from Sites where Name = @SiteName if @SiteID is null begin insert into Sites (Name) values (@SiteName) select @SiteID = @@IDENTITY end end declare @KeywordID int if @Keyword is not null begin select @KeywordID = KeywordID from Keywords where Keywords = @Keyword if @KeywordID is null begin insert into Keywords (Keywords) values (@Keyword) select @KeywordID = @@IDENTITY end end insert into Session (UserID, DateStart, FirstPageID, LastPageID, DateEnd, IPAddress, BrowserString, ReferralURL, BotID, SiteID, SearchEngineID, KeywordID) values (@UserID, getDate(), @PageID, @PageID, getDate(), @IPAddress, @BrowserString, @ReferralURL, @BotID, @SiteID, @SearchEngineID, @KeywordID) commit return @@identity
На самом деле заполнять дополнительное данные о роботах, реферралах и поисковых системах можно 2-мя путями – в момент добавления записи в БД или отдельной хранимой процедурой, обрабатывающей уже сохраненные в базу данные. Соответственно во втором случае изменение хранимой процедуры для добавления записи о сессии не нужно. И, так как этот путь немного проще, я сначала покажу его реализацию. Обработка логов в БД
Как я уже упомянул, эта процедура должна обрабатывать уже введенные данные. У меня это были данные, введенные за какой-то последний отрезок времени (конкретней – за последний час). Соответственно одним из параметров этой процедуры будет дата/время, с которой нужно обрабатывать данные. И, кроме того, также в нее передается параметр каким образом обрабатывать записи роботов – связывать их или же удалять
ALTER PROCEDURE p_Session_Fill @DateStart datetime = null, @ClearBots bit = 0 AS if @DateStart is null set @DateStart = dateadd(hh, -1, getDate())
Обработка записей роботов проста до примитивизма. При сохранении логов заходов роботов делается 2 простых update по связке таблиц Session и Bots (второй запрос нужен для того, чтобы обработать "невыясненных" роботов, милостиво соизволивших сообщить о себе, что они таки роботы). При очистке же базы от записей заходов роботов – две не менее простых команды delete.
if @ClearBots = 0 begin update Session set BotID = Bots.BotID from Bots where Session.BotID is null and DateStart > @DateStart and Mask <> %bot% and BrowserString like Mask update Session set BotID = Bots.BotID from Bots where Session.BotID is null and Mask = %bot% and DateStart > @DateStart and BrowserString like %bot% end else begin delete from Request where SessionID in (select SessionID from Session inner join Bots on BrowserString like Mask) delete from Session where SessionID in (select SessionID from Session inner join Bots on BrowserString like Mask) end
Не бОльшую сложность представляет собой и запрос по определению заходов с поисковых систем
update Session set SearchEngineID = SearchEngines.SearchEngineID from SearchEngines where ReferralURL is not null and DateStart > @DateStart and ReferralURL like SearchMask
В реферралы попадают все не попавшие в предыдущий запрос записи с непустым полем ReferralURL. При этом нужно получить только адрес сайта, с которого пришел реферрал.
declare @ReferralUrl varchar(4096) declare @SessionID int declare @SiteName varchar(255) declare @SiteID int declare cur1 cursor for select SessionID, ReferralURL from Session with (nolock) where ReferralURL is not null and DateStart > @DateStart and SiteID is null open cur1 fetch next from cur1 into @SessionID, @ReferralUrl while @@fetch_status = 0 begin set @SiteID = null set @SiteName = substring(@ReferralUrl, 8, len(@ReferralUrl) - 7) set @SiteName = substring(@SiteName, 1, charindex(/, @SiteName) - 1) select @SiteID = SiteID from Sites with (nolock) where Name = @SiteName if @SiteID is null begin insert into Sites(Name) values (@SiteName) set @SiteID = @@identity end update Session set SiteID = @SiteID where SessionID = @SessionID fetch next from cur1 into @SessionID, @ReferralUrl end close cur1 deallocate cur1
Ну и последний момент – выделение поисковых запросов из уже найденных приходов с поисковых систем. Для этого как раз таки и нужно поле KeywordMask – все, что будет найдено в строке между подстрокой их этого поля и символом & (или до конца строки, если такого символа больше нет) считается поисковой фразой. При этом, как я уже упомянул ранее, запросы, в которых поисковые фразы не выделяются параметрами URL, не учитываются (для запросов с таких поисковых систем значение KeywordMask равно /).
declare @SearchEngineID int declare @KeywordLabel varchar(10) declare cur2 cursor for select &
