!pip install mitzu
import mitzu
# 1. Open the sample project
dp = mitzu.get_sample_discovered_project(random_seed=1210)
# 2. Create notebook class model
m = dp.create_notebook_class_model()
Successfully finished dataset discovery.
dp.project.get_adapter().execute_query("SELECT name FROM sqlite_master WHERE type='table'")
nameobject
0
page_events
1
search_events
2
add_to_carts
3
checkouts
4
email_subscriptions
5
email_sent_events
6
email_opened_events
dp.project.get_adapter().execute_query("select * from page_events limit 100")
event_nameobject
page_visit79%
element_clicked_on_page21%
event_timeobject
2021-09-02 19:51:01.0000002%
2021-05-11 11:47:04.0000002%
77 others96%
0
page_visit
2021-09-04 19:20:07.000000
1
page_visit
2021-10-20 05:16:08.000000
2
page_visit
2021-09-02 19:51:01.000000
3
element_clicked_on_page
2021-09-02 19:51:01.000000
4
page_visit
2021-10-14 08:39:34.000000
5
page_visit
2021-02-11 10:52:55.000000
6
page_visit
2021-05-11 11:47:04.000000
7
element_clicked_on_page
2021-05-11 11:47:04.000000
8
page_visit
2021-06-28 13:06:05.000000
9
page_visit
2021-12-14 06:32:18.000000
m.page_visit
m.page_visit >> m.checkout
m.page_visit >= m.checkout
m.page_visit.domain == 'megamagasin.com'
# or simply just use tab to help you find the possible property values:
# m.page_visit.domain.is_megamagasin_com
m.page_visit.acquisition_campaign.is_facebook_amer & (m.page_visit.user_country_code == 'us')
(m.page_visit.domain.is_awesomestore_com).config(
start_dt="2021-05-01",
end_dt="2021-10-01",
group_by=m.page_visit.acquisition_campaign,
time_group="week"
)
(m.page_visit >> m.add_to_cart >> m.checkout).config(
start_dt="2021-06-01",
end_dt="2021-10-01",
group_by=m.page_visit.domain,
time_group="total",
conv_window="6 hours"
)
(m.page_visit >= m.checkout.user_country_code.is_us).config(
retention_window="1 month",
time_group="month",
start_dt="2021-01-01",
end_dt="2021-12-31")
(m.page_visit >= m.checkout.user_country_code.is_us).config(
retention_window="1 month",
time_group="total",
start_dt="2021-01-01",
end_dt="2021-12-31",
group_by=m.page_visit.acquisition_campaign)
metric = (m.page_visit >> m.add_to_cart >> m.checkout).config(
start_dt="2021-06-01",
end_dt="2021-10-01",
group_by=m.page_visit.domain,
time_group="total",
conv_window="6 hours"
)
metric.print_sql()
metric.get_df()
WITH anon_1 AS
(SELECT page_events.user_id AS _cte_user_id,
page_events.event_time AS _cte_datetime,
page_events.domain AS _cte_group
FROM page_events
WHERE page_events.event_name = 'page_visit'
AND page_events.event_time >= '2021-06-01 00:00:00'
AND page_events.event_time <= '2021-10-01 00:00:00'
AND TRUE),
anon_2 AS
(SELECT add_to_carts.user_id AS _cte_user_id,
add_to_carts.event_time AS _cte_datetime,
NULL AS _cte_group
FROM add_to_carts
WHERE add_to_carts.event_name = 'add_to_cart'
AND add_to_carts.event_time >= '2021-06-01 00:00:00'
AND add_to_carts.event_time <= '2021-10-01 06:00:00'
AND TRUE),
anon_3 AS
(SELECT checkouts.user_id AS _cte_user_id,
checkouts.event_time AS _cte_datetime,
NULL AS _cte_group
FROM checkouts
WHERE checkouts.event_name = 'checkout'
AND checkouts.event_time >= '2021-06-01 00:00:00'
AND checkouts.event_time <= '2021-10-01 06:00:00'
AND TRUE)
SELECT NULL AS _datetime,
anon_1._cte_group AS _group,
count(DISTINCT anon_1._cte_user_id) AS _user_count_1,
(count(DISTINCT anon_1._cte_user_id) * 100.0) / count(DISTINCT anon_1._cte_user_id) AS _agg_value_1,
count(DISTINCT anon_2._cte_user_id) AS _user_count_2,
(count(DISTINCT anon_2._cte_user_id) * 100.0) / count(DISTINCT anon_1._cte_user_id) AS _agg_value_2,
count(DISTINCT anon_3._cte_user_id) AS _user_count_3,
(count(DISTINCT anon_3._cte_user_id) * 100.0) / count(DISTINCT anon_1._cte_user_id) AS _agg_value_3
FROM anon_1
LEFT OUTER JOIN anon_2 ON anon_1._cte_user_id = anon_2._cte_user_id
AND anon_2._cte_datetime > anon_1._cte_datetime
AND anon_2._cte_datetime <= datetime(anon_1._cte_datetime, '+6 hour')
LEFT OUTER JOIN anon_3 ON anon_2._cte_user_id = anon_3._cte_user_id
AND anon_3._cte_datetime > anon_2._cte_datetime
AND anon_3._cte_datetime <= datetime(anon_1._cte_datetime, '+6 hour')
GROUP BY _datetime,
_group
_datetimeobject
_groupobject
0
None
awesomestore.com
1
None
awesomestore.fr
2
None
megamagasin.com