-
Notifications
You must be signed in to change notification settings - Fork 17
/
4_track_facts.view.lkml
56 lines (48 loc) · 1.36 KB
/
4_track_facts.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
# Determines event sequence numbers within session
view: track_facts {
derived_table: {
sql_trigger_value: select count(*) from ${sessions_trk.SQL_TABLE_NAME} ;;
sql: select t.anonymous_id
, t.timestamp
, t.event_id
, t.event AS event
, s.session_id
, t.looker_visitor_id
, row_number() over(partition by s.session_id order by t.timestamp) as track_sequence_number
from ${mapped_tracks.SQL_TABLE_NAME} as t
inner join ${sessions_trk.SQL_TABLE_NAME} as s
on t.looker_visitor_id = s.looker_visitor_id
and t.timestamp >= s.session_start_at
and (t.timestamp < s.next_session_start_at or s.next_session_start_at is null)
;;
}
dimension: event_id {
primary_key: yes
hidden: yes
sql: ${TABLE}.event_id ;;
}
dimension: event {
# hidden: true
sql: ${TABLE}.event ;;
}
dimension_group: timestamp {
type: time
hidden: yes
timeframes: [raw, time, date, week, month]
sql: ${TABLE}.timestamp ;;
}
dimension: session_id {
sql: ${TABLE}.session_id ;;
}
dimension: looker_visitor_id {
sql: ${TABLE}.looker_visitor_id ;;
}
dimension: sequence_number {
type: number
sql: ${TABLE}.track_sequence_number ;;
}
measure: count_visitors {
type: count_distinct
sql: ${looker_visitor_id} ;;
}
}