Tweet
Tweet

Slide 1

Slide 1 text

Building Funnels with Google BigQuery @devxoul

Slide 2

Slide 2 text

Human

Slide 3

Slide 3 text

100 view 200 click 300 view 400 click 500 click 600 buy

Slide 4

Slide 4 text

100 view 200 click 300 view 400 click 500 click 600 buy

Slide 5

Slide 5 text

100 view 200 click 300 view 400 click 500 click 600 buy

Slide 6

Slide 6 text

100 view 200 click 300 view 400 click 500 click 600 buy

Slide 7

Slide 7 text

view 2 click 2 buy 1

Slide 8

Slide 8 text

Computer

Slide 9

Slide 9 text

time view click buy 100 100 200 200 300 300 400 400 500 500 600 600

Slide 10

Slide 10 text

time view click buy 100 100 200 100 200 300 300 200 400 300 400 500 300 500 600 300 500 600

Slide 11

Slide 11 text

time view click buy 100 100 200 100 200 300 300 200 400 300 400 500 300 500 600 300 500 600

Slide 12

Slide 12 text

time view click buy 100 100 200 100 200 300 300 200 400 300 400 500 300 500 600 300 500 600 origin next next

Slide 13

Slide 13 text

time view click buy 100 100 200 100 200 300 300 200 400 300 400 500 300 500 600 300 500 600 < origin next next

Slide 14

Slide 14 text

time view click buy 100 100 200 100 200 300 300 400 300 400 500 300 500 600 300 500 600 origin next next

Slide 15

Slide 15 text

time view click buy 100 100 200 100 200 300 300 400 300 400 500 300 500 600 300 500 600 origin next next

Slide 16

Slide 16 text

time view click buy 100 100 200 100 100 300 300 400 300 300 500 300 300 600 300 300 300 origin next next

Slide 17

Slide 17 text

time view click buy 100 100 200 100 100 300 300 400 300 300 500 300 300 600 300 300 300

Slide 18

Slide 18 text

time view click buy 100 100 200 100 100 300 300 400 300 300 500 300 300 600 300 300 300 100 300 100 300 300

Slide 19

Slide 19 text

view 2 click 2 buy 1

Slide 20

Slide 20 text

Query

Slide 21

Slide 21 text

100 view 200 click 300 view 400 click 500 click 600 buy Before (1)

Slide 22

Slide 22 text

time view click buy 100 100 200 200 300 300 400 400 500 500 600 600 After (1)

Slide 23

Slide 23 text

Query (1) #standardSQL SELECT time, (CASE event WHEN "view" THEN time END) AS view, (CASE event WHEN "click" THEN time END) AS click, (CASE event WHEN "buy" THEN time END)AS buy FROM events

Slide 24

Slide 24 text

time view click buy 100 100 200 200 300 300 400 400 500 500 600 600 Before (2)

Slide 25

Slide 25 text

time view click buy 100 100 200 100 200 300 300 200 400 300 400 500 300 500 600 300 500 600 After (2)

Slide 26

Slide 26 text

#standardSQL SELECT LAST_VALUE(view) OVER (ORDER BY time) AS view, LAST_VALUE(click) OVER (ORDER BY time) AS click, LAST_VALUE(buy) OVER (ORDER BY time) AS buy FROM query_1_result ⚠ Query (2)

Slide 27

Slide 27 text

time view click buy 100 100 200 200 300 300 400 400 500 500 600 600 ⚠ Query (2)

Slide 28

Slide 28 text

time view click buy 100 100 NULL NULL 200 200 300 300 400 400 500 500 600 600 ⚠ Query (2)

Slide 29

Slide 29 text

time view click buy 100 100 NULL NULL 200 NULL 200 NULL 300 300 400 400 500 500 600 600 ⚠ Query (2)

Slide 30

Slide 30 text

time view click buy 100 100 NULL NULL 200 NULL 200 NULL 300 300 NULL NULL 400 400 500 500 600 600 ⚠ Query (2)

Slide 31

Slide 31 text

time view click buy 100 100 NULL NULL 200 NULL 200 NULL 300 300 NULL NULL 400 NULL 400 NULL 500 500 600 600 ⚠ Query (2)

Slide 32

Slide 32 text

