MetrocarFunnel
Metrocar
Funnel
Analysis
Analysis
Mastery project(DA106)
By Olayemi Elebute
29 October 2023
Table of Content
Project Overview…………………………………………………………………..…………………………………………….2
Metrocar funnel….……………………………….…………………………….……………………………………………….2
Data Exploration……………………………………………….………………………..……..……………………………….3
Result of funnel analysis……………………………………………….…….…..………………………………………….4
Advance task…….………………………………………………………..………………………………………………………7
Recommendations…………………………………………………………………………………………………..…………8
Appendices………………………………………………………………………………………………………………………….10
Project Overview
MetroCar, a ride-sharing app(similar to uber/lyft) is challenged set out to review its customer
funnel in order to identify customer drop-off points within the funnel and proffer lasting solutions
in form of funnel optimization. This task is critical to the business as such analysis, if done properly
will better position the company for increased revenue and more market share.
This project analyses the customer funnel of Metrocar in line with the business questions posed by
the company stakeholders and recommendations were made based on insights drawn from data.
MetroCar Funnel
Funnel analysis is used in data analysis to to track and understand the steps customers of a
product or service go through when interacting with the company. It help identify where
customers drop-off or convert and widely used to drive growth and revenue.
Figure 1 below show Metrocar’s funnel
Data Exploration
The data set structure is characterised by five tables with different columns as detailed in the
schema below. The data analyst utilize SQL(Postgre) to extract data-set which was further used for
analysis. Two main data-set was extracted as follows
1. User funnel - this dateset comprises funnel details at user level granularity starting from the first
stage (download) to final stage (review) excluding ride count as revealed in Table 1 below.
2. Ride funnel - this begins with the ride request stage to the last stage of the funnel. The dateset
include other columns of the user funnel but excluded the values of the download and sign up
stages since they both precede ride request stage. In addition, it has the ride count as shown in
Table 2 below.
All links to the SQL query used in generating the user and ride funnel is found in the Appendix.
Result of Funnel Analysis
The business questions presented by the stakeholders form the major result of the funnel analysis
as answers are provided from the insights gathered.
Business Question 1
What step of the funnel should we research and improve? Are there specific drop-off points
preventing users from completing their first ride?
Figure 2 below shows the percentage of top (percentage difference between each funnel stage
and the first stage) and of previous(percentage difference between each funnel stage and the
previous stage).
A notable drop-off point from ride accepted to ride completed stage was highest with 49.23% of
users unable to complete their first ride mostly because of cancellation. Another drop-off point is
the step between app download and sign up revealing about 25.35% of users failed to sign-up
after downloading the app.
Thus, the step between ride request, ride acceptance and completion should be investigated to
ascertain root causes of drop-off and improved accordingly.
Question 2
What insights can we make based on the platform to recommend where to focus metrocar’s
marketing budget for the coming year?
We can infer from the funnel chart in figure 3 below that there are more IOS users in each stage of
our user funnel, followed by Android users with approximately 60% and 30% users respectively.
Large parts of the marketing budget should be channeled towards the IOS and Android platform to
consolidate the statusquo and improve steps between sign-up and ride completed.
Question 3
What age group perform at each stage of our funnel? What age group is likely to contain our
target customers?
Insights from the data in figure 4 above showed users within the age range of 25-34 and 35-44
years were the top identifiable performing age groups across all part of our funnel with about 49%
users. However, the data-set also revealed null(found only in the download stage) and unknown
age-ranges(about 30% of users).
We can infer from the data that all known age groups should be of interest to the business with
specific focus on 25-34 and 35-44 age group. Since the percentage of unknown and null users is
slightly above 30%, it then mean other low performing age groups(18-24 and 45-54) must be
within the target radar as they probably constitute the unknown users.
NB: simplified sign up stage will help address this issue of null and known age-range
Question 4
If we want to adopt a price-surging strategy, what does the distribution of ride request looks like
throughout the day?
⚫
The insight drawn from the distribution of ride request revealed more request were made
between 08:00 to 10:00 and 16:00 to 19:00 hours.
⚫
Thus, a surge-pricing strategy will work best during those peak period (money and evening).
However, the company must exercise caution to avoid user disengagement due to ride cost.
Morning
Evening
Figure 5.
Question 5
What part of our funnel has the lowest conversion rate? What can we do to improve this part of
the funnel.
Figure 6 above revealed the funnel part with the lowest conversion rate is the drop-off from ride
acceptance to ride completion with only 50.77% converting after drivers accepted the ride.
Improvement of this funnel step has a lot to do with a thorough investigation of users cancellation
of accepted ride which was about 50%, and reasons for such cancellation.
Another way to improve this funnel step is to review user feedback and conduct an A/B test to
narrow down specific pain points and make corrections.
Advance Tasks(Dynamic Tableau Dashboard)
The essence of this section is to enable stakeholders within the organisation to explore the data on
their own through an interactive dashboard which will dive deeper and provide answers to other
questions they may have but did not raise.
Dashboard 1
The dashboard below visualizes the funnel with key features such as platform, age-range, and date
range which interact with a single select on any step within the funnel. A parameter was created to
allow the selection of percentage of top or percentage of previous with the default label set at
absolute value. This is shown in figure 7 below and the links to the dashboard can be found in the
appendix.
The link to the above dashboard is found in the appendix.
Dashboard 2
Figure 8 below show a parameter called funnel selector which allow users and stakeholders to
navigate between funnel step of the user and ride funnel. While the user funnel is 7 stage, the ride
funnel is 5 steps since it starts from the ride request stage(when customers made a request).
Recommendations
After a thorough analysis and visualization of the datasets from the Metrocar funnel analysis, I
strongly recommend the following
⚫
Metrocar should further review the funnel step between driver acceptance and ride
completion by using A/B testing or other statistical/behavioural test as majority of drop-off
occurred at this step.
⚫
A simplified user sign up process should be in place to accurately collect user information as
the data possess some null and unknown values of user ages which largely impede the output
of the funnel analysis across age group. However, based on the insights from available data,
the company should target the four categories of age group but specific focus should be on
age groups between (25-34) and (35-44) since they constitute large segment of our customer
base and this can be done through offer promotions/discounts and targeted social media
adverts.
⚫
IOS and Android platform should get more marketing funding in the coming year since
approximately 90% of users downloaded, signed up, requested and make payment via this
platforms. However, the web platform can be improved as necessary.
⚫
To ultimately resolve cancellation of rides, a combination of technology and human solution
must be employed. The use of data-driven algorithms, ensuring ETA is constant and a penalty
fee for cancellation emanating from users own situation will go a long way.
Appendices
A. Funnel step(business question 1 & 5)
https://public.tableau.com/views/UserFunnel_-/ridefunnel?:language=en-US&pu
blish=yes&:display_count=n&:origin=viz_share_link
Platform (Business question 2)
https://public.tableau.com/views/UserFunnel_-/platform?:language=en-US&:disp
lay_count=n&:origin=viz_share_link
Age group(business question 3)
https://public.tableau.com/views/UserFunnel_-/platform1?:language=en-US&:dis
play_count=n&:origin=viz_share_link
Surge-pricing(business question 4)
https://public.tableau.com/views/UserFunnel_-/Surgeprice?:language=en-US&pu
blish=yes&:display_count=n&:origin=viz_share_link
Advance(dynamic dashboard with filter)
https://public.tableau.com/views/UserFunnel_-/DynamicDashboard?:language=e
n-US&publish=yes&:display_count=n&:origin=viz_share_link
Advance(dynamic dashboard with funnel selector)
https://public.tableau.com/views/UserFunnel_-/Funnelselectordashboard?:langu
age=en-US&:display_count=n&:origin=viz_share_link
B. Spreadsheet link
C1. Query for Metrocar user funnel
with Funnel as(
select
1 as funnel_step,
'Download' as funnel_name,
platform,
s.age_range,
date(a.download_ts) as download_dt,
count(distinct app_download_key)as user_count
from app_downloads a
left join
s.session_id
signups
s
on
a.app_download_key
left join ride_requests r on s.user_id = r.user_id
=
left join transactions t on r.ride_id = t.ride_id
group
by
funnel_step,funnel_name,
s.age_range,download_dt
platform,
union all
select
2 as funnel_step,
'Sign_ups' as funnel_name,
a.platform,
age_range,
date(a.download_ts) as download_dt,
count(distinct s.user_id) as user_count
from app_downloads a
left join
s.session_id
signups
s
on
a.app_download_key
=
left join ride_requests r on s.user_id = r.user_id
LEFT join transactions t on r.ride_id = t.ride_id
where s.user_id is not null
group by funnel_step,funnel_name,
age_range, download_dt
a.platform,
union all
select
3 as funnel_step,
'Requested_ride' as funnel_name,
a.platform,
age_range,
date(a.download_ts) as download_dt,
count(distinct r.user_id) as user_count
from app_downloads a
left join
s.session_id
signups
s
on
a.app_download_key
left join ride_requests r on s.user_id = r.user_id
LEFT join transactions t on r.ride_id = t.ride_id
where r.request_ts is not null
=
group by funnel_step,funnel_name,
age_range,download_dt
a.platform,
union all
select
4 as funnel_step,
'Accepted_ride' as funnel_name,
a.platform,
age_range,
date(a.download_ts) as download_dt,
count(distinct r.user_id) as user_count
from app_downloads a
left join
s.session_id
signups
s
on
a.app_download_key
=
left join ride_requests r on s.user_id = r.user_id
LEFT join transactions t on r.ride_id = t.ride_id
where r.accept_ts is not null
group by funnel_step,funnel_name,
age_range,download_dt
a.platform,
union all
select
5 as funnel_step,
'Completed_ride' as funnel_name,
a.platform,
age_range,
date(a.download_ts) as download_dt,
count(distinct r.user_id) as user_count
from app_downloads a
left join
s.session_id
signups
s
on
a.app_download_key
=
left join ride_requests r on s.user_id = r.user_id
LEFT join transactions t on r.ride_id = t.ride_id
where r.cancel_ts is null
group
by
funnel_step,funnel_name,
a.platform,
age_range,download_dt
union all
select
6 as funnel_step,
'Payment' as funnel_name,
a.platform,
age_range,
date(a.download_ts) as download_dt,
count(distinct r.user_id) as user_count
from app_downloads a
left join
s.session_id
signups
s
on
a.app_download_key
=
left join ride_requests r on s.user_id = r.user_id
join transactions t on r.ride_id = t.ride_id
where charge_status = 'Approved'
group by funnel_step,funnel_name,
age_range,download_dt
a.platform,
union all
select
7 as funnel_step,
'Review' as funnel_name,
a.platform,
age_range,
date(a.download_ts) as download_dt,
count(distinct r.user_id) as user_count
from app_downloads a
left join
s.session_id
signups
s
on
a.app_download_key
=
left join reviews r on s.user_id = r.user_id
join transactions t on r.ride_id = t.ride_id
group by funnel_step,funnel_name,
age_range,download_dt
)
a.platform,
select
funnel_step,
funnel_name,
platform,
age_range,
download_dt,
user_count
from Funnel
order by funnel_step, platform,download_dt,age_range,
user_count desc
C2. Query for ride funnel
with Download as (
select
distinct app_download_key as users,
platform as platform,
age_range as age_range,
date(download_ts) as download_dt,
to_char(request_ts,'hh24') as book_time,
(select ride_id from metrocar where signup_ts >
request_ts) as rides
from metrocar
),
Sign_up as (
select
distinct user_id as users,
platform as platform,
age_range as age_range,
date(download_ts) as download_dt,
to_char(request_ts,'hh24') as book_time,
(select ride_id from metrocar where signup_ts >
request_ts) as rides
from metrocar
where signup_ts is not null
),
Request as (
select
distinct m.user_id as users,
m.platform as platform,
m.age_range as age_range,
date(m.download_ts) as download_dt,
to_char(m.request_ts,'hh24') as book_time,
m.ride_id as rides
from Sign_up s
left join metrocar m
on s.users = m.user_id
where m.request_ts is not null
),
Accepted
as (
select
distinct m.user_id as users,
m.platform as platform,
m.age_range as age_range,
date(m.download_ts) as download_dt,
to_char(m.request_ts,'hh24') as book_time,
m.ride_id as rides
from Request req
left join metrocar m
on req.users = m.user_id
where m.accept_ts is not null
),
Completed as (
select
distinct m.user_id as users,
m.platform as platform,
m.age_range as age_range,
date(m.download_ts) as download_dt,
to_char(m.request_ts,'hh24') as book_time,
m.ride_id as rides
from Request re
left join metrocar m
on re.users = m.user_id
where m.cancel_ts is null
),
Paid as (
select
distinct m.user_id as users,
m.platform as platform,
m.age_range as age_range,
date(m.download_ts) as download_dt,
to_char(m.request_ts,'hh24') as book_time,
m.ride_id as rides
from Completed ri
left join metrocar m
on ri.users = m.user_id
where m.charge_status = 'Approved'
),
Review as (
select
distinct m.user_id as users,
m.platform as platform,
m.age_range as age_range,
date(m.download_ts) as download_dt,
to_char(m.request_ts,'hh24') as book_time,
m.ride_id as rides
from Paid p
left join metrocar m
on p.users = m.user_id
where m.review is not null
),
steps as (
select
1 as funnel_step,
'Download' as funnel_name,
platform,
age_range,
download_dt,
book_time,
count(users) as user_count,
count(rides) as ride_count
from Download
group
by
funnel_step,
funnel_name,platform,age_range,download_dt,
book_time
union
select
2 as funnel_step,
'Sign_up' as funnel_name,
platform,
age_range,
download_dt,
book_time,
count(distinct users) as user_count,
count(rides) as ride_count
from Sign_up
group
by
funnel_step,
funnel_name,platform,age_range,download_dt,book_time
union
select
3 as funnel_step,
'Requested' as funnel_name,
platform,
age_range,
download_dt,
book_time,
count(distinct users) as user_count,
count(rides) as ride_count
from Request
group
by
funnel_step,
funnel_name,platform,age_range,download_dt,book_time
union
select
4 as funnel_step,
'Accepted' as funnel_name,
platform,
age_range,
download_dt,
book_time,
count(distinct users) as user_count,
count(rides) as ride_count
from Accepted
group
by
funnel_step,
funnel_name,platform,age_range,download_dt,book_time
union
select
5 as funnel_step,
'Completed' as funnel_name,
platform,
age_range,
download_dt,
book_time,
count(distinct users) as user_count,
count(rides) as ride_count
from Completed
group
by
funnel_step,
funnel_name,platform,age_range,download_dt,book_time
union
select
6 as funnel_step,
'Paid' as funnel_name,
platform,
age_range,
download_dt,
book_time,
count(distinct users) as user_count,
count(rides) as ride_count
from Paid
group
by
funnel_step,
funnel_name,platform,age_range,download_dt,book_time
union
select
7 as funnel_step,
'Review' as funnel_name,
platform,
age_range,
download_dt,
book_time,
count(distinct users) as user_count,
count(rides) as ride_count
from Review
group
by
funnel_step,
funnel_name,platform,age_range,download_dt,book_time
order by funnel_step
)
select
funnel_step,
funnel_name,
platform,
age_range,
download_dt,
book_time,
user_count,
ride_count
from steps
order by funnel_step
;
C3. Query for business question
4(surge-pricing)
with Download as (
select
distinct app_download_key as users,
platform as platform,
age_range as age_range,
date(download_ts) as download_dt,
to_char(request_ts,'hh24') as book_time,
(select ride_id from metrocar where signup_ts >
request_ts) as rides
from metrocar
),
Sign_up as (
select
distinct user_id as users,
platform as platform,
age_range as age_range,
date(download_ts) as download_dt,
to_char(request_ts,'hh24') as book_time,
(select ride_id from metrocar where signup_ts >
request_ts) as rides
from metrocar
where signup_ts is not null
),
Request as (
select
distinct m.user_id as users,
m.platform as platform,
m.age_range as age_range,
date(m.download_ts) as download_dt,
to_char(m.request_ts,'hh24') as book_time,
m.ride_id as rides
from Sign_up s
left join metrocar m
on s.users = m.user_id
where m.request_ts is not null
),
Accepted
as (
select
distinct m.user_id as users,
m.platform as platform,
m.age_range as age_range,
date(m.download_ts) as download_dt,
to_char(m.request_ts,'hh24') as book_time,
m.ride_id as rides
from Request req
left join metrocar m
on req.users = m.user_id
where m.accept_ts is not null
),
Completed as (
select
distinct m.user_id as users,
m.platform as platform,
m.age_range as age_range,
date(m.download_ts) as download_dt,
to_char(m.request_ts,'hh24') as book_time,
m.ride_id as rides
from Request re
left join metrocar m
on re.users = m.user_id
where m.cancel_ts is null
),
Paid as (
select
distinct m.user_id as users,
m.platform as platform,
m.age_range as age_range,
date(m.download_ts) as download_dt,
to_char(m.request_ts,'hh24') as book_time,
m.ride_id as rides
from Completed ri
left join metrocar m
on ri.users = m.user_id
where m.charge_status = 'Approved'
),
Review as (
select
distinct m.user_id as users,
m.platform as platform,
m.age_range as age_range,
date(m.download_ts) as download_dt,
to_char(m.request_ts,'hh24') as book_time,
m.ride_id as rides
from Paid p
left join metrocar m
on p.users = m.user_id
where m.review is not null
),
steps as (
select
1 as funnel_step,
'Download' as funnel_name,
platform,
age_range,
download_dt,
book_time,
count(users) as user_count,
count(rides) as ride_count
from Download
group
by
funnel_step,
funnel_name,platform,age_range,download_dt,
book_time
union
select
2 as funnel_step,
'Sign_up' as funnel_name,
platform,
age_range,
download_dt,
book_time,
count(distinct users) as user_count,
count(rides) as ride_count
from Sign_up
group
by
funnel_step,
funnel_name,platform,age_range,download_dt,book_time
union
select
3 as funnel_step,
'Requested' as funnel_name,
platform,
age_range,
download_dt,
book_time,
count(distinct users) as user_count,
count(rides) as ride_count
from Request
group
by
funnel_step,
funnel_name,platform,age_range,download_dt,book_time
union
select
4 as funnel_step,
'Accepted' as funnel_name,
platform,
age_range,
download_dt,
book_time,
count(distinct users) as user_count,
count(rides) as ride_count
from Accepted
group
by
funnel_step,
funnel_name,platform,age_range,download_dt,book_time
union
select
5 as funnel_step,
'Completed' as funnel_name,
platform,
age_range,
download_dt,
book_time,
count(distinct users) as user_count,
count(rides) as ride_count
from Completed
group
by
funnel_step,
funnel_name,platform,age_range,download_dt,book_time
union
select
6 as funnel_step,
'Paid' as funnel_name,
platform,
age_range,
download_dt,
book_time,
count(distinct users) as user_count,
count(rides) as ride_count
from Paid
group
by
funnel_step,
funnel_name,platform,age_range,download_dt,book_time
union
select
7 as funnel_step,
'Review' as funnel_name,
platform,
age_range,
download_dt,
book_time,
count(distinct users) as user_count,
count(rides) as ride_count
from Review
group
by
funnel_step,
funnel_name,platform,age_range,download_dt,book_time
order by funnel_step
)
select
funnel_step,
funnel_name,
platform,
age_range,
download_dt,
book_time,
user_count,
ride_count
from steps
order by funnel_step
;