Olayemi Samuel Elebute

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 ;