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

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.

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,
GROUP BY 1
ORDER BY 2 DESC;

--fit:

SELECT fit,
GROUP BY 1
ORDER BY 2 DESC;

--shape:

SELECT shape,
GROUP BY 1
ORDER BY 2 DESC;

--color:

SELECT color,
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