Funnel Discovery

This example notebook shows how to use the ‘Funnel Discovery’ model on your data collected with Objectiv. It’s also available as a full Jupyter notebook to run on your own data (see how to get started in your notebook), or you can instead run Objectiv Up to try it out. The dataset used here is the same as in Up.

In classical funnel analysis you predefine the steps, and then you analyze the differences for user attributes or behavior in each step.

However, this means you have to make assumptions about which steps matter, and you potentially miss important, impactful flows, e.g. because they are not very obvious or still small. Yet these can represent major opportunities to boost or optimize.

This is where Funnel Discovery comes in: to discover all the (top) user journeys that lead to conversion or drop-off, and run subsequent analyses on them.

In particular, we will discover in this example:

  • The most popular consecutive steps overall;
  • The steps/flows which lead to conversion;
  • The most common drop-offs;
  • The user journeys from marketing campaigns;
  • Etcetera.

Get started

We first have to instantiate the model hub and an Objectiv DataFrame object.

In:
 # set the timeframe of the analysis
start_date = '2022-02-01'
end_date = None
In:
 # instantiate the model hub, and set the default time aggregation to daily
# and set the global contexts that will be used in this example
from modelhub import ModelHub
modelhub = ModelHub(time_aggregation='%Y-%m-%d', global_contexts=['application', 'marketing'])
# get an Objectiv DataFrame within a defined timeframe
df = modelhub.get_objectiv_dataframe(db_url=DB_URL, start_date=start_date, end_date=end_date)

The location_stack column, and the columns taken from the global contexts, contain most of the event-specific data. These columns are JSON typed, and we can extract data from it using the keys of the JSON objects with SeriesLocationStack methods, or the context accessor for global context columns. See the open taxonomy example for how to use the location_stack and global contexts.

In:
 # add specific contexts to the data as columns
df['application_id'] = df.application.context.id
df['feature_nice_name'] = df.location_stack.ls.nice_name
In:
 # select which event type to use for further analysis - PressEvents to focus on what users directly interact with
df = df[df['event_type'] == 'PressEvent']

First: define what is conversion

As a prerequisite for Funnel Discovery, define the events you see as conversion.

In this example we will view someone as converted when they go on to read the documentation from our website, but you can use any event.

In:
 # define which data to use as conversion events; in this example, anyone who goes on to read the documentation
df['is_conversion_event'] = False
df.loc[df['application_id'] == 'objectiv-docs', 'is_conversion_event'] = True

Out of curiosity, let’s see which features are used by users that converted, sorted by their conversion impact.

In:
 # calculate the percentage of converted users per feature: (converted users per feature) / (total users converted)
total_converted_users = df[df['is_conversion_event']]['user_id'].unique().count().value
top_conversion_locations = modelhub.agg.unique_users(df[df['is_conversion_event']], groupby='feature_nice_name')
top_conversion_locations = (top_conversion_locations / total_converted_users) * 100

# show the results, with .to_frame() for nicer formatting
top_conversion_locations = top_conversion_locations.to_frame().rename(columns={'unique_users': 'converted_users_percentage'})
top_conversion_locations.sort_values(by='converted_users_percentage', ascending=False).head()
Out:
                                                                                   converted_users_percentage
feature_nice_name
Link: Quickstart Guide located at Root Location: home => Navigation: docs-sidebar 17.045455
Link: logo located at Root Location: home => Navigation: navbar-top 12.121212
Link: Tracking located at Root Location: home => Navigation: navbar-top 11.742424
Link: Taxonomy located at Root Location: modeling => Navigation: navbar-top 11.174242
Link: Modeling located at Root Location: tracking => Navigation: navbar-top 10.795455

See step sequences per user

Before we see what helped conversion and what didn’t, let’s have a look at which consecutive steps each user took (aka the features they used) in general, after starting their session, based on the location stack. We have to specify the maximum n steps, and use the get_navigation_paths operation.

In:
 # instantiate the FunnelDiscovery model from the open model hub
funnel = modelhub.get_funnel_discovery()
# set the maximum n steps
max_steps = 4
In:
 # for every user starting their session, find all maximum n consecutive steps they took
