Usage Funnels - Warby Parker

Hi, I’ve just finished a project assignment of usage funnels using fictional data from Warby Parker. Could anyone help to check my work, please? Thank you in advance.

Project link: https://www.codecademy.com/paths/analyze-data-with-sql/tracks/analyze-data-sql-analyze-real-data/modules/analyze-data-sql-usage-funnels/projects/analyze-data-sql-usage-funnels-with-warby-parker
The project is divided into 2 funnels:

  1. Quiz funnel which has one database called survey
  • survey has question, user_id, and response columns, all TEXT type and it has 1,986 rows
  1. Home Try-On Funnel which has 3 databases called quiz, home_try_on and purchase
  • quiz has user_id, style, fit, shape, and color columns, all TEXT type and it has 1,000 rows
    It’s a result of quiz of 5 questions of 1,000 users
  • home_try_on has user_id, number_of_pairs and address columns, all TEXT type and it has 750 rows
    It’s a list of users which went on the process, some decided to home try the product and some didn’t
  • purchase has user_id (TEXT), product_id (INTEGER), style (TEXT), model_name (TEXT), color (TEXT) and price (INTEGER)

The project original task is to make a presentation in order to answer all of the questions in the project and each answer should include a text explanation and data to support my claim. I haven’t made the presentation nor I will, I prefer to make a report in word instead.

First question: What is the number of responses for each question?

My query:
SELECT question, COUNT(DISTINCT user_id)
FROM survey
GROUP BY question;

OUTPUT:
|Question| Count|
|1. What are you looking for?| 500|
|2. What’s your fit?| 475|
|3. Which shapes do you like?| 380|
|4. Which colors do you like?| 361|
|5. When was your last eye exam?| 270|

Second question: Which question(s) of the quiz have a lower completion rates?

|Warby Parker Survey
|Question |Answers Count |Percentage|
|1. What are you looking for? | 500 | 100.00%|
|2. What’s your fit? | 475 | 95.00%|
|3. Which shapes do you like? | 380 | 80.00%|
|4. Which colors do you like? | 361 | 95.00%|
|5. When was your last eye exam? | 270 | 74.79%|

Based on this result, my answer would be the 5th.

Third question: What do you think is the reason?

I think most users think that the last eye exam is irrelevant with their needs of eyewear, maybe those who didn’t answer were looking for product which has nothing to do with the eye exam or they just don’t want the supplier to know about it.

Next: Create a new table with the following layout:
image

My query:
SELECT q.user_id,
CASE
WHEN q.user_id = h.user_id THEN ‘True’
ELSE ‘False’
END AS ‘is_home_try_on’,
h.number_of_pairs,
CASE
WHEN q.user_id = p.user_id THEN ‘True’
ELSE ‘False’
END AS ‘is_purchase’,
p.price
FROM quiz q
LEFT JOIN home_try_on h
ON q.user_id = h.user_id
LEFT JOIN purchase p
ON q.user_id = p.user_id
LIMIT 10;

Last post OUTPUT:
image

With the result of the last query I count the number of users in each process by using the following query:

WITH summary_of_3dbs AS (
SELECT DISTINCT(q.user_id),
CASE
WHEN q.user_id = h.user_id THEN ‘True’
ELSE ‘False’
END AS ‘is_home_try_on’,
h.number_of_pairs,
CASE
WHEN q.user_id = p.user_id THEN ‘True’
ELSE ‘False’
END AS ‘is_purchase’
FROM quiz q
LEFT JOIN home_try_on h
ON q.user_id = h.user_id
LEFT JOIN purchase p
ON q.user_id = p.user_id)
SELECT COUNT(user_id) AS ‘Users Count’,
COUNT(CASE WHEN is_home_try_on = ‘True’ THEN user_id END) AS ‘Home Try Users Count’,
COUNT(CASE WHEN is_purchase = ‘False’ THEN user_id END) AS ‘Purchase Users Count’
FROM summary_of_3dbs;

OUTPUT:
|Home Try On Funnel Users Count|
|Users Count|Home Try Users Count|Purchase Users Count|
| 1000 | 750 | 505 |
| 100.00% | 75.00% | 67.33%|

Congrats on finishing the project. :partying_face:

It might be better–for reviewers- and for presentation purposes, to provide a link to a GitHub repo or to a view-only google slides presentation.

1 Like

I also count the number of users who make the purchase by the type of home try-on using the following query:

WITH summary_of_3dbs AS (
SELECT DISTINCT(q.user_id),
CASE
WHEN q.user_id = h.user_id THEN ‘True’
ELSE ‘False’
END AS ‘is_home_try_on’,
h.number_of_pairs,
CASE
WHEN q.user_id = p.user_id THEN ‘True’
ELSE ‘False’
END AS ‘is_purchase’
FROM quiz q
LEFT JOIN home_try_on h
ON q.user_id = h.user_id
LEFT JOIN purchase p
ON q.user_id = p.user_id)
SELECT number_of_pairs,
COUNT(CASE WHEN is_purchase = ‘False’ THEN user_id END) AS ‘Purchase Users Count’
FROM summary_of_3dbs
GROUP BY number_of_pairs
ORDER BY 2 DESC;

OUTPUT:
|number_of_pairs|Purchase Users Count|
| 0 | 250 |
| 3 pairs | 178 |
| 5 pairs | 77 |

Based on this we can assume that the home try-on might encourage users to purchase the product because more than half users purchased after home try-on (home try-on purchase (255) is more than non-home try on purchase (250).
We can also assume that 3 pairs home try-on might encourage users to purchase more than 5 pairs since 3 pairs home try-on (178) represent 69.80% of the home try-on sales.

Thanks for the recommendation! I’ve never use GitHub before and frankly I still don’t know how to use SQL off platform. I’ll check it out.

1 Like

As promised, this is the link of the report for this assignment. Your feedback and time will be appreciated!