Build Funnels with Google BigQuery

https://www.slideshare.net/devxoul/building-funnels-with-google-bigquery

Transcript

1. Building Funnels with Google BigQuery @devxoul
2. Human
3. 100 view 200 click 300 view 400 click 500 click 600 buy
4. 100 view 200 click 300 view 400 click 500 click 600 buy
5. 100 view 200 click 300 view 400 click 500 click 600 buy
6. 100 view 200 click 300 view 400 click 500 click 600 buy
7. view 2 click 2 buy 1
8. Computer
9. time view click buy 100 100 200 200 300 300 400 400 500 500 600 600
10. time view click buy 100 100 200 100 200 300 300 200 400 300 400 500 300 500 600 300 500 600
11. time view click buy 100 100 200 100 200 300 300 200 400 300 400 500 300 500 600 300 500 600
12. 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
13. 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
14. time view click buy 100 100 200 100 200 300 300 400 300 400 500 300 500 600 300 500 600 origin next next
15. time view click buy 100 100 200 100 200 300 300 400 300 400 500 300 500 600 300 500 600 origin next next
16. time view click buy 100 100 200 100 100 300 300 400 300 300 500 300 300 600 300 300 300 origin next next
17. time view click buy 100 100 200 100 100 300 300 400 300 300 500 300 300 600 300 300 300
18. 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
19. view 2 click 2 buy 1
20. Query
21. 100 view 200 click 300 view 400 click 500 click 600 buy Before (1)
22. time view click buy 100 100 200 200 300 300 400 400 500 500 600 600 After (1)
23. 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
24. time view click buy 100 100 200 200 300 300 400 400 500 500 600 600 Before (2)
25. time view click buy 100 100 200 100 200 300 300 200 400 300 400 500 300 500 600 300 500 600 After (2)
26. #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)
27. time view click buy 100 100 200 200 300 300 400 400 500 500 600 600 ⚠ Query (2)
28. time view click buy 100 100 NULL NULL 200 200 300 300 400 400 500 500 600 600 ⚠ Query (2)
29. time view click buy 100 100 NULL NULL 200 NULL 200 NULL 300 300 400 400 500 500 600 600 ⚠ Query (2)
30. time view click buy 100 100 NULL NULL 200 NULL 200 NULL 300 300 NULL NULL 400 400 500 500 600 600 ⚠ Query (2)
31. 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)
32. 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)
33. 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)
34. 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)
35. #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)
36. time view click buy 100 100 200 200 300 300 400 400 500 500 600 600 ✅ Query (2)
37. time view click buy 100 100 NULL NULL 200 200 300 300 400 400 500 500 600 600 ✅ Query (2)
38. time view click buy 100 100 NULL NULL 200 100 200 NULL 300 300 400 400 500 500 600 600 ✅ Query (2)
39. time view click buy 100 100 NULL NULL 200 100 200 NULL 300 300 200 NULL 400 400 500 500 600 600 ✅ Query (2)
40. 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)
41. 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)
42. 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)
43. 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)
44. time view click buy 100 100 200 100 200 300 300 200 400 300 400 500 300 500 600 300 500 600 Before (3)
45. time view click buy 100 100 200 100 200 300 300 200 400 300 400 500 300 500 600 300 500 600 < After (3)
46. #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)
47. time view click buy 100 100 200 100 200 300 300 400 300 400 500 300 500 600 300 500 600 Before (4)
48. time view click buy 100 100 200 100 100 300 300 400 300 300 500 300 300 600 300 300 300 After (4)
49. #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)
50. time view click buy 100 100 200 100 100 300 300 400 300 300 500 300 300 600 300 300 300 Before (5)
51. view 2 click 2 buy 1 After (5)
52. #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)
53. Real World
54. user time view click buy A 100 100 B 200 200 B 300 300 A 400 400 A 500 500 A 600 600
55. user time view click buy A 100 100 B 200 200 B 300 300 A 400 400 A 500 500 A 600 600
56. user time view click buy A 100 100 B 200 200 B 300 300 A 400 400 A 500 500 A 600 600
57. view 2 click 2 1 buy 1
58. #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)
59. 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)
60. 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)
61. 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)
62. #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)
63. 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
64. 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
65. 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
66. 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
67. 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 < <
68. 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
69. 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
70. 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
71. 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
72. 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
73. view 2 click 1 buy 1
74. Thank you Complete Query: https://git.io/vpzbF