df_steps = funnel.get_navigation_paths(df, steps=max_steps, by='user_id')
df_steps.head()
Out:
                                                                    location_stack_step_1                                   location_stack_step_2                                   location_stack_step_3                                   location_stack_step_4
user_id
0000bb2f-66e9-4e48-8e2f-7d0a82446ef4 Link: about-us located at Root Location: home ... Link: logo located at Root Location: about => ... None None
00529837-d672-4747-9b87-fd09f2919326 Link: blog located at Root Location: home => N... Link: docs located at Root Location: blog => N... Link: bach-and-sklearn located at Root Locatio... Link: basic-product-analytics located at Root ...
00529837-d672-4747-9b87-fd09f2919326 Link: docs located at Root Location: blog => N... Link: bach-and-sklearn located at Root Locatio... Link: basic-product-analytics located at Root ... None
00529837-d672-4747-9b87-fd09f2919326 Link: bach-and-sklearn located at Root Locatio... Link: basic-product-analytics located at Root ... None None
00529837-d672-4747-9b87-fd09f2919326 Link: spin-up-the-demo located at Root Locatio... Link: blog located at Root Location: home => N... Link: docs located at Root Location: blog => N... Link: bach-and-sklearn located at Root Locatio...

See top step sequences for all users

For the bigger picture, calculate the most frequent consecutive steps that all users took after starting their session, based on the location stack.

In:
 df_steps.value_counts().to_frame().head(20)
Out:
                                                                                                                                                                                                                                                                                                                                                                                         value_counts
location_stack_step_1 location_stack_step_2 location_stack_step_3 location_stack_step_4
Pressable: after located at Root Location: home => Content: capture-data => Content: data-... NaN NaN NaN 87
Pressable: before located at Root Location: home => Content: capture-data => Content: data... Pressable: after located at Root Location: home => Content: capture-data => Content: data-... NaN NaN 49
Pressable: after located at Root Location: home => Content: capture-data => Content: data-... Pressable: before located at Root Location: home => Content: capture-data => Content: data... NaN NaN 48
Pressable: after located at Root Location: home => Content: modeling => Content: modeling-... NaN NaN 46
Pressable: before located at Root Location: home => Content: capture-data => Content: data... Pressable: after located at Root Location: home => Content: capture-data => Content: data-... Pressable: before located at Root Location: home => Content: capture-data => Content: data... 39
Pressable: after located at Root Location: home => Content: modeling => Content: modeling-... Pressable: before located at Root Location: home => Content: modeling => Content: modeling... NaN NaN 35
Pressable: before located at Root Location: home => Content: modeling => Content: modeling... Pressable: after located at Root Location: home => Content: modeling => Content: modeling-... NaN NaN 31
Pressable: hamburger located at Root Location: home => Navigation: navbar-top NaN NaN NaN 30
Pressable: after located at Root Location: home => Content: modeling => Content: modeling-... NaN NaN NaN 29
Pressable: before located at Root Location: home => Content: capture-data => Content: data... Pressable: after located at Root Location: home => Content: capture-data => Content: data-... Pressable: before located at Root Location: home => Content: capture-data => Content: data... Pressable: after located at Root Location: home => Content: capture-data => Content: data-... 29
Pressable: after located at Root Location: home => Content: capture-data => Content: data-... Pressable: before located at Root Location: home => Content: capture-data => Content: data... Pressable: after located at Root Location: home => Content: capture-data => Content: data-... NaN 28
Link: about-us located at Root Location: home => Navigation: navbar-top NaN NaN NaN 27
Pressable: after located at Root Location: home => Content: capture-data => Content: data-... Pressable: before located at Root Location: home => Content: capture-data => Content: data... Pressable: after located at Root Location: home => Content: capture-data => Content: data-... Pressable: after located at Root Location: home => Content: modeling => Content: modeling-... 25
Link: spin-up-the-demo located at Root Location: home => Content: hero NaN NaN NaN 24
Pressable: hamburger located at Root Location: home => Navigation: navbar-top Link: github located at Root Location: home => Navigation: navbar-top => Overlay: hamburge... NaN NaN 21
Link: star-us located at Root Location: home => Content: hero NaN NaN NaN 20
Pressable: hamburger located at Root Location: home => Navigation: navbar-top Pressable: close located at Root Location: home => Navigation: navbar-top => Overlay: hamb... NaN NaN 19
Link: logo located at Root Location: home => Navigation: navbar-top NaN NaN NaN 19
Pressable: after located at Root Location: home => Content: modeling => Content: modeling-... Pressable: before located at Root Location: home => Content: modeling => Content: modeling... Pressable: after located at Root Location: home => Content: modeling => Content: modeling-... Pressable: before located at Root Location: home => Content: modeling => Content: modeling... 18
Link: github located at Root Location: home => Navigation: navbar-top NaN NaN NaN 17