time view click buy 100 100 NULL NULL 200 NULL 200 NULL 300 300 NULL NULL 400 NULL 400 NULL 500 NULL 500 NULL 600 600 ⚠ Query (2)

Slide 33

Slide 33 text

time view click buy 100 100 NULL NULL 200 NULL 200 NULL 300 300 NULL NULL 400 NULL 400 NULL 500 NULL 500 NULL 600 NULL NULL 600 ⚠ Query (2)

Slide 34

Slide 34 text

time view click buy 100 100 NULL NULL 200 NULL 200 NULL 300 300 NULL NULL 400 NULL 400 NULL 500 NULL 500 NULL 600 NULL NULL 600 ⚠ Query (2)

Slide 35

Slide 35 text

#standardSQL SELECT LAST_VALUE(view IGNORE NULLS) OVER (ORDER BY time) AS view, LAST_VALUE(click IGNORE NULLS) OVER (ORDER BY time) AS click, LAST_VALUE(buy IGNORE NULLS) OVER (ORDER BY time) AS buy FROM query_1_result ✅ Query (2)

Slide 36

Slide 36 text

time view click buy 100 100 200 200 300 300 400 400 500 500 600 600 ✅ Query (2)

Slide 37

Slide 37 text

time view click buy 100 100 NULL NULL 200 200 300 300 400 400 500 500 600 600 ✅ Query (2)

Slide 38

Slide 38 text

time view click buy 100 100 NULL NULL 200 100 200 NULL 300 300 400 400 500 500 600 600 ✅ Query (2)

Slide 39

Slide 39 text

time view click buy 100 100 NULL NULL 200 100 200 NULL 300 300 200 NULL 400 400 500 500 600 600 ✅ Query (2)

Slide 40

Slide 40 text

time view click buy 100 100 NULL NULL 200 100 200 NULL 300 300 200 NULL 400 300 400 NULL 500 500 600 600 ✅ Query (2)

Slide 41

Slide 41 text

time view click buy 100 100 NULL NULL 200 100 200 NULL 300 300 200 NULL 400 300 400 NULL 500 300 500 NULL 600 600 ✅ Query (2)

Slide 42

Slide 42 text

time view click buy 100 100 NULL NULL 200 100 200 NULL 300 300 200 NULL 400 300 400 NULL 500 300 500 NULL 600 300 500 600 ✅ Query (2)

Slide 43

Slide 43 text

time view click buy 100 100 NULL NULL 200 100 200 NULL 300 300 200 NULL 400 300 400 NULL 500 300 500 NULL 600 300 500 600 ✅ Query (2)

Slide 44

Slide 44 text

time view click buy 100 100 200 100 200 300 300 200 400 300 400 500 300 500 600 300 500 600 Before (3)

Slide 45

Slide 45 text

time view click buy 100 100 200 100 200 300 300 200 400 300 400 500 300 500 600 300 500 600 < After (3)

Slide 46

Slide 46 text

#standardSQL SELECT view, (CASE WHEN view < click THEN click END) AS click, (CASE WHEN click < buy THEN buy END) AS buy FROM query_2_result Query (3)

Slide 47

Slide 47 text

time view click buy 100 100 200 100 200 300 300 400 300 400 500 300 500 600 300 500 600 Before (4)

Slide 48

Slide 48 text

time view click buy 100 100 200 100 100 300 300 400 300 300 500 300 300 600 300 300 300 After (4)

Slide 49

Slide 49 text

#standardSQL SELECT (CASE WHEN view IS NOT NULL THEN view END) AS view, (CASE WHEN click IS NOT NULL THEN view END) AS click, (CASE WHEN buy IS NOT NULL THEN view END) AS buy FROM query_3_result Query (4)

Slide 50

Slide 50 text

time view click buy 100 100 200 100 100 300 300 400 300 300 500 300 300 600 300 300 300 Before (5)

Slide 51

Slide 51 text

view 2 click 2 buy 1 After (5)

Slide 52

Slide 52 text

#standardSQL SELECT "view" AS step, COUNT(DISTINCT view) AS count FROM query_4_result UNION ALL SELECT "click", COUNT(DISTINCT click) FROM query_4_result UNION ALL SELECT "buy", COUNT(DISTINCT buy) FROM query_4_result Query (4)

