heap/event_flow.view.lkml at dev-erin-franz-mgwt · llooker/heap · GitHub

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
view: event_flow {
  derived_table: {
    sql_trigger_value: select count(*) from ${event_facts.SQL_TABLE_NAME} ;;
    sortkeys: ["sequence_number_for_event_flow"]
    distribution: "unique_event_id"
    sql: select      a.unique_event_id
            , a.event_id
            , a.session_id
            , a.sequence_number_for_event_flow
            , a.event_name
            , a.user_id
            , b.event_name as event_2
            , c.event_name as event_3
            , d.event_name as event_4
            , e.event_name as event_5

from ${event_facts.SQL_TABLE_NAME} a
left join ${event_facts.SQL_TABLE_NAME} b
on a.sequence_number_for_event_flow + 1 = b.sequence_number_for_event_flow
and a.user_id = b.user_id
and a.session_id = b.session_id
left join ${event_facts.SQL_TABLE_NAME} c
on a.sequence_number_for_event_flow + 2 = c.sequence_number_for_event_flow
and a.user_id = c.user_id
and a.session_id = c.session_id
left join ${event_facts.SQL_TABLE_NAME} d
on a.sequence_number_for_event_flow + 3 = d.sequence_number_for_event_flow
and a.user_id = d.user_id
and a.session_id = d.session_id
left join ${event_facts.SQL_TABLE_NAME} e
on a.sequence_number_for_event_flow + 4 = e.sequence_number_for_event_flow
and a.user_id = e.user_id
and a.session_id = e.session_id
 ;;
  }

  dimension: unique_event_id {
    primary_key: yes
    sql: ${TABLE}.unique_event_id ;;
    hidden: yes
  }

  dimension: session_id {
    hidden: yes
    sql: ${TABLE}.session_id ;;
  }

  dimension: sequence_number_for_event_flow {
    type: number
    hidden: yes
    sql: ${TABLE}.sequence_number_for_event_flow ;;
  }

  dimension: event_name {
    hidden: yes
    sql: ${TABLE}.event_name ;;
  }

  dimension: user_id {
    hidden: yes
    sql: ${TABLE}.user_id ;;
  }

  dimension: event_2 {
    label: "2nd Event"
    sql: ${TABLE}.event_2 ;;
  }

  measure: event_2_drop_off {
    label: "2nd Event Remaining Count"
    type: count

    filters: {
      field: event_2
      value: "-NULL"
    }
  }

  dimension: event_3 {
    label: "3rd Event"
    sql: ${TABLE}.event_3 ;;
  }

  measure: event_3_drop_off {
    label: "3rd Event Remaining Count"
    type: count

    filters: {
      field: event_3
      value: "-NULL"
    }
  }

  dimension: event_4 {
    label: "4th Event"
    sql: ${TABLE}.event_4 ;;
  }

  measure: event_4_drop_off {
    label: "4th Event Remaining Count"
    type: count

    filters: {
      field: event_4
      value: "-NULL"
    }
  }

  dimension: event_5 {
    label: "5th Event"
    sql: ${TABLE}.event_5 ;;
  }

  measure: event_5_drop_off {
    label: "5th Event Remaining Count"
    type: count

    filters: {
      field: event_5
      value: "-NULL"
    }
  }

  set: detail {
    fields: [unique_event_id, session_id, sequence_number_for_event_flow, event_name, user_id, event_2, event_3, event_4, event_5]
  }
}