E-Commerce company redesigning the homepage ➔ Potential questions: ➔ What’s the last page people visit before coming to the homepage? ➔ What are all the product pages people see after the homepage? ➔ How many people who visit the homepage go on to buy something? ➔ What if we limit that to buy within two days?
Some other “first this than that” questions ➔ Which salmon migrated to station 1 then station 3 before station 2? ➔ What drugs did people take in the last month before starting drug X? ➔ What was the last ad clicked before registering? ➔ What companies had their stock hit $100 per share then drop to $40?
Old workflow ➔ When was each user’s first landing and their first registration afterward? 1. Filter landed for the first row per user 2. Left join with registered on user_id 3. Filter for timestamp.y > timestamp.x or NA 4. Filter for first row of timestamp.y
Old workflow ➔ Who registered the first time ever after their last landing? 1. Filter landed for the last row per user 2. Filter registered for first row per user 3. Left join with registered on user_id 4. Filter for timestamp.y > timestamp.x or NA
after_join() ➔ When was each user’s first landing and their first registration afterward? ➔ Table 1 ➔ Table 2 ➔ User column names(s) ➔ Time column name(s) ➔ Type of afterjoin
after_join() structure ➔ When was each user’s first landing and their first registration afterward? ➔ Table 1 ➔ Table 2 ➔ User column names(s) ➔ Time column name(s) ➔ Type of afterjoin
16 types of funnels Table 1 type Table 2 type First (ever) First (ever) Last (ever) Last (ever) Any (all) Any (all) Lastbefore Firstafter Any combination of: