<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Motif Analytics on Medium]]></title>
        <description><![CDATA[Stories by Motif Analytics on Medium]]></description>
        <link>https://medium.com/@motifanalytics?source=rss-a36de1cfc346------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*AJpWhtRLUV9N2aKwdnlIdA.png</url>
            <title>Stories by Motif Analytics on Medium</title>
            <link>https://medium.com/@motifanalytics?source=rss-a36de1cfc346------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 06:05:33 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@motifanalytics/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Sunsetting external access to Motif]]></title>
            <link>https://motifanalytics.medium.com/sunsetting-external-access-to-motif-29486c8a86a6?source=rss-a36de1cfc346------2</link>
            <guid isPermaLink="false">https://medium.com/p/29486c8a86a6</guid>
            <dc:creator><![CDATA[Motif Analytics]]></dc:creator>
            <pubDate>Thu, 10 Apr 2025 17:34:28 GMT</pubDate>
            <atom:updated>2025-04-10T17:34:28.633Z</atom:updated>
            <content:encoded><![CDATA[<p>We’re writing today with a heavy heart to announce sunsetting external access to Motif.</p><p>Our vision has been to unlock interactive exploration of user behavior through sequence analytics. Over the past three years we pushed the boundaries of what processing and visualizing sequence data can look like. However, we have seen strong headwinds as the analytics sector is going through a consolidation phase, making it hard to sustain an independent sequence analytics product.</p><p>We have learned that Motif’s approach delivers the most value to large sophisticated organizations with complex event data and strong data teams, so we have decided to take Motif’s approach and team talents to a large tech company.</p><p>We’d like to thank all our amazing users, who believed in our vision and helped shape our sequence analytics journey.</p><p>We are looking forward to sharing our learnings about building Motif in separate posts.</p><p>Thank you,</p><p>Misha &amp; Theron</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=29486c8a86a6" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Causal discovery for product analytics]]></title>
            <link>https://motifanalytics.medium.com/causal-discovery-for-product-analytics-302ee16c0718?source=rss-a36de1cfc346------2</link>
            <guid isPermaLink="false">https://medium.com/p/302ee16c0718</guid>
            <dc:creator><![CDATA[Motif Analytics]]></dc:creator>
            <pubDate>Sun, 23 Feb 2025 23:16:44 GMT</pubDate>
            <atom:updated>2025-02-24T03:39:29.698Z</atom:updated>
            <content:encoded><![CDATA[<p>by <a href="https://www.linkedin.com/in/seanjtaylor/">Sean J. Taylor</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*MFfx7glBJLE4a29-.png" /></figure><p>Two years ago, <a href="https://motifanalytics.medium.com/bringing-more-causality-to-analytics-d378108bb15">I wrote about bringing more causality to analytics</a>. My observation was that product analytics is filled with causal questions but that causal modeling and causal inference methods were not particularly popular. Since then, we have talked to hundreds of data scientists and analysts about their challenges and those conversations have further convinced me that we can usher in a big change in how we ask and answer analytics questions.</p><p>Today’s post is about the progress we have made in fulfilling that original vision: <em>how can we leverage techniques from causal inference to learn more from our data and make better business decisions?</em> At Motif we have some exciting developments to share which leverage GLEAM, <a href="https://motifanalytics.medium.com/foundation-ai-models-for-product-analytics-8905ed524fb2">our foundation model for event sequence data</a>. Before we get into our solution, let me recap the problem we have been trying to solve.</p><h3>Forward and reverse causal questions</h3><p>In my previous post, I introduced the idea that <em>causal analysis</em> tasks can be categorized based on whether the cause and outcome are defined or undefined (at the time I called them “known” or “unknown”). I proposed a 2x2 framework, now shown in terms of DAGs:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1020/0*0VcRzyPvA9Q1H7fm.png" /></figure><p>The primary challenge with the three “undefined” quadrants is that they require updating our <a href="https://en.wikipedia.org/wiki/Directed_acyclic_graph#Causal_structures">causal DAG</a>, not estimating parameters of a DAG we have already specified. Every data team should strive to provide a more complete view of the process that generates value (and negative outcomes) and that means gradually improving causal models over time.</p><p>In our discussions with data scientists and analysts this framework has held up well in categorizing their work. I can roughly summarize our findings here (skipping quadrant 4 which I view as enabled by addressing quadrants 2 and 3):</p><h3>Quadrant 1: Experimentation is the gold standard</h3><p>The comfort zone for analysts is the lower-left quadrant, when cause and outcome are both defined. The task is particularly routine when experiments are run and they can use A/B testing tools like <a href="https://www.geteppo.com/">Eppo</a> and <a href="https://statsig.com/">Statsig</a>. When experiments can’t be run, some data scientists turn to weaker evidence or try to use causal inference strategies, such as synthetic control methods.</p><h3>Quadrant 2: Experiments aren’t always straightforward</h3><p>Many teams we have talked to identified Quadrant 2 is a pain point. It is common for organizations to struggle with tests or launches that don’t go as anticipated, leading to exploratory analysis intended to understand the story. This can become a time-consuming project because experimentation platforms generally rely on a limited set of pre-configured metrics may not cover every possible outcome of interest.</p><p>Motif is already helping teams with these kinds of analyses. <a href="https://docs.motifanalytics.com/use-cases/root-cause-analysis">Sequence comparisons</a> <a href="https://docs.motifanalytics.com/use-cases/experiment-analysis">allow users to investigate effects</a> of launches and experiments in a more flexible and expansive way than experimentation tools, frequently yielding important insights and leading to faster turnaround to followup experiments.</p><h3>Quadrant 3: What drives my key product outcomes?</h3><p>We discovered strong demand for tackling the quadrant 3. Analysts commonly frame these tasks as <em>finding opportunities</em> for improving signup, retention, or revenue. Often they would like to generate ideas and help prioritize product and engineering work; other times they are searching for intermediate metrics that can be used as milestones of success. In either case, there are no standard tools or approaches available.</p><p>Most causal inference methods are tasked with measuring effects of already-hypothesized causes rather than uncovering new causes. In 2013, two titans of causal inference, Andrew Gelman and Guido Imbens, <a href="http://www.stat.columbia.edu/~gelman/research/unpublished/reversecausal_13oct05.pdf">called this problem “reverse causal questions.”</a> In that paper they share an excellent quote from <a href="https://www.kaiserfung.com/">Kaiser Fung</a> summarizing the situation:</p><blockquote><em>A lot of real world problems are of the reverse causality type, and it’s an embarrassment for us to ignore them… Most business problems are reverse causal… If sales amount suddenly drops, then the executives will want to know what caused the drop. By a process of elimination, one can drill down to a small set of plausible causes. This is all complex work that gives approximate answers.</em></blockquote><p>Kaiser’s example introduces an additional subtlety — some reverse causal questions are about what tends to cause an outcome of interest (e.g. sales) while others are about what causes a specific change in that outcome that has been observed (e.g. a drop in sales). They are obviously related and answering either requires a method for discovering and prioritizing new causes in the causal DAG.</p><h3>Discovering new causes enables product improvement</h3><p>Hopefully I have convinced you that causal discovery is a task with a lot of room for improvement. Here is the hero story for how learning a new cause yields an impactful product change. There are three main phases:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*cqnkIGhSUQCzs12g.png" /></figure><h4>Phase 1: Discover a new potential cause</h4><p>In this phase, we generate and select a new hypothesis for a cause of the outcome we’re interested in. Graphically, the cause is a new node in the DAG that potentially has a directed edge toward our outcome node.</p><p><strong>Example</strong>: In the free version of Motif, we provide example data sets but also allow users to work with data locally (in their browser) without ever transmitting data to us. We observed that users who loaded their own data were more likely to use the product again in the following week. This is a plausible cause because direct experience with familiar data may help users learn the value of the tool more quickly. However, there is a clear confounding story as well because users with higher intent are more likely to jump through the hoop of gathering and loading data.</p><h4>Phase 2: Generate ideas for product changes that act on that cause</h4><p>In the second phase, we use our imaginations to come up with hypothetical interventions which can act on the cause we’ve discovered. This is creative work because the node in the DAG does not exist in the current data; it is something we will build or reconfigure in order to act on the hypothesized mechanism. In causal inference terminology, we can think of this intervention as an instrument which acts on the cause we’ve identified.</p><p><strong>Example</strong>: We brainstormed ways to reduce friction for loading data and to encourage users to do so. A variety of ideas emerged, including redesigning our data loading form and changing our onboarding dialogs to nudge users to bring their own data. We even considered removing the demo data sets as a very strong encouragement.</p><h4>Phase 3: Run an experiment and validate the hypothesis</h4><p>The final phase is to validate the hypotheses we generated. The improvement in the outcome relies on two things being true:</p><ul><li><strong>First stage</strong>: the product change we developed increases the probability of the hypothesized cause.</li><li><strong>Second stage</strong>: the hypothesized cause has an practically significant effect on the outcome of interest.</li></ul><p>These two stages mirror the steps in estimating an <a href="https://en.wikipedia.org/wiki/Instrumental_variables_estimation#Interpretation_as_two-stage_least_squares">instrumental variables model</a>, which is because this is exactly the setting we are in: we have introduced an exogenous instrument which affects the outcome of interest via the causal variable we have discovered. The hope is that the instrument is strong enough and also that the causal relationship we identified in Phase 1 is strong enough to yield benefits.</p><p><strong>Example</strong>: We launched simplified data loading in Motif. Data loading rates went up by about 100% and retention improved as a result. (This is a bit of creative storytelling, we did not actually run an experiment because we are moving pretty quickly!)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*ywxwRMPxK_owsu7r.png" /></figure><h3>How can we reliably discover new causes?</h3><p>If you agree with the process I laid out in the previous section, you might wonder how we can make that story play out faster and more reliably.</p><ul><li><strong>Phase 2</strong> is generally the purview of PMs, designers, and engineers, who are very skilled at the task of developing and implementing useful product improvements.</li><li><strong>Phase 3</strong> is well addressed by experimentation. There is a reliable technology for testing causal hypotheses. Across the industry, thousands of data scientists, analysts, designers, and product managers have converged on using experiments to evaluate product changes and make decisions in a principled way. We have industry-wide conviction about this approach because it is well-designed to produce business value and solves an ongoing decision problem teams face.</li></ul><p>That leaves us with <strong>Phase 1</strong>: <em>the biggest opportunity to use data to improve products is to discover and prioritize hypotheses which lead to successful product changes with high probability</em>. For analytics teams which have invested in rich instrumentation and data quality, it can be frustrating that there are no standard approaches for turning that data into value to their cross-functional partners.</p><p>In practice, the most common approach is (nebulously) exploratory data analysis (EDA) intended to help generate new insights. Indeed providing “actionable insights” is described as the specific value proposition of BI software beyond their reporting capabilities, as if somehow the same slicing, dicing, and aggregation could do everything that’s needed.</p><p>Practitioners need a reliable procedure for generating useful hypotheses from data, but have no rigorous, scientific approach to this task. This leads to a systematic underinvestment in causal discovery work. Exploratory projects can drag on for a long time and end without strong conclusions so they are not given many resources.</p><p><em>How can we create a process for generating hypotheses that is as reliable as experimentation is for testing hypotheses?</em></p><h4>Approach #1: Group by, aggregate</h4><p>The most common approach to hypothesis generation (and to product analytics in general) is comparing groups of users across attributes that are usually convenient to define and measure.</p><ul><li>Country</li><li>Device or application</li><li>Cohort based on signup date</li><li>Referral source (like search, organic, ads, etc)</li></ul><p>Breakdowns by (slow moving or never-changing) user-level attributes are easy to create in most BI tools and explain significant variation in outcomes of interest. We can think of this breakdown as estimating a structural model:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/852/1*Yz8UrzEM85yf7wMtkM9PUg.png" /></figure><p>There are limitations inherent in this approach:</p><ol><li>There are only so many variables readily available for breakdowns. They are probably not particularly rich because they are unchanging user-level facts. There is a low ceiling for this type of model.</li><li>The model does not predict any useful counterfactuals. Even if we observe that users in a specific cohort are more likely to sign up for a premium plan, this does not straightforwardly imply a course of action.</li><li>The estimated relationships have no clear causal relationship. For instance, we could attempt to recruit more users from a high-performing country, but it is unclear how that would scale and whether the difference would persist if we did do that.</li></ol><p>Adding a time dimension can increase the richness of the structural model:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/634/1*ENRM2PGsE3SbQ6kwZz8i_A.png" /></figure><p>We can sometimes spot changes in time series data in particular subgroups, suggesting a cause was introduced at a specific time, which can be reasonable pointer for discovering a cause. However this method can generate more questions than answers — we then have to investigate what else changed at that time and form theory about why it may have affected only one group of users.</p><h4>Approach #2: Behavioral correlations</h4><p>A great example of gaining an insight through a behavioral correlation comes from <a href="https://amplitude.com/case-studies/calm">an Amplitude case study about a valuable insight from the Calm app</a>. They write:</p><p>Calm built a Daily Reminder feature that allowed users to set a reminder for their daily meditation session, but the reminder feature was buried deep on the Settings page of the app. Very few users, less than 1%, were finding and using reminders. To their surprise, <strong>they found an almost 3x increase in retention for users who set Daily Reminders</strong>. With such a small sample size of users, they couldn’t know whether this was a causal relationship. It could be that the power users of their app, who would have been well-retained anyways, were the ones digging into the Settings page and finding the Reminders feature.</p><p>This case study fits our causal discovery story quite well, and they later explain that they ran an experiment to confirm the generated hypothesis. The story is a bit unclear about how the behavioral correlation was found, but we can imagine the approach was to segment users into groups who used a feature and those who did not, and compare them on some key outcome (e.g. retention). The structural model is roughly:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/762/1*viOzNiCtU3S4dbqnirgv3w.png" /></figure><p>This model addresses a key limitation in Approach 1: the hypothesized cause is a behavior captured by event logging rather than a more persistent user property. However, it comes with its own set of potential limitations:</p><ol><li>How did they decide to look at the correlation for this particular feature? Many apps have quite a rich set of functionality, were they going on a hunch or did Amplitude surface this proactively from among a large set of hypotheses?</li><li>Is the correlation they estimated really a causal effect? Usage of almost every feature will be correlated with retention, so what makes this particular one special? (Conveniently in the case study the correlation turns out to be unbiased 🤯)</li></ol><p>In the next section, I explain how we address these limitations.</p><h4>Approach #3: Correlations adjusted for prior behaviors</h4><p>Finding correlations between behaviors and outcomes in product data is almost a given, because the following graph is often a good representation of the causal structure:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*NwwfhV2-quv7NKyW.png" /></figure><p>The unobserved confounder here will usually bias the estimated correlation between feature usage and outcome. The opportunity we have is to adjust for the user’s earlier behaviors, which can mitigate the bias. In an ideal world, the pre-feature usage behaviors are a perfect measure of the latent intent variable, in which case adjusting for them will block the backdoor path and eliminate the bias. Experienced students of causal inference will know that there are other possible DAGs here, including one where this adjustment <em>increases</em> bias, though in reasonable simulations we have run, the bias reduction is generally far larger than the amplification.</p><h4>Adjusting for pre-event behaviors automatically</h4><p>The key to automatically adjusting the correlations and minimizing bias comes from the <a href="https://motifanalytics.medium.com/foundation-ai-models-for-product-analytics-8905ed524fb2">foundation models for event sequences we introduced earlier this year</a>. Recall that these models are trained with the same self-supervised objective for generative AI models of text. So for any event (or pattern of events) we view as a cause, the model can estimate the probability of that happening at any position in the event sequence.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*qEmZkJgAQfIPb2Nd.png" /></figure><p>We can use this estimate to take events in the “treatment” group and assign them a probability of treatment (propensity score), as well as to construct a control group — positions in event sequences where the treatment could have occurred but didn’t:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*brXqsUlMtOHmMK-j.png" /></figure><p>In a world without the GLEAM estimates, we would have a treatment group but we would have to use a non-representative group of users as control — those who are systematically less likely to have exhibited the cause we are interested in. By balancing the propensity score distribution, we can achieve a substantial reduction in bias, mimicking as closely as possible the thought experiment where we intervene to prevent the cause from happening for some users.</p><p>The intuition bias reduction achieved by this procedure is captured in the following diagram:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*NylNyY5nbejrwruV.png" /></figure><p>Note that the treatment group estimate does not change from this procedure. We are constructing a more realistic control group which will tend to move their estimate closer to the treatment group. We are targeting the ATT (average treatment effect on the treated) in this setting. For some treatments, we are unable to find enough users with similar propensity scores to the treatment group, and in those cases the effect estimates are high variance and it is natural to ignore them.</p><h3>A world with automatic causal inference</h3><p>As we have seen, we can use the propensity scores estimated from a transformer architecture to reduce the bias from confounding and this can be done automatically. The assumptions aren’t always realistic, but we have a powerful tool that can adjust data for prior events in a very flexible way (without feature engineering or even training a new model). Let’s take a look at what it looks like in practice to wield this capability to generate hypotheses.</p><h4>Empirical example: e-commerce activity</h4><p>Since we can’t share results from customer data, we have been experimenting with a <a href="https://github.com/coveooss/SIGIR-ecom-data-challenge">simple e-commerce data set</a> shared as <a href="https://sigir-ecom.github.io/">2021 SIGIR eCom</a> Data Challenge (h/t <a href="https://jacopotagliabue.it/">Jacopo</a>), which is one of the best public data sets we’ve been able to find for evaluating this approach. You can think of a simple web shop, where users can search for or browse items, run searches, and add items to their cart that they want to purchase.</p><p>Interestingly, that SIGIR competition included an intent-prediction task: a model is shown a session containing an add-to-cart event, and it is asked to predict whether the item will be bought before the end of the session. One can think of our causal discovery task as pivoting this from a predictive task to an interpretability task — to which events can we attribute the purchase outcome? Data scientists are used to using something like <a href="https://christophm.github.io/interpretable-ml-book/pdp.html">partial dependence plots</a> to show how one feature influences the prediction, and our approach adapts this concept to the sequential setting.</p><p>This figure shows the distribution of probability scores from GLEAM for each of the main events, broken down into what I think of as “factual” and “counterfactual” cases.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Nbb7QQpsRQlr5quq.gif" /></figure><p>The differences between these distributions is a source of confounding — the sequences where the event occurred are different in various ways that we need to correct. We can discard the non-overlapping regions and then balance the two distributions by applying weights to the control cases, making the grey distribution match the green.</p><p>Below we see the estimates of the effects of events on the user’s probability of making a purchase.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7yIk8spbotFHqOhFTY6tKg.png" /></figure><p>Observe the following:</p><ul><li>Adjustment makes the effect estimates more conservative in all cases. The unadjusted correlations are all larger in magnitude than the adjusted ones. The effects of pageview and result_click events are close to zero after adjustment.</li><li>The purchase effect is mechanical because the outcome is defined to be 1 if it occurs. However the estimate has an interesting interpretation — at the moments when users are about the purchase, since the control group has about a 1 - 0.63= 37% probability of purchasing. One can think of the correlational estimate as using the roughly 10% probability of purchasing as the baseline.</li><li>The adjusted estimates tend to be estimated with greater error (see the wider confidence intervals). We have made a bias-variance tradeoff, reducing bias through adjustment. The results seem favorable if we believe the adjusted estimates have strictly less bias since the variance is not so much larger.</li><li>The sign of the remove treatment reverses after adjustment — removing an item from cart has a strong positive unadjusted association but a negative effect after adjustment. The positive effect has a clear confounding story, users are likely very close to purchasing if they are removing items from their carts. The negative estimate seems more plausible in this case.</li></ul><p>Overall these estimates seem to have some face validity, in some world where we could nudge the user to make these events happen more frequently (e.g. through changing the design of the store). Certainly they make more sense and are more conservative than relying on correlations.</p><h4>Scaling up to more and more complex hypotheses</h4><p>The next step is to scale up the number hypotheses we consider — casting a wide net to find potentially useful causes. There are a variety of ways to add complexity to the hypothesis space:</p><ul><li><strong>Sequences of events</strong>: we can define patterns of events (e.g. something that could be <a href="https://motifanalytics.medium.com/introducing-sol-sequence-operations-language-87a0d1d73497">matched in SOL</a>) as treatments. For instance “what is the effect of using this feature twice in an hour?”</li><li><strong>Accounting for properties of events</strong>: events typically have many properties which could plausibly modify their effects.</li><li><strong>Contextual effects</strong>: Users in certain states (having used other features previously) have smaller/larger effects.</li><li><strong>Time-variation in effects</strong>: Effect of certain events can change over time, revealing latent changes driven by product changes.</li><li><strong>Dose-response effects</strong>: effects of events can accumulate, and we can estimate these curves.</li></ul><p>Regardless of how we define the the cause and its context, the machinery for estimating its treatment effect is the same, completely automatic, and relatively fast. We can estimate hundreds or thousands of effects and look at their distribution.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Tt5O243Spr6NuKImb48bbw.png" /></figure><p>Strikingly, the adjustment procedure tends to result in effects which are centered at zero and with relatively small estimates, which fits our prior that most of our hypothetical treatments do not influence the outcome. There is also an asymmetry that makes sense — for rare outcomes like purchase it is unlikely to find treatments that lower the probability by a lot, but some treatments can have strong positive effects.</p><p>Applying a false discovery rate control procedure helps us select potentially interesting effects for investigation. By applying <a href="https://projecteuclid.org/journals/annals-of-statistics/volume-29/issue-4/The-control-of-the-false-discovery-rate-in-multiple-testing/10.1214/aos/1013699998.full">Benjamini-Yekutieli</a> to the p-values from the estimates, we can set a false discovery rate threshold we are comfortable with. Here with over 300 treatment definitions, we set the rate to 0.1% and find 27 interesting effects to follow up on.</p><p>A human analyst and product manager with knowledge of the product and its instrumentation form a human-in-the-loop process to make sense of the estimates. We find that often a few can be immediately discarded as either obvious or not amenable to interventions. They can be incrementally filtered out, leaving more actionable hypotheses to follow up on.</p><h3>Validating a new causal discovery method</h3><p>In my post from two years ago, I wrote:</p><blockquote><em>If you’re reading this and you’re anything like me, alarm bells are going off: what makes any of this “causal”?</em></blockquote><p>And in this post I have likely triggered more alarm bells for the folks worried about <a href="https://xkcd.com/882/">multiple comparisons problems</a>. So now there are two important reasons we should expect this method to lead to occasional false discoveries: from the bias we’ve been unable to remove through adjustment and from the noise that would allow us to find something interesting by sheer chance.</p><p>I find it important to emphasize the difference between estimating effects to inform a decision or policy, and estimating effects to generate a new hypothesis about a cause-effect relationship:</p><ul><li><strong>Hypothesis testing</strong>: we experience estimation error as making the wrong decision about what to launch, leading to worse products and unhappy users.</li><li><strong>Hypothesis generation</strong>: we experience estimation error as not knowing how or what to focus on in our products, leading to poor product prioritization and a “guess and check” development cycle.</li></ul><p>My contention is that we should shift the weight in our loss function from caring mostly about false positives to caring about false negatives for cause-effect relationships. Put differently: if the status quo is using human intuition and correlations to decide what contributes to the success of our product, what kinds of mistakes could we be making? Wouldn’t it be worth seeing if we missed something through that methodology?</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*p9o9mwhbSZZImhfO.png" /></figure><p>A healthy scientific attitude toward our products is that we face bottlenecks in both testing ideas AND generating new ideas. The strides we have made in routinizing experimentation have not yet been met with a corresponding innovation in discovery. But as of today, Motif’s new causal discovery engine is ready for testing with our customers and we’re excited to see what we can learn from your event sequence data. If you’d like to try Motif’s approach on your data, <a href="/service/mailto:mikpanko@gmail.com">reach out to us</a>.</p><h3>Acknowledgements</h3><p>This has been a long project with a lot of conceptual challenges. I’d like to thank a few people who’ve given lots of valuable feedback as I fumbled through this, including: Alex Chin, Avi Feller, Dean Eckles, Cam Bruggeman, Alex Deng, and David Robinson.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=302ee16c0718" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Progressive Event Stream Analytics at Scale Using Kubernetes, DragonflyDB and TypeScript]]></title>
            <link>https://motifanalytics.medium.com/progressive-event-stream-analytics-at-scale-using-kubernetes-dragonflydb-and-typescript-e8b44592ca78?source=rss-a36de1cfc346------2</link>
            <guid isPermaLink="false">https://medium.com/p/e8b44592ca78</guid>
            <dc:creator><![CDATA[Motif Analytics]]></dc:creator>
            <pubDate>Sat, 22 Feb 2025 13:53:38 GMT</pubDate>
            <atom:updated>2025-02-24T03:44:06.759Z</atom:updated>
            <content:encoded><![CDATA[<p>by <a href="https://www.linkedin.com/in/pmaciolek">Przemyslaw Maciolek</a></p><p>We have been asked how Motif Analytics work underneath on more than one occasion. There is a simplified answer for that question (”workers process <a href="https://motifanalytics.medium.com/introducing-sequence-analytics-93eadd921440">SOL query</a> and we map/reduce results”), but it does not bring all the interesting design decisions we did and optimized over the last two years.</p><p>When we started working on it, we initially used an approach similar to our <a href="https://docs.motifanalytics.com/getting-started/local-vs-cloud">local (in-browser)</a> mode, hoping to reuse a lot of abstractions. I.e. run the main process and a bunch of workers tightly coupled with it. Unfortunately, <a href="https://nodejs.org/api/worker_threads.html">Worker Threads</a> just didn’t work as well as <a href="https://www.motifanalytics.com/posts/my-browser-wasmt-prepared-for-this-using-duckdb-apache-arrow-and-web-workers-in-real-life">Web Workers</a> for us. We’ve been hitting some unexpected scaling limits rather quickly and using Worker Threads with Typescript (and the multi-module code transpilation) was painful on its own. Add to this the limitations in memory sharing between the “threads” and we ended up seeing almost no benefits of using them. So we took a step back and fully decoupled the worker from the main thread, leveraging the shared-nothing architecture approach. It took us a while to get there but we ended up with a pretty robust and scalable architecture.</p><p>I am an engineer myself and concluded it might be fun to read, hold tight!</p><h3>The challenge</h3><h4><strong>Motif is <em>sequence analytics</em></strong></h4><p>One of the fundamental distinguishing features for Motif Analytics is that it operates on the concept of <em>sequence,</em> rather than <em>tabular</em> data or <em>event streams.</em> A sequence is essentially an ordered list of <em>events</em>, grouped by <em>actor</em> and can be trivially generated from a table, It brings <a href="https://motifanalytics.medium.com/introducing-sol-sequence-operations-language-87a0d1d73497">new exciting capabilities and requires special tools to operate on it</a>. For that reason, we brought SOL (Sequence Operations Language) which allows working with this data model.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*neQcoLLK5p7sO7rv.png" /></figure><p>In database taxonomy, Motif Analytics might be considered a NoSQL Array Database. Was it really required to come up with another database? In their paper: “<a href="https://db.cs.cmu.edu/papers/2024/whatgoesaround-sigmodrec2024.pdf">What Goes Around Comes Around… And Around…</a>”, Michael Stonebraker and Andrew Pavlo argue that repeated efforts to replace either SQL or the Relational Model are destined to failure. They review the last 20 years in databases and conclude that all alternatives were either already replaced by RDBMS/SQL or are going to get merged soon. <strong>However</strong>, one exception is <em>Array Databases</em>:</p><blockquote><em>They may become more important because RDBMSs cannot efficiently store and analyze arrays despite new SQL/MDA enhancements.</em></blockquote><p>Even if we’re building a database engine, it’s not as hard as it used to be. We reuse a lot of existing parts and Apache Arrow makes it much easier than it was, say, 10 years ago. Yet still, it’s not a trivial project.</p><h4><strong>Motif is </strong><a href="https://arxiv.org/abs/1607.05162"><strong><em>progressive analytics</em></strong></a></h4><p>It is our core belief that we should remove friction, to the extent possible, from the analytics workflow. One way to look at it is that not all queries are created equal and different tradeoffs might be associated with them.</p><ul><li>exploration (S1) queries are formed many times, should be interactive (e.g. &lt;2s) and might have lower sampling rate associated</li><li>for hypothesis forming (S2) the demands on confidence intervals are tighter, though the queries can also be sampled and should still be fairly fast (e.g. &lt;10s)</li><li>for reporting (S3) the queries are run less frequently and can be even considered a batch job, yet these should not be sampled anymore</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*kDUUL1BlL5r3BuOo.png" /></figure><p>All three modes are available with Motif. Additionally, rather than implying a predefined sampling rate, Motif uses adaptive random sampling techniques, which ultimately means that it’s all effortless to use. To top it off, the sampling rate can be progressively increased, if needed, via our incremental query engine.</p><h4>We believe in simplicity</h4><p>I very much remember my first interview with Theron, CTO and co-founder of Motif. He mentioned how important it is for him to focus on simplicity in engineering and I could not agree with him more. This comes at a caveat though: <em>building complex is simple, yet building simple is hard!</em></p><p>Simplicity comes in different shapes and forms. I see it mainly as (to the extent possible):</p><ul><li>use stateless, ephemeral processes</li><li>favor composition over inheritance</li><li>balance new abstractions</li><li>keep unidirectional flow</li><li>prefer idempotent operations</li><li>support fault tolerance</li><li>leverage existing standards, formats, services and libraries if they fit well and don’t come at high maintenance cost</li></ul><h4>Our code is 100% TypeScript (at least right now)</h4><p>TypeScript is great and makes it easy to build things running both on the server and in the browser. It can deliver outstanding performance for a scripting language, yet it also has some limitations which make it challenging for high-throughput data processing engines. Perhaps the biggest one is inherent cost of serializing/deserializing data, which significantly reduces the usability of distributed caching. We found that in many cases serializing and then deserializing data using the fastest means possible takes as much time as fully processing it from raw input.</p><h3>Motif Cloud Processing Architecture</h3><p>Motif provides two ways to work with data. In the local mode, data can be loaded and processed fully in the browser, which works great for evaluating smaller datasets (typically, up to 2M events). For larger datasets, we offer Cloud Mode, which currently supports up to 10B+ event datasets (we’re working on supporting larger datasets), runs on Kubernetes and handles autoscaling.</p><p>Before we get there, lets look at our processing pipeline bottom-up.</p><h4>Data Blocks</h4><p>Motif Analytics introduces two core model primitives. You probably know these fairly well already. We just provide some standardization here:</p><ul><li><em>event</em> is a unique record, which is labeled by a <em>name</em>, associated with an <em>actor</em> and has some monotonic <em>order</em> field (typically by <em>timestamp</em>)</li><li><em>sequence</em> is an ordered collection of events associated with a single actor</li></ul><p>The ingestion first partitions the raw events by specified <em>actor</em> field, then sorts it and stores in <em>data blocks</em> (with all normalization and transformation done along the way). A <em>block</em> is a unique partition of data, which contains a collection of <em>sequences.</em> It might contain a single sequence (when the sequence is very large) or tens of thousands of them (when these are shorter). What’s crucial though is that a single sequence cannot span more than one data block. This approach simplifies feeding the query engine with data and allows to run many parallel query operations. As an added benefit, sampling for large datasets can be handled through randomly picking data blocks and processing them.</p><p>The ideal data block size is the one which allows for maximum parallelism, while not adding unnecessary overhead. In our case, this means that minimally, single block overall processing P95 latency should be below our allowed interactive query runtime (&lt;2s).</p><h4>Apache Arrow as the physical model (mostly)</h4><p>We use a columnar model underneath and while initially we started with a custom implementation (for performance reasons) we eventually settled on Apache Arrow. This has a slight impact on the overall throughput (due to increased access times to values stored in memory through Apache Arrow), but makes it much easier to integrate with other frameworks and libraries. For example, we use DuckDB for reading the Parquet files (which we use for storing the Data Blocks). When the query needs to access only several dimensions, it is trivial to push them down to Parquet reader in DuckDB and avoid unnecessary deserialization and processing of the unused columns.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*plbb_8r5ZTtiJwfM.png" /></figure><p>Of course, Apache Arrow columnar model still represents tabular data rather than a collection of sequences. To deal with that, we built a lightweight representation of sequences. Our ingestion layer ensures that all events in data block are ordered by actor and timestamp, which means we can assume they the sequences are contiguous in the input. Then, each sequence can be described by an array of positions in the Arrow Table. For example, if a sequences has 4 events, which are stored as records at row indexes <em>100–103</em>, then the array would contain [100,101,102,103].</p><h4>Removing and adding events</h4><p>While update operations of individual dimension values can be handled directly through Apache Arrow API, it’s more complex for operations which remove or add events.</p><p>Actually, removal is the simplest case. If, through <em>REPLACE</em> operation, an event would be removed or reordered, it is simply a matter of updating the event array indexes accordingly. E.g. removing the second event would update the example array to 100,102,103</p><p>Adding events (again, through SOL <em>REPLACE</em> operation) requires more effort. Adding new records through Apache Arrow directly is rather expensive and would require rewriting the data. To avoid that, we use overlay columns and append new data at the indexes following the original Apache Arrow input data. For example, if now a new event was added to our example sequence, we might have added a new entry at row 200001 using the overlays and the array would now contain [100,102,103,200001]</p><h4>Computing at scale</h4><p>The datasets we handle for our customers are frequently made out of tens of thousands of data blocks. This allows for great parallelism, but also requires some coordination of the effort. Also, we do not have unlimited resources. If the dataset contains 10,000 data blocks, it does not mean we will run it concurrently on 10,000 workers. Instead, we have a pool of available workers (which autoscales as needed) and we feed blocks to it.</p><p>Let us bring some abstractions we use in our architecture:</p><ul><li><em>Metadata</em> is a file which contains information about the dataset and its specific data blocks, including each block path, number of sequences, etc.</li><li><em>Object Storage</em> stores <em>Data Blocks</em> and <em>Metadata</em></li><li><em>Task Scheduler</em> is a subsystem which manages adding (or removing) tasks for ongoing request. It is aware of what blocks are available for specified dataset and which were processed already.</li><li><em>Worker</em> is a process which can consume and process tasks from <em>Scheduler</em> and output the results down the stream. It fetches the blocks directly from the <em>Object Storage</em></li><li><em>Combiner</em> can combine multiple outputs from Workers for a specific request (to reduce the further processing volume)</li><li><em>Reducer</em> does the last step of processing, gathering all results and outputting the data which can be consumed by the client</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*228AmVNrIt5B_ePE.png" /></figure><p>The processing following a <em>map-combine</em>-<em>reduce</em> flow (you can think of it as of a version of leaf→intermediate aggregator→root aggregator, as <a href="https://research.facebook.com/publications/scuba-diving-into-data-at-facebook/">described in Facebook Scuba paper</a>). The map operations are most processing intensive. They take data blocks, SOL query and requests params on the input.</p><p>It’s critical to keep low latency for all of this this and handle all failures gracefully. We found that DragonflyDB is a great choice for that. It is lightweight, has great developer experience and implements the <a href="https://github.com/redis/redis-specifications/blob/master/protocol/RESP3.md">Redis protocol</a>, which nicely complements our core beliefs in simplicity:</p><blockquote><em>The Redis protocol has served us well in the past years, showing that, if carefully designed, a simple human readable protocol is not the bottleneck in the client server communication, and that the simplicity of the design is a major advantage in creating a healthy client libraries ecosystem.</em></blockquote><h4>Task Scheduler</h4><p>The task processing management can be broken into four interconnected parts:</p><ul><li>task management, which decides whether more tasks need to be pushed (or the request should be cancelled); as a simple rule, it can be assumed that each task represents one input data block for a specific query request</li><li>regular task stream, which allows to pull the task by a random worker</li><li>cached task stream, which sends the task to a specific worker, which advertise to have cache results available</li><li>the worker itself, which gets and processes the task</li></ul><p>When the request from the client comes to the backend, the request state is initialized and a new task scheduler instance is created. It will create a list of blocks to process from the dataset metadata and start pushing these for the workers to handle. Each iteration the current capacity is checked. These properties are tracked in DragonflyDB instance and updated by workers on starting and finishing the tasks. If there are task slots available for the given request, a block ID is removed from the list and a new task is created. It includes the request details and is associated with a single data block. It also includes information about where to route the responses back, so these could be reduced when ready.</p><p>The task is sent using <a href="https://redis.io/docs/latest/develop/data-types/streams/">Streams</a> and <a href="https://redis.io/docs/latest/commands/xreadgroup/">Consumer Groups</a>. These can be considered a type of a message queue, which some extra capabilities for delivering and acknowledging the messages.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*MdxYGItrcRx4x776.png" /></figure><p>The task scheduler keeps scheduling the tasks until all of them were processed or timeout happens. E.g. if it’s an interactive task, the scheduler can decide it will return the responses after 2s basing on what it collected already and cancel the already scheduled tasks.</p><p>Request cancellation notifications are handled through a separate <a href="https://redis.io/docs/latest/commands/pubsub-channels/">pubsub channel</a>, so workers are aware of the requests which should be no longer processed and tasks can be dropped.</p><p>In an <em>incremental task scheduler</em> mode, rather than cancelling the task, it’s paused instead and its current state is kept in DragonflyDB. When a continuation request comes for this particular instance, it’s unpaused and processing continues. This provides continuous progressive analytics experience.</p><h4>Worker</h4><p>The worker receives the task and processes it. It first fetches the associated block from Object Storage, then executes SOL, does the Map operation (relevant to specific request, e.g. for Metrics API, it calculates aggregates and groups them accordingly) and eventually pushes the results out. They typically go through combiner using PUBSUB first and are periodically combined with other worker responses for the same request and then are stored in DragonflyDB for the final reduce phase.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*1l2tg2uH2c0C1vsw.png" /></figure><p>We use heavily the Redis Stream/Consumer Group concepts (though implemented via DragonflyDB). When a message (task request) arrives at the worker using <a href="https://www.dragonflydb.io/docs/command-reference/stream/xreadgroup">XREADGROUP</a>, it starts being processed and is acknowledged on success (via <a href="https://www.dragonflydb.io/docs/command-reference/stream/XACK">XACK</a>). If the worker fails for whatever reason, the message status will timeout and eventually it will be picked by one of the other workers through <a href="https://www.dragonflydb.io/docs/command-reference/stream/xautoclaim">XAUTOCLAIM</a>, unless the request was already cancelled. This allows to scale the number of workers quite significantly (we have successfully run several thousand concurrently so far).</p><p>There is however one interesting detail which justifies getting into another layer of depth: <em>caching</em>.</p><h3>Regular vs Cached Task Stream</h3><p>Out of all processing steps, running SOL is typically the phase which takes the biggest chunk of time (though frequently on par with deserializing the data from Parquet into Apache Arrow). Frequently, the user navigates through various visualizations or changes some params, which trigger a new API request, though fundamentally based on the same SOL query, for the same dataset.</p><p>If our backend would be written in Rust, Zig, Java or C++ (or many other languages), we would probably try reusing a distributed cache framework for these. However, with our backend in Javascript, things are more complicated. TLDR, the serialization/deserialization costs are prohibitive for using a distributed cache. So we came up with something on our own. We built a worker-aware local SOL results cache.</p><p>When the worker sees a specified SOL query + dataset combination for the first time, it caches the query results in a local, in-memory LRU-cache and sends a notification to a centralized key-value store (a Hashmap in DragonflyDB), indicating that a specified block is cached at a specified worker instance. The cache has some expiration time associated and additionally, if worker removes the entry from its local cache, the central key-value store is updated with information that the cache is no longer available.</p><p>Then, when the task scheduler is processing another block, it checks if it was potentially cached. If it was, instead of pushing a regular Redis Protocol Stream message, it instead sends a message directly to the specified worker PUBSUB channel and to another Redis Protocol Stream, dedicated for cached results. Worker then has one second (by default) to process the message. If it does not succeed, the cache request expires and it is later processed just like any other request through Redis Protocol Streams.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*NILcTBld45vc1lJq.png" /></figure><p>This approach has the best of both worlds — we have the ability to reuse the cached results, yet the request has some guarantees of not being lost, even when the worker is abruptly shutdown (e.g. due to Kubernetes Pod being killed).</p><h3>Fault Resilience and Tolerance</h3><h4>Workers</h4><p>Using message queue for feeding workers with tasks makes it much easier to reason about modes of handling failures. This is a capability not unique to Redis protocol, though it provides some unique semantics for dealing with it. On reading the message, it enters a <em>Pending Entries List (PEL)</em>. This indicates the message is being processed. When worker processing the message unexpectedly dies, the message can be eventually picked up again through <em>XAUTOCLAIM</em>, which lists the messages that were idle for specified duration (essentially, a timeout). Only when message is acknowledged (<em>XACK</em>) it can be considered processed and is taken down from the <em>PEL</em>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*fyoUUyk_4BSkSmRx.png" /></figure><p>There’s a built-in safety mechanism for duplicate message processing. If the message was already acknowledged or removed from the queue, <em>XACK</em> will return “0” when trying to acknowledge it again, so it can be stopped from sending down the stream.</p><p>It’s worth noting though that in case of interactive queries, to not delay getting the response, a failed task might be not retried, in favor of returning partial (sampled) results at reduced latency.</p><h4>Server and Task Scheduler</h4><p>The fault-tolerance gets more interesting when dealing with server/task-scheduler failures. These can be actually continued! The key to achieve that is realizing that our Incremental Task Scheduler does not really keep a state in memory but rather updates/reads state via DragonflyDB.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*iOUagMA-g99D99Bq.png" /></figure><p>In case request fails (e.g. network connection issue), the client can actually retry it. By providing the same UUID of the request ID, the server can actually <em>continue</em> it rather than restart, reusing the already processed task results. This also makes the overall architecture much more resilient, since the only single-point-of-failure is now DragonflyDB (and it provides replication for high-availability).</p><h3>Wrap Up</h3><p>Where does this bring us? For the past three months we saw following stats:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/778/0*1s3Zh_pMadqSgtyR.png" /></figure><p>The system is stable, scales well and haven’t caused a single outage (knock on the wood). We achieved that largely through keeping the design <em>simple,</em> but not too simple!. The key decisions were:</p><ul><li><strong><em>breaking the dataset into independent data blocks</em></strong> (great for parallelism and fault resiliency)</li><li><strong><em>using Apache Arrow whenever possible, yet keeping a lightweight logical model over it</em></strong> (reusing existing OSS components but retaining the <em>Sequence</em> semantics)</li><li><strong><em>building a task scheduler on top of DragonflyDB (Redis Protocol) Streams &amp; Consumer Groups</em></strong> (high throughput, low latency and good fault resilience)</li><li><strong><em>making service instances stateless and instead keeping the state in DragonflyDB</em></strong> (great for autoscaling, fault resilience and pausing/continuing the requests)</li></ul><p>Our Cloud Architecture provides us with the fabric, which allows us run the SOL Query Engine over the data effortlessly. <a href="https://motifanalytics.medium.com/introducing-sol-sequence-operations-language-87a0d1d73497">Our sequence-oriented language</a> helps solve complex problems elegantly, though implementing it brings some interesting challenges from engineering perspective. Going to cover it in the subsequent post. Subscribe and stay tuned!</p><p>‍</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e8b44592ca78" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to Build a Query Engine]]></title>
            <link>https://motifanalytics.medium.com/how-to-build-a-query-engine-0622074654f1?source=rss-a36de1cfc346------2</link>
            <guid isPermaLink="false">https://medium.com/p/0622074654f1</guid>
            <dc:creator><![CDATA[Motif Analytics]]></dc:creator>
            <pubDate>Sat, 22 Feb 2025 13:53:22 GMT</pubDate>
            <atom:updated>2025-02-24T03:46:51.365Z</atom:updated>
            <content:encoded><![CDATA[<p>by <a href="https://www.linkedin.com/in/pmaciolek">Przemyslaw Maciolek</a></p><p>In our early days, all the queries doing sequence analytics and feeding the output to visualization layer were built using SQL underneath. Rather quickly, the code for generating these queries become complex and the queries themselves were massive and slow. The attempts to use MATCH_RECOGNIZE (a row pattern matching capability <a href="https://trino.io/blog/2021/05/19/row_pattern_matching.html">added in SQL:2016</a> and now available in multiple SQL query engines) were somewhat disappointing and we came to the conclusion that we must take a different path.</p><p>We didn’t yet have SOL back then yet. Just a small glimpse of a DSL (Domain Specific Language), prototyped by our CEO, Misha, in <a href="https://observablehq.com/@mikpanko/nfl-plays-sunburst-chart">his Observable Notebooks</a>. The critical part of the DSL (and the one we struggled the most while trying to express it via SQL) was a capability to match and reference sub-sequences of events in a flexible way. Essentially being able to use array semantics within a window of events for a specific actor. The fundamental issue with SQL is that it is not useful for expressing operations across rows (unlike across columns), which makes this a challenging task. Similar observation was made by <a href="https://db.cs.cmu.edu/papers/2024/whatgoesaround-sigmodrec2024.pdf">Michael Stonebraker and Andy Pavlo in their excellent paper</a> reviewing the landscape of database management systems, where they state that <em>“(…) RDBMSs cannot efficiently store and analyze arrays despite new SQL/MDA enhancements.”.</em></p><p>When dealing with clickstreams or other types of sequence events, this sort of the capability matters a lot. We looked at various available options as of 2022 and concluded that <a href="https://motifanalytics.medium.com/introducing-sol-sequence-operations-language-87a0d1d73497">we really need a dedicated language to solve this problem</a>, though borrowing multiple ideas from existing concepts, such as <a href="https://people.cs.umass.edu/~yanlei/publications/07-03.pdf">SASE+</a>, <a href="https://neo4j.com/docs/cypher-manual/current/introduction/">Cypher syntax</a> or regex <a href="https://www.stat.berkeley.edu/~spector/extension/python/notes/node82.html">capture groups</a>.</p><p>To give an example, imagine having logs from a pet store and trying to:</p><ol><li>Find cases when the same product is being looked at twice before adding to cart.</li><li>When it occurs, measure the duration since seeing the product for the first time to adding it to cart.</li></ol><p>This category of problem is surprisingly hard to express using SQL, yet it can be written in a remarkably elegant way when using SOL (Sequence Operations Language), which uses much more fitting abstractions for sequence analytics.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*RXot5Xfys2gyRB9T.png" /></figure><p>We spent a good amount of time on SOL and iterating over it. In our initial plans it was supposed to be just an internal layer, used by visual editor components and allowing us to move fast. However, it turned out to be so powerful that we felt the urge to share it with the world, made it a central point of the experience working with Motif and announced it publicly in 2023.</p><h4>The overall architecture</h4><p>Getting the results from the queried dataset involves several steps. As the user enters a SOL query, it is then processed and executed top-down as in the following diagram.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*MmheGAN1vsHJCD80.png" /></figure><h3>Parsing</h3><p>The first step is to actually parse the provided string. We use <a href="https://ohmjs.org/">Ohm</a>, which provides a nice layer for working with PEG (<a href="https://en.wikipedia.org/wiki/Parsing_expression_grammar">Parsing Expression Grammar</a>) and yields an AST (Abstract Syntax Tree) model on the output.</p><h4>Post-processing and error handling</h4><p>Error handling and all of the things which cannot be handled by the grammar itself are handled immediately after. This allows to resolve some ambiguities (e.g. determine types of the references) and make the errors more human readable. Just to give an example, it is much easier to reason about this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/716/0*sB5S5Bzm31cTbZyS.png" /></figure><p>Than trying to figure out what to do after seeing invalid token at position 6 or so.</p><h4>Abstract Syntax Tree</h4><p>After successful parsing, it outputs the AST representation of the query, which allows to build the automata and evaluate expressions. For a sake of the example, lets look at the output for following query:</p><pre>match A(product_page)</pre><p>The query matches a single event named “<em>product_page</em>” and puts it under <em>tag</em> “A”.</p><blockquote><em>Tags allow to mark and access (reference) matched events and their dimension values. You can consider tags a variation of </em><a href="https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Regular_expressions/Named_capturing_group"><em>named capture groups in regular expressions</em></a><em>.</em></blockquote><p>The output is somewhat verbose so lets just look into the relevant AST sections. The first one is the actual pattern match model. It describes a single tag, aliased (named) “A”. It also says it expects to match exactly one occurrence of it.</p><pre>&quot;pattern&quot;: {<br>  &quot;tags&quot;: [<br>    {<br>      &quot;alias&quot;: &quot;A&quot;,<br>      &quot;quantifier&quot;: {<br>        &quot;min&quot;: 1,<br>        &quot;max&quot;: 1<br>      }<br>    }<br>  ]<br>}</pre><p>Following the match pattern, the next section contains the conditions (predicates) which must be met each of the match tags. In our case, this is just one predicate:</p><pre>&quot;if&quot;: {<br>  &quot;predicates&quot;: [<br>    {<br>      &quot;alias&quot;: &quot;A&quot;<br>      &quot;type&quot;: &quot;RELATIONAL&quot;,<br>      &quot;operator&quot;: &quot;=&quot;,<br>      &quot;arg1&quot;: {<br>        &quot;alias&quot;: &quot;A&quot;,<br>        &quot;dimensionName&quot;: &quot;name&quot;,<br>        &quot;type&quot;: &quot;EVENT_DIM_REFERENCE&quot;<br>      },<br>      &quot;arg2&quot;: {<br>        &quot;arg1&quot;: &quot;product_page&quot;,<br>        &quot;arg1Type&quot;: &quot;string&quot;,<br>        &quot;type&quot;: &quot;LITERAL&quot;<br>      },<br>    }<br>  ]<br>}</pre><p>Decoding it should be rather straightforward. It’s a <em>relational</em> <em>equality</em> expression, meaning there are two arguments which must be equal to each other. One is <em>name</em> dimension of tag aliased with “A”. The other is string literal <em>“product_page”</em>. This builds a condition which will be <em>true</em> when the name will be equal to “<em>product_page</em>”.</p><h4>Building the automata</h4><p>Having the AST representation of the query, we can now start building the automaton (one for each <em>MATCH</em> statement). It’s a process which itself consists out of several steps (as a refresher, we highly recommend “<a href="https://www.udemy.com/course/automata-theory-building-a-regexp-machine/">Automata Theory: inside a RegExp machine</a>” course at Udemy). In our case, this is:</p><ol><li>Building the NFA (nondetermininistic finite automaton).</li><li>Converting it into a pseudo-DFA (deterministic finite automaton) table. Due to expressivity of SOL, we’re not strictly able to convert NFA into a fully deterministic automata, yet we still convert it into a state-transition table, which reduces the overall number of transitions to check.</li><li>On top of that, we create a sequence of operations executed after running the match automaton (e.g. REPLACE or COMBINE statements).</li></ol><p>It is not strictly necessary to build DFA and some regex libraries are actually using NFA instead. This allows to save memory and keep implementation simple, as in many cases DFA’s end up having much larger number of states than the NFA’s. Also, in our case, the transition functions can be quite complex, reference previous matches (tags) in their arguments and require <a href="https://en.wikipedia.org/wiki/Backtracking">backtracking</a> to traverse the search tree until finding a solution (if one exists).To deal with it, we have a somewhat hybrid approach (which we call “pseudo-DFA”), where we follow the steps of creating a state-transition table from NFA graph (including exploring epsilon-closures), but still allow for multiple transitions from a given state, just order them by priority and support backtracking.</p><h4>Basic example</h4><p>Taking our example AST from above (slightly oversimplified, will explain in a minute), lets examine what would be the output. In the very basic form, we would get following transitions (both the NFA graph and pseudo-DFA transition table are presented):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*7Lj9L3sWzhUICFb_.png" /></figure><p>The graph has three circles, each describing a specific state. For a single-event match, we have three of them:</p><ul><li>before:A, which is associated with entry point for testing if it is an A match; it has only one transition, labeled as A, which tests if all the predicates associated with A are all true before going to the next state and consuming the event; in our case, it will check if <em>name</em> is equal to <em>“product_page”</em></li><li>event_match:A, which marks a successful test result; it has only one, ε (epsilon) transition, which allows to switch to next state spontaneously (without consuming any event,</li><li>after:A, the accepting (final) state (hence the double border), which marks end of a match and allows to exit</li></ul><p>Converting this graph to a pseudo-DFA table reduces the number of states to two (indicated in the first column):</p><ul><li>0, from which the only allowed path is to 1, through predicates associated with A</li><li>1, which is the final, accepting state and does not allow any further transition</li></ul><h4>PREFIX and SUFFIX</h4><p>The automaton above would easily match a single-event sequence containing an event named “product_page”. But what if it contains other events?</p><p>As mentioned previously, the example was slightly oversimplified. In fact, it can match only a single-event sequence. In real life, we usually want is to allow for any other preceding and succeeding events, as needed, so the whole sequence containing the searched event is matched.</p><p>We call these <em>PREFIX</em> and <em>SUFFIX</em> respectively. We imply these are always present, <strong>unless</strong> using special anchors. The example from above would expressed in SOL as MATCH START &gt;&gt; A(product_page) &gt;&gt; END.</p><p>When the anchors are skipped (default behavior), the automaton gets more complex, though one can start seeing building blocks there.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*zGtRDjtIRAiM58di.png" /></figure><p>Lets focus on the transition table. As we start with the beginning of the sequence, there are two possible transitions:</p><ul><li>one labeled as A (when name=&quot;product_page&quot; and with <em>priority=1</em>), moving to state 1</li><li>another labeled as PREFIX (on any input), with lower <em>priority=4</em> and keeping the state at 0</li></ul><p>We evaluate the transitions according to their priority, so we first check the condition name=&quot;product_page&quot;. If it’s true, we move to state 1 and mark the event as A. Otherwise, we try the other transition, which marks the event as PREFIX and stays at state 0.</p><p>When automata eventually moves to state 1, it can either accept the sequence (if there are no further events) or immediately follows to state 2, where it accepts any further events and keeps marking them as SUFFIX until the end of the sequence is reached. A sample matched sequence might look like following:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/852/0*CuREDkJ5IzE3Qw4n.png" /></figure><h4>Quantifiers</h4><p>Of course, matching exactly one event is somewhat limitting. SOL supports all sorts of quantifiers, which you might know already from regular expressions:</p><ul><li>? (zero or one match)</li><li>* (any number of matches, as in <a href="https://en.wikipedia.org/wiki/Kleene_star">Kleene closure</a>)</li><li>+ (one or more matches)</li><li>{m,n} (any number of matches between m and n)</li></ul><p>Adding a quantifier impacts the automaton by adding extra <em>epsilon</em> transitions or increasing the number of states (for {m,n} quantifiers). For example, match A(product_page)* yields following output:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*bOQMBldSFExTjFys.png" /></figure><p>Now, when the automata is in state 1 and the current event matches the associated predicate, it will keep appending it to tag A as long as possible. For example, it can match following:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/844/0*CdioUgSC9yZ6YjDG.png" /></figure><h4>Tags are the building blocks</h4><p>As more tags are added to the query, the automaton becomes more complex and both the graph and state transition representations take more space. This makes it challenging to read, though one way to reason about it is that each tag (with its specific set of transitions) is a single building block of the automaton graph, appended serially per its position in the match statement.</p><p>Lets take the query solving the problem brought in the intro for consideration:</p><pre>match<br>  A(product_page) &gt;&gt; * &gt;&gt; B(product_page) &gt;&gt; * &gt;&gt; add_to_cart<br>if<br>  A.pid = add_to_cart.pid<br>and<br>  B.pid = add_to_cart.pid<br>set<br>  time_since_first = duration(A, add_to_cart)</pre><p>As the corresponding automaton graph got much larger, the layout is adjusted a bit. You can notice the building blocks for each of the matches in it.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*II4O5B1kcT7puvZR.png" /></figure><p>To see how it works, consider a simple, 4-event sequence and steps required to match it fully. This sequence does not require backtracking. To make it less verbose, we’ll be following the state transition table. The transitions are sorted according to priority. In each state we test the available transitions until one is true and keep following the transitions until all events are consumed and automata is in accepting state.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*-G3-iiquEYpCYq-5.gif" /></figure><h4>Backtracking</h4><p>The above example is somewhat optimistic though and it shows how the automata proceeds when no backtracking is required. It might have been mistaken for a continuous list of the steps, though in reality, match statements are <em>declarative</em> rather than <em>imperative</em>. I.e. they describe what conditions must be met for match to happen rather than what steps are required to execute them.</p><p>Finding the solution can be solved with <a href="https://en.wikipedia.org/wiki/Backtracking">backtracking</a>, which is a bit like navigating a maze. When you reach a dead end, you need to go back to the previous intersection and continue from there using a different route.</p><p>The following sequence is bit harder than the one in the previous example. Following the optimistic path will eventually lead to <em>add_to_cart</em> transition failing due to “pid” condition not matching:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*mgQcccRmKpbPXo64.gif" /></figure><p>The automaton needs to backtrack and keep trying the other available transitions until it succeeds or all transitions are exhausted. The continuation of the search can be observed below (in a somewhat simplified form)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*BxK8fzPpscEkHYf3.gif" /></figure><h4>Search space</h4><p>As we noted, the match pattern is in fact a declarative statement, which means that it describes what kind of pattern we are trying to find in the sequence (if present) rather than how to build it. Depending on the actual pattern and predicates, the search space can be quite vast. The role of the automaton is to traverse it in a prioritized way, until end of the sequence is reached and the final state is an accepting one or all possible transitions were exhausted and the query does not match the sequence.</p><p>This can be visualized using a tree describing all possible transitions for the sequence and query from the previous example. The actual tests made by automata are marked at each transition.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*MWKKzRI0Xegk1OE_.png" /></figure><p>As you can imagine, when the match patterns gets more complex, the actual search space becomes even bigger. Some of the queries are quite expensive to run, though many of the non-matching cases can be pruned out. For example, if there’s a simple condition that needs to be matched for at least one event (e.g. “A.foo=1234”) and it’s never true for a particular sequence, the automaton does not even need to be executed on it.</p><h4>Initially-False/Initially-True Predicates</h4><p>By building the SOL as a declarative language, we also aimed at making it intuitive to use with tags consisting of any number of events. This had interesting side effects for some categories of queries.</p><p>Consider the following predicate example. Essentially, trying to use an aggregation function on a multi-event tag dimensions. In this case, calculating <em>duration</em> between the first and the last event in a matched tag (where user was viewing <em>product_pages</em> continuously for more than 5 minutes).</p><pre>match<br>  product_page+<br>if<br>  duration(product_page[0],product_page[-1]) &gt; 5min</pre><blockquote><em>Note: </em><em>product_page[0] references the first event of the matched tag. </em><em>product_page[-1] references the last event of the matched tag. </em><em>duration(...) implies it’s used on the </em><em>ts field, which describes event timestamp.</em></blockquote><p>Now, let’s imagine the consecutive <em>product_page</em> events had following timestamps:</p><ul><li><em>09:00am</em></li><li><em>09:01am</em></li><li><em>09:06am</em></li></ul><p>The problem is evident already at the first event. As the automaton is iterating and starts with the first product_page, the tag has just a single event. Duration between <em>09:00am</em> and <em>09:00am</em> is zero, so it does not match the <em>duration(…) &gt; 5min</em> condition…</p><p>Even if we would create some sort of exception and only start looking at the second event, it would be still not enough. Only after having the first three events, the predicate finally evaluates as true.</p><p>To capture this category of matches, we extend the automaton to cover two types of cases:</p><ul><li>when the predicate is <strong>initially true</strong>, continue until it becomes false (like we did beforehand)</li><li>when the predicate is <strong>initially false</strong> (like in our example), keep trying until it becomes true, then keep running as long as it’s still true</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*I5afEwrDjSuGXvz9.png" /></figure><p>We use this feature only in case of <em>+1</em> predicates (such as * or +). It expands the search space even more, though it allows for some exceptional flexibility of the language and finding complex patterns, even some crazy ones, such as match A()+ &gt;&gt; B()+ if sum(A.foo) = sum(B.bar)</p><h4>Post-automaton Expressions</h4><p>Building and executing the automaton is usually the most complex and processing-intensive part of the whole process. After all matches are set and tags are associated with events, the engine executes the remaining operations, which access the specific events directly or via the (now available) tags.</p><p>For example, SET statement can reference the tags and assign or calculate some formulas:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*BvLcvKoQAF9Pio5-.png" /></figure><p>Similarly, REPLACE can be used to transform or remove events from sequences using the tag names as references:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*HEvMe8ig-7hyJVmB.png" /></figure><p>The post-automaton operations can be also executed on the whole sequences. To keep only sequences matching some specific criteria, FILTER statement needs to be used</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*PQEGc0cn0ZXYnA-P.png" /></figure><h4>Mutating Arrow Data</h4><p>We use Apache Arrow as the underlying data model, which is the de-facto standard of in-memory columnar storage today. This makes it easier to integrate external libraries for input/output or integrate our engine with third party tools. While Apache Arrow is great for interoperability and reading/writing data, it is not optimized for mutating data. And this is a problem, since many of the operations we support, such as SET or REPLACE are built in a way which does mutate data. To deal with it, we use two techniques.</p><p>The first one is having an <strong>in-memory event view</strong> which describes which of the Arrow Vector (column) indexes represent each event in a sequence. For example, for the first sequence in the dataset which has 5 events, this can be [0,1,2,3,4]. If the second sequence has 3 events, it will be represented by [5,6,7] and so forth. Now, if the second event of the first sequence is to be removed, the array will now contain [0,2,3,4]. Using this technique the events can be reordered or even added to the sequence. E.g. if the whole dataset contains 10,000 events and a two new ones ares added to the first sequence, it might now contain indexes [0,2,3,4,10000,10001].</p><p>The second technique is having a <strong>vector overlay</strong> over the immutable Apache Arrow Vector. It might be also considered a type of a write-through cache. When the value is mutated, we simply use an additional sparse JavaScript array and set a new value under the given index. When reading, we first check for the value in the overlay layer and if it is not set, read from the original Apache Arrow Vector. This arrays can be also easily grown if a new event is added.</p><p>A more complex example presents the diagram below. We use a mix of removing the events, updating the values and replacing one event with two other ones.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*roXtJMxxB9mx2q4U.png" /></figure><p>The output of this <em>overlaid</em> vectors can be converted back into Apache Arrow, if needed, though more frequently it’s a subject of Motif-specific aggregations (such as our Metrics API).</p><h4>Performance and Stability</h4><p>If you read through all of this and you are concerned about overall performance of this (or just curious if we have any troubles with stability) then let me say this are great questions. Even if our engine is currently implemented using TypeScript, it’s reasonably fast. So fast, that our SOL code execution times are typically on par with times to read a Parquet file into Apache Arrow in memory model. The fastest technique we found for the latter is using DuckDB NodeJS library, which means that it runs using native library code (implemented in C++).</p><p>It’s possible to write SOL queries which have quadratic computational complexity (depending on the number of events per sequence), though most of them are linear in nature and we observe <strong>per-CPU throughput in the order of 500k-2M events per second</strong>. To get there, we spent quite a bit of time optimizing the code and improved our original numbers by an order of magnitude since the initial implementation. There are several techniques which help with this (e.g. replacing conditional blocks with closures, push-down predicates on actor ids, filtering the fetched dimensions or pruning sequences) which are perhaps worth another blog post.</p><p>As for the stability and accuracy, we were prioritizing this since day one and other than taking extra care during development and reviews, we implemented a massive suite of test cases. It consists of (literally) several thousands of test cases like this one:</p><pre>expect(&#39;MATCH SPLIT A(a)&#39;).toYieldBufferedSequence(<br>  &#39;babab&#39;,<br>  &#39;PREFIX(b)|A(a)SUFFIX(b)|A(a)SUFFIX(b)&#39;<br>);</pre><p>Needless to say, bugs do happen in every computer system though these seem to be rare with our query execution engine. During the last 12 months we saw around 100,000 queries executed overall and we are aware of just one case of a SOL-specific bug discovered during that time.</p><h4>Summary</h4><p>As an array database, SOL offers capabilities that are not readily available or easily implementable in SQL. The ability to express complex patterns, handle time-based sequences, and perform advanced matching operations gives it a distinct advantage in scenarios where event order and relationships are crucial. Our users love it and can now perform analyses that were previously out of reach or required extensive (and complex) workarounds.</p><p>To make it easier to adopt for current SQL lovers, we’re investigating how we can extend SQL with some of the powerful features we’ve developed for SOL, potentially offering a hybrid solution that combines the familiarity of SQL with the advanced sequence analytics we built at Motif. This ongoing exploration reflects our commitment to continual improvement and our recognition that different users and use cases may benefit from varied approaches.</p><p>We’re curious to hear your thoughts on this. Have you encountered situations where traditional SQL fell short for sequence or event-based analytics? Do you see value in a specialized language like SOL, or would you prefer extensions to SQL? Your insights and experiences could help shape the future direction of our query engine development. Feel free to share your perspectives, either in the comments or <a href="/service/mailto:mikpanko@gmail.com">email us</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=0622074654f1" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Foundation AI Models for Product Analytics]]></title>
            <link>https://motifanalytics.medium.com/foundation-ai-models-for-product-analytics-8905ed524fb2?source=rss-a36de1cfc346------2</link>
            <guid isPermaLink="false">https://medium.com/p/8905ed524fb2</guid>
            <dc:creator><![CDATA[Motif Analytics]]></dc:creator>
            <pubDate>Sat, 22 Feb 2025 13:52:56 GMT</pubDate>
            <atom:updated>2025-02-24T03:50:29.387Z</atom:updated>
            <content:encoded><![CDATA[<p>by <a href="https://www.linkedin.com/in/ajwchin/">Alex Chin</a> and <a href="https://www.linkedin.com/in/seanjtaylor/">Sean J. Taylor</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*s2EQPMYdeDmEg9cc.png" /></figure><h3>Introduction</h3><p>How can AI help us understand our products and our users better? Code generation and automatic data analysis tools are good starts, but we believe this indirect approach still leaves a lot on the table. The LLMs powering code generation are trained on billions or even trillions of tokens in order to develop a deep understanding of the semantics of sequences of tokens. Product usage data also consists of large collections of sequences of user event tokens, making it a good fit for deep learning architectures similar to those used for natural language and code.</p><p>Consider the workflows of three analysts.</p><ul><li>Analyst 1 is doing analytics the traditional way, writing code to interact with the data warehouse.</li><li>Analyst 2 has access to AI-powered chatbot and copilot tool that can write SQL for her and compile basic summary tables into charts and graphs. She can use the tool to improve her productivity and can get faster answers to her questions.</li><li>Analyst 3, on the other hand, is working with Motif’s AI models that have read and understood her underlying event sequence data. She can directly explore the resulting insights with interactive visualizations, providing a faster feedback loop and surfacing more useful insights.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/989/0*pwN-xlIUplXOVVfZ.png" /></figure><p>At Motif, we are applying breakthroughs in language modeling to train <strong><em>foundation models of event sequences</em></strong> in order to surface more useful and important insights for decision makers. In this post we’ll explain how we do it, why we are doing it, and how we are applying these foundation models in practice.</p><h3>Generalized Large Event Analytics Models (GLEAM)</h3><p>Product event sequences are rich data sets generated by websites, apps, and backend infrastructure as they operate to serve user-facing requests. Product instrumentation is organized around capturing a log of immutable events, typically user identifiers, timestamps, and a variety of additional properties about the event.</p><p>Event log data sets trace all the steps that users take, as well as the specific processes that backend and frontend software execute in order to facilitate those user steps. They are often generated by several different systems and need to be merged together. When systems are instrumented well, raw event data is an attractive data asset to work with because it contains the most complete possible representation of a user’s behavior and experience.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6TPGzUrT9OEQR4y4F-WMKw.png" /></figure><p>To model event sequences with minimal changes to the underlying data, our approach is to mimic the training of LLMs on text data but with two key modifications:</p><ol><li><strong>Event tokenization</strong>: We developed tokenizers for events that richly capture their timing and various properties that have been logged in a flexible way.</li><li><strong>Multi-time-scale loss function</strong>: Modify the training objective to make sequence models less myopic, encouraging them to learn about how sequences will evolve over the next few minutes, hours and days rather than simply the next event.</li></ol><p>These architectures comprise what we are calling Generalized Large Event Analytics models (✨GLEAM✨).</p><h3>Tokenization</h3><p>In NLP, tokenization refers to the mapping of short strings of text characters into integer IDs that can be parsed by the AI model. For event log datasets, the events comprise a more complex nested data structure with useful information that we want to make sure is not discarded during the tokenization procedure. We create a <em>vocabulary</em> for the set of things that can happen in the system, and that vocabulary needs to tradeoff size and fidelity.</p><p>A key part of that vocabulary is the relative timing of events, which convey rich information about the underlying user behaviors and experiences. It matters a great deal if it’s been a minute or day between two user actions, indicating quite different levels of engagement. To capture this we developed a duration binning that encodes this information at multiple time-scales, while keeping an efficient vocabulary size.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*mh7ujosf9hZpIRUu.gif" /></figure><p>Durations are binned on a human-interpretable, roughly logarithmic scale. Boundaries are roughly 2–5x larger than the previous boundary.</p><p>The values of event properties are assigned tokens, so events are represented with multiple tokens, and take on different lengths depending on which properties are captured. This is a flexible framework that extends to rich event data while allowing us to use off-the-shelf sequence models designed for text data.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*d1ISFYKaKQU_4QNm.gif" /></figure><h3>Long-term objective</h3><p>The semi-supervised objective for decoder models (or auto-regressive models) is to predict the next token in a sequence. While undoubtedly useful for generative models which are used in text-generation tasks, generating sequences is not our primary goal and we have found that this myopic prediction task does not generalize as well to <strong>analytical AI</strong> tasks. Product analytics data has a relatively constrained grammar and information content compared to language, and sequence models can do well at predicting the next event. However, achieving good performance on predicting user behaviors such as churn, retention, and purchasing patterns involves predicting tokens that are typically observed far into the future of the sequence.</p><p>To give the model feedback about the longer-term trajectories of event sequences, we generate future-to-past <a href="https://en.wikipedia.org/wiki/Convolution">convolutions</a> of the input data to provide additional supervision. The model learns a representation to forecast what is likely to happen to the user over standard time-horizons for analytics tasks, in contrast to predicting the next event to generate realistic event sequences. We have found these representations to be quite different in practice and that strictly auto-regressive models do not generalize well to longer-term estimation without this kind of pre-training.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*7Gonc7V8wo46jjXU.png" /></figure><p>The user-specified value function can be quite flexible and can include non-parametric objects of interest such as the <a href="https://en.wikipedia.org/wiki/Survival_function">survival function</a> and <a href="https://en.wikipedia.org/wiki/Incidence_(epidemiology)">cumulative incidence</a>.</p><h3>GLEAM eliminates an information bottleneck</h3><p>Because event sequence data is so rich and complex, it is usually heavily summarized before it is analyzed by data scientists, analysts, and product managers. It is common to segment users based on easily measurable properties such as the cohort when they signed up, their country, their device, or level of recent activity. These user segments are undoubtedly useful but they tend to make restrictive assumptions:</p><ol><li><strong>They should be straightforward to compute in SQL</strong>: Due to convenience, analysts often stick to properties that are already available in source tables rather than ones which are challenging or expensive to compute.</li><li><strong>They should be simple</strong>: A small number of segments providing relatively coarse decompositions of user behavior and product experiences. Typically user segments must be <a href="https://en.wikipedia.org/wiki/MECE_principle">MECE</a> and relatively low-dimensional for them to be useful in practice.</li><li><strong>They (mostly) should not vary over time</strong>: Time-varying segments are more challenging to work with analytically, as users drift between segments the analyses and breakdowns grow more complex and <a href="https://en.wikipedia.org/wiki/Dimensional_modeling">dimensional modeling</a> breaks down.</li></ol><p>When data transformations are used to create pre-modeled datasets for scientists and analysts to use, it creates an information bottleneck in downstream analyses, discarding potentially valuable information in the pre-transformation event data. The set of hypotheses that can be tested using this approach is constrained by how well the transformations have captured the important properties of the data. As datasets grow ever larger and richer while products and use cases grow ever more complex, standard modeling techniques create blindspots — hypotheses that are difficult to test without changing how data is processed, burdening data engineering teams.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*dRNcvVSKnfpvqjzA.png" /></figure><p>The workaround we have observed in practice is for data scientists to develop machine learning models for predicting user behaviors. Churn, retention, and <a href="https://en.wikipedia.org/wiki/Customer_lifetime_value">LTV</a> models can be trained on complex, human-designed features based on lagged counts and rates of user actions. These task-specific models are obviously valuable but they have some deficiencies of their own:</p><ol><li><strong>Complexity and time cost:</strong> For purely analytical use-cases, it is difficult to justify building and maintaining complex machine learning models.</li><li><strong>Low reliability</strong>: Behavioral models depend on the upstream data quality being maintained, and may not be resilient to data pipeline changes and outages.</li><li><strong>Feature engineering bottlenecks</strong>: Developing features which can be computed, are predictive, and provide valuable insights is an ongoing challenge. Even with diligent work, it can still result in many hypotheses being difficult to test without re-training.</li></ol><p>Our foundation model approach addresses these deficiencies in a convenient way. Once pre-trained, GLEAM is a rolling feature generator, obviating the need for feature engineering pipelines and making creating new models a simple fine-tuning step. Representations computed from GLEAM on an event sequence approach have some important advantages:</p><ul><li>they provide features for any downstream task that are size-efficient</li><li>they are resilient to messy or missing data; and</li><li>they automatically adapt and encode information from logging improvements over time.</li></ul><p>Through training and inference procedures we’re able to produce a useful latent space for user trajectories, and the user’s (time-varying) position in this latent space is valuable and simple-to-use source of information about their past and future behaviors. However, interpretation of this latent space requires us to <em>decode</em> shifts in the latent space and understand what that means, which is a data exploration task.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*zBwr8WBAsmV7T_PE.gif" /></figure><h3>How we use GLEAM in practice</h3><p>So we’ve trained a GLEAM architecture on event sequence data — so what? These models have a super-power and it is that they create a fixed-length, information-rich representation of a user’s entire event history for every event in the sequence. These are more than user embeddings, they are user-<strong>history</strong> embeddings capturing time-variation in the user’s state. Meaningful shifts in the latent space over time tend to be interesting: they capture users learning about the product, making it through certain onboarding milestones, using different sets of features, and becoming more or less likely to buy, churn, retain, or convert.</p><p>The GLEAM latent space can be projected into two-dimensional space using a standard dimensionality reduction technique such as UMAP or t-SNE, giving a user-history space that useful for exploratory visualizations. These latent space visualizations depict:</p><ul><li><strong>States</strong>: clusters of states that users can be in, indicated by areas of density in the latent space.</li><li><strong>Flows</strong>: subsequences of events that transition users between states according to the mode</li></ul><p>Generally visualizations of high-dimensional spaces can be tricky to get right, and we have found this to be true in this application. But the points in the visualization can be directly linked to the original event data — there is a mapping between them that is preserved and can provides the user with a clear interpretation of what the states and paths mean by focusing on example users.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*1Akv_pYJ1jUIYAXK.png" /></figure><h3>Fine-tuning to outcomes and finding examples</h3><p>The latent space is a compressed form of the raw data, from which we can project into what we call the “outcome space,” which captures the expected value of some important outcome of interest for the analyst. The outcome space is a low-dimensional state space that we can directly interpret as business value, allowing analysts to interpret latent space in meaningful terms.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/800/0*IfCzcOCArLXI5s1I.gif" /></figure><p>Large shifts in the outcome space are typically interesting for analysts to investigate. They point to events which meaningfully change the estimate of some outcome for the user once observed by the model. These shifts can be immediate, from observing a single event, or the gradual accumulation of events over a contiguous time period. From a causal inference perspective we can think of shifts in the outcome space as either:</p><ul><li><strong>Revealing information about the user</strong>: the user has an unobserved probability of the outcome that we learn more about through their behaviors and experiences.</li><li><strong>Causing the user’s outcome to change</strong>: in a counterfactual sense, observing an alternative event would result in a different outcome for the user. For example, if the event were an exposure to random assignment of an A/B test, then the change in the outcome space will capture an estimate of the individual treatment effect.</li></ul><p>As the analyst sifts through shifts in the outcome space, she can determine the most important events in the business process and generate hypotheses about which ones might be suitable for interventions and product changes. This theory building can be accomplished by:</p><ul><li><strong>Inspecting interesting examples</strong>: viewing the event sequence in the data associated with the shift.</li><li><strong>Retrieving nearest neighbors in the latent space</strong>: determining how common the context for this shift is using the latent space to find sequences with similar user contexts.</li></ul><h3>Generalizing from specific observations</h3><p>The path from raw event data to the outcome space is bi-directional, and this enables powerful new workflows we have built into Motif. If the analyst observes a large shift in outcome space, it means that the model has some evidence that something important has happened to this user. The natural step is to ascertain why this may be happening, which is a model interpretation task. The approach enabled by the latent space is to use <a href="https://en.wikipedia.org/wiki/Nearest_neighbor_search">nearest neighbors search</a> to find users in a similar states with similar shifts, very similar to retrieving related documents in a RAG context. Roughly we capture three sets of users-histories:</p><ol><li><strong>Similar state users</strong>: this set contains all users who have similar historical sequences to the users. There are two subsets of this set that are interesting:</li><li><strong>Treated paths</strong>: users for whom the shift we are interested in has occurred.</li><li><strong>Counterfactual paths</strong>: users who were in the similar state, but for whom the shift did not occur.</li></ol><p>We can analyze this retrieved data set as an observational experiment. Obviously the differences are not always caused by the path the user took (there are many sources of confounding), but by inspecting the underlying events using Motif’s visualizations, we can determine via domain knowledge if the difference between the two groups yields potential for intervention. <a href="https://motifanalytics.medium.com/bringing-more-causality-to-analytics-d378108bb15">Based on the framework introduced in our earlier post</a>, this workflow supports the tasks of finding previously unknown effects (inspecting the outcome space) and determining unknown causes (by inspecting sets of examples where the effect appears to be present).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/960/0*bXP55kgbklTJo4RO.gif" /></figure><h3>Unlocking event sequence data</h3><p>Motif’s mission is to help our customers deeply understand their products and their users. In interviews with hundreds of data organizations, we have observed that they collect far richer data than they are able to use effectively. We believe the bottleneck to accessing these insights is not in writing better SQL queries, but creating a more expressive system for interacting with event sequence data. That means we need to innovate on:</p><ol><li><strong>Representations of event sequence data</strong>: Transformations which uncover valuable and important structure in the data, capturing the rich variation in behaviors between users and across time. Foundation models trained for specific data sets help us organize and index large data sets.</li><li><strong>Affordances for querying event sequence data</strong>: the questions people really care about are not well served by SQL (too restrictive) or by writing arbitrary code (too non-specific). Motif’s <a href="https://motifanalytics.medium.com/introducing-sol-sequence-operations-language-87a0d1d73497">Sequence Operations Language</a> was being developed for this task and has dramatically accelerated our analyses.</li><li><strong>Visualizations that help users uncover patterns</strong>: we have developed a variety of innovative interactive visualizations that spur curiosity and investigation.</li></ol><p>If this sounds like something that would be useful for your organization, <a href="/service/mailto:mikpanko@gmail.com">we’d love to collaborate with you</a>!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8905ed524fb2" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Motif Analytics Emerges from Private Beta with $5.7M]]></title>
            <link>https://motifanalytics.medium.com/motif-analytics-emerges-from-private-beta-with-5-7m-38ab744b1cb0?source=rss-a36de1cfc346------2</link>
            <guid isPermaLink="false">https://medium.com/p/38ab744b1cb0</guid>
            <dc:creator><![CDATA[Motif Analytics]]></dc:creator>
            <pubDate>Thu, 20 Feb 2025 23:31:29 GMT</pubDate>
            <atom:updated>2025-02-20T23:31:29.240Z</atom:updated>
            <content:encoded><![CDATA[<h3>Motif Analytics Emerges from Private Beta with $5.7M Seed to Empower Data Teams to Fuel Growth and Operations Through AI-Driven Sequence Analytics</h3><p><strong>Also </strong><a href="https://techcrunch.com/2024/02/12/motif-analytics-brings-sequence-analytics-to-growth-teams/"><strong>reported in TechCrunch</strong></a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*vaw1ca54xo59G2jMqMwcYw.png" /></figure><p>San Francisco, CA — February 12, 2024 — <a href="https://motifanalytics.com/">Motif Analytics</a>, a pioneering data analytics startup, today announced its emergence from private beta with selected partners to provide general availability to its analytics tool. This launch comes on the heels of a $5.7 million seed funding round led by Amplify Partners and Felicis, with participation from the InvestInData angel group.</p><p>Motif was founded at the end of 2021 by <a href="https://www.linkedin.com/in/mikpanko/">Mikhail Panko</a>, CEO, and <a href="https://www.linkedin.com/in/theronji/">Theron Ji</a>, CTO. The duo met at Google where they developed a predecessor to Motif, which became widely used across the company. Several months into 2022, <a href="https://www.linkedin.com/in/seanjtaylor/">Sean Taylor</a> joined Motif as Chief Scientist. Sean is well known in the tech community for co-authoring a popular forecasting package <a href="https://facebook.github.io/prophet/">Prophet</a> and for his deep expertise in causal inference. Cumulatively the founders bring over three decades of experience building data and analytics tools at top Silicon Valley companies.</p><p>Growth and operations teams are in continuous search of levers for improving key metrics and optimizing business processes and want to leverage quantitative company data rather than relying on qualitative anecdotes and hunches. Today they have to choose between asking data teams to run statistically rigorous but slow deep dives or self-serving using product analytics tools, which often result in misinterpreting imperfect data and chasing spurious correlations.</p><p>“I haven’t met a Head of Growth or Operations who was happy about their team’s ability to use company data in everyday decision-making,” said Mikhail. “They try and get disillusioned with self-serve analytics tools, which promise one-click solutions and trivialize the complexity of cleaning, modeling and interpreting data. There is a reason why data teams don’t want to touch them. I’ve observed many companies who can’t point to any meaningful wins from expensive product analytics tools they bought.”</p><p>Analytics tools used by data teams provide more expressivity and fine-grained control for working with data but they were built for standardized reporting, lag behind on interactivity and don’t support efficient collaboration between analytics and business domain experts for exploratory questions.</p><p>Motif re-imagines analytics from the ground up with the goal of providing the fastest statistically rigorous path from raw data to practical insights in modern tech companies. The key insight is that practical business questions are not about computing metrics but rather about identifying growth and optimization levers, which is better served using sequence analytics for working naturally with user and business event flows.</p><p>Motif is built on 5 key innovations, which come together to speed up exploratory analysis:</p><ul><li>Rich interactive sequence visualizations for identifying patterns in user and business flows</li><li>Small but expressive set of sequence operations to query flow data the way analysts think — most practical queries take less than 10 lines of code</li><li>Incremental query engine to seamlessly trade between query precision, speed and cost according to analyst’s needs</li><li>AI for identifying conversion predictors in event sequences using neural network models behind breakthrough LLM products like ChatGPT (adapted to model sequences of events instead of words)</li><li>In-browser local mode to experience Motif capabilities on proprietary data instantly without setup, data prep or data sharing.</li></ul><p>“Flow-based questions are everywhere in data-driven tech companies: interpreting experiment results, measuring attribution, optimizing user funnels, detecting fraud, etc.,” said Alex Deng, Principal Data Scientist at Airbnb and an early adopter of Motif. “SQL and existing data tools were not built for analyzing sequences and don’t quite work despite everybody trying to shoehorn them in. Motif has reimagined analytics for sequence questions from the ground up. I’ve never seen anything providing such a natural and powerful way to query, visualize and model event sequences to drive practical business decisions. I am especially excited about Motif’s novel AI approach to analytics bringing LLMs to event sequences.”</p><p>Motif was selected by InvestInData, a collective of over 50 data executives from a variety of cutting-edge tech companies such as DoorDash, Netflix, PayPal, Pinterest and Shopify, which picks a small number of most promising data startups each year to invest in. Joining the InvestInData portfolio provides Motif with a stamp of approval for its unique approach and access to unparalleled expertise of industry leaders with a birds-eye view of their data organizations.</p><p>Azadeh Moghtaderi, InvestInData investor and VP of Data at Coursera, praised Motif’s approach: “Throughout my career in data, I have worked with and evaluated numerous analytics tools. Few made me as excited as Motif did. Relying on their deep expertise with analytics in tech companies, the founders have reimagined a new powerful set of data abstractions and tradeoffs for an analytics tool, which respect the importance of statistical soundness but bring analysis much closer to the practical needs of business stakeholders. After seeing Motif in action, I had to be a part of this revolution.”</p><p>Viviana Faga, General Partner at Felicis, is excited about Motif’s position in the market: “As a tech marketing executive and investor, I have observed firsthand how the analytics space has become overcrowded with similar-sounding products, which always overpromise and underdeliver. Motif’s fresh and differentiated approach uses sequence analytics and has a unique way to apply AI to analytics data. Most companies struggle with complex data stacks and mountains of effort to deliver practical insights for everyday business decisions — now with Motif there’s an easy-to-use solution that will reinvent how tech companies use their data.”</p><p>For more information on how Motif Analytics is shaping the future of data analysis, visit <a href="https://motifanalytics.com/">motifanalytics.com</a>.</p><p><strong>About Motif</strong></p><p>Motif Analytics is an analytics product tailored to answer complex business questions through analyzing event sequences. It goes beyond traditional reporting (what is happening to the key business metrics?) and instead focuses on root-causing (why are metrics moving?) and guiding (how can the metrics be moved in the right direction?). Motif solves this by providing an interactive data wrangling, visualization and exploration experience to find insights in event sequences — “motifs”. This approach has been proven at Google and Uber, and is ripe for bringing to the wider data ecosystem. Learn more at <a href="https://motifanalytics.com/">motifanalytics.com</a>.</p><p><strong>About Amplify Partners</strong></p><p>Amplify Partners is an early-stage venture capital firm focused on AI, developer tools, data, and cybersecurity. With a decade-long track record of partnering with technical founders, Amplify has been the first investor in over 100 companies, including dbt Labs, Datadog, Fastly, Hex, Runway, SiMa.ai, Temporal, and more. Through its Build Team, Amplify collaborates with founders to establish connections with early customers, achieve product-market fit, and attract top talent. The firm is headquartered in Menlo Park and San Francisco, California. Discover more about Amplify at <a href="https://amplifypartners.com/">amplifypartners.com</a>.</p><p><strong>About Felicis</strong></p><p>Founded in 2006, Felicis is a venture capital firm investing in companies reinventing core markets, as well as those creating frontier technologies. The firm was the first to offer a Founder Development pledge, providing needed resources to help founders scale themselves. Felicis focuses on early-stage investments and currently manages over $3B in capital across nine funds. The firm is an early backer of more than 49 companies valued at $1B+. More than 100 of its portfolio companies have been acquired or gone public, including Adyen, Credit Karma, Cruise, Fitbit, Guardant Health, Meraki, Ring, and Shopify. The firm is based in Menlo Park and San Francisco in California. Learn more at <a href="https://felicis.com/">felicis.com</a>.</p><p>‍<strong>About InvestInData</strong></p><p>InvestInData is an angel syndicate made up of over 50 data executives from companies like DoorDash, Netflix, PayPal, Pinterest, and Shopify. The group leverages their collective experience to fund and advise early-stage data companies, solving problems they experience day to day. Learn more at <a href="https://investindata.ai/">investindata.ai</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=38ab744b1cb0" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[My browser WASM’t prepared for this. Using DuckDB, Apache Arrow and Web Workers in real life]]></title>
            <link>https://motifanalytics.medium.com/my-browser-wasmt-prepared-for-this-using-duckdb-apache-arrow-and-web-workers-in-real-life-e3dd4695623d?source=rss-a36de1cfc346------2</link>
            <guid isPermaLink="false">https://medium.com/p/e3dd4695623d</guid>
            <dc:creator><![CDATA[Motif Analytics]]></dc:creator>
            <pubDate>Thu, 20 Feb 2025 23:28:01 GMT</pubDate>
            <atom:updated>2025-02-24T03:53:17.149Z</atom:updated>
            <content:encoded><![CDATA[<h4>by <a href="https://www.linkedin.com/in/pmaciolek/">Przemyslaw Maciolek</a></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*K2C-Z0FG5L_9QOvt.jpg" /></figure><h3>Introduction</h3><p>At <em>Motif Analytics</em>, we are building a highly-interactive analytics tool, which allows finding insights in relatively large datasets, <strong>fully in-browser</strong>. We have a more traditional cloud-mode too, but the “local” (fully in-browser) mode is an important way for our users to experiment with <em>Motif</em> and see if it matches their needs, without making any commitments on their end. To make that possible, we employ several techniques, which include running <em>DuckDB WASM</em>, <em>Apache Arrow</em>, and <em>Web Workers</em>.</p><p>During the past months we learned some upsides and downsides of using these technologies. While this experience was gained on our specific use-cases, we believe that it is still somewhat universal and might be interesting for anyone working on modern, responsive web apps which process non-trivial volumes of data.</p><h3>What does parallelization have to do with web browsers?</h3><p>The idea of speeding up processing-intensive analytics tasks by parallelising them is almost as old as computing itself. It made a long way, starting with <em>shared memory architectures</em> of 1960’s and 1970’s, to be replaced by <em>clusters</em>, <em>Grid Computing</em> and eventually <em>Cloud</em>. I still remember the fun of building academic projects using the somewhat forgotten technologies, such as <a href="https://en.wikipedia.org/wiki/Parallel_Virtual_Machine"><em>PVM</em></a> or <a href="https://www.mpi-forum.org/"><em>MPI</em></a>. The world is very much different today, but ultimately all data-processing problems are very similar at their core and revolve around being able to break large tasks into smaller independent pieces and synchronizing their execution together.</p><p>The emergence of energy efficient multi-core cpus allows to bring the heavy computation even closer to the user by running it directly in the web browser. Computing-intensive tasks can be parallelized using <a href="https://developer.mozilla.org/en-US/docs/Web/API/Web_Workers_API"><strong><em>Web Workers</em></strong></a>, which are supported by all modern browsers and are becoming an <a href="https://www.smashingmagazine.com/2021/06/web-workers-2021/">important tool for writing responsive apps</a>, though with important limitations.</p><p>An interactive data analysis can be streamlined by using familiar tools (such as describing the queries using <em>SQL</em>). Here comes <a href="https://duckdb.org/"><strong><em>DuckDB</em></strong></a>, an Open-Source database designed for <em>OLAP</em> <em>(Online Analytics Processing)</em>. It’s to data warehouses what <em>Sqlite</em> is to <em>RDBMS</em>. It has few external dependencies, has rich language (<a href="https://duckdb.org/docs/sql/introduction.html">beautifully documented</a>) and runs very quickly, largely thanks to leveraging <a href="https://duckdb.org/why_duckdb.html#standing-on-the-shoulders-of-giants">state-of-the-art in database analytics</a>. <a href="https://www.vldb.org/pvldb/vol15/p3574-kohn.pdf"><strong><em>DuckDB WASM</em></strong></a> is a special version of <em>DuckDB</em>, compiled as <a href="https://webassembly.org/"><em>WebAssembly</em></a>, which allows to run it in the browser virtual machine, alongside the page script.</p><p>These two (<em>Web Workers</em> and <em>DuckDB WASM</em>) can be used together for a more sophisticated processing architecture. For example, some custom, domain-specific processing code can be executed in parallel using <em>Web Workers</em> and then fed to <em>DuckDB WASM</em>, where a convenient <em>SQL</em> query can handle the results and calculate aggregates.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/866/0*hAZICbPCvFDR-i0q.png" /></figure><p>Gluing the two together can be done using <a href="https://arrow.apache.org/"><strong><em>Apache Arrow</em></strong></a>, a language-agnostic framework using a standardized column-oriented memory format.</p><p>To make it even more compelling, the same architecture can be executed in server mode. <a href="https://nodejs.org/api/worker_threads.html"><em>Node.js worker threads</em></a> and <a href="https://github.com/duckdb/duckdb-node"><em>DuckDB Node library</em></a> can be organized in a very similar way, allowing for code reuse.</p><p>Wait! There are more use-cases! <em>DuckDB</em> can also be used for (fast) interchange using some of the popular data formats, such as <a href="https://duckdb.org/docs/api/wasm/data_ingestion.html"><em>Parquet, CSV or JSON</em></a>.</p><p>While this all sounds exciting, it also brings challenges, which we discuss below.</p><h3>DuckDB WASM Performance</h3><p>While <em>DuckDB WASM </em>offers practically the same capabilities as <a href="https://duckdb.org/docs/archive/0.9.2/api/overview">any other version of <em>DuckDB</em></a>, it is executed in a <a href="https://blog.container-solutions.com/wtf-is-webassembly">stack-based virtual machine</a> rather than as a native library code. While <em>WASM</em> is considered fast, it adds overhead over running the code natively. Additionally, currently <em>DuckDB WASM</em> is a single-threaded engine (a remnant of the now-outdated <a href="https://duckdb.org/2021/10/29/duckdb-wasm.html#multithreading"><em>SharedArrayBuffer</em> usage limitations</a>), with experimental <a href="https://web.dev/articles/cross-origin-isolation-guide"><em>COI</em></a> support.</p><p>Ultimately, this means that the queries are running much slower than in a native <em>DuckDB</em> instance locally (executed either through shell or as a <em>Node</em>/<em>Python</em>/etc. library).</p><p><strong>Example 1: TPC-H lineitem query</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*AkH6BNhO3Z1S4bk-.png" /></figure><p>So what sort of performance difference can we expect from <em>DuckDB WASM</em> vs. native CLI? Here’s an ad-hoc benchmark on my <em>M1 Pro</em>.</p><p><strong>Result 1: DuckDB (native lib) CLI 0.9.2</strong></p><pre>D PRAGMA threads=1;<br>D .timer on<br>D select ...<br>┌──────────────┬──────────────┬──────────┬────────────────────┬─────┐<br>│ l_returnflag │ l_linestatus │ sum_qty  │   sum_base_price   │ ... │<br>│   varchar    │   varchar    │  double  │       double       │ ... │<br>├──────────────┼──────────────┼──────────┼────────────────────┼─────┤<br>│ A            │ F            │ 380456.0 │  532348211.6500006 │ ... │<br>│ N            │ F            │   8971.0 │        12384801.37 │ ... │<br>│ N            │ O            │ 742802.0 │ 1041502841.4499991 │ ... │<br>│ R            │ F            │ 381449.0 │  534594445.3500006 │ ... │<br>└──────────────┴──────────────┴──────────┴────────────────────┴─────┘<br>Run Time (s): real 0.807 user 0.590743 sys 0.035380</pre><p><strong>Result 2: DuckDB WASM Shell 0.9.2 on Chrome 119.0</strong></p><pre>duckdb&gt; .timer on<br>duckdb&gt; select ...<br>┌──────────────┬──────────────┬─────────┬────────────────────┬─────┐<br>│ l_returnflag ┆ l_linestatus ┆ sum_qty ┆ sum_base_price     ┆ ... │<br>╞══════════════╪══════════════╪═════════╪════════════════════╪═════╡<br>│ A            ┆ F            ┆  380456 ┆  532348211.6499983 ┆ ... │<br>│ N            ┆ F            ┆    8971 ┆ 12384801.369999997 ┆ ... │<br>│ N            ┆ O            ┆  742802 ┆ 1041502841.4499979 ┆ ... │<br>│ R            ┆ F            ┆  381449 ┆  534594445.3499986 ┆ ... │<br>└──────────────┴──────────────┴─────────┴────────────────────┴─────┘<br>Elapsed: 03.257 s</pre><p>We’re getting <strong>0.8s</strong> (native lib, single thread) vs. <strong>3.3s</strong> (<em>WASM</em>), which makes <em>WASM</em><strong>4x slower</strong> for this particular case. But hold on. What if we copy the data to a temporary table first?</p><pre>CREATE TABLE lineitem AS SELECT * FROM &#39;lineitem.parquet&#39;;</pre><p>This made it faster for both cases, taking the time down to <strong>0.1s</strong> (native lib, single thread) vs <strong>0.4s</strong> (<em>WASM</em>), keeping the 4x factor we observed before.</p><p><strong>Example 2: Calling an expensive function</strong></p><p>How about another example? We will use the same dataset, but construct a different query. One calling a somewhat expensive function, which must be executed for all rows.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*_kmcxfJEEPIO8lKh.png" /></figure><p><strong>Result 3: DuckDB (native lib) CLI 0.9.2</strong></p><pre>D PRAGMA threads=1;<br>D .timer on<br>D select ...<br>┌──────────────────────────────────┐<br>│             md5_val              │<br>│             varchar              │<br>├──────────────────────────────────┤<br>│ 0000031428caeaafcddcf1a1f5d4ea26 │<br>│ 000005560d5cc20991b8bb57e64f58a0 │<br>│ 00000aa836f58524e6ae1d75c9246511 │<br>│ 000016d700b533970506d1164d0ee003 │<br>│ 00001921a51a1880a184ed25fc3d95b8 │<br>└──────────────────────────────────┘<br>Run Time (s): real 1.679 user 1.533280 sys 0.098910</pre><p>What if we increase the number of threads? Here’s what happens on my <em>M1 Pro</em> laptop:</p><pre>D PRAGMA threads=8;<br>D select ...<br>┌──────────────────────────────────┐<br>│             md5_val              │<br>│             varchar              │<br>├──────────────────────────────────┤<br>│ 0000031428caeaafcddcf1a1f5d4ea26 │<br>│ 000005560d5cc20991b8bb57e64f58a0 │<br>│ 00000aa836f58524e6ae1d75c9246511 │<br>│ 000016d700b533970506d1164d0ee003 │<br>│ 00001921a51a1880a184ed25fc3d95b8 │<br>└──────────────────────────────────┘<br>Run Time (s): real 0.313 user 1.742058 sys 0.019078</pre><p><strong>Result 4: DuckDB WASM Shell 0.9.2 on Chrome 119.0</strong></p><pre>duckdb&gt; .timer on<br>duckdb&gt; select ...<br>┌──────────────────────────────────┐<br>│ md5_val                          │<br>╞══════════════════════════════════╡<br>│ 0000031428caeaafcddcf1a1f5d4ea26 │<br>│ 000005560d5cc20991b8bb57e64f58a0 │<br>│ 00000aa836f58524e6ae1d75c9246511 │<br>│ 000016d700b533970506d1164d0ee003 │<br>│ 00001921a51a1880a184ed25fc3d95b8 │<br>└──────────────────────────────────┘<br>Elapsed: 01.881 s</pre><p>Now, the durations are almost on par. We’re getting <strong>1.7s</strong> (<strong>native lib, single thread</strong>) vs <strong>1.9s</strong> (<strong><em>WASM</em></strong>), which makes for a modest 10% penalty when executing the query as <em>WASM</em>. Quite impressive!</p><h3>Takeaway</h3><p>As always, YMMV depending on the properties of the dataset and query complexity, but observing this sort of differences is not surprising. Unlike the native version, <em>WASM</em> runs in a virtual machine. Additionally, making it use multiple workers is fairly new (with next-coi <a href="https://duckdb.org/2021/10/29/duckdb-wasm.html#multithreading">experimental version</a> the single-thread limitation should be lifted) and I’m not sure if it can be already recommended.</p><p>On top of the above, <strong>memory available to <em>WASM</em> is limited by the browser</strong> (in case of <em>Chrome</em>, the limit is currently set at 4GB per tab). <strong>This can be learnt painfully when trying to load larger datasets or doing non-trivial joins.</strong> <em>DuckDB</em> is famous for “never throwing out of memory” (<a href="https://bwlewis.github.io/duckdb_and_r/taxi/taxi.html">and rightfully so</a>) but <em>WASM</em> cannot use the <a href="https://github.com/duckdb/duckdb-wasm/issues/872">local persistency yet</a> for spilling data while processing the query if it doesn’t fit in the available memory. Again, the latter will hopefully change soon, <a href="https://github.com/duckdb/duckdb-wasm/pull/1490">with work underway</a>. Until this happens though, it is just another known <em>WASM</em> limitation.</p><p>Despite all of this, <a href="https://shell.duckdb.org/versus"><strong><em>DuckDB WASM</em> is currently one of the fastest (if not the fastest)</strong></a> engine for querying fully in-browser. However, <strong>when possible, running native code (outside of the browser limitations) will always be faster and support much bigger queries</strong>.</p><h3>Web Workers and Schema Consistency</h3><p>In our architecture we’re using <em>Web Workers</em> to parallelize a complex task and feed its output to <em>DuckDB</em>. Before we go into some specific details, it’s worth noting that using <em>Web Workers</em> is much different to, say, writing concurrent code in <em>Golang</em>, <em>Java</em> or <em>Rust</em>. This is nicely discussed by <a href="https://www.smashingmagazine.com/2021/06/web-workers-2021/#javascript-s-concurrency-models">Surma in his article</a>, but TLDR, <strong><em>Web Workers</em> are largely independent from each other and can only pass <em>Byte Array</em> buffers</strong> (either as a <a href="https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/SharedArrayBuffer"><em>SharedArrayBuffer</em></a><em> </em>or <a href="https://developer.mozilla.org/en-US/docs/Web/API/Web_Workers_API/Transferable_objects">transferable <em>ArrayBuffer</em></a>).</p><p>This implies that the volume of messages used for synchronization should be fairly low and special care must be taken when selecting the serialization/deserialization method. It’s just another reason why <em>Apache Arrow</em> fits neatly here, as its <em>IPC</em> buffers make a perfect match (the vector data model does not even need to be serialized and can be just passed as chunks of memory).</p><p><em>DuckDB</em> adds only a very small overhead when reading data in <em>Arrow</em> format while executing the query, which makes it all a very compelling use-case. The one we would hope to use in a low-latency analytical application.</p><p>This architecture can largely be implemented as in the following diagram.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*dzZKOzX-v60pJYME.png" /></figure><p>Before we run the <em>SQL</em> query, we just need to collect the <em>Apache Arrow</em> tables from the workers and pass these to <em>DuckDB</em>, forming a single logical table. The code for achieving that goal can be illustrated in the following snippet:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*uGmIU_yAzul7lLbX.png" /></figure><p>Unfortunately, running it <strong>will fail</strong> with a following error:</p><pre>Conversion Error: Failed to insert into table &#39;books&#39;:<br>    Could not convert string &#39;4th century BC&#39; to DOUBLE<br>       at O.onMessage (index.js:11583:17)</pre><p>While each <em>Arrow Table</em> is a valid table on its own and all their column names match, they have (slightly) inconsistent schemas and thus they are rejected by <em>DuckDB</em> while executing the insert. As it turns out, this can be tricky even for simpler cases, where field can be null in some partitions, as seen in <a href="https://github.com/apache/arrow/issues/19233">ARROW-2860</a>, which is 5 years old.</p><p>Using multiple <em>Arrow Tables</em>, where each might contain a separate set of <em>BatchRecords</em> doesn’t seem to be addressed by <a href="https://arrow.apache.org/docs/format/Columnar.html#serialization-and-interprocess-communication-ipc"><em>Apache Arrow</em> spec</a>, so perhaps it can be considered a “grey area” and it seems one should just do whatever possible to not have to deal with this problem.</p><p>Ultimately, there seems to be no simple generic solution to schema reconciliation. <em>C++ Arrow API</em> has <a href="https://arrow.apache.org/docs/cpp/api/table.html#_CPPv4N5arrow17ConcatenateTablesERKNSt6vectorINSt10shared_ptrI5TableEEEE24ConcatenateTablesOptionsP10MemoryPool">ConcatenateTables</a>, which allows to handle some cases via setting the <a href="https://github.com/apache/arrow/blob/apache-arrow-14.0.0/cpp/src/arrow/table.h#L276-L290">unify_schemas</a> property to true (and providing more <a href="https://github.com/apache/arrow/blob/79c72a67f90719fb1af315a9b2b8caa25d667775/cpp/src/arrow/type.h#L407-L477">details as needed</a>), but it cannot be called from Javascript (or any other language where it’s not feasible to call <em>FFI</em>). There are some exotic initiatives to bridge the gap in having access to the richness of the <em>C++</em> library, like <em>Apache Arrow WebAssembly</em> library implementations, but these seem to be <a href="https://github.com/domoritz/arrow-wasm">not actively developed</a> or are based on an <a href="https://github.com/perspective-community/arrow-wasm-cpp">older Arrow version</a>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*wdwLIJDCZ0v0C4D2.png" /></figure><p>Eventually, one must handle this case oneself or (preferably) <strong>avoid dealing with schema reconciliation in the first place</strong>, though it might not always be possible.</p><h3>Gaps in the capabilities and… bugs</h3><p>While <em>DuckDB WASM</em> is getting a lot of attention recently, it’s perhaps the <em>Python</em> and Java libs which are used most actively (a year ago, <a href="https://drive.google.com/file/d/1bscd15MjXnWc7SGEX0AF8F2zYdLVgx-q/view">they were getting an order of magnitude more downloads</a> than the <em>Nodejs</em> library). As such, probably neither <em>WASM</em> nor <em>Node.js DuckDB</em> library should yet be considered a very mature project. The community is very active and <em>DuckDB</em> team pushes good efforts into it, but simply put, various gaps can be easily encountered.</p><p>To give a personal perspective, here are some examples, which I hit recently:</p><ul><li><a href="https://github.com/duckdb/duckdb-wasm/issues/1477">Querying some <em>JSON</em> strings or <em>Parquet</em> files</a> throws “memory access out of bounds” error. It doesn’t seem to happen on <em>DuckDB WASM v1.27.0</em> or earlier, so downgrading is a viable workaround until the fix is made.</li><li><a href="https://github.com/duckdb/duckdb-node/issues/32">Only the first table is registered by register_buffer()</a> — the function implies that it might be possible to pass several <em>Apache Arrow</em> tables, which could form a single logical <em>DuckDB</em> table. It’s not the case though. The workaround I found is registering each <em>Apache Arrow</em> table separately and concatenating into a temporary table. Probably a better one would be reconciling all record batches and registering a single <em>Apache Arrow</em> table.</li><li><a href="https://github.com/duckdb/duckdb/issues/8944">Crashing the library on some UNION ALL and ORDER BY queries</a> — the fix for this one is under active development. This makes the <em>DuckDB Node</em> library unsalvageable and the whole process needs to be restarted (ouch!). Downgrading to <em>DuckDB v0.8</em> works as a temporary workaround.</li></ul><p>All of this might look underwhelming but it can also be seen as a consequence of the sudden explosion of interest in the project, which needs to go through the teething stage. <a href="https://github.com/duckdb/duckdb/blob/main/CONTRIBUTING.md">The community is welcoming contributors</a> and as with any open-source project, it’s ultimately in the hands of us, the users, to report and <a href="https://github.com/duckdb/arrow/pull/23">help fix</a> the discovered issues.</p><p>Also, some of the limitations from just a few months ago are no longer present thanks to the hard work of the team. E.g., it’s <a href="https://duckdb.org/docs/api/wasm/extensions.html">now possible to use many of the extensions in <em>DuckDB WASM</em></a>. I had do write some workarounds for this a few months ago (when I needed to have better <em>JSON</em> support). These are now no longer needed thanks to enabling the <a href="https://duckdb.org/docs/extensions/json">JSON extension</a> in <em>DuckDB WASM</em> (and that’s just one of them). It’s a topic for another post, but <a href="https://duckdb.org/docs/extensions/overview.html"><em>DuckDB</em> extensions architecture is truly amazing</a>.</p><h3>Wrap up</h3><p>Building fast in-browser processing stacks can be accelerated by using some of the recently popularized technologies. The recipe is basically: take <em>DuckDB WASM</em> (data interchange and <em>SQL</em>), <em>Web Workers</em> (parallelizing processing-intensive, domain-specific tasks) and glue them together via <em>Apache Arrow</em>.</p><p>Unfortunately, they all have limitations, which sometimes require extra effort to make them work together. I believe many of these issues will go away in the upcoming months. For example, <em>DuckDB WASM</em> capacity limitations should be much improved with adding support for <a href="https://github.com/duckdb/duckdb-wasm/pull/1490"><em>OPFS</em></a> and embracing <em>COI</em> version. The “teething problems” will hopefully diminish as more testing will happen by the community and the discovered bugs will get resolved.</p><p>From a developer perspective, one can look at this as an opportunity. DuckDB brings many sought-after capabilities and is on the <strong>curve of accelerated traction. </strong>We are going to see even more use cases for it. As of today, it still has some challenges, which make it a terrific opportunity to jump in, help the community and learn at the same time.</p><p>If you’re interested in learning more, you can <a href="/service/mailto:mikpanko@gmail.com">reach out to us</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e3dd4695623d" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[A Beginner’s Guide to Sequence Analytics in SQL]]></title>
            <link>https://motifanalytics.medium.com/a-beginners-guide-to-sequence-analytics-in-sql-e4779daaf67f?source=rss-a36de1cfc346------2</link>
            <guid isPermaLink="false">https://medium.com/p/e4779daaf67f</guid>
            <dc:creator><![CDATA[Motif Analytics]]></dc:creator>
            <pubDate>Thu, 20 Feb 2025 23:23:00 GMT</pubDate>
            <atom:updated>2025-02-20T23:23:00.258Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*gGQaTC9dhEBAi_GX.png" /></figure><p><em>(this is a guest post from </em><a href="https://www.linkedin.com/in/gagejustin/"><em>Justin Gage</em></a><em>)</em></p><p><strong>Sequences:</strong> they’re all around us. More specifically, they’re in your data warehouse with timestamps, payloads, and mysterious columns from Segment. Many of the real, course-changing insights that data teams dream are hidden deep inside these elusive event streams. This post will help you find them, using your favorite neighborhood query language.</p><p>For the purposes of this journey, imagine you’re a Data Scientist at Netflix. You and your team want to better understand what your watch funnel looks like — what does a successful session entail? — as well as understand how users interact with important features like search.</p><h3>Getting started: event prep work</h3><p>Before we can dig into what’s going on in our sequence data, we’ll need to clean up and organize a bit. Most event data I’ve worked with needs a good deal of prep to get it ready for analysis: timestamps are funky, data is missing, some events mean the same thing, etc. We’ll walk through a few common prep steps and sample queries to get them done.</p><h4><strong>Grouping events that mean the same thing together</strong></h4><p>In our dataset, we have 4 different events that relate to interacting with the search feature:</p><ol><li>search_bar_clicked — the user clicked on the search icon</li><li>search_bar_typed — the user typed something into the search bar</li><li>search_results_page — the user submitted what they typed in the bar and got some results</li><li>search_result_clicked — the user clicked on a result in the search results</li></ol><p>If this project was all about search and understanding how users engage with it, each one of these events would carry individual meaning. But for our purposes, it’s more than enough to just know that the user engaged with the search feature; we don’t need to know every detail about the search experience. So it makes sense to collapse any search related event into a single event type. Here’s how to do that in SQL:</p><pre>SELECT<br>	user_id,<br>	&#39;search&#39; AS event_name,<br>	timestamp,<br>	movies_number,<br>	browser,<br>	app,<br>	country,<br>	language,<br>	position,<br>	id,<br>	title,<br>	category,<br>	rating,<br>	duration,<br>	video_quality,<br>	volume,<br>	subtitles,<br>	search_results<br>FROM &quot;netflix.csv&quot;<br>WHERE event_name IN (&#39;search_bar_clicked&#39;,&#39;search_bar_typed&#39;,&#39;search_results_page&#39;,&#39;search_result_clicked&#39;)</pre><p>This simple statement filters the dataset for search related events, and renames them all the same thing. We’ll string it together with subsequent cleaning ops in a chain of CTEs. You could also do it inline with a CASE WHEN:</p><pre>SELECT<br>	user_id,<br>	CASE WHEN event_name IN (&#39;search_bar_clicked&#39;,&#39;search_bar_typed&#39;,&#39;search_results_page&#39;,&#39;search_result_clicked&#39;)<br>	THEN &#39;search&#39; ELSE event_name END AS event_name,<br>	timestamp,<br>	movies_number,<br>	browser,<br>	app,<br>	country,<br>	language,<br>	position,<br>	id,<br>	title,<br>	category,<br>	rating,<br>	duration,<br>	video_quality,<br>	volume,<br>	subtitles,<br>	search_results<br>FROM event_data</pre><h4>Double checking how long events have existed for</h4><p>Given how iteratively most companies implement event tracking, it’s likely that some events started being captured earlier or later than others. Before diving into analysis, it’s important to get a sense of how far back tracking reliability goes for what you think the important events in your sequence might be. Grouping events by their minimum and maximum timestamp is a good place to start:</p><pre>SELECT<br>	event_name,<br>	MIN(timestamp) AS min_timestamp,<br>	MAX(timestamp) AS max_timestamp<br>FROM event_data<br>GROUP BY 1<br>ORDER BY 2 DESC</pre><p>In our case, it looks like the favorites_add event has a min_timestamp of only a few weeks ago. After some back and forth with the product team, it turns out that the event tracking library was under maintenance when they built the “add to favorites” feature, so tracking for that event was only added in a recent sprint. Bummer, but either way, we now know not to include this event in our analysis. This is a much better situation than spending 4–5 hours wondering why conversion to favorites is so low in your funnel, only to realize that the events are missing.</p><h4>Renaming events for the purposes of this analysis</h4><p>Events get named with many different stakeholders in mind, and sometimes those names are less than ideal for the analysis you’re trying to do. In our data set, there are some events with names like user_watch_time_start that can be simplified to watch_start with the same logic as above:</p><pre>SELECT<br>	user_id,<br>	CASE WHEN event_name = &#39;user_watch_time_start` THEN `watch_start` ELSE event_name END AS event_name,<br>	...<br>FROM event_datasq</pre><h4>Defining a session window</h4><p>Perhaps the most important part of prep! To analyze a set of events or a funnel, there needs to be some sort of definition of when it “starts” and when it “stops.” For many use cases — like e-commerce, or a web app with clear “presence” like Netflix — this is pretty straightforward. But for others, like when I was working at DigitalOcean and analyzing how people spun up and shut down servers, session definition can require some creativity.</p><p>Sessions are most simply defined as any sequence of events with at least some time frame of inactivity as the “end,” usually an hour or so. It’s not a perfect definition, but it probably means that your user at least stepped away from the computer.</p><p>The goal is to end up with some sort of session_id or session_number column (or both) attached to each event. If you’re working as part of a data team, there’s a good chance you already have this done for you in the data warehouse. In the past I’ve worked off of this <a href="https://github.com/dbt-labs/segment/blob/main/models/sessionization/docs.md">sample SQL snippet from dbt</a> made for Segment events, tweaked for my use case.</p><p>It’s important to note that this sample snippet is helpful, but just a start. Sessionizing your events is not a trivial task, and can require double digit hours of work and follow up tweaks as your team grows.</p><h3>Creating an intermediate event dataset for analysis</h3><p>For funnel analysis, most of the time you can ignore the order that events happened in: later steps (like added to cart) naturally cannot happen before earlier steps (visited website). But for more general analysis, you need your events to be in a roughly standardized format with order attached. We need to get to something like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*aBRvvcgRMeaj-cMJ.png" /></figure><p>Each one of these columns will be important for queries we’ll want to write later:</p><ul><li>num_session — starting at 1, which number session this is for the user. For analyzing how users onboard, we might filter this for 1, or for analyzing post onboarding activity, &gt;1.</li><li>num_event — starting at 1, which number event this is in the current session for the user.</li><li>prev_event — the event the user did directly before this one</li><li>next_event — the event the user did directly after this one</li><li>time_since_last_event</li><li>time_to_next_event</li><li>payload — any data associated with the event (e.g. if it’s movie_watch, the name of the movie)</li></ul><p>I also will sometimes add things like total number of events in the session, whether the session ended with a conversion event or not, etc. I should mention that this is the schema <em>I like</em> when I’m analyzing sequences, but you may find some of the columns superfluous, or think I’m missing some. Everyone’s data and project is different!</p><p>Before getting started with some sample queries, you’re going to need to make a decision about how you want to build this intermediate data set. I’ve used all of these methods for different projects, it really just depends on your constraints and how long you intend this data set to live for.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*sNMsgfPXgVK_bjE0.png" /></figure><p>For this post, I’ll write each SQL query as a standalone statement for simplicity’s sake. Let’s walk through how to derive the data set we need using some basic (and a little intermediate) window function action.</p><h4>Calculating event order with ROW_NUMBER</h4><p>The ROW_NUMBER function tells you what order a given row is in a given window given an ordering criteria. In English, this will help us know which event the current event is in a given session. Because we want the event number to reset every time there’s a new session, we’ll partition by session_num and order by timestamp.</p><pre>SELECT<br>	user_id,<br>	event_name,<br>	timestamp,<br>	ROW_NUMBER() OVER (PARTITION BY user_id, session_id<br>		ORDER BY timestamp) AS event_sequence_number<br>FROM event_data</pre><p>Note that for window functions like ROW_NUMBER, it’s absolutely critical that your event timestamps are in good working order. If there’s a slight delay on when the event gets emitted, or two events are given the same timestamp when they really happened one after another, this SQL logic will not work. Which is also why we’re using ROW_NUMBER here instead of RANK or DENSE_RANK, because we’re assuming that there will be 0 events with identical timestamps for a given user.</p><h4>Calculating previous and next events</h4><p>This one is easy:</p><pre>SELECT<br>	user_id,<br>	event_name,<br>	timestamp,<br>	LAG(event_name) OVER (PARTITION BY user_id ORDER BY timestamp) AS prev_event,<br>	LEAD(event_name) OVER (PARTITION BY user_id ORDER BY timestamp) AS next_event<br>FROM event_data</pre><p>Depending on what you’re trying to do, you can also increase the LEAD or LAG distance to more than one and get two events ago, 3 events in the future, etc.</p><pre>SELECT<br>	user_id,<br>	event_name,<br>	timestamp,<br>	LAG(event_name,3) OVER (PARTITION BY user_id, session_id<br>ORDER BY timestamp) AS three_events_prev,<br>	LEAD(event_name,2) OVER (PARTITION BY user_id, session_id<br>ORDER BY timestamp) AS two_events_next<br>FROM event_data</pre><p>Another thing I’ll sometimes use these functions for is calculating time distance between events. This is necessary for session logic (you need to break up a session if a previous event is more than N minutes in the past), but also useful for looking at which events take a long time to get to (and thus perhaps indicate areas of improvement for your product).</p><pre>SELECT<br>	user_id,<br>	event_name,<br>	timestamp,<br>	timestamp - LAG(timestamp) OVER (PARTITION BY user_id, session_id<br>	ORDER BY timestamp) AS time_since_last_event,<br>	LEAD(timestamp) OVER (PARTITION BY user_id, session_id<br>	ORDER BY timestamp) - timestamp AS time_to_next_event,<br>FROM event_data</pre><h4>First and last event in the sequence</h4><p>Another common question in analysis is “what event did this session start with?” In our data set, it’s going to be useful to know if a watch session started with search, as opposed to starting with the favorites tab or a different source. And for that we can use the lesser known FIRST_VALUE and LAST_VALUE functions. They grab the first event in the given partition by your order clause.</p><pre>SELECT<br>	user_id,<br>	event_name,<br>	timestamp,<br>	FIRST_VALUE(event_name) OVER (PARTITION BY user_id, session_id<br>ORDER BY timestamp) AS first_event,<br>	LAST_VALUE(event_name) OVER (PARTITION BY user_id, session_id<br>ORDER BY timestamp) AS last_event,<br>FROM event_data</pre><p>By now you’ve probably realized that this post could have been titled “an intermediate guide to window functions” because analyzing sequences in SQL is basically window function con.</p><h3>Analyzing our nice, clean dataset</h3><p>By now, you’ve already done the hard work. Now it’s time to ask questions of our pristine, ordered, information-full event sequence data set. Here are some common ones that I’ve done in the past.</p><h4>Building a basic funnel</h4><p>By far the most common use case for event data like this is building a funnel for your business. Our data is product related (i.e. there are no marketing-specific events like CTA clicked) so we can design a (very) simple one:</p><pre>Looked around: home_page OR searchFound a movie: movie_pageStarted a movie: watch_startFinished a movie: watch_end</pre><p>Our team is mostly concerned with how many users accomplished each of these funnel stages over the course of a discrete time frame (in the future, compared to a previous time frame). Here’s a basic query for it:</p><pre>SELECT<br>	COUNT(DISTINCT CASE WHEN event_name = &#39;search&#39; OR event_name = &#39;home_page&#39; THEN user_id ELSE NULL END) AS count_users_stage_one,<br>	COUNT(DISTINCT CASE WHEN event_name = &#39;movie_page&#39; THEN user_id ELSE NULL END) AS count_users_stage_two,<br>	COUNT(DISTINCT CASE WHEN event_name = &#39;watch_start&#39; THEN user_id ELSE NULL END) AS count_users_stage_three,<br>	COUNT(DISTINCT CASE WHEN event_name = &#39;watch_end&#39; THEN user_id ELSE NULL END) AS count_users_stage_four<br>FROM cleaned_data</pre><p>And indeed our results do look like a funnel:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Z_MRm0eF56jymHDa.png" /></figure><p>Most startups I’ve worked at use a simple query barely more complex than this one to power their dashboard funnel. But the astute reader will note several limitations of this overly simplistic funnel design: it’s missing events users can take, finishing a movie isn’t the only desirable user path, events can happen out of order, etc. Building funnels can get arbitrarily complex; it’s a topic that deserves its own blog post.</p><h4>How many users took a specific path?</h4><p>Our data team is curious about how many watch sessions start with visits to the home page (and not search or the favorites tab). Expressed in a sequence, that’s:</p><pre>home_page → home_page_click → movie_page → watch_start</pre><p>The key is that these events happened after one another, it doesn’t matter where in the sequence they did (e.g. if the home_page event’s event_num is 1 or 12).There is theoretically a way to do this with our existing intermediate dataset but it’s going to be much easier to update the data set and add more LAG functions. We’re looking for a 4 event sequence, and we already have one LAG, so we just need to add 3 more:</p><pre>SELECT<br>	user_id,<br>	event_name,<br>	timestamp,<br>	LAG(event_name) OVER (PARTITION BY user_id ORDER BY timestamp) AS prev_event,<br>	LAG(event_name,2) OVER (PARTITION BY user_id ORDER BY timestamp) AS two_prev_event,<br>	LEAD(event_name,3) OVER (PARTITION BY user_id ORDER BY timestamp) AS three_prev_event,<br>FROM event_data</pre><p>We can then write a simple query to see how many users took a specific path:</p><pre>SELECT<br>	COUNT(DISTINCT user_id)<br>FROM lag_data<br>WHERE event_name = &#39;watch_start&#39;<br>	AND prev_event = &#39;movie_page&#39;<br>	AND two_prev_event = &#39;home_page_click&#39;<br>	AND three_prev_event = &#39;home_page&#39;</pre><p>Astute readers will notice that this method is crude, because it doesn’t account for <em>any</em> deviations from the prescribed path. A user might be perusing the home page, click on a movie, and then go back to the home page before finding the movie they actually want to watch. This lack of flexibility in expression can make SQL a difficult language to use for sequence analysis.</p><h4>How many users did X events at any point in their path?</h4><p>If order doesn’t matter (in contrast to the above query), this one is pretty simple: we can do a distinct count of users who have at least one event. Our team is curious to know how many users actually end up finishing a full trailer:</p><pre>SELECT COUNT(DISTINCT user_id)<br>FROM event_data<br>WHERE event_name = &#39;trailer_end&#39;</pre><p>If we want to filter for users who have done multiple events in a sequence, but order doesn’t matter, you can do something like this:</p><pre>WITH sequence_data AS<br>(<br>	SELECT user_id<br>	FROM event_data<br>	GROUP BY user_id<br>	HAVING (MAX(CASE WHEN event_name = &#39;home_page&#39; THEN 1 ELSE 0 END) +<br>		MAX(CASE WHEN event_name = &#39;home_page_click&#39; THEN 1 ELSE 0 END)) &gt; 1<br>)<br>SELECT COUNT(user_id)<br>FROM sequence_data</pre><p>The first CTE aggregates all of the users who have at least one home_page event and one home_page_click event, and the second one counts how many there are. Note that we need to use MAX here instead of summing the two columns, otherwise you could have a user with two home page visits and no home page clicks who makes it through the filter.</p><h4>How many users did NOT do a specific event in their path?</h4><p>A hacky way to do this is to get the total number of users who did the specific event, and subtract it from the total number of users in the data set. If we wanted to aggregate the number of users in our data set who have never watched a trailer:</p><pre>SELECT<br>	COUNT(DISTINCT user_id) AS count_users,<br>	COUNT(DISTINCT CASE WHEN event_name = &#39;trailer_start&#39; THEN user_id ELSE NULL END) AS count_users_watched_trailer<br>FROM event_data</pre><p>This is a little trick I picked up a few years ago to avoid needing a filter or multiple queries to get a user count based on row data (I’m using DuckDB and there’s no COUNT_IF function). You set up a CASE WHEN statement that returns the user_id if true, and NULL if false.</p><p>By the way, in many cases churn is actually a special case of this query, where no event has happened in that past X timeframe.</p><h4>Which events tend to be followed by other events?</h4><p>This one requires a bit of string manipulation. What we’ll do is create string event pairs using the event_name and prev_event columns, and group and count them. Here’s a simple version:</p><pre>SELECT<br>	CONCAT(prev_event, &#39;--&gt;&#39;, event_name) as event_pair,<br>	COUNT(*) as count,<br>	COUNT(DISTINCT user_id) as count_users<br>FROM event_data<br>GROUP BY 1<br>ORDER BY 2 DESC</pre><p>Here’s a version that removes identical event pairs, and events with no previous event. The watch_heartbeat event gets sent every few seconds while a user is watching something, so we can remove that too.</p><pre>SELECT<br>	CONCAT(prev_event, &#39;--&gt;&#39;, event_name) as event_pair,<br>	COUNT(*) as count,<br>	COUNT(DISTINCT user_id) as count_users<br>FROM cleaned_data<br>WHERE event_name != prev_event<br>	AND event_name != &#39;watch_heartbeat&#39;<br>	AND prev_event IS NOT NULL<br>GROUP BY 1<br>ORDER BY 2 DESC</pre><p>Interesting…it seems like the home_page to home_page_click is the most popular event pair in our data set.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*q7oxVh-rJlJvvS2B.png" /></figure><h4>How much time is there between different event pairs?</h4><p>Here we’ll build off of the previous query, but instead of counting occurrences of the pairs, we’ll measure the time between them. I’ve added median, average, max, and min functions, and included the filters from the last section.</p><pre>SELECT<br>	CONCAT(prev_event, &#39;--&gt;&#39;, event_name) AS event_pair,<br>	MEDIAN(DATE_PART(&#39;millisecond&#39;, time_since_last_event)) AS median_ms_since_last_event,<br>	AVG(DATE_PART(&#39;millisecond&#39;, time_since_last_event)) AS avg_ms_since_last_event,<br>	MAX(DATE_PART(&#39;millisecond&#39;, time_since_last_event)) AS max_ms_since_last_event,<br>	MIN(DATE_PART(&#39;millisecond&#39;, time_since_last_event)) AS min_ms_since_last_event,<br>FROM cleaned_data<br>WHERE event_name != prev_event<br>	AND event_name != &#39;watch_heartbeat&#39;<br>	AND prev_event IS NOT NULL<br>GROUP BY 1</pre><h3>A final disclaimer: this was a bit too easy</h3><p>Lest the reader get the sense from this post that sequence analytics in SQL is a straightforward, simple task, this section is meant to disabuse you of that notion. The sample queries — and situations — in this post are great simplifications of what you’ll most likely encounter in the wild. Here are a few examples:</p><h4>1. Sessionization is a major pain point</h4><p>Sessionizing your events is a lot more complex than copying a few lines of SQL, and will require continuous tweaking as you learn more about your downstream needs.</p><h4>2. Consolidating events requires care and iteration</h4><p>We renamed all search events to “search” which roughly works for this analysis, but is likely too simplistic for more complex ones. Often, the dimensions you need will be spread across different types of these events and you’ll need to figure out a way to get what you need.</p><h4>3. Single queries are easy, but bigger ones are hard</h4><p>Each one of the sample queries in the last section is simple enough on their own, but where things get hairy is when you need to combine and iterate on them. As with our 4-lag example, you can see that some analysis requires you to “destroy” the data set in a sense, and having to do this over and over again is hard.</p><h4>4. Chaining larger queries is computationally intensive</h4><p>Once you gather these queries together and they get bigger, they will start to take a long time to run and get expensive.In other words, take caution when approaching this branch of analytics — it’s not something that’s as simple as “I learned how to do it in a blog post.” Having said that, I do hope this was helpful 😉</p><p><em>Other questions we missed? Let us know at </em><a href="/service/mailto:hello@motifanalytics.com"><em>hello@motifanalytics.com</em></a><em>.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e4779daaf67f" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Introducing SOL: Sequence Operations Language]]></title>
            <link>https://motifanalytics.medium.com/introducing-sol-sequence-operations-language-87a0d1d73497?source=rss-a36de1cfc346------2</link>
            <guid isPermaLink="false">https://medium.com/p/87a0d1d73497</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[product-analytics]]></category>
            <category><![CDATA[analytics-engineering]]></category>
            <category><![CDATA[analytics]]></category>
            <category><![CDATA[data-analytics]]></category>
            <dc:creator><![CDATA[Motif Analytics]]></dc:creator>
            <pubDate>Thu, 31 Aug 2023 18:41:22 GMT</pubDate>
            <atom:updated>2023-08-31T18:41:22.326Z</atom:updated>
            <content:encoded><![CDATA[<p>by <a href="https://twitter.com/seanjtaylor">Sean J. Taylor</a>, Chief Scientist at <a href="https://www.motifanalytics.com/">Motif Analytics</a></p><p>Shortly after we started <a href="https://motifanalytics.com/">Motif</a> last year, my co-founder Misha asked me to think about what core set of sequence operations we should support. Thus began our quest for a small, yet expressive set of operations that allowed our users to do almost anything they could imagine with their event sequence data. We’ve made a lot of progress since then, culminating in a carefully crafted domain-specific language we call <em>SOL: Sequence Operations Language</em>. I’m excited to share more about how we designed SOL — it will help you understand how Motif is attacking the problem of making it joyful and exciting to work with event sequence data we collect from our product instrumentation.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/568/1*xW9cs6J_ocnD6MRl5y6d9Q.png" /><figcaption>SOL is pronounced “Soul,” as in the title of this 1981 book by Tracy Kidder.</figcaption></figure><p>Before we begin, it’s worth clarifying what we mean by an <em>event sequence</em>. An <em>event</em> is a structured record of some specific thing that occurred, often created and recorded by a logging framework (e.g. a JSON object or Python dictionary). An event must have a timestamp associated with it, the moment it was observed or recorded. Events must also have a unique identifier, like the user associated with the event. Sorting events in order by timestamp, partitioned by these unique identifiers, we end up with a <em>set</em> of event sequences, each capturing everything that’s been observed about one particular user over time.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*i3t2Ku1bEq3bfZMgmK-7Ug.png" /><figcaption>Partitioning and sorting event data yields a data structure with one user sequence per “row.”</figcaption></figure><p>This is a very common shape for the data companies collect in practice, and it can easily be stored in tables (with events represented as rows). But answering even simple sequential questions in SQL is a challenge that analysts struggle with — there is an obvious mismatch between the language and the transformations you may want to perform on event sequence data. Complicated joins, window functions, and UDTFs can <a href="https://github.com/teej/sf-funnels/tree/main">work in some cases</a>, but it felt like they were not really intended for the tasks we had. So we took a step back and thought about what we were trying to do from first principles.</p><p>I’ve been a huge fan of <a href="https://hadley.nz/">Hadley Wickham</a>’s <a href="https://dplyr.tidyverse.org/">dplyr</a> (which he calls a “grammar of data manipulation”) since it was released almost ten years ago. The beauty of dplyr is that it “unbundles” SQL into a small set of operations that share a similar interface, and can be composed into a wide variety of procedures for processing tabular data. The dplyr operations are applied to a table in an order that makes intuitive sense to the analyst, and after each operation the result is an intermediate table that can be inspected for correctness. It’s an elegant way to specify a procedure for transforming data from its raw format into something suitable for visualization and analysis, and it totally <a href="https://en.wikipedia.org/wiki/Linguistic_relativity">changed the way I thought</a> about working with data by giving me better language to describe what I am doing. We decided early on that we should emulate the <a href="https://design.tidyverse.org/unifying.html">best parts of this design</a>:</p><ul><li>A small and orthogonal set of operations that were expressive in our domain.</li><li>An intuitive way to compose these operations (<a href="https://en.wikipedia.org/wiki/Method_chaining">chaining</a>) to construct useful transformations of the data.</li><li>A syntax similar to existing tools (SQL and Python) that users would find familiar.</li></ul><p>In addition, we aligned on an important new affordance: all SOL queries are automatically linked to intuitive visualizations in Motif so changes to SOL queries always result in visible changes in our user interface. This makes it easy to check that your query is doing what you intend and to evaluate how modifications affect your results.</p><h3>Prior Art</h3><p>It’s helpful to draw an analogy to some existing data processing problems: dplyr operations that operate on tables and unix commands that operate on streams.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xl2iYrP858ijiEEE9qHaHg.png" /><figcaption>Comparison of different composable systems for processing streams of items.</figcaption></figure><p>You can see in our analogy that event sequences are comparable to a line in a file or a row in a table. They are not fixed-length so they are more like lines of characters than rows (which are tuples and have fixed length in the relational model). But they are more like rows than lines in that they contain structured information.</p><p>At this point one may ask (perhaps again): why not just use SQL which can naturally operate on events stored as rows? The fundamental issue is that SQL is not nearly as useful for expressing operations <em>across rows</em> as it is for operations across columns. There are three reasonable approaches to working “across” rows in SQL and <a href="https://github.com/teej/sf-funnels/tree/main">each has drawbacks</a> in terms of verbosity, performance, and expressivity:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xEmxjPBniHuLbrBGxq9gHg.png" /><figcaption>Different strategies for operating “across rows” in SQL. Sequences can be projected into columns, referenced as windows, or as arrays of primitive types.</figcaption></figure><p>I’d also add <a href="https://docs.snowflake.com/en/sql-reference/constructs/match_recognize">match recognize</a> to the list of SQL-based approaches here — we were inspired by how powerful it is but found barely anybody uses it because it is challenging to learn, has limited support, and is slow to execute.</p><h3>The SOL Runtime</h3><p>SOL is made up of a handful of operations which “map” over the set of event sequences in parallel. At present, the user does not typically need to specify <a href="https://en.wikipedia.org/wiki/Fold_(higher-order_function)">reduce operations</a>, we perform data aggregation in our backend that is customized for the visualizations and analyses we provide. Aggregation of sequence data is a complex topic worthy of a future blog post.</p><p>A SOL program is an ordered list of operations, each usually consuming one sequence and outputting zero or more sequences. SOL syntax is implicitly chained, with the first operation always operating on the input sequences (provided by the user, which are partitioned and sorted) and the last operation always passing its output to the Motif user interface.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/952/1*x_VDx_F3LSrF5JqbzSR1Rg.png" /></figure><h3>Core SOL Operations</h3><h4>Match</h4><p>The most fundamental challenge in working with event sequences is creating references to events that you’d like to use in subsequent operations. For this purpose, we designed the <strong>match</strong> operation, which is inspired by regular expressions for finding patterns in strings, and <a href="https://docs.python.org/3/howto/regex.html#non-capturing-and-named-groups">named groups</a>, which make it easy to reference parts of a pattern you’ve matched. We also borrowed syntax ideas from the <a href="https://en.wikipedia.org/wiki/Cypher_(query_language)">Cypher language</a> for querying graphs in <a href="https://neo4j.com/">neo4j</a>.</p><p>The <strong>match</strong> operation in SOL is a generalization of regular expressions (and is similarly implemented as a state machine), because it includes the same quantifiers but you can use arbitrary predicates about individual events (and groups of events) to define a pattern. The result of running a <strong>match</strong> operation is that the sequence has labels referencing what we call “fragments,” (our term for a referenced array of events) for use in subsequent operations.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DCZgAb5kypX0wIT7EM9tjw.gif" /><figcaption>Matching an “a” event followed by a “b” event and naming them A and B.</figcaption></figure><p>Though not shown here, <strong>match</strong> also has an if-clause which adds additional constraints as boolean expressions, so you can specify that matches require events to have specific joint properties or relative timing (e.g. B must be within 10 minutes of A).</p><p>For many data analysts, <strong>match</strong> is the most unusual operation to learn in SOL. Regular expressions are powerful but can be difficult to learn and debug and <strong>match</strong> inherits some of these challenges, but we find that example recipes and instant feedback from our development environment (like in <a href="https://regex101.com/">regex testers</a>) can get users up to speed quickly. And once users understand how to match, they can specify any event pattern with very little code!</p><h4>Match Split + Combine</h4><p>When a pattern matches a sequence in multiple locations, SOL provides <strong>match split</strong> which finds all occurrences of the specified pattern and splits the sequence at the start of the match. This operation disaggregates sequences into subsequences, allowing for very flexible <a href="https://en.wikipedia.org/wiki/Session_(web_analytics)">sessionization</a>, more advanced than simple inactivity conditions. Splitting can be done multiple times, creating a nested hierarchy of subsequences.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zp_ClHBYzg61mzjPHEGt4w.gif" /><figcaption>Splitting a sequence before every “a” event, which is labeled A.</figcaption></figure><p>A common pattern in SOL is to split sequences into parts and perform some operation, then merge them back together, a variation of <a href="https://vita.had.co.nz/papers/plyr.pdf">the split-apply-combine pattern</a>, providing iteration without for-loops. The <strong>combine</strong> operation does this while allowing the user to specify aggregates that should be propagated upward to the original level of granularity. Iterating between <strong>match split</strong> and <strong>combine</strong> allows the user to seamlessly move between different units of analysis in SOL and avoid explicit iteration.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*C1mIR0ywfgrQ4s--zq4bGA.gif" /><figcaption>Splitting and un-splitting using the combine command.</figcaption></figure><h4>Replace</h4><p>We recognized early on that we could learn a lot from how string manipulation works in unix tools (like grep) and scripting languages (like Perl and Python). One of the most powerful operations they provide is the ability to replace part of a string with a different string (or an empty string for deletion!). This operation allows you to clean up event sequences by removing unnecessary parts and replacing many events with a single event to simplify sequences.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mYgidTLxRrfaFg7UkSf9Aw.gif" /><figcaption>Replacing an event labeled B with a new event “d” which is labeled D.</figcaption></figure><p><strong>Replace</strong> is more general than you may expect, and can be used for prepending, appending, inserting, and removing events, plus completely rewriting the sequence. It also provides lightweight syntax for event literals that are added to the sequence, including using expressions and any previously labeled events to compute new dimensions.</p><h4>Set and filter</h4><p>In addition to more sequence-focused functionality, SOL provides more standard analytical operations. The <strong>set</strong> operation allows users to construct expressions and assign new dimensions of events, fragments, or the entire sequence. This mirrors dplyr’s mutate operation or <a href="https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.assign.html">assign in Pandas</a>, but with some flexibility to broadcast assignment to different parts of the sequence.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Lv113CIzjiCJgLdhukmhwg.gif" /><figcaption>We use fragments created by match to assign a new dimension to all the events in that fragment.</figcaption></figure><p>The <strong>filter</strong> operation functions analogously to filter in dplyr (and to <a href="https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.query.html">query in Pandas</a>). The user can either discard or keep specific sequences that meet specific conditions, and the Motif user interface provides information on how many sequences were kept. Very commonly <strong>filter</strong> is applied directly after <strong>match</strong> in order to retain only sequences where a match was found.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*JHSgi1AmWT1l2V4qM6XcTg.gif" /></figure><p>Right now SOL is very compact, and we have only omitted one operation in this post (<strong>top</strong>, which finds the most extreme sequences by an expression the user defines). We may add some other operations to SOL over time — an entire ecosystem of packages been folded into the <a href="https://www.tidyverse.org/">tidyverse</a> over time to augment dplyr’s core capabilities. As we encounter new problems and challenges, we’re open to growing SOL by adding simple, self-contained operations that compose with the core operations and extend its capabilities in useful ways.</p><h3>Let our Powers Combine!</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/480/0*iqDdbw21idLe8XFe" /></figure><p>Our main design goal for SOL was <a href="https://en.wikipedia.org/wiki/Expressive_power_(computer_science)">expressive power</a> — you should be able to accomplish almost any sequence analysis task you can think of with SOL with just a small number of operations. To stress test this, we started a fun team activity called “SOL Question of the Week” where we take turns trying to come up with questions that might be difficult or impossible to answer in SOL. We’ve been doing this for almost a year now and haven’t found too many gaps left to plug. SOL is a highly useful and usable addition to the analyst toolkit and it may just change the way you think about how to answer your next product question.</p><p>The bar for a new analytics language <a href="https://erikbern.com/2018/08/30/i-dont-want-to-learn-your-garbage-query-language.html">is very high</a>, so we’ve been very careful to design one we’re proud of that addresses real gaps in existing tools. We think you’ll find that SOL is worth learning and streamlines your work by providing a faster path to specifying what you’re trying to achieve. The most interesting and impactful questions are often natural to answer using <a href="https://motifanalytics.medium.com/introducing-sequence-analytics-93eadd921440">sequence analytics</a> and with SOL. The equivalent SQL for some SOL queries is difficult to write correctly (even for LLMs) and even harder to read and debug. <a href="https://www.youtube.com/watch?v=t6m9z874SLM">And SOL pairs well with SQL</a> because it operates directly on columnar data that is easily queryable in popular databases like <a href="https://duckdb.org/">DuckDB</a>. We hope you give it a try <a href="https://airtable.com/app9vnLQR8Cc2rQlJ/shrSPLc9Il2rPEFut">by signing up to test Motif</a>!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=87a0d1d73497" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Introducing Sequence Analytics]]></title>
            <link>https://motifanalytics.medium.com/introducing-sequence-analytics-93eadd921440?source=rss-a36de1cfc346------2</link>
            <guid isPermaLink="false">https://medium.com/p/93eadd921440</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[technology]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[analytics]]></category>
            <dc:creator><![CDATA[Motif Analytics]]></dc:creator>
            <pubDate>Wed, 09 Aug 2023 18:39:15 GMT</pubDate>
            <atom:updated>2023-09-15T03:22:33.770Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XtXZoFBqmXJpzv97vTodMw.png" /></figure><p><strong><em>Have you noticed that some business questions are easy to express in words, but not in code?</em></strong> For example, you might want to find “abandoned cart” moments: a user adds an item to a cart but then doesn’t checkout in the next hour. This is not trivial to describe in SQL unless there is already a field for this use case in a user session table (and it never seems to be there, does it?).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*WCwhgKr4i4UUnh01" /></figure><p>Questions like this one, which rely on<em> </em><strong><em>order or duration between events</em></strong>, are hard to approach using relational data and SQL. They usually involve 50+ lines of window functions and/or self-joins with all the ensuing debugging. And when you are finally done with the query, you need to wait for your result, because these queries are computed inefficiently. SQL wasn’t designed for these kinds of <strong><em>sequence-oriented</em> <em>questions</em></strong>.</p><p>Meanwhile, these questions are everywhere and are increasingly important for understanding user behavior and business performance:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*iSLysHBRrlNurZI3" /></figure><p>At Motif, we are creating <strong><em>sequence analytics</em></strong> — a toolkit for naturally working with sequence data. We have developed <strong><em>Sequence Operations Language (SOL)</em></strong> — a small but powerful DSL to easily query and wrangle ordered lists of events. It is not intended as a replacement for SQL (long live the king!), but rather as its complement. In fact, you can use them both in the same analysis because sequences are stored as a relational table of events with a thin layer of sequence metadata on top.</p><p>We got inspiration for SOL from many existing languages and systems: <a href="https://en.wikipedia.org/wiki/Regular_expression">regular expressions</a>, <a href="https://neo4j.com/developer/cypher/">Cypher</a>, <a href="https://docs.snowflake.com/en/sql-reference/constructs/match_recognize">SQL’s MATCH_RECOGNIZE</a>, <a href="https://docs.python.org/3/tutorial/introduction.html#lists">Python lists</a>, <a href="http://sase.cs.umass.edu/">SASE+</a>, <a href="https://dplyr.tidyverse.org/">dplyr</a>. Neither of them seemed sufficient by itself, but by combining the best parts of each, we believe we found a good balance:</p><ul><li>powerful pattern matching from regex</li><li>intuitive readable syntax from Cypher</li><li>vector operations and slicing from Python lists</li><li>minimalism and chaining from dplyr.</li></ul><p>The key idea: matching event patterns to define important events and sub-sequences (purple variables in the examples below) and then operating on them as arrays of events. For example, here is how you can approach example questions above using SOL:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rFL4HTEdgjWW_K6Ap4IUpA.png" /></figure><p>Succinct and fairly readable even without knowing anything about the language, isn’t it?</p><p>SOL is great not just for querying sequences but also for modifying them through “replace”, “match split”/”combine” and “set” operations. This way you can wrangle your dataset on the fly into the shape most appropriate for exploring your question. Here are a few examples:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uI6I1F6Y3hEfvFQbofgHow.png" /></figure><p>You can learn more about SOL by going through a <a href="https://docs.motifanalytics.com/getting-started/quickstart">quickstart</a> or exploring <a href="https://docs.motifanalytics.com/how-motif-works/sol-intro">documentation</a>.</p><p>No introductory post would be complete without throwing in a framework or two, so here are a couple of interesting ones to contrast sequential and relational approaches for working with data. Here we provide just a quick taste and will elaborate in future posts.</p><h4>State vs. history lenses</h4><p>Complex systems can be described through their <strong><em>state</em></strong> or <strong><em>history</em></strong>. For example, the game of chess is usually specified through a position of pieces on the board (state), but can also be described through the sequence of moves players made in the game (history). Most analytics data comes in as “history” (events) but analytics in data warehouses today is done through the “state” lens — user events are filtered and aggregated into tables of pre-selected important moments and counts. SQL nudges to explore data through the “state” lens, while SOL is natural for working with “history”.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*qL3CP5qtAiAj6-Ei" /></figure><h4>Cross-sectional, time series and panel data</h4><p>The relational model usually operates on <a href="https://en.wikipedia.org/wiki/Cross-sectional_data"><strong><em>cross-sectional</em></strong></a> (user dimension tables) or <a href="https://en.wikipedia.org/wiki/Time_series"><strong><em>time series</em></strong></a> (metrics tables) data, while the sequential model works with more complex <a href="https://en.wikipedia.org/wiki/Panel_data"><strong><em>panel</em></strong></a> data, which combines both.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*2YiM5YrcmUAROxGV" /></figure><p>At its core Motif is a sequence analytics company. Even the company name is about helping to uncover business <strong><em>motifs</em></strong> — important patterns in event sequences. We’ll be sharing more about our work in the near future, but if you have reached the end of this post, you are definitely ready to <a href="https://www.motifanalytics.com/">try sequence analytics yourself</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=93eadd921440" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>