Skip to content

샘플 쿼리

이 파트너 페이지에서는 쿼리를 설정할 때 참조할 수 있는 활용 사례별 샘플 쿼리를 제공합니다.

일반적인 쿼리 중 하나는 이벤트를 시간별로 필터링하는 것입니다.

발생 시간을 기준으로 필터링할 수 있습니다. 이벤트 테이블은 time을 기준으로 클러스터링되어 있으므로 time으로 필터링하는 것이 최적입니다:

1
2
3
4
5

-- find custom events that occurred after 04/15/2019 @ 7:02pm (UTC) i.e., timestamp=1555354920
SELECT *
FROM users_behaviors_customevent_shared
WHERE time > 1555354920
LIMIT 10;

sf_created_at을 사용하여 Snowflake 데이터 웨어하우스에 저장된 시간을 기준으로 이벤트를 필터링할 수도 있습니다. sf_created_at과 time은 동일하지 않지만 일반적으로 비슷하므로, 이 쿼리도 유사한 성능 특성을 가집니다:

1
2
3
4
5

-- find custom events that arrived in Snowflake after time 04/15/2019 @ 7:02pm (UTC)
SELECT *
FROM users_behaviors_customevent_shared
WHERE sf_created_at > to_timestamp_ntz('2019-04-15 19:02:00')
LIMIT 10;

Campaign 이름과 Canvas 이름은 이벤트 자체에 포함되어 있지 않습니다. 대신 체인지로그 테이블에 게시됩니다.

Campaign 체인지로그 테이블과 조인하여 Campaign 관련 이벤트의 Campaign 이름을 확인할 수 있습니다. 다음과 같은 쿼리를 사용합니다:

1
2
3
4
5
6

SELECT event.id, event.time, ccs.time, ccs.name, ccs.conversion_behaviors[event.conversion_behavior_index]
FROM USERS_CAMPAIGNS_CONVERSION_SHARED event
LEFT JOIN CHANGELOGS_CAMPAIGN_SHARED ccs
ON ccs.id = event.campaign_id
AND ccs.time < event.time
qualify row_number() over (partition by event.id ORDER BY ccs.time DESC) = 1;

주의할 사항은 다음과 같습니다:

  • 여기서는 Snowflake의 윈도우 함수가 사용됩니다.
  • LEFT JOIN을 사용하면 Campaign과 관련 없는 이벤트도 포함됩니다.
  • campaign_id가 있지만 Campaign 이름이 없는 이벤트가 보이는 경우, 데이터 공유가 제품으로 존재하기 전에 이름이 지정된 Campaign이 생성되었을 가능성이 있습니다.
  • CHANGELOGS_CANVAS_SHARED 테이블과 조인하는 유사한 쿼리를 사용하여 Canvas 이름을 확인할 수 있습니다.

Campaign 이름과 Canvas 이름을 모두 확인하려면 다음 서브쿼리를 사용해야 할 수 있습니다:

1
2
3
4
5
6
7
8
9
10
11

SELECT campaign_join.*, canvas.name AS canvas_name
FROM
(SELECT e.id AS event_id, e.external_user_id, e.time, e.user_id, e.device_id, e.sf_created_at,
    e.campaign_api_id, e.canvas_id, e.canvas_step_api_id,
    campaign.name AS campaign_name
  FROM USERS_MESSAGES_INAPPMESSAGE_CLICK_SHARED AS e
  LEFT JOIN CHANGELOGS_CAMPAIGN_SHARED AS campaign ON campaign.id = e.campaign_id
  WHERE e.time >= 1574830800 AND e.time <= 1575176399
  qualify row_number() over (partition by e.id ORDER BY campaign.time DESC) = 1) AS campaign_join
LEFT JOIN CHANGELOGS_CANVAS_SHARED AS Canvas ON canvas.id = campaign_join.canvas_id
qualify row_number() over (partition by campaign_join.event_id ORDER BY canvas.time DESC) = 1;

