В этой статье я хочу поделиться опытом и рассказать о 30 наиболее важных SQL-запросах, которые позволяют извлечь максимум пользы из базы данных веб-аналитики. Многие из этих запросов являются нестандартными и редко используемыми аналитиками. И в этом их главная ценность, так как они позволяют посмотреть на ваши данные совершенно с неожиданных углов.
Для удобства я разбил все запросы на отдельные категории – по типам того, что мы собираемся исследовать. И начнем мы, разумеется, с базовых запросов.
1. Анализ посещаемости сайта
Первая группа запросов поможет нам понять общую картину посещаемости нашего веб-сайта.
1.1. Общее количество посещений за определенный период:
SELECT COUNT(DISTINCT VisitID) AS total_visits
FROM web_analytics
WHERE VisitDateTime BETWEEN '2024-01-01' AND '2024-01-31';
Этот запрос даст нам представление о том, сколько уникальных сессий было на нашем сайте за указанный период.
Важно отметить, что мы используем COUNT(DISTINCT VisitID), а не просто COUNT(*), чтобы избежать дублирования сессий, если у нас есть несколько записей для одной сессии.
1.2. Общее количество уникальных пользователей за определенный период:
SELECT COUNT(DISTINCT ClientID) AS total_users
FROM web_analytics
WHERE VisitDateTime BETWEEN '2024-01-01' AND '2024-01-31';
Заменив одну строчку в нашем предыдущем запросе, мы уже получаем подсчет не количества сессий, а числа уникальных пользователей сайта за период.
1.3. Распределение посещений по дням недели:
SELECT
DAYNAME(VisitDateTime) AS day_of_week,
COUNT(DISTINCT VisitID) AS visits
FROM web_analytics
GROUP BY DAYNAME(VisitDateTime)
ORDER BY FIELD(day_of_week, 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday');
Этот запрос поможет нам выявить дни недели с наибольшей активностью пользователей. Мы используем функцию DAYNAME для получения названия дня недели и FIELD для сортировки результатов в правильном порядке.
1.4. Среднее время, проведенное на сайте:
SELECT AVG(VisitDuration) / 60 AS avg_visit_duration_minutes
FROM web_analytics;
Здесь мы вычисляем среднюю продолжительность визита в минутах. Обратите внимание, что мы делим на 60, предполагая, что VisitDuration хранится в секундах.
Анализ посещаемости сайта является фундаментальным для понимания общей картины взаимодействия пользователей с вашим ресурсом. Эти базовые метрики дают нам отправную точку для дальнейшего, более глубокого анализа.
2. Анализ источников трафика
Следующая группа запросов поможет нам разобраться, откуда приходят наши пользователи и какие каналы привлечения наиболее эффективны.
2.1. Распределение трафика по источникам:
SELECT
Source,
COUNT(DISTINCT VisitID) AS visits,
COUNT(DISTINCT VisitID) * 100.0 / SUM(COUNT(DISTINCT VisitID)) OVER () AS percentage
FROM web_analytics
GROUP BY Source
ORDER BY visits DESC;
Этот запрос не только показывает количество визитов из каждого источника, но и вычисляет процентное соотношение. Мы используем оконную функцию SUM() OVER () для подсчета общего числа визитов, что позволяет нам вычислить процент прямо в запросе.
2.2. Эффективность UTM-меток:
SELECT
UTMSource,
UTMMedium,
UTMCampaign,
COUNT(DISTINCT VisitID) AS visits,
COUNT(DISTINCT CASE WHEN GoalsID IS NOT NULL THEN VisitID END) AS conversions,
COUNT(DISTINCT CASE WHEN GoalsID IS NOT NULL THEN VisitID END) * 100.0 / COUNT(DISTINCT VisitID) AS conversion_rate
FROM web_analytics
GROUP BY UTMSource, UTMMedium, UTMCampaign
ORDER BY visits DESC;
Этот запрос анализирует эффективность различных маркетинговых кампаний, учитывая UTM-метки. Мы подсчитываем не только количество визитов, но и количество конверсий (посещений, в которых была достигнута хотя бы одна цель), а также вычисляем коэффициент конверсии.
2.3. Анализ реферальных источников:
SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX(Referer, '//', -1), '/', 1) AS referring_domain,
COUNT(DISTINCT VisitID) AS visits
FROM web_analytics
WHERE Source = 'referral'
GROUP BY referring_domain
ORDER BY visits DESC
LIMIT 10;
Этот запрос извлекает домен из URL реферера и подсчитывает количество визитов с каждого домена. Мы используем функцию SUBSTRING_INDEX для извлечения домена из полного URL.
Анализ источников трафика крайне важен для оптимизации маркетинговых усилий и распределения бюджета. Он позволяет понять, какие каналы привлечения работают лучше всего, и где есть потенциал для улучшения.
3. Анализ поведения пользователей на сайте
Теперь давайте углубимся в то, как пользователи взаимодействуют с нашим сайтом после того, как они на него попали.
3.1. Наиболее популярные страницы:
SELECT
URL,
COUNT(*) AS pageviews,
COUNT(DISTINCT VisitID) AS unique_pageviews,
AVG(TIMESTAMPDIFF(SECOND, HitDateTime, LEAD(HitDateTime) OVER (PARTITION BY VisitID ORDER BY HitDateTime))) AS avg_time_on_page
FROM web_analytics
GROUP BY URL
ORDER BY pageviews DESC
LIMIT 20;
Этот запрос не только показывает наиболее часто просматриваемые страницы, но и вычисляет среднее время, проведенное на каждой странице. Мы используем оконную функцию LEAD() для определения времени следующего хита в рамках одной сессии.
3.2. Анализ пути пользователя по сайту:
WITH path_analysis AS (
SELECT
VisitID,
GROUP_CONCAT(URL ORDER BY HitDateTime SEPARATOR ' -> ') AS user_path
FROM web_analytics
GROUP BY VisitID
)
SELECT
user_path,
COUNT(*) AS path_frequency
FROM path_analysis
GROUP BY user_path
ORDER BY path_frequency DESC
LIMIT 10;
Этот запрос использует общее табличное выражение (CTE) для создания строки, представляющей путь пользователя по сайту, а затем анализирует наиболее частые пути. Функция GROUP_CONCAT помогает нам объединить все URL, посещенные в рамках одной сессии, в одну строку.
3.3. Анализ выхода с сайта:
SELECT
EndURL,
COUNT(*) AS exit_count,
COUNT(*) * 100.0 / (SELECT COUNT(*) FROM web_analytics) AS exit_rate
FROM web_analytics
GROUP BY EndURL
ORDER BY exit_count DESC
LIMIT 10;
Этот запрос показывает страницы, на которых пользователи чаще всего завершают свою сессию, а также вычисляет процент выходов для каждой страницы.
Анализ поведения пользователей на сайте помогает выявить сильные и слабые стороны вашего веб-сайта. Он позволяет понять, какой контент наиболее интересен вашей аудитории, где пользователи теряют интерес, и какие страницы требуют оптимизации.
4. Анализ конверсий и достижения целей
Конверсии и достижение целей – это ключевые метрики для оценки эффективности вашего сайта. Давайте рассмотрим несколько запросов, которые помогут нам глубже проанализировать эти аспекты.
4.1. Общий коэффициент конверсии:
SELECT
COUNT(DISTINCT CASE WHEN GoalsID IS NOT NULL THEN VisitID END) * 100.0 / COUNT(DISTINCT VisitID) AS overall_conversion_rate
FROM web_analytics;
Этот запрос вычисляет общий коэффициент конверсии, то есть процент сессий, в которых была достигнута хотя бы одна цель.
4.2. Коэффициент конверсии по уникальным пользователям:
SELECT
COUNT(DISTINCT CASE WHEN GoalsID IS NOT NULL THEN ClientID END) * 100.0 / COUNT(DISTINCT ClientID) AS user_conversion_rate
FROM web_analytics;
Заменяем одну строку и получаем процент уникальных пользователей, которые достигли хотя бы одной цели.
4.3. Анализ конверсий по целям:
SELECT
g.goal_id,
COUNT(*) AS goal_completions,
COUNT(*) * 100.0 / (SELECT COUNT(DISTINCT VisitID) FROM web_analytics) AS goal_conversion_rate
FROM web_analytics w
CROSS APPLY (
SELECT CAST(value AS INT) AS goal_id
FROM STRING_SPLIT(w.GoalsID, ',')
) g
GROUP BY g.goal_id
ORDER BY goal_completions DESC;
Этот запрос анализирует достижение различных целей. Мы используем CROSS APPLY и STRING_SPLIT для работы с массивом GoalsID. Обратите внимание, что этот запрос предполагает, что GoalsID хранится как строка с разделителями-запятыми.
4.4. Время до конверсии:
SELECT
AVG(TIMESTAMPDIFF(SECOND, VisitDateTime, MIN(g.goal_time))) / 60 AS avg_time_to_conversion_minutes
FROM web_analytics w
CROSS APPLY (
SELECT CAST(value AS DATETIME) AS goal_time
FROM STRING_SPLIT(w.GoalsDateTime, ',')
) g
WHERE w.GoalsID IS NOT NULL;
Этот запрос вычисляет среднее время от начала сессии до первой конверсии. Мы снова используем CROSS APPLY для работы с массивом GoalsDateTime.
4.5. Конверсии по источникам трафика:
SELECT
Source,
COUNT(DISTINCT VisitID) AS visits,
COUNT(DISTINCT CASE WHEN GoalsID IS NOT NULL THEN VisitID END) AS conversions,
COUNT(DISTINCT CASE WHEN GoalsID IS NOT NULL THEN VisitID END) * 100.0 / COUNT(DISTINCT VisitID) AS conversion_rate
FROM web_analytics
GROUP BY Source
ORDER BY conversion_rate DESC;
Этот запрос анализирует эффективность различных источников трафика с точки зрения конверсий.
Анализ конверсий и достижения целей является ключевым для оценки эффективности вашего сайта и маркетинговых усилий. Он позволяет понять, какие действия пользователей наиболее ценны для вашего бизнeсa, и как различные факторы влияют на вероятность совершения этих действий.
5. Сегментация пользователей
Сегментация пользователей – это мощный инструмент для понимания различных групп вашей аудитории и их поведения. Давайте рассмотрим несколько запросов, которые помогут нам в этом.
5.1. Сегментация по частоте посещений:
WITH user_visits AS (
SELECT
ClientID,
COUNT(DISTINCT VisitID) AS visit_count
FROM web_analytics
GROUP BY ClientID
)
SELECT
CASE
WHEN visit_count = 1 THEN 'One-time visitors'
WHEN visit_count BETWEEN 2 AND 5 THEN 'Occasional visitors'
WHEN visit_count BETWEEN 6 AND 10 THEN 'Regular visitors'
ELSE 'Loyal visitors'
END AS user_segment,
COUNT(*) AS segment_size,
AVG(visit_count) AS avg_visits_per_user
FROM user_visits
GROUP BY
CASE
WHEN visit_count = 1 THEN 'One-time visitors'
WHEN visit_count BETWEEN 2 AND 5 THEN 'Occasional visitors'
WHEN visit_count BETWEEN 6 AND 10 THEN 'Regular visitors'
ELSE 'Loyal visitors'
END
ORDER BY segment_size DESC;
Этот запрос сегментирует пользователей на основе частоты их визитов. Мы используем общее табличное выражение (CTE) для подсчета визитов каждого пользователя, а затем группируем пользователей по сегментам. Такой анализ может помочь в разработке стратегий удержания пользователей и повышения их лояльности.
5.2. Сегментация по времени на сайте:
WITH user_time AS (
SELECT
ClientID,
AVG(VisitDuration) AS avg_visit_duration
FROM web_analytics
GROUP BY ClientID
)
SELECT
CASE
WHEN avg_visit_duration < 60 THEN 'Bounced visitors'
WHEN avg_visit_duration BETWEEN 60 AND 300 THEN 'Short-term visitors'
WHEN avg_visit_duration BETWEEN 301 AND 900 THEN 'Medium-term visitors'
ELSE 'Long-term visitors'
END AS user_segment,
COUNT(*) AS segment_size,
AVG(avg_visit_duration) / 60 AS avg_minutes_per_visit
FROM user_time
GROUP BY
CASE
WHEN avg_visit_duration < 60 THEN 'Bounced visitors'
WHEN avg_visit_duration BETWEEN 60 AND 300 THEN 'Short-term visitors'
WHEN avg_visit_duration BETWEEN 301 AND 900 THEN 'Medium-term visitors'
ELSE 'Long-term visitors'
END
ORDER BY avg_minutes_per_visit;
Этот запрос сегментирует пользователей на основе среднего времени, проведенного на сайте. Такая сегментация может помочь выявить группы пользователей, которые наиболее вовлечены в контент сайта, а также тех, кто быстро покидает сайт.
5.3. Сегментация по достижению целей:
WITH user_goals AS (
SELECT
ClientID,
COUNT(DISTINCT VisitID) AS total_visits,
COUNT(DISTINCT CASE WHEN GoalsID IS NOT NULL THEN VisitID END) AS conversion_visits
FROM web_analytics
GROUP BY ClientID
)
SELECT
CASE
WHEN conversion_visits = 0 THEN 'Non-converters'
WHEN conversion_visits * 1.0 / total_visits < 0.2 THEN 'Low converters'
WHEN conversion_visits * 1.0 / total_visits BETWEEN 0.2 AND 0.5 THEN 'Medium converters'
ELSE 'High converters'
END AS user_segment,
COUNT(*) AS segment_size,
AVG(conversion_visits * 1.0 / total_visits) AS avg_conversion_rate
FROM user_goals
GROUP BY
CASE
WHEN conversion_visits = 0 THEN 'Non-converters'
WHEN conversion_visits * 1.0 / total_visits < 0.2 THEN 'Low converters'
WHEN conversion_visits * 1.0 / total_visits BETWEEN 0.2 AND 0.5 THEN 'Medium converters'
ELSE 'High converters'
END
ORDER BY avg_conversion_rate;
Этот запрос сегментирует пользователей на основе их склонности к достижению целей на сайте. Такая сегментация может помочь в разработке персонализированных стратегий для различных групп пользователей.
Сегментация пользователей – это мощный инструмент для понимания вашей аудитории и оптимизации маркетинговых стратегий. Она позволяет адаптировать ваш подход к различным группам пользователей, что может значительно повысить эффективность ваших усилий по привлечению и удержанию клиентов.
6. Анализ эффективности контента
Анализ эффективности контента помогает понять, какие страницы и материалы на вашем сайте наиболее привлекательны для пользователей и способствуют достижению бизнес-целей.
6.1. Страницы с наибольшим временем просмотра:
SELECT
URL,
COUNT(*) AS pageviews,
AVG(TIMESTAMPDIFF(SECOND, HitDateTime, LEAD(HitDateTime) OVER (PARTITION BY VisitID ORDER BY HitDateTime))) AS avg_time_on_page_seconds
FROM web_analytics
GROUP BY URL
HAVING COUNT(*) > 100 -- Исключаем страницы с малым количеством просмотров
ORDER BY avg_time_on_page_seconds DESC
LIMIT 20;
Этот запрос показывает страницы, на которых пользователи проводят наибольшее количество времени. Мы используем оконную функцию LEAD() для расчета времени, проведенного на странице, и исключаем страницы с малым количеством просмотров для получения более надежных результатов.
6.2. Страницы с наивысшим коэффициентом конверсии:
WITH page_conversions AS (
SELECT
URL,
COUNT(*) AS pageviews,
COUNT(DISTINCT CASE WHEN GoalsID IS NOT NULL THEN WatchID END) AS conversions
FROM web_analytics
GROUP BY URL
)
SELECT
URL,
pageviews,
conversions,
conversions * 100.0 / pageviews AS conversion_rate
FROM page_conversions
WHERE pageviews > 100 -- Исключаем страницы с малым количеством просмотров
ORDER BY conversion_rate DESC
LIMIT 20;
Этот запрос выявляет страницы, которые наиболее эффективно приводят к конверсиям. Мы учитываем только страницы с достаточным количеством просмотров для обеспечения статистической значимости результатов.
6.3. Анализ глубины просмотра:
WITH session_depth AS (
SELECT
VisitID,
COUNT(DISTINCT URL) AS pages_viewed
FROM web_analytics
GROUP BY VisitID
)
SELECT
pages_viewed,
COUNT(*) AS session_count,
COUNT(*) * 100.0 / SUM(COUNT(*)) OVER () AS percentage
FROM session_depth
GROUP BY pages_viewed
ORDER BY pages_viewed;
Этот запрос анализирует, сколько уникальных страниц просматривают пользователи за одну сессию. Такой анализ может помочь понять, насколько глубоко пользователи изучают ваш сайт и где они теряют интерес.
6.4. Анализ эффективности контента по источникам трафика:
SELECT
Source,
URL,
COUNT(*) AS pageviews,
AVG(TIMESTAMPDIFF(SECOND, HitDateTime, LEAD(HitDateTime) OVER (PARTITION BY VisitID ORDER BY HitDateTime))) AS avg_time_on_page_seconds,
COUNT(DISTINCT CASE WHEN GoalsID IS NOT NULL THEN WatchID END) * 100.0 / COUNT(*) AS conversion_rate
FROM web_analytics
GROUP BY Source, URL
HAVING COUNT(*) > 50 -- Исключаем комбинации с малым количеством просмотров
ORDER BY Source, pageviews DESC;
Этот запрос позволяет оценить эффективность различных страниц в зависимости от источника трафика. Он может помочь выявить, какой контент наиболее привлекателен для пользователей из разных источников.
Анализ эффективности контента – это ключевой элемент оптимизации веб-сайта. Он позволяет понять, какой контент наиболее ценен для ваших пользователей, и сосредоточить усилия на создании подобного контента в будущем.
7. Анализ пользовательских сценариев
Анализ пользовательских сценариев помогает понять, как пользователи взаимодействуют с вашим сайтом, какие пути они выбирают и где могут возникать проблемы.
7.1. Анализ последовательности просмотра страниц:
WITH page_sequence AS (
SELECT
VisitID,
URL,
ROW_NUMBER() OVER (PARTITION BY VisitID ORDER BY HitDateTime) AS page_order
FROM web_analytics
)
SELECT
t1.URL AS first_page,
t2.URL AS second_page,
COUNT(*) AS frequency
FROM page_sequence t1
JOIN page_sequence t2 ON t1.VisitID = t2.VisitID AND t1.page_order = t2.page_order - 1
WHERE t1.page_order = 1
GROUP BY t1.URL, t2.URL
ORDER BY frequency DESC
LIMIT 20;
Этот запрос анализирует наиболее частые последовательности из двух страниц в начале пользовательской сессии. Он может помочь выявить типичные пути пользователей по сайту и оптимизировать навигацию.
7.2. Анализ путей к конверсии:
WITH conversion_paths AS (
SELECT
VisitID,
GROUP_CONCAT(URL ORDER BY HitDateTime SEPARATOR ' -> ') AS path,
MAX(CASE WHEN GoalsID IS NOT NULL THEN 1 ELSE 0 END) AS converted
FROM web_analytics
GROUP BY VisitID
)
SELECT
path,
COUNT(*) AS frequency,
SUM(converted) AS conversions,
SUM(converted) * 100.0 / COUNT(*) AS conversion_rate
FROM conversion_paths
GROUP BY path
HAVING COUNT(*) > 10 -- Исключаем редкие пути
ORDER BY conversion_rate DESC
LIMIT 20;
Этот запрос анализирует наиболее эффективные пути пользователей, приводящие к конверсии. Мы используем GROUP_CONCAT для создания строки, представляющей путь пользователя, и учитываем только достаточно частые пути для обеспечения статистической значимости.
7.3. Анализ времени между посещениями:
WITH visit_intervals AS (
SELECT
ClientID,
VisitDateTime,
LAG(VisitDateTime) OVER (PARTITION BY ClientID ORDER BY VisitDateTime) AS prev_visit
FROM web_analytics
GROUP BY ClientID, VisitDateTime
)
SELECT
CASE
WHEN TIMESTAMPDIFF(DAY, prev_visit, VisitDateTime) <= 1 THEN 'Same day'
WHEN TIMESTAMPDIFF(DAY, prev_visit, VisitDateTime) <= 7 THEN 'Within a week'
WHEN TIMESTAMPDIFF(DAY, prev_visit, VisitDateTime) <= 30 THEN 'Within a month'
ELSE 'More than a month'
END AS return_interval,
COUNT(*) AS frequency,
COUNT(*) * 100.0 / SUM(COUNT(*)) OVER () AS percentage
FROM visit_intervals
WHERE prev_visit IS NOT NULL
GROUP BY
CASE
WHEN TIMESTAMPDIFF(DAY, prev_visit, VisitDateTime) <= 1 THEN 'Same day'
WHEN TIMESTAMPDIFF(DAY, prev_visit, VisitDateTime) <= 7 THEN 'Within a week'
WHEN TIMESTAMPDIFF(DAY, prev_visit, VisitDateTime) <= 30 THEN 'Within a month'
ELSE 'More than a month'
END
ORDER BY
CASE return_interval
WHEN 'Same day' THEN 1
WHEN 'Within a week' THEN 2
WHEN 'Within a month' THEN 3
ELSE 4
END;
Этот запрос анализирует интервалы между повторными посещениями сайта пользователями. Он может помочь понять, как часто пользователи возвращаются на ваш сайт, что может быть полезно для планирования кампаний по удержанию пользователей и оценки эффективности ваших усилий по созданию лояльной аудитории.
Анализ пользовательских сценариев – это ключевой элемент в понимании поведения пользователей на вашем сайте. Он позволяет выявить типичные паттерны поведения, оптимизировать пользовательский опыт и повысить эффективность вашего сайта в достижении бизнес-целей.
8. Технический анализ
Технический анализ помогает оценить производительность сайта и выявить потенциальные проблемы, которые могут влиять на пользовательский опыт.
8.1. Анализ возникновения 404 и 500х страниц:
SELECT
SUBSTRING_INDEX(URL, '?', 1) AS base_url,
COUNT(*) AS error_count,
COUNT(DISTINCT VisitID) AS affected_sessions,
COUNT(DISTINCT ClientID) AS affected_users
FROM web_analytics
WHERE URL LIKE '%error%' OR URL LIKE '%404%' OR URL LIKE '%500%'
GROUP BY SUBSTRING_INDEX(URL, '?', 1)
ORDER BY error_count DESC
LIMIT 20;
Этот запрос анализирует страницы, которые могут вызывать ошибки у пользователей. Мы ищем URL, содержащие ключевые слова, связанные с ошибками, и подсчитываем количество таких ошибок, а также количество затронутых сессий и пользователей. Использование SUBSTRING_INDEX позволяет нам группировать URL без учета параметров запроса.
8.2. Анализ скорости загрузки по устройствам:
SELECT
CASE
WHEN UserAgent LIKE '%Mobile%' THEN 'Mobile'
WHEN UserAgent LIKE '%Tablet%' THEN 'Tablet'
ELSE 'Desktop'
END AS device_type,
AVG(TIMESTAMPDIFF(MILLISECOND, VisitDateTime, HitDateTime)) AS avg_load_time_ms,
COUNT(*) AS pageviews
FROM web_analytics
GROUP BY
CASE
WHEN UserAgent LIKE '%Mobile%' THEN 'Mobile'
WHEN UserAgent LIKE '%Tablet%' THEN 'Tablet'
ELSE 'Desktop'
END
ORDER BY avg_load_time_ms DESC;
Этот запрос анализирует среднее время загрузки страниц для различных типов устройств. Он может помочь выявить проблемы с производительностью на определенных типах устройств и оптимизировать сайт соответствующим образом.
Технический анализ крайне важен для обеспечения высокого качества пользовательского опыта. Он позволяет выявить и устранить проблемы, которые могут негативно влиять на восприятие вашего сайта пользователями и, как следствие, на достижение ваших бизнес-целей.
9. Анализ эффективности рекламных кампаний
Анализ эффективности рекламных кампаний помогает оценить отдачу от инвестиций в маркетинг и оптимизировать стратегии привлечения пользователей.
9.1. Анализ эффективности трафика по UTM-меткам:
SELECT
UTMSource,
UTMMedium,
UTMCampaign,
COUNT(DISTINCT VisitID) AS visits,
COUNT(DISTINCT CASE WHEN GoalsID IS NOT NULL THEN VisitID END) AS conversions,
COUNT(DISTINCT CASE WHEN GoalsID IS NOT NULL THEN VisitID END) * 100.0 / COUNT(DISTINCT VisitID) AS conversion_rate,
AVG(VisitDuration) / 60 AS avg_visit_duration_minutes
FROM web_analytics
WHERE UTMSource IS NOT NULL AND UTMMedium IS NOT NULL AND UTMCampaign IS NOT NULL
GROUP BY UTMSource, UTMMedium, UTMCampaign
ORDER BY visits DESC
LIMIT 20;
Этот запрос анализирует эффективность различных рекламных кампаний, учитывая UTM-метки. Мы рассматриваем количество визитов, конверсий, коэффициент конверсии и среднюю продолжительность визита для каждой комбинации UTM-параметров.
9.2. Анализ возврата инвестиций (ROI) для рекламных кампаний:
WITH campaign_revenue AS (
SELECT
UTMSource,
UTMMedium,
UTMCampaign,
SUM(CASE WHEN GoalsID IS NOT NULL THEN Revenue ELSE 0 END) AS total_revenue,
COUNT(DISTINCT VisitID) AS visits
FROM web_analytics
WHERE UTMSource IS NOT NULL AND UTMMedium IS NOT NULL AND UTMCampaign IS NOT NULL
GROUP BY UTMSource, UTMMedium, UTMCampaign
)
SELECT
UTMSource,
UTMMedium,
UTMCampaign,
total_revenue,
visits,
total_revenue / visits AS revenue_per_visit,
(total_revenue - (visits * cost_per_click)) / (visits * cost_per_click) * 100 AS ROI_percentage
FROM campaign_revenue
CROSS JOIN (SELECT 0.5 AS cost_per_click) AS campaign_cost -- Предполагаемая стоимость клика
ORDER BY ROI_percentage DESC
LIMIT 20;
Этот запрос рассчитывает возврат инвестиций для каждой рекламной кампании. Мы предполагаем некоторую стоимость за клик (в данном случае 0.5) и используем ее для расчета ROI. В реальной ситуации вам нужно будет заменить это значение на фактические данные о стоимости рекламы.
9.3. Анализ атрибуции конверсий:
WITH attribution AS (
SELECT
VisitID,
UTMSource,
UTMMedium,
UTMCampaign,
CASE
WHEN ROW_NUMBER() OVER (PARTITION BY VisitID ORDER BY HitDateTime) = 1 THEN 'First Touch'
WHEN ROW_NUMBER() OVER (PARTITION BY VisitID ORDER BY HitDateTime DESC) = 1 THEN 'Last Touch'
ELSE 'Middle Touch'
END AS attribution_model,
CASE WHEN GoalsID IS NOT NULL THEN 1 ELSE 0 END AS conversion
FROM web_analytics
WHERE UTMSource IS NOT NULL AND UTMMedium IS NOT NULL AND UTMCampaign IS NOT NULL
)
SELECT
UTMSource,
UTMMedium,
UTMCampaign,
attribution_model,
SUM(conversion) AS conversions,
COUNT(*) AS touches,
SUM(conversion) * 100.0 / COUNT(*) AS conversion_rate
FROM attribution
GROUP BY UTMSource, UTMMedium, UTMCampaign, attribution_model
ORDER BY conversions DESC
LIMIT 30;
Этот запрос анализирует атрибуцию конверсий по моделям “первого касания”, “последнего касания” и “промежуточного касания”. Это помогает понять, какие рекламные кампании наиболее эффективны на разных этапах пути пользователя к конверсии.
Анализ эффективности рекламных кампаний критически важен для оптимизации маркетинговых расходов и повышения ROI. Он позволяет выявить наиболее эффективные каналы и стратегии привлечения пользователей, а также оптимизировать распределение бюджета между различными кампаниями.
Выводы
В этой статье мы рассмотрели 30 ключевых SQL-запросов для анализа базы данных веб-аналитики. Эти запросы охватывают широкий спектр аспектов, от базового анализа посещаемости до анализа эффективности рекламных кампаний.
Использование этих SQL-запросов позволит вам глубже понять поведение пользователей на вашем сайте, оптимизировать маркетинговые стратегии и улучшить общую эффективность вашего веб-сайта.