Глава 3.Проектные решения по аис сбора и анализа статистики посещаемости сайта
На данный момент не существует способа сбора абсолютно точной статистики о посетителях сайта. Для практического использования данных статистики в бизнесе необходим комплексный подход, использующий все возможные способы сбора данных, т.е. по настоящему ценным является совместный анализ данных полученных пассивным и активным подходом, что и будет использоваться в проектируемой системе. Такой анализ позволяет соотносить идентифицированные с помощью Cookie браузеры с результатами заполнения анкет и ответов на опросы. Это открывает путь к определению предпочтений клиентов и групп клиентов, позволяя строить персонализированные сайты.
Для накопления данных будет использоваться локальная база данных, так как это позволит избежать критической нагрузки на нее, поможет оперативно (он-лайн) контролировать посетителей сайта, анализировать деятельность роботов на сайте.
3.1.Описание логической и физической моделей базы данных
В качестве модели данных для проектируемой системы была выбрана реляционная модель. Исходя из выбранной модели данных, была спроектирована с помощью CASE - средства ERwin 4.1 и экспортированных данных из модели DFD схема логической модели данных, представленная ниже(см.рис.3.1)[5].
Рис. 3.1 Схема логической модели сущность-связь
На основании спроектированной логической модели данных, был проведен соответствующий анализ, в ходе которого были выявлены следующие сущности (отношения):
stat_adminsessions - хранит информацию о работе пользователей с системой;
stat_cities - справочник, содержит данные о городах России;
stat_config - хранит настройки системы;
stat_countries - справочник, содержит данные о странах;
stat_datas - справочник, содержит данные, необходимые для определения роботов, поисковых систем, каталогах, рейтингах, почтовых систем, браузера посетителя и операционной системы посетителя;
stat_datasuser - справочник, содержит пользовательские данные, необходимые для отслеживания целей и анализа рекламных кампаний;
stat_ip2city - справочник, содержит данные о диапазоне IP-адресов для определения города посетителя;
stat_ip2country - справочник, содержит данные о диапазоне IP-адресов для определения страны посетителя;
stat_languages - справочник, содержит данные о языках;
stat_log - хранит статистику о посещении сайта пользователем;
stat_logrobots - хранит статистику о посещении сайта роботами;
stat_regions - справочник, содержит данные о регионах России;
stat_sessions - хранит данные о сессиях посетителей;
stat_total - хранит рассчитанные данные о количестве просмотров, хитов, хостов и пользователей сайта;
stat_users - хранит данные о пользователях сайта.
Более подробное описание данных сущностей приведено в таблицах 3.1 - 3.15
Таблица 3.1 - Таблица stat_adminsessions
Поле |
Тип |
Ноль |
Комментарий |
Hash |
varchar (32) |
нет |
Хэш пользователя системы (PK) |
Login |
varchar (20) |
нет |
Логин пользователя системы (FK) |
time_first |
datetime |
нет |
Время захода в систему |
time_last |
datetime |
нет |
Время просмотра последнего отчета |
Ip |
varchar (64) |
нет |
IP-адрес пользователя системы |
C |
int (10) |
нет |
Количество просмотренных отчетов |
Таблица 3.2 - Таблица stat_cities
Поле |
Тип |
Ноль |
Комментарий |
Id |
smallint (5) |
нет |
Идентификатор города (PK) |
Region |
smallint (5) |
нет |
Идентификатор региона (FK) |
Name |
varchar (255) |
нет |
Название города |
Таблица 3.3 - Таблица stat_regions
Поле |
Тип |
Ноль |
Комментарий |
Id |
smallint (5) |
нет |
Идентификатор региона (PK) |
Name |
varchar (255) |
нет |
Название региона |
Таблица 3.4 - Таблица stat_config
Поле |
Тип |
Ноль |
Комментарий |
Variable |
varchar (50) |
нет |
Название переменной (PK) |
Value |
varchar (255) |
нет |
Значение переменной |
Таблица 3.5 - Таблица stat_countries
Поле |
Тип |
Ноль |
Комментарий |
Id |
smallint (5) |
нет |
Идентификатор страны (PK) |
Domain |
char (2) |
нет |
Сокращенное обозначение страны |
Name |
varchar (255) |
нет |
Название страны |
Таблица 3.6 - Таблица stat_datas
Поле |
Тип |
Ноль |
Комментарий |
Id |
int (10) |
нет |
Идентификатор данных (PK) |
type |
enum ('', 'dm', 'br', 'os', 'ct', 'ss', 'rb', 'tp', 'ml') |
нет |
Тип данных |
Name |
varchar (100) |
нет |
Наименование для отображения в отчете |
d1 |
varchar (255) |
нет |
Условие поиска 1 |
d2 |
varchar (255) |
нет |
Условие поиска 2 |
d3 |
varchar (255) |
нет |
Условие поиска 3 |
Таблица 3.7 - Таблица stat_datasuser
Поле |
Тип |
Ноль |
Комментарий |
Id |
int (10) |
нет |
Идентификатор пользовательских данных (PK) |
Type |
enum ('', 'dm', 'pr', 'kn', 'cl', 'rk') |
нет |
Тип пользовательских данных |
Name |
varchar (100) |
нет |
Наименование для отображения в отчете |
d1 |
varchar (255) |
нет |
Условие поиска |
Таблица 3.8 - Таблица stat_ip2city
Поле |
Тип |
Ноль |
Комментарий |
Id |
int (10) |
нет |
Идентификатор записи (PK) |
ip_start |
int (10) |
нет |
Начало диапазона ip-адресов |
ip_end |
int (10) |
нет |
Конец диапазона ip-адресов |
City |
smallint (5) |
нет |
Идентификатор города (FK) |
Таблица 3.9 - Таблица stat_ip2country
Поле |
Тип |
Ноль |
Комментарий |
Id |
int (10) |
нет |
Идентификатор записи (PK) |
ip_start |
int (10) |
нет |
Начало диапазона ip-адресов |
ip_end |
int (10) |
нет |
Конец диапазона ip-адресов |
Country |
smallint (5) |
нет |
Идентификатор страны (FK) |
Таблица 3.10 - Таблица stat_languages
Поле |
Тип |
Ноль |
Комментарий |
Code |
char (2) |
нет |
Идентификатор языка (PK) |
Eng |
varchar (50) |
нет |
Английское наименование языка |
Rus |
varchar (50) |
нет |
Русское наименование языка |
Таблица 3.11 - Таблица stat_log
Поле |
Тип |
Ноль |
Комментарий |
Id |
int (10) |
нет |
Идентификатор лога (PK) |
id_session |
varchar (32) |
нет |
Идентификатор сессии (FK) |
id_user |
int (10) |
нет |
Идентификатор посетителя (FK) |
Ip |
int (10) |
нет |
IP-адрес посетителя |
Proxy |
int (10) |
да |
Прокси сервер посетителя |
ref_domain |
varchar (50) |
да |
Ссылающийся домен |
ref_page |
varchar (255) |
да |
Ссылающаяся страница |
ref_pagequery |
varchar (255) |
да |
Ссылающаяся строка запроса |
ref_catalog |
int (10) |
да |
Ссылающийся каталог |
ref_search_sys |
int (10) |
да |
Ссылающаяся поисковая система |
ref_search_query |
varchar (255) |
да |
Ссылающаяся поисковая фраза |
ref_mail_sys |
int (10) |
да |
Ссылающаяся почтовая система |
ref_rating |
int (10) |
да |
Ссылающийся рейтинг |
Agent |
varchar (255) |
да |
Поле заголовка User-Agent |
Browser |
int (10) |
да |
Браузер посетителя (FK) |
Os |
int (10) |
да |
Операционная система посетителя (FK) |
Acceptlang |
varchar (100) |
да |
Языковая метка поля Accept-Languages браузера |
Date |
int (8) |
нет |
Дата посещения |
Time |
mediumint (6) |
нет |
Время посещения |
Domain |
varchar (30) |
нет |
Посещенный домен |
request_uri |
varchar (255) |
нет |
URI, который был задан для доступа к странице |
Page |
varchar (20) |
нет |
Посещенная страница |
Titlepage |
varchar (255) |
нет |
Заголовок посещенной страницы |
page_rateload |
float |
нет |
Время загрузки страницы |
Existspage |
enum ('Y', 'N') |
нет |
Наличие страницы |
City |
smallint (5) |
да |
Город посетителя (FK) |
Country |
smallint (5) |
да |
Страна посетителя (FK) |
Таблица 3.12 - Таблица stat_logrobots
Поле |
Тип |
Ноль |
Комментарий |
Id |
int (10) |
нет |
Идентификатор лога роботов (PK) |
id_robot |
int (10) |
нет |
Идентификатор робота (FK) |
Ip |
int (10) |
нет |
IP-адрес робота |
Agent |
varchar (255) |
нет |
Поле заголовка User-Agent |
Date |
int (8) |
нет |
Дата посещения |
Time |
mediumint (6) |
нет |
Время посещения |
Domain |
varchar (30) |
нет |
Посещенный домен |
request_uri |
varchar (255) |
нет |
URI, который был задан для доступа к странице |
Page |
varchar (20) |
нет |
Посещенная страница |
Titlepage |
varchar (255) |
нет |
Заголовок посещенной страницы |
Existspage |
enum ('Y', 'N') |
нет |
Наличие страницы |
Таблица 3.13 - Таблица stat_sessions
Поле |
Тип |
Ноль |
Комментарий |
Id |
varchar (32) |
нет |
Идентификатор сессии (PK) |
id_user |
int (10) |
нет |
Идентификатор посетителя (FK) |
Timestamp |
int (10) |
нет |
Время последнего обновления страницы в UNIX формате |
Таблица 3.14 - Таблица stat_total
Поле |
Тип |
Ноль |
Комментарий |
Date |
int (8) |
нет |
Дата расчета (PK) |
Views |
int (10) |
нет |
Количество просмотров |
Hits |
int (10) |
нет |
Количество хитов |
Hosts |
int (10) |
нет |
Количество хостов |
Users |
int (10) |
нет |
Количество пользователей |
Таблица 3.15 - Таблица stat_users
Поле |
Тип |
Ноль |
Комментарий |
Id |
int (10) |
нет |
Идентификатор посетителя (PK) |
id_user_reg |
int (10) |
да |
Идентификатор регистрации посетителя (FK) |
ip_first |
int (10) |
нет |
IP-адрес первого посещения |
date_first |
int (8) |
нет |
Дата первого посещения |
time_first |
mediumint (6) |
нет |
Время первого посещения |
domain_first |
varchar (30) |
нет |
Домен первого посещения |
request_uri_first |
varchar (255) |
нет |
URI первого посещения, который был задан для доступа к странице |
page_first |
varchar (20) |
нет |
Страница первого посещения |
ref_domain_first |
varchar (255) |
да |
Ссылающийся домен первого посещения |
ref_page_first |
varchar (255) |
да |
Ссылающаяся страница первого посещения |
ref_pagequery_first |
varchar (255) |
да |
Ссылающаяся строка запроса первого посещения |
ref_catalog_first |
tinyint (3) |
да |
Ссылающийся каталог первого посещения |
ref_search_sys_first |
tinyint (3) |
да |
Ссылающаяся поисковая система первого посещения |
ref_search_query_first |
varchar (255) |
да |
Ссылающаяся поисковая фраза первого посещения |
ref_mail_sys_first |
tinyint (3) |
да |
Ссылающаяся почтовая система первого посещения |
ref_rating_first |
tinyint (3) |
да |
Ссылающийся рейтинг первого посещения |
Screensize |
varchar (10) |
да |
Разрешение экрана посетителя |
colorsdepth |
varchar (5) |
да |
Глубина цвета посетителя |
Cookies |
enum ('Y', 'N') |
нет |
Проверка включения cookies |
Java |
enum ('Y', 'N') |
нет |
Проверка включения java |
Javascript |
varchar (5) |
да |
Проверка включения javascript |
ip_last |
int (10) |
нет |
IP-адрес последнего посещения |
date_last |
int (8) |
нет |
Дата последнего посещения |
time_last |
mediumint (6) |
нет |
Время последнего посещения |
request_uri_last |
varchar (255) |
нет |
URI последнего посещения, который был задан для доступа к странице |
page_last |
varchar (20) |
нет |
Страница последнего посещения |
При проектировании базы данных определены связи между таблицами, первичные (Primary) и внешние (Foreign) ключи. Анализ ключей и связей между сущностями приведён в таблице 3.16
Таблица 3.16 - Анализ ключей и связей между сущностями
Сущности |
Связь |
Добавленное поле |
Таблица |
Primary Key |
Foreign Key |
stat_log |
M |
id_user |
stat_log |
id |
id_user |
stat_users |
1 |
|
stat_users |
id |
|
stat_log |
M |
browser |
stat_log |
id |
browser |
stat_datas |
1 |
|
stat_datas |
id |
|
stat_log |
M |
os |
stat_log |
id |
|
stat_datas |
1 |
|
stat_datas |
id |
os |
stat_log |
M |
city |
stat_log |
id |
city |
stat_cities |
1 |
|
stat_cities |
id |
|
stat_log |
M |
country |
stat_log |
id |
country |
stat_countries |
1 |
|
stat_countries |
id |
|
stat_cities |
M |
region |
stat_ cities |
id |
region |
stat_regions |
1 |
|
stat_ regions |
id |
|
stat_logrobots |
M |
id_robot |
stat_logrobots |
id |
id_robot |
stat_datas |
1 |
|
stat_datas |
id |
|
stat_users |
M |
id_user_reg |
stat_users |
id |
id_user_reg |
Author |
1 |
|
author |
id |
|
stat_users |
M |
ref_catalog_first |
stat_users |
id |
ref_catalog_first |
stat_datas |
1 |
|
stat_datas |
id |
|
stat_users |
M |
ref_search_sys_first |
stat_users |
id |
ref_search_sys_first |
stat_datas |
1 |
|
stat_datas |
id |
|
stat_users |
M |
ref_mail_sys_first |
stat_users |
id |
ref_mail_sys_first |
stat_datas |
1 |
|
stat_datas |
id |
|
stat_users |
M |
ref_rating_first |
stat_users |
id |
ref_rating_first |
stat_datas |
1 |
|
stat_datas |
id |
|
stat_ip2city |
M |
city |
stat_ip2city |
id |
city |
stat_cities |
1 |
|
stat_cities |
id |
|
stat_ip2country |
M |
country |
stat_ip2country |
id |
country |
stat_countries |
1 |
|
stat_countries |
id |
|
stat_sessions |
1 |
id_user |
stat_sessions |
id |
id_user |
stat_users |
1 |
|
stat_users |
id |
|