Что такое база данных

База данных в первом приближении — это организованная информация.

Например телефонный справочник по сути база данных. 

Структура

Часто информация хранится во многих связанных друг с другом таблицах, такие базы называются реляционными.

Актуально, когда данных и различных свойств много.

Отношения и связи между таблицами

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

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

Отношения могут быть:

• один к одному;
• один ко многим;
• много к одному;
• много ко многим.

Один к одному — каждой записи в одной таблице соответствует только одна запись в другой таблице. 

Один ко многим — одна запись в таблице соответствует многим в другой.

Много к одному — всё наоборот.

Много ко многим — каждая запись одной таблицы связана со многими записями в другой и наоборот. 

Ключи

Ключи позволяют избежать дублей записей и устанавливают связи между таблицами. 

Бывают первичные и внешние.

Первичный ключ однозначно определяет запись в таблице — чтобы не было дублей одной и той же записи. 

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

Этапы эволюции отчётов

Первая ступень — руками:

Смотрим в Директ:

AdWords:

Analytics:

Руками собираем отчёт:

Вторая ступень — ручной отчёт с использованием сводных таблиц (pivot)

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

Главное в этом отчёте сводный лист со статистикой.

Третья ступень — отчёт на базе данных (БД)

Отличие от прошлой версии отчёта, в том, что данные по площадкам хранятся не на отдельных вкладках в excel-файле, а в специальных таблицах в БД. 

При помощи языка запросов sql создаётся представление (view), которое является аналогом сводного листа из прошлой версии.

Excel подключается к БД, получает оттуда view с данными, и на них уже накидывается сводная таблица pivot.

Напомню, что pivot — это средство визуализации, сводная таблица, накидывается на данные, даёт возможность изменять представления, создавать расчётные столбцы.

Структура БД

Атом — начальная сущность БД

В БД все данные привязываются к атомам. Это сущность, которая состоит из комбинации Город + Источник + Рекламная кампания + Сегмент. 

На практике обычно это выглядит так: Москва + Яндекс Поиск + Целевые = рекламная кампания msk_y_celevie_poisk.

Далее к атому добавляется правило в виде сегмента, т.е. какую статистику к этому атому привязывать. Сегмент записывается как ga:campaign==msk_y_celevie_poisk, что соответствует расширенному сегменту в интерфейсе Google Analytics.

Из чего состоит БД

БД — система таблиц. В наших БД есть таблицы с данными, таблицы-справочники и таблицы с правилами.

Таблицы с данными — в них хранятся данные за определённый период времени, которые привязаны к определённому атому. 

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

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

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

Таблицы с правилами — в них записаны правила, которые используются сторонними программами и объясняющие какую статистику брать и к чему её привязывать.

Сейчас все правила используются только Элеанорой — внутренней системой автоматического сбора статистики. 

В качестве примера можно рассмотреть таблицу core.Segments. В данной таблице мы записываем правила создания и привязки динамических сегментов к источникам. 

Т.е. Элеанора берёт эти правила, накидывает их на общий массив данных в Google Analytics, а потом привязывает эти данные к нужному атому.

Строение БД в примерах

Основные таблицы:

core.Atoms

core.Cities

core.Sources

core.Campaigns

core.Segments

core.AdEngineCampaigns

core.SourceData

core.GaData

Рассмотрим пример, поисковая рекламная кампания по названию брендов конкурентов в Google AdWords, город Екатеринбург.

Основная таблица в данном примере core.Atoms, в ней записаны все свойства, которые присущи атому — рекламной кампании.

Таблица core.Atoms:

Id	Version	SourceId	CampaignId	CityId	SegmentId	IsEnabled	StartDate	EndDate
1001	1	3	2	3	1253678835	1	16.05.2013	NULL

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

Таблица core.Segments

Id	GaProfileId	Name	IsDynamic	GaDynamicFilter	GaDynamicSegment	GaSegmentId
1253678835	1	vkladi_ekt_g_konkurenti_poisk	1	NULL	ga:campaign==Plus-bank.ru: Екатеринбург — Конкуренты — Поиск,ga:campaign==Вклады: Екатеринбург — Конкуренты — Поиск	1253678835

