База данных веб-аналитики: Топ 30 основных запросов в SQL

В этой статье я хочу поделиться опытом и рассказать о 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-запросов позволит вам глубже понять поведение пользователей на вашем сайте, оптимизировать маркетинговые стратегии и улучшить общую эффективность вашего веб-сайта.