See step sequences that lead to conversion

Now let’s find the sequences that actually lead to conversion.

First, see which step resulted in conversion to the dataframe, which will be NaN for sequences that did not convert.

In:
 # add which step resulted in conversion to the dataframe, with the `add_conversion_step_column` param
df_first_conversion_step = funnel.get_navigation_paths(df, steps=max_steps, by='user_id', add_conversion_step_column=True)
df_first_conversion_step.head(10)
Out:
                                                                                                             location_stack_step_1                                                                         location_stack_step_2                                                                         location_stack_step_3                                                                         location_stack_step_4  _first_conversion_step_number
user_id
0000bb2f-66e9-4e48-8e2f-7d0a82446ef4 Link: about-us located at Root Location: home => Navigation: navbar-top Link: logo located at Root Location: about => Navigation: navbar-top None None NaN
00529837-d672-4747-9b87-fd09f2919326 Link: blog located at Root Location: home => Navigation: navbar-top Link: docs located at Root Location: blog => Navigation: navbar-top Link: bach-and-sklearn located at Root Location: modeling => Navigation: docs-sidebar => ... Link: basic-product-analytics located at Root Location: modeling => Navigation: docs-side... 3.0
00529837-d672-4747-9b87-fd09f2919326 Link: docs located at Root Location: blog => Navigation: navbar-top Link: bach-and-sklearn located at Root Location: modeling => Navigation: docs-sidebar => ... Link: basic-product-analytics located at Root Location: modeling => Navigation: docs-side... None 2.0
00529837-d672-4747-9b87-fd09f2919326 Link: blog located at Root Location: home => Navigation: navbar-top Pressable: after located at Root Location: home => Content: capture-data => Content: data... Link: spin-up-the-demo located at Root Location: home => Content: hero Link: blog located at Root Location: home => Navigation: navbar-top NaN
00529837-d672-4747-9b87-fd09f2919326 Link: bach-and-sklearn located at Root Location: modeling => Navigation: docs-sidebar => ... Link: basic-product-analytics located at Root Location: modeling => Navigation: docs-side... None None 1.0
00529837-d672-4747-9b87-fd09f2919326 Pressable: after located at Root Location: home => Content: capture-data => Content: data... Link: spin-up-the-demo located at Root Location: home => Content: hero Link: blog located at Root Location: home => Navigation: navbar-top Link: docs located at Root Location: blog => Navigation: navbar-top NaN
00529837-d672-4747-9b87-fd09f2919326 Link: spin-up-the-demo located at Root Location: home => Content: hero Link: blog located at Root Location: home => Navigation: navbar-top Link: docs located at Root Location: blog => Navigation: navbar-top Link: bach-and-sklearn located at Root Location: modeling => Navigation: docs-sidebar => ... 4.0
005aa19c-7e80-4960-928c-a0853355ee5f Link: check-out-thijs-obj-on-github located at Root Location: about => Content: core-team... Link: jobs located at Root Location: about => Navigation: navbar-top None None NaN
007f5fd7-7535-434e-aa3e-3d52f06d63ce Link: Open model hub basics located at Root Location: modeling => Navigation: docs-sideba... Link: Modeling located at Root Location: modeling => Navigation: navbar-top None None 1.0
007f5fd7-7535-434e-aa3e-3d52f06d63ce Link: docs located at Root Location: home => Navigation: navbar-top Link: Modeling located at Root Location: home => Navigation: navbar-top Link: Basic product analytics located at Root Location: modeling => Navigation: docs-side... Link: Open model hub basics located at Root Location: modeling => Navigation: docs-sideba... 2.0