이 푸시 퍼널 쿼리를 사용하여 푸시 발송 원시 이벤트 데이터부터 전달 원시 이벤트 데이터, 열람 원시 이벤트 데이터까지 집계할 수 있습니다. 이 쿼리는 각 원시 이벤트가 일반적으로 별도의 테이블을 가지므로 모든 테이블을 어떻게 조인해야 하는지 보여줍니다:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

SELECT
    COUNT(DISTINCT send."ID" ) AS "users_messages_pushnotification_send.push_sent",
    COALESCE((COUNT(DISTINCT send."ID" )),0)-COALESCE((COUNT(DISTINCT bounce."ID" )),0) AS "users_messages_pushnotification_send.push_delivered",
    COUNT(DISTINCT open."ID" ) AS "users_messages_pushnotification_open.push_opens"
FROM users_messages_pushnotification_send_shared AS send
LEFT JOIN USERS_MESSAGES_PUSHNOTIFICATION_OPEN_shared AS open ON (send."USER_ID")=(open."USER_ID")
    AND
    (send."DEVICE_ID")=(open."DEVICE_ID")
    AND
    ((send."MESSAGE_VARIATION_API_ID")=(open."MESSAGE_VARIATION_API_ID")
    OR
    (send."CANVAS_STEP_API_ID")=(open."CANVAS_STEP_API_ID"))
LEFT JOIN users_messages_pushnotification_bounce_shared AS bounce ON (send."USER_ID")=(bounce."USER_ID")
    AND
    (send."DEVICE_ID")=(bounce."DEVICE_ID")
    AND
    ((send."MESSAGE_VARIATION_API_ID")=(bounce."MESSAGE_VARIATION_API_ID")
    OR
    (send."CANVAS_STEP_API_ID")=(bounce."CANVAS_STEP_API_ID"))
LIMIT 500;

이 일별 이메일 메시징 주기 쿼리를 사용하여 사용자가 이메일을 수신하는 간격을 분석할 수 있습니다.

예를 들어, 사용자가 하루에 이메일 두 통을 받았다면 0 "days since last received"에 해당합니다. 월요일에 한 통, 화요일에 한 통을 받았다면 1 "days since last received" 코호트에 해당합니다.

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

WITH email_messaging_cadence AS (WITH deliveries AS
      (SELECT TO_TIMESTAMP(time) AS delivered_timestamp,
      email_address AS delivered_address,
      message_variation_api_id AS d_message_variation_api_id,
      canvas_step_api_id AS d_canvas_step_api_id,
      campaign_api_id AS d_campaign_api_id,
      canvas_api_id AS d_canvas_api_id,
      id AS delivered_id,
      rank() over (partition by delivered_address ORDER BY delivered_timestamp ASC) AS delivery_event,
      min(delivered_timestamp) over (partition by delivered_address ORDER BY delivered_timestamp ASC) AS first_delivered,
      datediff(day, lag(delivered_timestamp) over (partition by delivered_address ORDER BY delivered_timestamp ASC), delivered_timestamp) AS diff_days,
      datediff(week, lag(delivered_timestamp) over (partition by delivered_address ORDER BY delivered_timestamp ASC), delivered_timestamp) AS diff_weeks
      from USERS_MESSAGES_EMAIL_DELIVERY_SHARED GROUP BY 1,2,3,4,5,6,7),      opens AS
      (SELECT DISTINCT email_address AS open_address,
      message_variation_api_id AS o_message_variation_api_id,
      canvas_step_api_id AS o_canvas_step_api_id
      FROM USERS_MESSAGES_EMAIL_OPEN_SHARED),      clicks AS
      (SELECT DISTINCT email_address AS click_address,
      message_variation_api_id AS c_message_variation_api_id,
      canvas_step_api_id AS c_canvas_step_api_id
      FROM USERS_MESSAGES_EMAIL_CLICK_SHARED)      SELECT * FROM deliveries
      LEFT JOIN opens
      ON (deliveries.delivered_address)=(opens.open_address)
      AND ((deliveries.d_message_variation_api_id)=(opens.o_message_variation_api_id) OR (deliveries.d_canvas_step_api_id)=(opens.o_canvas_step_api_id))
      LEFT JOIN clicks
      ON (deliveries.delivered_address)=(clicks.click_address)
      AND ((deliveries.d_message_variation_api_id)=(clicks.c_message_variation_api_id) OR (deliveries.d_canvas_step_api_id)=(clicks.c_canvas_step_api_id))
      )
