forked from llooker/braze_message_engagement_block
-
Notifications
You must be signed in to change notification settings - Fork 0
/
users_messages_email_click.view.lkml
147 lines (128 loc) · 3.64 KB
/
users_messages_email_click.view.lkml
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
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
# Email Click Events
view: users_messages_email_click {
sql_table_name: PUBLIC.USERS_MESSAGES_EMAIL_CLICK ;;
dimension: id {
primary_key: yes
description: "unique id of this event"
hidden: yes
type: string
sql: ${TABLE}."ID" ;;
}
dimension: campaign_id {
description: "id of the campaign if from a campaign"
hidden: yes
type: string
sql: ${TABLE}."CAMPAIGN_ID" ;;
}
dimension: campaign_name {
description: "name of the campaign"
hidden: yes
type: string
sql: ${TABLE}."CAMPAIGN_NAME" ;;
}
dimension: canvas_id {
description: "id of the canvas if from a canvas"
hidden: yes
type: string
sql: ${TABLE}."CANVAS_ID" ;;
}
dimension: canvas_name {
description: "name of the canvas"
hidden: yes
type: string
sql: ${TABLE}."CANVAS_NAME" ;;
}
dimension: canvas_step_id {
description: "id of the step for this message if from a Canvas"
hidden: yes
type: string
sql: ${TABLE}."CANVAS_STEP_ID" ;;
}
dimension: canvas_variation_id {
description: "id of the Canvas variation the user is in if from a Canvas"
hidden: yes
type: string
sql: ${TABLE}."CANVAS_VARIATION_ID" ;;
}
dimension: email_address {
description: "email address for this event"
hidden: yes
type: string
sql: ${TABLE}."EMAIL_ADDRESS" ;;
}
dimension_group: email_clicked_time {
description: "timestamp of the email click event"
type: time
datatype: epoch
timeframes: [
raw,
time,
date,
day_of_week,
hour_of_day,
week,
month,
quarter,
year
]
sql: ${TABLE}."TIME" ;;
}
dimension: email_clicked_timezone {
description: "IANA timezone of the user at the time of the email click event"
hidden: yes
type: string
sql: ${TABLE}."TIMEZONE" ;;
}
dimension: external_user_id {
description: "External ID of the user"
hidden: yes
type: string
sql: ${TABLE}."EXTERNAL_USER_ID" ;;
}
dimension: message_variation_id {
description: "id of the message variation if from a campaign"
hidden: yes
type: string
sql: ${TABLE}."MESSAGE_VARIATION_ID" ;;
}
dimension: send_id {
description: "id of the message if specified for the campaign (See Send Identifier under REST API Parameter Definitions)"
hidden: yes
type: string
sql: ${TABLE}."SEND_ID" ;;
}
dimension: user_agent_click {
description: "description of the user's system and browser for the click event (only use with click measures)"
hidden: yes
type: string
sql: ${TABLE}."USER_AGENT" ;;
}
dimension: user_id {
description: "Braze id of the user"
hidden: yes
type: string
sql: ${TABLE}."USER_ID" ;;
}
measure: email_total_clicks {
description: "distinct count of email click events (counts users opening an email more than once)"
type: count_distinct
sql: ${TABLE}."ID" ;;
}
measure: email_total_click_rate {
description: "total clicks/emails delivered--may be over 100% at the user level"
type: number
value_format_name: percent_2
sql: ${email_total_clicks}/NULLIF(${users_messages_email_send.emails_sent},0) ;;
}
measure: email_unique_clicks {
description: "distinct count of times a user clicked an email (does not count the same person clicking an email more than once)"
type: count_distinct
sql: ${TABLE}."EMAIL_ADDRESS" ;;
}
measure: email_unique_click_rate {
description: "unique clicks/emails delivered--may be over 100% at the user level"
type: number
value_format_name: percent_2
sql:${email_unique_clicks}/NULLIF(${users_messages_email_send.emails_sent},0) ;;
}
}