To filter down to all sequences that have actually converted, use the only_converted_paths parameter.

In:
 # filter down to all sequences that have actually converted with the `only_converted_paths` param
df_steps_till_conversion = funnel.get_navigation_paths(df, steps=max_steps, by='user_id', add_conversion_step_column=True, only_converted_paths=True)
df_steps_till_conversion.head(5)
Out:
                                        location_stack_step_1                                   location_stack_step_2                                   location_stack_step_3                                   location_stack_step_4                                       _first_conversion_step_number
user_id
00529837-d672-4747-9b87-fd09f2919326 Link: spin-up-the-demo located at Root Locatio... Link: blog located at Root Location: home => N... Link: docs located at Root Location: blog => N... Link: bach-and-sklearn located at Root Locatio... 4
00529837-d672-4747-9b87-fd09f2919326 Link: docs located at Root Location: blog => N... Link: bach-and-sklearn located at Root Locatio... None None 2
00529837-d672-4747-9b87-fd09f2919326 Link: blog located at Root Location: home => N... Link: docs located at Root Location: blog => N... Link: bach-and-sklearn located at Root Locatio... None 3
007f5fd7-7535-434e-aa3e-3d52f06d63ce Link: docs located at Root Location: home => N... Link: Modeling located at Root Location: home ... None None 2
007f5fd7-7535-434e-aa3e-3d52f06d63ce Link: docs located at Root Location: home => N... Link: Modeling located at Root Location: home ... None None 2

We can use this to for instance see which sequences converted on the 4th step.

In:
 # filter down to sequences that converted on the 4th step
condition_convert_on_step_4 = df_steps_till_conversion['_first_conversion_step_number'] == 4
df_steps_till_conversion[condition_convert_on_step_4].head()
Out:
                                        location_stack_step_1                                   location_stack_step_2                                   location_stack_step_3                                   location_stack_step_4                                   _first_conversion_step_number
user_id
00529837-d672-4747-9b87-fd09f2919326 Link: spin-up-the-demo located at Root Locatio... Link: blog located at Root Location: home => N... Link: docs located at Root Location: blog => N... Link: bach-and-sklearn located at Root Locatio... 4
01891784-6333-40f1-8be6-739f3adfdb97 Link: about-us located at Root Location: home ... Pressable: hamburger located at Root Location:... Link: faq located at Root Location: home => Na... Pressable: navbar-toggle located at Root Locat... 4
02f197f4-90d1-4374-b8a5-89e2e91310d7 Pressable: after located at Root Location: hom... Pressable: before located at Root Location: ho... Pressable: after located at Root Location: hom... Link: logo located at Root Location: modeling ... 4
05bd4058-b7df-4476-8174-dcf0be107b5c Link: blog located at Root Location: home => N... Link: jobs located at Root Location: blog => N... Link: faq located at Root Location: jobs => Na... Link: Objectiv.io located at Root Location: ho... 4
0605440c-1eba-479a-9000-8294576199aa Link: check-out-jansenbob-on-github located at... Link: blog located at Root Location: about => ... Link: read-more located at Root Location: blog... Link: MediaEvent located at Root Location: tax... 4

Visualize these sequences in a Sankey diagram

We can use a Sankey diagram to visualize these customer journeys that lead to conversion (or drop-off). This helps you to select which sequences are most interesting to analyze further.

Let’s plot it for the example above, where we filtered down to the sequences that converted on the 4th step. The width of each link represents the amount of times that flow was used, and you can hover over each link to see the source and target node.

In:
 # plot the Sankey diagram using the top 15 examples via the `n_top_examples` param
funnel.plot_sankey_diagram(df_steps_till_conversion[condition_convert_on_step_4], n_top_examples=15)

Funnel Discovery Sankey diagram for users that dropped off

Deep-dive: top drop-off features

Also interesting to analyze is which features are used relatively often before users drop off. We can do this by finding all last used features by non-converted users, and calculating their usage share.

In:
 # select only non-converted users
