ReSQL.ru
     
powered by simpleCommunicator - 2.0.51     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / BigQuery - корректировка запроса.
11 сообщений из 11, страница 1 из 1
BigQuery - корректировка запроса.
    #40094208
Wilcat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Wilcat
Гость
Привет, есть запрос в BiqQuery. Диалект - standart SQL. Набор данных - GA4.
Запрос рассчитывает:

a) Сколько в среднем дней проходит от первого касания пользователя с сайтом до выполнения указанного события
б) Сколько в среднем касаний (сессий) необходимо пользователю, чтобы выполнить указанное событие.
Результат на выходе:

Код: sql
1.
2.
Row  ---  avg_days_to_event  ---  avg_sessions
 1   ---    0.13             ---   1.37



В части запроса, отвечающей за avg_sessions, есть ошибка.
Ошибка известна - в count_sessions не уникальные пользователи и соответственно ответ на второй вопрос не верный.

Помогите, пожалуйста, исправить.

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
WITH sessions_source AS ( 
SELECT 
    MIN(event_timestamp) session_start_timestamp, 
    user_pseudo_id, 
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = "ga_session_id") || '_' || user_pseudo_id AS ga_session_id, 
    IFNULL(MAX((SELECT FIRST_VALUE(value.string_value) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) FROM UNNEST(event_params) WHERE key = 'medium')),'(none)') as medium, 
    IFNULL(MAX((SELECT FIRST_VALUE(value.string_value) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) FROM UNNEST(event_params) WHERE key = 'source')),'(direct)') as source 
FROM 
    `TABLE_NAME` 
GROUP BY 
    user_pseudo_id, 
    ga_session_id 
), 
events_data AS ( 
    SELECT *, 
        LAG(event_timestamp) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS previous_event_timestamp, 
    FROM( 
        SELECT 
            event_timestamp, 
            user_first_touch_timestamp, 
            event_name, 
            user_pseudo_id, 
            (SELECT value.int_value FROM UNNEST(event_params) WHERE key = "ga_session_id") || '_' || user_pseudo_id AS ga_session_id, 
            (SELECT value.int_value FROM UNNEST(event_params) WHERE key = "ga_session_number") AS ga_session_number 
        FROM `TABLE_NAME` 
        WHERE event_name IN ('MY_EVENT')  
        ORDER BY user_pseudo_id, event_timestamp 
    ) 
), mcf AS ( 
SELECT  
    extract(date from timestamp_micros(event_timestamp) at time zone 'TIME_ZONE/City') event_date, 
    event_timestamp, 
    extract(date from timestamp_micros(user_first_touch_timestamp) at time zone 'TIME_ZONE/City') first_touch_date, 
    user_first_touch_timestamp, 
    events_data.ga_session_number AS ga_session_number, 
    events_data.event_name AS event_name, 
    events_data.user_pseudo_id AS user_pseudo_id, 
    events_data.ga_session_id AS event_ga_session_id, 
    sessions_source.ga_session_id AS sessions_ga_session_id, 
    sessions_source.source || ' / ' || sessions_source.medium AS source_medium 
FROM events_data 
LEFT JOIN sessions_source 
ON events_data.user_pseudo_id = sessions_source.user_pseudo_id  
    AND events_data.event_timestamp >= sessions_source.session_start_timestamp 
    AND (events_data.previous_event_timestamp IS NULL  
        OR events_data.previous_event_timestamp < sessions_source.session_start_timestamp 
        OR events_data.ga_session_id = sessions_source.ga_session_id) 
ORDER BY events_data.user_pseudo_id, sessions_source.session_start_timestamp ASC 
), 
count_sessions AS (
SELECT 
    event_date,
    first_touch_date,
    DATE_DIFF(event_date,first_touch_date,day) days_to_event,
    user_pseudo_id,
    event_ga_session_id,
    MAX(ga_session_number) session_count
FROM mcf
WHERE first_touch_date >= "DATE"
GROUP BY 1,2,4,5
) 
SELECT 
    ROUND(AVG(days_to_event),2) avg_days_to_event,
    ROUND(AVG(session_count),2) avg_sessions
FROM count_sessions
...
Рейтинг: 0 / 0
01.09.2021, 00:47
| Ответить | Цитировать | Написать  
BigQuery - корректировка запроса.
    #40094224
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
1. Зряплату за вас тоже надо получить?
2. MAX(ga_session_number) session_count - у вас точно сессии каждого пользователя нумеруются последовательно?
...
Рейтинг: 0 / 0
01.09.2021, 05:39
| Ответить | Цитировать | Написать  
BigQuery - корректировка запроса.
    #40094250
Wilcat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Wilcat
Гость
aleks222,

1) Я не жадный, поделюсь ))

2) да
...
Рейтинг: 0 / 0
01.09.2021, 09:24
| Ответить | Цитировать | Написать  
BigQuery - корректировка запроса.
    #40094285
godsql
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
godsql
Гость
Если вам попроще надо, то сделайте оконными функциями.
т.е. distinct user_pseudo_id пользователя и к нему avg over(patition by user_pseudo_id)

а вообще, я бы переписал count_sessions. странный он какой-то. логики не пойму
...
Рейтинг: 0 / 0
01.09.2021, 11:00
| Ответить | Цитировать | Написать  
BigQuery - корректировка запроса.
    #40094288
Wilcat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Wilcat
Гость
godsql,

мне бы пример, если не сложно, на данный момент - задача почти неподъемная.
...
Рейтинг: 0 / 0
01.09.2021, 11:06
| Ответить | Цитировать | Написать  
BigQuery - корректировка запроса.
    #40094517
godsql
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
godsql
Гость
Wilcat,