SELECT
    email_messaging_cadence."DIFF_DAYS"  AS "email_messaging_cadence.days_since_last_received",
    (count(distinct email_messaging_cadence."OPEN_ADDRESS", email_messaging_cadence."O_MESSAGE_VARIATION_API_ID")
      +count(distinct email_messaging_cadence."OPEN_ADDRESS", email_messaging_cadence."O_CANVAS_STEP_API_ID"))/(COUNT(DISTINCT email_messaging_cadence."DELIVERED_ID" ))  AS "email_messaging_cadence.unique_open_rate"
FROM email_messaging_cadence GROUP BY 1
ORDER BY 1
LIMIT 500;

이 고유 이메일 클릭 수 쿼리를 사용하여 지정된 기간 내의 고유 이메일 클릭을 분석할 수 있습니다. 이를 계산하는 알고리즘은 다음과 같습니다:

  1. 키(app_group_id, message_variation_id, dispatch_id, email_address)를 기준으로 이벤트를 파티셔닝합니다.
  2. 각 파티션에서 이벤트를 시간순으로 정렬하며, 첫 번째 이벤트는 항상 고유 이벤트입니다.
  3. 이후의 모든 이벤트에 대해, 이전 이벤트로부터 7일 이상 경과한 경우 고유 이벤트로 간주합니다.

Snowflake의 윈도우 함수를 사용하여 이를 구현할 수 있습니다. 다음 쿼리는 최근 365일간의 모든 이메일 클릭을 반환하며, is_unique 열에서 어떤 이벤트가 고유한지 표시합니다:

1
2
3
4
5
6
7
8
9

SELECT id, app_group_id, message_variation_api_id, dispatch_id, email_address, time,
  ROW_NUMBER()       OVER (PARTITION BY app_group_id, message_variation_api_id, dispatch_id, email_address order by time) row_number,
  LAG(time, 1, time) OVER (PARTITION BY app_group_id, message_variation_api_id, dispatch_id, email_address order by time) previous_time,
  time - previous_time AS diff,
  IFF(row_number = 1, true, IFF(diff >= 7*24*3600, true, false)) AS is_unique
FROM USERS_MESSAGES_EMAIL_CLICK_SHARED
WHERE
  time < DATE_PART('EPOCH_SECOND', TO_TIMESTAMP(CURRENT_TIMESTAMP()))
  AND time > DATE_PART('EPOCH_SECOND', TO_TIMESTAMP(CURRENT_TIMESTAMP())) - 365*24*3600;

고유 이벤트만 확인하려면 QUALIFY 절을 사용합니다:

1
2
3
4
5
6
7
8
9
10

SELECT id, app_group_id, message_variation_api_id, dispatch_id, email_address, time,
  ROW_NUMBER()       OVER (PARTITION BY app_group_id, message_variation_api_id, dispatch_id, email_address order by time) row_number,
  LAG(time, 1, time) OVER (PARTITION BY app_group_id, message_variation_api_id, dispatch_id, email_address order by time) previous_time,
  time - previous_time AS diff,
  IFF(row_number = 1, true, IFF(diff >= 7*24*3600, true, false)) AS is_unique
FROM USERS_MESSAGES_EMAIL_CLICK_SHARED
WHERE
  time < DATE_PART('EPOCH_SECOND', TO_TIMESTAMP(CURRENT_TIMESTAMP()))
  AND time > DATE_PART('EPOCH_SECOND', TO_TIMESTAMP(CURRENT_TIMESTAMP())) - 365*24*3600
QUALIFY is_unique = true;

이메일 주소별로 그룹화된 고유 이벤트 수를 추가로 확인하려면 다음을 사용합니다:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