df_non_converted = df[~df['is_conversion_event']]
converted_users = df[df['is_conversion_event']]['user_id']
# select the events of these non converted users
df_non_converted = df_non_converted[~df_non_converted['user_id'].isin(converted_users)]
In:
 # get the last used features by non-converted users, sorted by their usage share compared to all features
modelhub.aggregate.drop_off_locations(df_non_converted, groupby='user_id', percentage=True).head()
Out:
                                                                                               percentage
__feature_nice_name
Pressable: after located at Root Location: home => Content: capture-data => Content: data-... 15.329218
Pressable: after located at Root Location: home => Content: modeling => Content: modeling-... 12.345679
Pressable: before located at Root Location: home => Content: capture-data => Content: data... 5.349794
Link: about-us located at Root Location: home => Navigation: navbar-top 4.218107
Pressable: hamburger located at Root Location: home => Navigation: navbar-top 4.218107

Deep-dive: marketing campaign journeys

The same analyses can be run for journeys that start from a marketing campaign, e.g. to analyze why campaigns do or do not convert.

In:
 # first, add marketing data to the dataframe
df_marketing = df.copy()
df_marketing['utm_campaign'] = df_marketing.marketing.context.campaign

# filter the dataframe down to users that came in via a marketing campaign
user_list = df_marketing[~df_marketing['utm_campaign'].isnull()].user_id
df_marketing = df_marketing[df_marketing['user_id'].isin(user_list)]

df_marketing.head()
Out:
                                             day                  moment                               user_id                                                                                location_stack  event_type                              stack_event_types  session_id  session_hit_number                                                                                   application                                                                                     marketing    application_id                                                                             feature_nice_name  is_conversion_event utm_campaign
