Warby Parker usage funnels (SQL, but includes some Python!) + PowerPoint presentation

Projects
1

Hello all, this is my first project feedback thread. I’ve not only done the SQL part extensively, but also created a funnel percentage calculator using Python. Look below the SQL code to find it.

PowerPoint presentation:
https://docs.google.com/presentation/d/1RUlCZDcwe4cU_ETzcseOIEM1bM4jcoHhppENRiVdvAw/edit?usp=sharing

The project itself:

 -- Ex. 1
 
 SELECT * FROM survey
 LIMIT 10;

-- Ex. 2

SELECT question, 
	COUNT(DISTINCT(user_id)) AS 'no_of_users' 
FROM survey
GROUP BY 1
ORDER BY 2 DESC;

-- Ex. 3 below, done in Python.

-- Ex. 4:

SELECT * FROM quiz
LIMIT 5;

SELECT * FROM home_try_on
LIMIT 5;

SELECT * FROM purchase
LIMIT 5;

-- Ex. 5:

SELECT DISTINCT quiz.user_id,
	home_try_on.user_id IS NOT NULL AS 'is_home_try_on',
	home_try_on.number_of_pairs,
	purchase.user_id IS NOT NULL AS 'is_purchase'
FROM quiz
LEFT JOIN home_try_on
	ON quiz.user_id = home_try_on.user_id
LEFT JOIN purchase
	ON quiz.user_id = purchase.user_id
LIMIT 10;

-- conversions funnel: quiz -> home_try_on and home_try_on -> purchase

WITH funnels AS(
	SELECT DISTINCT quiz.user_id AS 'u_id',
		home_try_on.user_id IS NOT NULL AS 'is_home_try_on',
		home_try_on.number_of_pairs,
		purchase.user_id IS NOT NULL AS 'is_purchase'
	FROM quiz
	LEFT JOIN home_try_on
		ON quiz.user_id = home_try_on.user_id
	LEFT JOIN purchase
		ON quiz.user_id = purchase.user_id
)

SELECT COUNT(u_id) AS 'Step1', 
	SUM(is_home_try_on) AS 'Step2', 
	SUM(is_purchase) AS 'Step3' 
FROM funnels;

-- by number of pairs

WITH funnels AS(
	SELECT DISTINCT(quiz.user_id) AS 'u_id',
		home_try_on.user_id IS NOT NULL AS 'is_home_try_on',
		home_try_on.number_of_pairs AS 'no_of_pairs',
		purchase.user_id IS NOT NULL AS 'is_purchase'
	FROM quiz
	LEFT JOIN home_try_on
		ON quiz.user_id = home_try_on.user_id
	LEFT JOIN purchase
		ON quiz.user_id = purchase.user_id
)

SELECT no_of_pairs, 
	SUM(is_purchase) AS 'purchase rate' 
FROM funnels
GROUP BY 1
ORDER BY 2 DESC;

-- sales by gender pref:
WITH purchases AS(
	SELECT style, 
  	model_name, 
  	user_id IS NOT NULL AS 'is_purchase', 
  	price 
	FROM purchase
)
SELECT style, 
	SUM(is_purchase) AS 'no_of_purchases', 
	price * SUM(is_purchase) AS 'revenue' 
FROM purchases
GROUP BY 1
ORDER BY 2 DESC;

-- most popular model:

WITH purchases AS(
	SELECT style, 
  	model_name, 
  	user_id IS NOT NULL AS 'is_purchase' 
	FROM purchase
)

SELECT model_name, 
	SUM(is_purchase) AS 'no_of_purchases'
FROM purchases
GROUP BY 1
ORDER BY 2 DESC;

--most popular color purchased:
WITH purchases AS(
	SELECT product_id, 
  	style, 
  	color, 
  	price, 
  	user_id IS NOT NULL AS 'is_purchase' 
	FROM purchase
)

SELECT color, 
	COUNT(is_purchase) AS 'no_of_purchases' 
FROM purchases
GROUP BY 1
ORDER BY 2 DESC;
  
-- revenue by product:

WITH purchases AS(
	SELECT product_id,
  	style, 
  	color, 
  	price, 
  	user_id IS NOT NULL AS 'is_purchase' 
	FROM purchase
)

SELECT product_id, 
	style, 
	color, 
	price * SUM(is_purchase) AS 'revenue' 
FROM purchases
GROUP BY 1
ORDER BY 4 DESC;

-- most common results of quiz:
--style:

SELECT style, 
	COUNT(user_id) AS 'no_of_answers' FROM quiz
GROUP BY 1
ORDER BY 2 DESC;

--fit:

SELECT fit,
	COUNT(user_id) AS 'no_of_answers' FROM quiz
GROUP BY 1
ORDER BY 2 DESC;

--shape:

SELECT shape, 
	COUNT(user_id) AS 'no_of_answers' FROM quiz
GROUP BY 1
ORDER BY 2 DESC;

--color:

SELECT color, 
	COUNT(user_id) AS 'no_of_answers' FROM quiz
GROUP BY 1
ORDER BY 2 DESC;

--purchases by price:

SELECT price, 
	COUNT(user_id) AS 'no_of_purchases'
FROM purchase
GROUP BY 1
ORDER BY 2 DESC;

--revenue by price:

SELECT price, 
	COUNT(user_id) * price AS 'revenue'
FROM purchase
GROUP BY 1
ORDER BY 2 DESC;

Now the Funnel percentage calculator:

#User inputs:
no_of_i = int(input('Number of steps: '))
input1 = int(input('1st step: '))

#Inputting subsequent inputs and making a list out of all user inputs:

inputs = []

inputs.append(input1)

while True:
    subs_i = int(input('Subsequent step: '))
    if subs_i <= input1 and (no_of_i -1) > len(inputs):
        inputs.append(subs_i)
        subs_i = input1
    elif subs_i >= input1 or (no_of_i -1) < len(inputs) or subs_i < 0:
        print('Invalid value')
    else:
        break
inputs.append(subs_i)
print("Your funnel values are: " + str(inputs))

#Calculating percentages using items in the list

def percentage(inputs):
    x = 0
    y = 1
    for i in inputs:
        prev_i = inputs[y-1]
        x += 1
        y = x
        perc = i / prev_i * 100
        print('Step ' + str(x) + ': ' + str(perc))
        
#Execution
    
percentage(inputs)
input('Press Enter to terminate the program')

Hope you find it useful! Appreciate any feedback.

2 Likes