WITH unique_events AS(
  SELECT id, app_group_id, message_variation_api_id, dispatch_id, email_address, time,
  ROW_NUMBER()       OVER (PARTITION BY app_group_id, message_variation_api_id, dispatch_id, email_address order by time) row_number,
  LAG(time, 1, time) OVER (PARTITION BY app_group_id, message_variation_api_id, dispatch_id, email_address order by time) previous_time,
  time - previous_time AS diff,
  IFF(row_number = 1, true, iff(diff >= 7*24*3600, true, false)) AS is_unique
FROM USERS_MESSAGES_EMAIL_CLICK_SHARED
WHERE
  time < DATE_PART('EPOCH_SECOND', TO_TIMESTAMP(CURRENT_TIMESTAMP()))
  AND time > DATE_PART('EPOCH_SECOND', TO_TIMESTAMP(CURRENT_TIMESTAMP())) - 365*24*3600
QUALIFY is_unique = true)
SELECT email_address, count(*) AS count
FROM unique_events
GROUP BY email_address;

이 쿼리를 사용하여 Snowflake 이메일 열람 이벤트에서 고유 열람을 근사적으로 계산할 수 있습니다. 예를 들어, 대시보드의 고유 열람 열과 비교하여 검증하는 데 활용할 수 있습니다.

이 예시는 세 가지 수치를 반환합니다:

  • 고유 열람(7일 롤링): 7일 롤링 기간 동안의 고유 열람 수입니다.
  • 고유 열람(지정 기간 내): 지정된 기간 내의 고유 열람 수입니다. 해당 기간 이전에 발생한 열람과는 무관합니다.
  • 고유 열람(동일 기간 내 전달된 이메일 대상): 동일한 기간 내에 전달 이벤트도 발생한 이메일에 대한 고유 열람 수입니다(해당 기간에 전달된 메시지에 연결된 열람만 확인하려는 경우 유용합니다).
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

/*
    Set or comment out variables if not required. These are set per session.
    You can obtain the from and to dates from the Campaign/Canvas/Canvas step URL. These are the startDate and endDate parameters.

    For example, endDate=1656799199&startDate=1656194400

    To run, select all of this code block (CMD + A) and run to first set the necessary variables and run the SELECT statements below.
*/

SET fromDateTime = '1656194400';
SET toDateTime = '1656799199';
-- SET campaignID = '';
-- SET canvasID = '';
SET canvasStepID = '61b0a249745a0c5ac67a11d3';

SELECT
    'Unique Opens (over 7 days)' metric, COUNT(DISTINCT(user_id, dispatch_id)) total
FROM
    users_messages_email_open_shared
WHERE
/* Comment out where not required */
    -- campaign_id = $campaignID AND
    -- canvas_id = $canvasID AND
    canvas_step_id = $canvasStepID AND
    time BETWEEN $fromDateTime and $toDateTime AND
    not exists (select
                umeo.user_id
            from
                users_messages_email_open_shared umeo
            where
                umeo.user_id = users_messages_email_open_shared.user_id and
                umeo.canvas_step_id = users_messages_email_open_shared.canvas_step_id and
                to_timestamp(umeo.time) between dateadd(day, -7, to_timestamp(users_messages_email_open_shared.time)) and dateadd(second, -1, to_timestamp(users_messages_email_open_shared.time)))
UNION
SELECT
    'Unique Opens (during date window)' metric, COUNT(DISTINCT(user_id, dispatch_id)) total
FROM
    users_messages_email_open_shared
WHERE
/* Comment out where not required */
    -- campaign_id = $campaignID AND
    -- canvas_id = $canvasID AND
    canvas_step_id = $canvasStepID AND
    time BETWEEN $fromDateTime and $toDateTime
UNION
SELECT
    'Unique Opens (for emails delivered within same timeframe)' metric, COUNT(DISTINCT(user_id, dispatch_id)) total
FROM
    users_messages_email_open_shared
WHERE
/* Comment out where not required */
    -- campaign_id = $campaignID AND
    -- canvas_id = $canvasID AND
    canvas_step_id = $canvasStepID AND
    time BETWEEN $fromDateTime and $toDateTime AND
    EXISTS (select user_id
            from users_messages_email_delivery_shared umed
            where
                umed.user_id = users_messages_email_open_shared.user_id and
                umed.dispatch_id = users_messages_email_open_shared.dispatch_id and
                umed.time between $fromDateTime and $toDateTime);
New Stuff!