event_id
d1c72d21-4233-40dc-b93d-3323dbf4cf75 2022-06-01 2022-06-01 18:55:35.074 04ac1790-825a-47a3-aac3-dccfeee61ade [{'id': 'blog', '_type': 'RootLocationContext', '_types': ['AbstractContext', 'AbstractLo... PressEvent [AbstractEvent, InteractiveEvent, PressEvent] 3399 1 [{'id': 'objectiv-website', '_type': 'ApplicationContext', '_types': ['AbstractContext', ... [{'id': 'utm', 'term': None, '_type': 'MarketingContext', '_types': ['AbstractContext', '... objectiv-website Link: logo located at Root Location: blog => Navigation: navbar-top False blog
3a714be8-20aa-46cb-8deb-fa03635e20a9 2022-04-12 2022-04-12 12:43:29.990 09cb57db-1a41-4f9f-bade-64a7f9f374ad [{'id': 'home', '_type': 'RootLocationContext', '_types': ['AbstractContext', 'AbstractLo... PressEvent [AbstractEvent, InteractiveEvent, PressEvent] 651 1 [{'id': 'objectiv-website', '_type': 'ApplicationContext', '_types': ['AbstractContext', ... [{'id': 'utm', 'term': 'open source analytics', '_type': 'MarketingContext', '_types': ['... objectiv-website Link: docs located at Root Location: home => Navigation: navbar-top False 16526831451
99fed3fd-6f22-430c-92a4-78149d85c78e 2022-04-12 2022-04-12 12:43:36.151 09cb57db-1a41-4f9f-bade-64a7f9f374ad [{'id': 'home', '_type': 'RootLocationContext', '_types': ['AbstractContext', 'AbstractLo... PressEvent [AbstractEvent, InteractiveEvent, PressEvent] 651 4 [{'id': 'objectiv-docs', '_type': 'ApplicationContext', '_types': ['AbstractContext', 'Ab...
[] objectiv-docs Link: Quickstart Guide located at Root Location: home => Navigation: doc-paginator-naviga... True None
ff88125e-49d0-47e8-a60a-48b7e244e5ac 2022-03-21 2022-03-21 23:36:20.587 0b7fa533-64ca-48c9-84d9-04c54b0fa069 [{'id': 'home', '_type': 'RootLocationContext', '_types': ['AbstractContext', 'AbstractLo... PressEvent [AbstractEvent, InteractiveEvent, PressEvent] 359 3 [{'id': 'objectiv-website', '_type': 'ApplicationContext', '_types': ['AbstractContext', ... [{'id': 'utm', 'term': 'open source analytics software', '_type': 'MarketingContext', '_t... objectiv-website Pressable: hamburger located at Root Location: home => Navigation: navbar-top False 16526831451
38d7bf60-6c65-4521-9dbc-82138a862d4f 2022-03-21 2022-03-21 23:36:23.769 0b7fa533-64ca-48c9-84d9-04c54b0fa069 [{'id': 'home', '_type': 'RootLocationContext', '_types': ['AbstractContext', 'AbstractLo... PressEvent [AbstractEvent, InteractiveEvent, PressEvent] 359 5 [{'id': 'objectiv-website', '_type': 'ApplicationContext', '_types': ['AbstractContext', ... [{'id': 'utm', 'term': 'open source analytics software', '_type': 'MarketingContext', '_t... objectiv-website Link: docs located at Root Location: home => Navigation: navbar-top => Overlay: hamburger... False 16526831451

Let’s define what you see as conversion events for these users. In this example, we’ll again view someone as converted when they go on to read the documentation from our website, but you can use any event.

In:
 # define which data to use as conversion events; in this example, anyone who goes on to read the documentation
df_marketing['is_conversion_event'] = False
df_marketing.loc[df_marketing['application_id'] == 'objectiv-docs', 'is_conversion_event'] = True
In:
 # get converted and non converted users as dataframes
users_converted = df_marketing[df_marketing['is_conversion_event']].user_id
users_non_converted = df_marketing[~df_marketing['user_id'].isin(users_converted)].user_id

df_marketing_converted = df_marketing[df_marketing['is_conversion_event']]
df_marketing_non_converted = df_marketing[df_marketing['user_id'].isin(users_non_converted)]

For an overall look: let’s calculate the share of converted and non-converted users

In:
 n_users_converted = df_marketing_converted['user_id'].unique().count().value
n_users_non_converted = df_marketing_non_converted['user_id'].unique().count().value
n_users_total = n_users_converted + n_users_non_converted

print(f'Converted users: {round((n_users_converted / n_users_total) * 100)}%\nNon-converted users: {round((n_users_non_converted / n_users_total) * 100)}%')
Out:
Converted users: 27%
Non-converted users: 73%

Now we’re most interested in the large share of users who did not convert; let’s have a look at them next.

Top drop-off features for users from a marketing campaign

Similar to before, we will have a look at which features are used relatively often before users drop off, and this time when they came from a marketing campaign.

In:
 modelhub.aggregate.drop_off_locations(df_marketing_non_converted, groupby='user_id', percentage=True).head()
Out:
                                                                                               percentage
__feature_nice_name
Link: logo located at Root Location: blog => Navigation: navbar-top 10.000000
Pressable: hamburger located at Root Location: home => Navigation: navbar-top 8.333333
Link: star-us-on-github located at Root Location: home => Content: hero 8.333333
Link: star-us-on-github located at Root Location: home => Navigation: hero 8.333333
Link: objectiv-quickstart-guide located at Root Location: home => Content: the-stack-quick... 5.000000

Visualize the sequences in a Sankey diagram for non-converted users from a marketing campaign

Similar to before, we can use a Sankey diagram to visualize the customer journeys, this time the ones that lead to dropped-off users that came from a marketing campaign.

Remember that the width of each link represents the amount of times that flow was used, and you can hover over each link to see the source and target node.

In:
 max_steps = 4
df_steps = funnel.get_navigation_paths(df_marketing_non_converted, steps=max_steps, by='user_id')
funnel.plot_sankey_diagram(df_steps, n_top_examples=15)

Funnel Discovery Sankey diagram for users that came from a marketing campaign

Get the SQL for any analysis

The SQL for any analysis can be exported with one command, so you can use models in production directly to simplify data debugging & delivery to BI tools like Metabase, dbt, etc. See how you can quickly create BI dashboards with this.

In:
 # show SQL for analysis; this is just one example, and works for any Objectiv model/analysis
display_sql_as_markdown(top_conversion_locations)
Out:

WITH "manual_materialize___98e5bd0cc63a3e9a9e1a6f1bdd82bc66" AS (
SELECT "event_id" AS "event_id",
"day" AS "day",
"moment" AS "moment",
"cookie_id" AS "user_id",
"value"->>'_type' AS "event_type",
cast("value"->>'_types' AS JSONB) AS "stack_event_types",
cast("value"->>'location_stack' AS JSONB) AS "location_stack",
cast("value"->>'time' AS bigint) AS "time",
jsonb_path_query_array(cast("value"->>'global_contexts' AS JSONB), '$[*] ? (@._type == $type)', '{"type":"ApplicationContext"}') AS "application",
jsonb_path_query_array(cast("value"->>'global_contexts' AS JSONB), '$[*] ? (@._type == $type)', '{"type":"MarketingContext"}') AS "marketing"
FROM "data"
),
"getitem_where_boolean___d0a208add8c64ef83a59cef7edf355e4" AS (
SELECT "event_id" AS "event_id",
"day" AS "day",
"moment" AS "moment",
"user_id" AS "user_id",
"event_type" AS "event_type",
"stack_event_types" AS "stack_event_types",
"location_stack" AS "location_stack",
"time" AS "time",
"application" AS "application",
"marketing" AS "marketing"
FROM "manual_materialize___98e5bd0cc63a3e9a9e1a6f1bdd82bc66"
WHERE ((("day" >= cast('2022-02-01' AS date))) AND (("day" <= cast('2022-06-30' AS date))))
),
"context_data___eaf1462be86e6368151e0cf9af3e04ed" AS (
SELECT "event_id" AS "event_id",
"day" AS "day",
"moment" AS "moment",
"user_id" AS "user_id",
"location_stack" AS "location_stack",
"event_type" AS "event_type",
"stack_event_types" AS "stack_event_types",
"application" AS "application",
"marketing" AS "marketing"
FROM "getitem_where_boolean___d0a208add8c64ef83a59cef7edf355e4"
),
"session_starts___27f8c4162872ab094450b32a4ed48317" AS (
SELECT "event_id" AS "event_id",
"day" AS "day",
"moment" AS "moment",
"user_id" AS "user_id",
"location_stack" AS "location_stack",
"event_type" AS "event_type",
"stack_event_types" AS "stack_event_types",
"application" AS "application",
"marketing" AS "marketing",
CASE WHEN (extract(epoch FROM (("moment") - (lag("moment", 1, cast(NULL AS timestamp WITHOUT TIME ZONE)) OVER (PARTITION BY "user_id" ORDER BY "moment" ASC NULLS LAST, "event_id" ASC NULLS LAST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)))) <= cast(1800 AS bigint)) THEN cast(NULL AS boolean)
ELSE cast(TRUE AS boolean)
END AS "is_start_of_session"
FROM "context_data___eaf1462be86e6368151e0cf9af3e04ed"
),
"session_id_and_count___d78d64ff0d102b2362de6240a7287c98" AS (
SELECT "event_id" AS "event_id",
"day" AS "day",
"moment" AS "moment",
"user_id" AS "user_id",
"location_stack" AS "location_stack",
"event_type" AS "event_type",
"stack_event_types" AS "stack_event_types",
"application" AS "application",
"marketing" AS "marketing",
"is_start_of_session" AS "is_start_of_session",
CASE WHEN "is_start_of_session" THEN row_number() OVER (PARTITION BY "is_start_of_session" ORDER BY "moment" ASC NULLS LAST, "event_id" ASC NULLS LAST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
ELSE cast(NULL AS bigint)
END AS "session_start_id",
count("is_start_of_session") OVER (ORDER BY "user_id" ASC NULLS LAST, "moment" ASC NULLS LAST, "event_id" ASC NULLS LAST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "is_one_session"
FROM "session_starts___27f8c4162872ab094450b32a4ed48317"
),
"objectiv_sessionized_data___2b0854f76f7d3c374b0b7861e1e81c02" AS (
SELECT "event_id" AS "event_id",
"day" AS "day",
"moment" AS "moment",
"user_id" AS "user_id",
"location_stack" AS "location_stack",
"event_type" AS "event_type",
"stack_event_types" AS "stack_event_types",
"application" AS "application",
"marketing" AS "marketing",
"is_start_of_session" AS "is_start_of_session",
"session_start_id" AS "session_start_id",
"is_one_session" AS "is_one_session",
first_value("session_start_id") OVER (PARTITION BY "is_one_session" ORDER BY "moment" ASC NULLS LAST, "event_id" ASC NULLS LAST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "session_id",
row_number() OVER (PARTITION BY "is_one_session" ORDER BY "moment" ASC NULLS LAST, "event_id" ASC NULLS LAST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "session_hit_number"
FROM "session_id_and_count___d78d64ff0d102b2362de6240a7287c98"
),
"getitem_where_boolean___05da29b84521bd6d7acbd271d371b26d" AS (
SELECT "event_id" AS "event_id",
"day" AS "day",
"moment" AS "moment",
"user_id" AS "user_id",
"location_stack" AS "location_stack",
"event_type" AS "event_type",
"stack_event_types" AS "stack_event_types",
"session_id" AS "session_id",
"session_hit_number" AS "session_hit_number",
"application" AS "application",
"marketing" AS "marketing",
"application"->0->>'id' AS "application_id",
(
SELECT string_agg(replace(regexp_replace(value ->> '_type', '([a-z])([A-Z])', '\1 \2', 'g'), ' Context', '') || ': ' || (value ->> 'id'), ' => ')
FROM jsonb_array_elements("location_stack") WITH
ORDINALITY
WHERE
ORDINALITY = jsonb_array_length("location_stack")
) || (CASE WHEN jsonb_array_length("location_stack") > 1 THEN ' located at ' || (SELECT string_agg(replace(regexp_replace(value ->> '_type', '([a-z])([A-Z])', '\1 \2', 'g'), ' Context', '') || ': ' || (value ->> 'id'), ' => ') FROM jsonb_array_elements("location_stack") WITH ORDINALITY WHERE ORDINALITY < jsonb_array_length("location_stack") ) ELSE '' END) AS "feature_nice_name"
FROM "objectiv_sessionized_data___2b0854f76f7d3c374b0b7861e1e81c02"
WHERE ("event_type" = 'PressEvent')
),
"getitem_where_boolean___3b536504b37038abde053debeea64172" AS (
SELECT "event_id" AS "event_id",
"day" AS "day",
"moment" AS "moment",
"user_id" AS "user_id",
"location_stack" AS "location_stack",
"event_type" AS "event_type",
"stack_event_types" AS "stack_event_types",
"session_id" AS "session_id",
"session_hit_number" AS "session_hit_number",
"application" AS "application",
"marketing" AS "marketing",
"application_id" AS "application_id",
"feature_nice_name" AS "feature_nice_name",
CASE WHEN ("application_id" = 'objectiv-docs') THEN cast(TRUE AS boolean)
ELSE cast(FALSE AS boolean)
END AS "is_conversion_event"
FROM "getitem_where_boolean___05da29b84521bd6d7acbd271d371b26d"
WHERE CASE WHEN ("application_id" = 'objectiv-docs') THEN cast(TRUE AS boolean)
ELSE cast(FALSE AS boolean)
END
) SELECT "feature_nice_name" AS "feature_nice_name",
((cast(count(DISTINCT "user_id") AS double precision) / cast(528 AS bigint)) * cast(100 AS bigint)) AS "converted_users_percentage"
FROM "getitem_where_boolean___3b536504b37038abde053debeea64172"
GROUP BY "feature_nice_name"

That’s it! Join us on Slack if you have any questions or suggestions.

Next Steps

Play with this notebook in Objectiv Up

Spin up a full-fledged product analytics pipeline with Objectiv Up in under 5 minutes, and play with this example notebook yourself.

Use this notebook with your own data

You can use the example notebooks on any dataset that was collected with Objectiv’s tracker, so feel free to use them to bootstrap your own projects. They are available as Jupyter notebooks on our GitHub repository. See instructions to set up the Objectiv tracker.

Now that you’ve discovered the customer journeys that lead to conversion or drop-off, you can further analyze each of them to understand which ones could be optimized, or should get more/less focus. Another next step could be to have a more in-depth look at the marketing campaign data differences per source.