это мое имхо, как я понял задачу :)
смотрите. у вас запрос
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
count_sessions AS (
SELECT 
    event_date,
    first_touch_date,
    DATE_DIFF(event_date,first_touch_date,day) days_to_event,
    user_pseudo_id,
    event_ga_session_id,
    MAX(ga_session_number) session_count
FROM mcf
WHERE first_touch_date >= "DATE"
GROUP BY 1,2,4,5


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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
count_sessions AS (
SELECT 
    user_pseudo_id,
--    event_date,
--    first_touch_date,
    min(DATE_DIFF(event_date,first_touch_date,day)) days_to_event,
--    event_ga_session_id,
    MAX(ga_session_number) session_count
FROM mcf
WHERE first_touch_date >= "DATE"
GROUP BY 1
)


Единственно, меня смущает event_ga_session_id - как он связан с ga_session_number,
...
Рейтинг: 0 / 0
01.09.2021, 17:53
| Ответить | Цитировать | Написать  
BigQuery - корректировка запроса.
    #40094559
Wilcat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Wilcat
Гость
godsql,

1. Спасибо большое !

2. Абсолютно правильно смущает.
Это, в некотором роде, тестовое задание.
Есть запрос, с помощью этого запроса получаем информацию о многоканальных последовательностях для конверсий.

Как таковое, задание изменить код, чтобы получить ответ на 2 вопроса (в 1-м посте).

Но т.к. багаж знаний, увы, не велик, лишнее трогаю с опаской.
Сам основной запрос выглядит так:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
WITH sessions_source AS (
SELECT
    MIN(event_timestamp) session_start_timestamp,
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = "ga_session_id") || '_' || user_pseudo_id AS ga_session_id,
    IFNULL(MAX((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium')),'(none)') as medium,
    IFNULL(MAX((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source')),'(direct)') as source
FROM
    `TABLE_NAME`
GROUP BY
    user_pseudo_id,
    ga_session_id
),
events_data AS (
    SELECT *,
        LAG(event_timestamp) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS previous_event_timestamp,
    FROM(
        SELECT 
            event_timestamp,
            event_name,
            user_pseudo_id,
            (SELECT value.int_value FROM UNNEST(event_params) WHERE key = "ga_session_id") || '_' || user_pseudo_id AS ga_session_id,
        FROM `TABLE_NAME`
        WHERE event_name IN ('YOUR_EVENT')
        ORDER BY user_pseudo_id, event_timestamp
    )
), mcf AS (
SELECT 
    extract(date from timestamp_micros(event_timestamp) at time zone 'TIME_ZONE/City') event_date,
    event_timestamp,
    events_data.event_name AS event_name,
    events_data.user_pseudo_id AS user_pseudo_id,
    events_data.ga_session_id AS event_ga_session_id,
    sessions_source.ga_session_id AS sessions_ga_session_id,
    sessions_source.source || ' / ' || sessions_source.medium AS source_medium
FROM events_data
LEFT JOIN sessions_source
ON events_data.user_pseudo_id = sessions_source.user_pseudo_id 
    AND events_data.event_timestamp >= sessions_source.session_start_timestamp
    AND (events_data.previous_event_timestamp IS NULL 
        OR events_data.previous_event_timestamp < sessions_source.session_start_timestamp
        OR events_data.ga_session_id = sessions_source.ga_session_id)
ORDER BY events_data.user_pseudo_id, sessions_source.session_start_timestamp ASC
)
SELECT 
    event_date,
    event_timestamp,
    user_pseudo_id,
    ARRAY_TO_STRING(sm, ' > ')
FROM(
SELECT 
    event_date,
    event_timestamp,
    user_pseudo_id,
   ARRAY_AGG(source_medium) AS sm
FROM mcf
GROUP BY 1,2,3
)
...
Рейтинг: 0 / 0
01.09.2021, 19:01
| Ответить | Цитировать | Написать  
BigQuery - корректировка запроса.
    #40094593
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Wilcat,

сортировки в подзапросах с какой целью?
...
Рейтинг: 0 / 0
01.09.2021, 20:14
| Ответить | Цитировать | Написать  
BigQuery - корректировка запроса.
    #40094627
Wilcat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Wilcat
Гость
Владислав Колосов,

пользователь попадает на сайт, нас интересует какое-то конкретное событие.
Допустим, нам интересно отслеживать пользователей, которые пробыли на сайте 2 минуты и при этом проскролили его не менее чем на 50%.
При соблюдении этих условий, получаем в аналитику событие.
Если хотим узнать, сколько пользователю потребовалось заходов (касаний) до наступления, считаем time_stamp от первого захода, до захода с наступлением события в одной сессии.
...
Рейтинг: 0 / 0
01.09.2021, 21:58
| Ответить | Цитировать | Написать  
BigQuery - корректировка запроса.
    #40094637
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Wilcat,

Как вам в этом помогает сортировка в подзапросах, какую роль она выполняет? Что случится если выражения ORDER BY убрать?
...
Рейтинг: 0 / 0
01.09.2021, 22:35
| Ответить | Цитировать | Написать  
BigQuery - корректировка запроса.
    #40094645
Wilcat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Wilcat
Гость
Владислав Колосов,

http://clip2net.com/s/4dcc179

http://clip2net.com/s/4dcc0Xt
...
Рейтинг: 0 / 0
01.09.2021, 23:20
| Ответить | Цитировать | Написать  
11 сообщений из 11, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / BigQuery - корректировка запроса.
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&msg=40094224&tid=1684350]:
 0ms
get settings:
 7ms
get forum list:
 11ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 138ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 54ms
get tp. blocked users:
 2ms
others: 13ms
total:  247ms
созд. / загр.: 247ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]