Dynamic Funnel Analysis Using Solr + Cassandra
What is a funnel?
A funnel is defined as a sequence of events that a user goes through within an application.
For example, a funnel in e-commerce apps could be a sequence of events that define the checkout flow of the app.
By way of example, here is a hypothetical Amazon funnel:
A user:
- Gets an email from Amazon with book recommendations
- Clicks on a book
- Proceeds to the product page
- Clicks on “Buy now”
- Arrives at the checkout page
- Pays
- Exits Amazon
The value of an effective funnel analysis solution
As a product owner, knowing your funnels and how they’re performing is a great way to get to the bottom line of the state of your features. Problem is, when one of your features isn’t performing well, traditional analytics tools don’t provide a way to discover why this particular feature doesn’t perform optimally. You can see where the user exited, but numbers alone don’t explain why he did so. This is where effective funnel analysis solutions bring value.
The challenge
Companies are always looking for new, user-friendly ways to investigate flows and funnels in their web apps. One of the great challenges in providing them with an effective funnel analysis solution is in providing them with sufficient meaningful data while not complicating the implementation process.
To get meaningful insights about a funnel’s performance, one must collect as much data as possible. The problem is that, as you research your data, you will often find missing data points that you didn’t track.
Why? Because you can’t track everything.
Companies often try to solve this by adding more and more tracking events to their funnel(s)’ steps. This approach risks wasting time and money, and creating a bottomless pit of data collection; there’s always one more thing you could track next time.
We’ve long known this is an issue when optimizing funnels, and have tailored our solution to address, and bypass it.
The Visions value-add
Because of our Visions product’s video session recording capabilities, many of our clients use it to expand their understanding of their users’ interactions with their product(s) beyond the raw numerical data points to which most companies are limited.
With Visions, we wanted to take funnel analysis a step further by allowing our customers to slice and dice their sessions, using events they didn’t need to pre-define (e.g., Clicks, Input changes, URLs, Errors), and to be able to dynamically play with the funnel definition.
We wanted our clients’ funnel analysis capabilities to have a one-to-one correlation with our preexisting querying mechanism, so all our clients would need to do to test it out is “flip a switch,” so to speak:
Before you continue reading — understand that this is not a blog post about conversion rate optimization. It’s a blog post about Visions’ unique technique that allows our clients to dissect and understand their funnel(s) and flow(s).
How we track funnels in Visions (tech talk)
We use Cassandra (aka C*) to store and keep track of all our customers’ sessions and the events they blast at us, peaking at 10,000 events per second. We chose C* as our database, as it fits well with our write-heavy workload. Unfortunately, the C* query engine compromises query power in favor of performance, which is why most companies combine C* with Solr/ElasticSearch. Since we already use DataStax Enterprise, it was only natural for us to use its tight Solr integration.
Funnel analysis is not an easy problem to solve, especially at scale. On top of that, we wanted to be able to run any funnel query and get results back instantly.
In the wild, there aren’t many resources explaining how to approach funnels technically. The best ones we’ve found are this great post by Heap, showing a very expressive way to calculate funnels in PostgreSQL and this excellent primer by Segment. We’ve found another very cool way to do it with Solr, and we are sharing here it in our desire to nurture this sparse knowledge ecosystem.*
Our data model before implementing funnels consisted of two Solr cores:
- Sessions Core — This is where we store all the metadata of a session for all of our customers’ sessions.
- Timeline Events Core — This stores all of the events that are query-able (e.g., URL, Clicks, Input changes, Errors) for each session.
Using the Solr Join feature enabled us to run queries like this:
“Give me all the sessions where a user clicked on ‘Sign up’”
“Give me all the sessions where a user had an exception”
For funnel analysis, it’s generally not feasible to use this data model to retrieve a summary of the funnel steps and the sessions matching it. This is true for our stack as well since there’s no option in Solr to run a recursive query, which would allow one to go over each session and check if it’s a match for the funnel.
After some research, however, we found two Solr features that allow one to run an ordered query against documents, which can be very useful for funnel analysis:
The first is the SurroundQueryParser, which lets you search for phrases in documents that are separated from each other by a given distance.
The second is the ComplexPhraseQueryParser which is similar to SurroundQueryParser, except that it doesn’t have a distance limitation. This meets our needs in the cases in which, between two steps in the funnel, there are thousands of events.
Our challenge in utilizing ComplexPhrase for funnels was to figure out how to serialize session events to a string.
To this end, we introduced a new string column to the Sessions Core which encapsulates all of the events that occurred in a session as a single string — This string preserves the order of the events, and each event is represented as a hash. We could now create powerful ComplexPhrases that find sessions containing a sequence of events.
While this was a big win, another challenge remained: ComplexPhrase has a limitation where queries with wildcards can quickly hit the MaxBooleanClause limit. The way we decided to overcome this limitation is to store each event as a hash of its metadata and create another Core (Projects Events Core) that maps between a hash and the original event metadata.
The cool thing about ComplexPhrase is that you can execute both ordered and unordered queries with it by defining it at the query level, using the inOrder attribute. Ordered queries are relevant when we’d like to query for funnels, and unordered queries are relevant for any typical query.
Let’s take this funnel for example, and see how we’d go about querying for it:
Step 1: “Give me all the sessions where a user navigated to ‘example.com/registration’”
Step 2: “Give me all the sessions where a user clicked on ‘Sign Up!”
The steps we need to take to do these queries are as follows:
- Query the Project Events Core to get back all the matches (Event Hashes) in the project for events that match the steps query.
- Execute a ComplexPhrase query on the Sessions Core to get back all the sessions that had a match for the Event Hashes found in step 1.
Conclusion
In summation, we think this is a very cool use case for the ComplexPhrase feature in Solr. So far it’s been working great for us here at Visions, allowing us to run complex funnel queries over hundreds of millions of events with an almost instant response time. We did have one set back with the MaxBooleanClauses limit, which we solved withthe hashing trick, but other than that this has proved to be a very successful approach to the funnel problem.
Want to try it for yourself? go ahead and get your free Visions account now!
*To view our entire tech stack, you can visit our Stackshare page.
Shout out to the awesome folks @ DataStax Startup Plan for all their support.