Таблица core.AdEngineCampaigns — указывает Элеаноре из какой кампании в интерфейсе брать данные.

AtomId	Version	AdEngineId	CampaignId
1001	1	2	108989341

При помощи таблиц справочников, мы понимаем к чему статистика относится. 

Таблица core.Cities

Id	Name	OrderBy
3	Екатеринбург	97

Таблица core.Sources

Id	Name	OrderBy
3	Google Поиск	70

Таблица core.Campaigns

Id	Name	OrderBy
2	Конкуренты	80

Итогом являются таблицы с данными.

core.SourceData

AtomId	Version	Date	Clicks	Shows	Budget
1001	1	11.12.2013	3	40	462,947

core.GaData

AtomId	Version	GaGoalId	Date	Clicks	Goals
1001	1	1	2013-12-11	2	2
1001	1	2	2013-12-11	2	0

Названия столбцов в таблицах и их расшифровка

AdEngineId — закодированное название площадки, откуда Элеаноре нужно получать статистику, например 1 — Яндекс Директ, 2 — Google AdWords. Расшифровка в таблице core.AdEngine.

AtomId — номер атома.

Budget — потраченные деньги.

CampaignId — закодированное название рекламной кампании. Расшифровка в таблице core.Campaigns.

CityId — закодированное название города. Расшифровка в таблице core.Cities.

Clicks — количество кликов.

Date — дата. В нашей базе данных вся статистика хранится по дням.

EndDate — дата окончания, если у какого-то атома будет стоять дата окончания, при перезагрузке статистики за большой промежуток времени, данные будут получаться только до даты окончания.

GaDynamicFilter — правила создания динамического фильтра. 

GaDynamicSegment — правила создания динамического сегмента.

GaGoalId — номер цели, которую необходимо получать из Google Analytics.

GaProfileId — номер профиля Google Analytics, из которого нужно получать статистику.

GaSegmentId — номер сегмента в Google Analytics (больше не используется).

Goals — количество целей.

Id — номер.

IsDynamic — является ли этот сегмент динамическим. Если нет, то Элеанора будет пытаться получить статистику по номеру сегмента GaSegmentId.

IsEnabled — включен ли атом. Если стоит 0, атом выключен и не будет

Name — название.

OrderBy — сортировка. Нужно, чтобы выгруженные данные можно было легко сортировать от большего к меньшему.

SegmentId — номер сегмента.

Shows — показы.

SourceId — закодированное название источника. Расшифровку можно посмотреть в таблице core.Sources.

StartDate — дата начала. При перезагрузке статистики за большой промежуток времени, данные будут получаться только с даты начала.

Version —  версия. Нужна для управления версиями. 

Разберём пример. Есть атом с сегментом “celevie_poisk”. Кампания была запущена 2014-01-01. 

2014-06-01 клиент запускает дополнительный город и мы решили в рекламные метки utm_campaign добавить параметр города. Метка celevie_poisk стала msk_celevie_poisk. 

Если мы изменим правило сегмента с celevie_poisk на msk_celevie_poisk, и переполучим статистику с 2014-01-01 по 2014-06-01, то потеряем все данные, потому что раньше метка была другой.

Значит нам необходимо создать новый сегмент. Но к старому атому привязан старый сегмент.

Чтобы не создавать совершенно новый атом, мы можем у имеющегося атома установить дату окончания и создать новую версию этого же атома, но уже с новым сегментом.

Доступ к БД

Доступы к БД можно получить у программистов.  Есть различные сервисы, для подключения к базам.

Элеанора онлайн — удобный редактор всего, что чаще всего приходится добавлять в базу. Никаких специальных умений не требует. Как видно из названия доступно онлайн. Доступна не вся база, а только некоторые части. Удобно вносить небольшие изменения, например новый атом, новый сегмент и т.д.

Microsoft SQL Management Studio. Программа для windows, доступна в пакете Microsoft SQL Server Express. Для работы нужно знать язык SQL запросов. Есть доступ ко всем таблицам и функциям, если пользователь имеет соответствующие права. Подходит для изучения базы, написания view, работы с большим количеством записей.

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