Slide 53

Slide 53 text

Real World

Slide 54

Slide 54 text

user time view click buy A 100 100 B 200 200 B 300 300 A 400 400 A 500 500 A 600 600

Slide 55

Slide 55 text

user time view click buy A 100 100 B 200 200 B 300 300 A 400 400 A 500 500 A 600 600

Slide 56

Slide 56 text

user time view click buy A 100 100 B 200 200 B 300 300 A 400 400 A 500 500 A 600 600

Slide 57

Slide 57 text

view 2 click 2 1 buy 1

Slide 58

Slide 58 text

#standardSQL SELECT LAST_VALUE(view IGNORE NULLS) OVER (ORDER BY time) AS view, LAST_VALUE(click IGNORE NULLS) OVER (ORDER BY time) AS click, LAST_VALUE(buy IGNORE NULLS) OVER (ORDER BY time) AS buy FROM query_1_result Back to Query (2)

Slide 59

Slide 59 text

user time view click buy A 100 100 B 200 200 B 300 300 A 400 400 A 500 500 A 600 600 Before (2)

Slide 60

Slide 60 text

user time view click buy A 100 100 B 200 100 200 B 300 300 200 A 400 300 400 A 500 300 500 A 600 300 500 600 ⚠ After (2)

Slide 61

Slide 61 text

user time view click buy A 100 100 B 200 200 B 300 300 200 A 400 100 400 A 500 100 500 A 600 100 500 600 ✅ After (2)

Slide 62

Slide 62 text

#standardSQL SELECT LAST_VALUE(view IGNORE NULLS) OVER (PARTITION BY user ORDER BY time) AS view, LAST_VALUE(click IGNORE NULLS) OVER (PARTITION BY user ORDER BY time) AS click, LAST_VALUE(buy IGNORE NULLS) OVER (PARTITION BY user ORDER BY time) AS buy FROM query_1_result ✅ Query (2)

Slide 63

Slide 63 text

user time view click buy A 100 100 A 400 400 A 500 500 A 600 600 PARTITION BY user user time view click buy A 100 100 B 200 200 B 300 300 A 400 400 A 500 500 A 600 600 user time view click buy B 200 200 B 300 300

Slide 64

Slide 64 text

user time view click buy A 100 100 A 400 100 400 A 500 100 500 A 600 100 500 600 LAST_VALUE & PARTITION BY user time view click buy A 100 100 B 200 200 B 300 300 A 400 400 A 500 500 A 600 600 user time view click buy B 200 200 B 300 300 200

Slide 65

Slide 65 text

user time view click buy A 100 100 A 400 100 400 A 500 100 500 A 600 100 500 600 user time view click buy B 200 200 B 300 300 200 user time view click buy A 100 100 B 200 200 B 300 300 200 A 400 100 400 A 500 100 500 A 600 100 500 600 LAST_VALUE & PARTITION BY

Slide 66

Slide 66 text

user time view click buy A 100 100 B 200 200 B 300 300 200 A 400 100 400 A 500 100 500 A 600 100 500 600

Slide 67

Slide 67 text

user time view click buy A 100 100 B 200 200 B 300 300 200 A 400 100 400 A 500 100 500 A 600 100 500 600 < <

Slide 68

Slide 68 text

user time view click buy A 100 100 B 200 B 300 300 A 400 100 400 A 500 100 500 A 600 100 500 600

Slide 69

Slide 69 text

user time view click buy A 100 100 B 200 B 300 300 A 400 100 100 A 500 100 100 A 600 100 100 100

Slide 70

Slide 70 text

user time view click buy A 100 100 B 200 B 300 300 A 400 100 100 A 500 100 100 A 600 100 100 100

Slide 71

Slide 71 text

user time view click buy A 100 100 B 200 B 300 300 A 400 100 100 A 500 100 100 A 600 100 100 100

Slide 72

Slide 72 text

user time view click buy A 100 100 B 200 B 300 300 A 400 100 100 A 500 100 100 A 600 100 100 100 100 300 100 100

Slide 73

Slide 73 text

view 2 click 1 buy 1

Slide 74

Slide 74 text

Thank you Complete Query: https://git.io/vpzbF