The answers to these questions apply directly to product changes and can be determined in PostgreSQL now that it supports lateral joins.Without lateral joins, we would need to resort to PL/pgSQL to do this analysis. How do these conversion rates change over time or with different A/B test variants? We might follow up with:ĭoes using the demo increase the likelihood of a signup?ĭo users who discover our homepage via an ad convert with the same likelihood as users from other sources? From here, the expressive power of PostgreSQL allows us to drill down on these results and thoroughly analyze the performance of our website. This gives us the three-step conversion funnel from viewing the homepage to using the demo within a week to entering the credit card within a week of that.
We can add intermediate steps to this funnel with more lateral joins to evaluate which portions of our flow we should focus on improving.** Let’s add a use_demo step between viewing the homepage and entering a credit card. If we aggregate over the numerical columns, we get a tidy summary of this conversion funnel: Otherwise, the subqueries would be evaluated independently and there would be no way to access results from one when evaluating the other.Then we wrap the whole thing in a select, which returns something like this:īecause this is a LEFT JOIN, the query still produces result rows for users with no matching enter_credit_card event, as long as there is a view_homepage event. Because this is a lateral join, our subquery can make reference to the view_homepage_time results from the previous subquery. I.e., for each user, get the first time he or she performed the enter_credit_card event within two weeks of view_homepage_time. This is equivalent to taking the query below and running it for each resulting row: Then our lateral join allows us to iterate over each resulting row and perform a parametrized version of the next subquery. That is, get the initial time each user did a view_homepage event. Nobody likes 30-line SQL queries, so let’s break this down into pieces. But, in 9.3, we can use a lateral join to compute this in one efficient query, with no extensions or PL/pgSQL. If we were using an older version of PostgreSQL, we might write some custom functions in PL/pgSQL, PostgreSQL’s builtin procedural language. We’ll assume that we’ve instrumented our frontend to log events along this flow and that all of the data lives in the event table specified above.*Īs an initial question, let’s figure out how many people view our homepage and what percentage of them enter a credit card within two weeks of that initial homepage view. The first step is to figure out where we’re losing users in our conversion funnel.Īn example conversion funnel between four steps in a signup flow. At Heap, these properties might include the DOM hierarchy of a click, the window title, the session referrer, and so forth.Let’s say we want to optimize our landing page to increase signups. What can we do with this?Ĭonsider a table of click events with the following schema:Įach event is associated with a user and has an ID, a timestamp, and a JSON blob with the event’s properties. Loosely, it means that a LATERAL join is like a SQL foreach loop, in which PostgreSQL will iterate over each row in a result set and evaluate a subquery using that row as a parameter. This is repeated for each row or set of rows from the column source table(s). The resulting row(s) are joined as usual with the rows they were computed from. When a FROM item contains LATERAL cross-references, evaluation proceeds as follows: for each row of the FROM item providing the cross-referenced column(s), or set of rows of multiple FROM items providing the columns, the LATERAL item is evaluated using that row or row set’s values of the columns. (Without LATERAL, each sub- SELECT is evaluated independently and so cannot cross-reference any other FROM item.) This allows the sub- SELECT to refer to columns of FROM items that appear before it in the FROM list. The LATERAL key word can precede a sub- SELECT FROM item. The best description in the documentation comes at the bottom of the list of FROM clause options : Interested in learning more about Heap Engineering? Meet our team to get a feel for what it’s like to work at Heap! What is a LATERAL join? In this post, I’ll walk through a conversion funnel analysis that wouldn’t be possible in PostgreSQL 9.2. PostgreSQL 9.3 has a new join type! Lateral joins arrived without a lot of fanfare, but they enable some powerful new queries that were previously only tractable with procedural code.
0 kommentar(er)
