240528 Today I Learn
ํผ๋(Funnel)์ด๋?
๐ก ํผ๋(Funnel)์ด๋?
์๋น์์ ํ๋์ ๊ธฐ์ ๊ด์ ์์ ์ฌ๊ตฌ์ฑํ ๊ฒ์ผ๋ก, ๊ณ ๊ฐ์ด ์ ์ ๋์ด ์ ํ์ ์ด๋ฅด๋ ๋จ๊ณ๋ฅผ ์์น๋ก ํ์ธํ๊ณ ๋ถ์ํ๋ ๋ฐฉ๋ฒ๋ก
→ ์ฐ๋ฆฌ๊ฐ ์ํ๋ ๊ฒ์ ๊ณ ๊ฐ์ด ํผ๋์ ๋๊น์ง ํต๊ณผํ๋ ๊ฒ! ์ด๋ฅผ ์ ํ(conversion)/ Key Event ๋ผ๊ณ ํ๋ค.
ํผ๋์ ์ข ๋ฅ๋ค
1. ๋ง์ผํ ํผ๋
2.AARRR ํผ๋
3. ์ฌ์ฉ์ ๊ทธ๋ฃน๋ณ ํผ๋ ๋น๊ต
ํผ๋ ๋จ๊ณ ์๋ฆฝํ๊ธฐ
๐ ๋ง๋ณด๊ธฐ ์์
๋ง์ฝ ์ฌ๋ฌ๋ถ๋ค์ด ์คํ๋ฅดํ ์ฝ๋ฉ ํด๋ฝ์ ๋ฐ์ดํฐ ๋ถ์๊ฐ์ด๊ณ , ์ ์ฒญ ๋ง๊ฐ์ผ์ด ๋ค๊ฐ์ ์ผ์ฃผ์ผ ์์ ๋ฐ์ดํฐ ๋ถ์ ํธ๋ 3๊ธฐ์ ์ฐธ๊ฐ์ ์๋ฅผ 5๋ฐฐ ์ด์ ์ฆ๊ฐ์ํค๊ณ ์ถ๋ค๋ฉด, ์ด๋ป๊ฒ ํด์ผํ ๊น์?
โ ๋ด๊ฐ ์ด ๋ต์
1. ํ๋ - ์น์ฌ์ดํธ ๋ฐฉ๋ฌธ, ๊ณต๊ณ ์กฐํ, ์ปค๋ฆฌํ๋ผ ์กฐํ
2. ํ์ฑํ - ํ์๊ฐ์
3. ํ๋ - ์ฐธ๊ฐ ์ง์์ ์์์ ์ฅ
4. ์ ์ง - ์ฐธ๊ฐ ์ง์์ ์ ์ถ
5. ์ถ์ฒ - ์ด๋, ๊ณต์
1. ์๋น์ค์ BM(Business Model)์ ํ์ ํ๋ค.
- ์ด๋ป๊ฒํ๋ฉด ์์ต์ ๊ทน๋ํํ ์ ์์๊น?
- ์ฐ๋ฆฌ ํ์ฌ, ์ด ์๋น์ค๊ฐ ๋งค์ถ์ ๋ด๋ ๊ตฌ์กฐ๋ ๋ฌด์์ผ๊น?
- ์์ต๊ณผ ์ง์ถ์ ํ๋ฆ์ ์ด๋จ๊น?
2. BM์ ๊ณ ๋ คํด ํต์ฌ ์งํ์ ํผ๋ ์คํ ์ ์ค๊ณํ๊ธฐ
3. ์ดํ๋ฅ ์ด ํฐ ๋จ๊ณ๋ฅผ ์ค์ฌ์ผ๋ก ํผ๋์ ์ฌ์ค๊ณํ๊ธฐ
4. ๊ฐ ํผ๋๋ณ ์ ํ์จ์ SQL๋ก ๊ตฌํด๋ณด๊ธฐ
5. ํผ๋์ ์ต์ ํํ๋ ๋ฐฉ๋ฒ
(1) ์ง์ ์จ์ ๋์ด๊ธฐ : ๊น๋๊ธฐ ์์ ๋ฌผ์ ๋ง์ด ๋ฃ๊ธฐ
- ํด๋น ํ๋ฉด๊น์ง์ ์ง์
๋ฅ (์ฆ ํผ๋ ์ ์
๋)์ ๋์ด๋ ๋ฐฉํฅ
- ๊ด๊ณ ํจ์จ์ ๋์ธ๋ค.
- ๊ด๊ณ ๋น๋ฅผ ์ฆ์กํ๋ค.
(2) ์ ํ์จ ๋์ด๊ธฐ : ํ์ฃผ๋ฅผ ๋ง์๋ผ..!
- ํผ๋ ์์ ๋ฐ๊พธ๊ธฐ
- ํผ๋ ๊ฐ์ ๋๋ฆฌ๊ธฐ : ๊ฐ์๋ฅผ ๋๋ฆฌ์ง๋ง ์ค๋๋ ฅ์ ๋์ธ๋ค
- ํผ๋ ๊ฐ์ ์ค์ด๊ธฐ → ์ดํ์ ๋ฐฉ์ดํ๋ค.
- ํผ๋ ๊ตฌ์กฐ ๋ค์ง๊ธฐ → ์ต์ข CTA ๋ฒํผ์ ๋งจ ์์ผ๋ก ๊ฐ์ ธ์ค๊ธฐ
ํผ๋ ๋ถ์ ๊ณผ์
240529 Today I FINISHED
๐ก ์ ํ์จ
์ ํ์จ = ํน์ ๋จ๊ณ๋ก ์ ํ๋ ์ฌ์ฉ์ ์ / ์ ์ฒด ์ฌ์ฉ์ ์
ํผ๋์์ ๊ฐ๊ฐ์ ๋จ๊ณ๋ฅผ ๋์ด๊ฐ๋ ๊ฒ์ ์ ํ(Conversion)์ด๋ผ๊ณ ๋ถ๋ฅด๊ณ ๊ทธ ๋น์จ์ ์ ํ์จ(Conversion rate)์ด๋ผ๊ณ ํ๋ค.
→ ์น์ฌ์ดํธ๋ฅผ ๋ฐฉ๋ฌธํ ์ฌ๋ ์ค, ์์ ์ ์ ๋๋ ํ์๋ฅผ ํ ๋ฐฉ๋ฌธ์์ ๋น์จ
๊ณผ์ 1. ์ ์ ์ ๊ณ์ ์์ฑ ํ ํ์๊ฐ์ ์๋ฃ ์ ํ์จ (location, device๋ณ)
๐ ๋ชฉํ: ์ฌ์ฉ์๊ฐ ๊ณ์ ์ ์์ฑํ ํ 24์๊ฐ ์ด๋ด์ ํ์๊ฐ์ ์ ์๋ฃํ๋ ์ ํ์จ์ ์ฌ์ฉ์ ์์น์ ๊ธฐ๊ธฐ๋ณ๋ก ๊ณ์ฐ.
์ ์ ์ ๊ณ์ ์์ฑ (create_user) ํ ํ์๊ฐ์ ์๋ฃ (complete_signup) ์ ํ์จ์ ์ฌ์ฉ์ ์์น(location), ๊ธฐ๊ธฐ(device) ๋ณ๋ก ๊ตฌํด์ฃผ์ธ์.
๐ก ๊ฒฐ๊ณผ: location๊ณผ device๋ฅผ ๊ธฐ์ค์ผ๋ก ์ ํ์จ์ ๋ฐฑ๋ถ์จ๋ก ์ฐ์ถ.
- ์์ซ์ ๋์์ง๋ฆฌ๊น์ง ์ถ๋ ฅ
- ๋ชจ๋ ๋จ๊ณ๋ ์ฒ์ ์ ์ ๊ฐ ๊ณ์ ์ ์์ฑํ ํ ํ๋ฃจ ์์ ์๋ฃ๋์์ด์ผ ํจ.
- ๋ฐฑ๋ถ๋ฅ ๋ก ๊ตฌํด์ฃผ์ธ์.
![](https://blog.kakaocdn.net/dn/b4iG0B/btsHHhFqGM4/gJP0BDssJLbKZnOkhefoJ1/img.png)
WITH create_user as
(
SELECT location,
device,
user_id,
occurred_at AS occurred_at_create_user
FROM Basic.yammer_events y1
WHERE event_name = 'create_user'
),
complete_signup as
(
SELECT user_id, occurred_at AS occurred_at_complete_signup
FROM Basic.yammer_events y2
WHERE event_name = 'complete_signup')
SELECT location, device, ROUND(COUNT(CASE WHEN occurred_at_complete_signup IS NOT NULL THEN 1 ELSE NULL END)/COUNT(*)*100.0,2) AS converted_rate_percent
FROM create_user cu LEFT JOIN complete_signup cs
ON cu.user_id = cs.user_id
WHERE occurred_at_complete_signup IS NULL
OR DATEDIFF(occurred_at_complete_signup, occurred_at_create_user) <= 1
GROUP BY location, device;
๊ณผ์ 2. ํ์๊ฐ์ ์ ํ์จ ๋จ๊ณ๋ณ ๋ถ์
๐ ๋ชฉํ: ์ ์ ์ ๊ณ์ ์์ฑ๋ถํฐ ํ์๊ฐ์ ์๋ฃ๊น์ง์ ๊ฐ ๋จ๊ณ๋ณ ์ ํ์จ์ 24์๊ฐ ์ด๋ด์ ์๋ฃ๋ ๊ฒฝ์ฐ๋ก ๊ณ์ฐ.
- ๊ณ์ ์์ฑ(create_user) ํ ์ด๋ฉ์ผ ์ ๋ ฅ(enter_email) ํ๋ฉด ์ง์ ๋ฅ .
- ์ด๋ฉ์ผ ์ ๋ ฅ ํ๋ฉด(enter_email) ์ง์ ํ ๊ฐ์ธ ์ ๋ณด ์ ๋ ฅ(enter_info) ํ๋ฉด ์ง์ ๋ฅ .
- ๊ฐ์ธ ์ ๋ณด ์ ๋ ฅ ํ๋ฉด ์ง์ (enter_info) ํ ํ์๊ฐ์ ์๋ฃ(complete_signup) ์ ํ์จ.
- ๊ณ์ ์์ฑ(create_user) ํ ํ์๊ฐ์ ์๋ฃ(complete_signup) ์ ํ์จ.
๐ก ๊ฒฐ๊ณผ: ๊ฐ ๋จ๊ณ๋ณ ์ ํ์จ์ ๋ฐฑ๋ถ์จ๋ก ์ฐ์ถ.
- ์์ซ์ ๋์์ง๋ฆฌ๊น์ง ์ถ๋ ฅ
- ๋ชจ๋ ๋จ๊ณ๋ ์ฒ์ ์ ์ ๊ฐ ๊ณ์ ์ ์์ฑํ ํ ํ๋ฃจ ์์ ์๋ฃ๋์์ด์ผ ํจ.
- ๋ฐฑ๋ถ๋ฅ ๋ก ๊ตฌํด์ฃผ์ธ์.
![](https://blog.kakaocdn.net/dn/z0oOd/btsHGhl6VIF/K0ouBHFrNP1K4V8ASbnJKK/img.png)
WITH create_user AS
(
SELECT location,
device,
user_id,
MIN(occurred_at) AS occurred_at_create_user
FROM Basic.yammer_events y1
WHERE event_name = 'create_user'
GROUP BY location, device, user_id
),
complete_signup AS
(
SELECT user_id, MIN(occurred_at) AS occurred_at_complete_signup
FROM Basic.yammer_events y2
WHERE event_name = 'complete_signup'
GROUP BY user_id),
enter_email AS
(
SELECT user_id, MIN(occurred_at) AS occurred_at_enter_email
FROM Basic.yammer_events y3
WHERE event_name = 'enter_email'
GROUP BY user_id),
enter_info AS
(
SELECT user_id, MIN(occurred_at) AS occurred_at_enter_info
FROM Basic.yammer_events y4
WHERE event_name = 'enter_info'
GROUP BY user_id)
SELECT ROUND(COUNT(CASE WHEN occurred_at_enter_email IS NOT NULL THEN 1 ELSE NULL END)
/COUNT(*)*100.0,2) AS create_user_to_enter_email,
ROUND(COUNT(CASE WHEN occurred_at_enter_info IS NOT NULL THEN 1 ELSE NULL END)
/ COUNT(CASE WHEN occurred_at_enter_email IS NOT NULL THEN 1 ELSE NULL END)*100.0,2) AS enter_email_to_enter_info,
ROUND(COUNT(CASE WHEN occurred_at_complete_signup IS NOT NULL THEN 1 ELSE NULL END)
/ COUNT(CASE WHEN occurred_at_enter_info IS NOT NULL THEN 1 ELSE NULL END)*100.0,2) AS enter_info_to_complete_signup,
ROUND(COUNT(CASE WHEN occurred_at_complete_signup IS NOT NULL THEN 1 ELSE NULL END)
/COUNT(*)*100.0,2) AS create_user_to_complete_signup
FROM create_user cu
LEFT JOIN enter_email ee ON cu.user_id = ee.user_id
LEFT JOIN enter_info ei ON cu.user_id = ei.user_id
LEFT JOIN complete_signup cs ON cu.user_id = cs.user_id
WHERE occurred_at_enter_email IS NULL
OR occurred_at_enter_info IS NULL
OR occurred_at_complete_signup IS NULL
OR DATEDIFF(occurred_at_complete_signup, occurred_at_create_user) <= 1;
๊ณผ์ 3. ํ์๊ฐ์ ์ ํ์จ ๋ฐ ์ดํ ํ๋ ์ ํ์จ ๋ถ์
๐ ๋ชฉํ: ์ ์ ๊ฐ ๊ณ์ ์ ์์ฑํ ํ 30๋ถ ์ด๋ด์ ๊ฐ ๋จ๊ณ๋ณ ์ ํ์จ์ ๊ณ์ฐ.
- ๊ณ์ ์์ฑ(create_user) ํ ํ์๊ฐ์ ์๋ฃ(complete_signup) ์ ํ์จ.
- ํ์๊ฐ์ ์๋ฃ(complete_signup) ํ ํํ์ด์ง(home_page) ์ง์ ๋ฅ .
- ํํ์ด์ง ์ง์ (home_page) ํ ๋ฉ์์ง ์ข์์(like_message) ํด๋ฆญ๋ฅ .
- ํ์๊ฐ์ ํ(complete_signup) ๋ฉ์์ง ์ข์์(like_message) ํด๋ฆญ ์ ํ์จ.
๐ก ๊ฒฐ๊ณผ: ๊ฐ ๋จ๊ณ๋ณ ์ ํ์จ์ ๋ฐฑ๋ถ์จ๋ก ์ฐ์ถ.
- ์์ซ์ ๋์์ง๋ฆฌ๊น์ง ์ถ๋ ฅ
- ๋ชจ๋ ๋จ๊ณ๋ ์ฒ์ ์ ์ ๊ฐ ๊ณ์ ์ ์์ฑํ ํ 30๋ถ ์์ ์๋ฃ๋์์ด์ผ ํฉ๋๋ค.
- ๋ฐฑ๋ถ๋ฅ ๋ก ๊ตฌํด์ฃผ์ธ์.
![](https://blog.kakaocdn.net/dn/b6hlRB/btsHH9s9Z6f/SfEa4VYH7Q2pGCFcXkkcK0/img.png)
WITH create_user AS
(
SELECT user_id,
MIN(occurred_at) AS occurred_at_create_user
FROM Basic.yammer_events y1
WHERE event_name = 'create_user'
GROUP BY user_id
),
complete_signup AS
(
SELECT user_id, MIN(occurred_at) AS occurred_at_complete_signup
FROM Basic.yammer_events y2
WHERE event_name = 'complete_signup'
GROUP BY user_id),
home_page AS
(
SELECT user_id, MIN(occurred_at) AS occurred_at_home_page
FROM Basic.yammer_events y3
WHERE event_name = 'home_page'
GROUP BY user_id),
like_message AS
(
SELECT user_id, MIN(occurred_at) AS occurred_at_like_message
FROM Basic.yammer_events y4
WHERE event_name = 'like_message'
GROUP BY user_id
)
SELECT ROUND(COUNT(CASE WHEN occurred_at_complete_signup IS NOT NULL THEN 1 ELSE NULL END)
/COUNT(*)*100.0,2) AS create_user_to_complete_signup,
ROUND(COUNT(CASE WHEN occurred_at_home_page IS NOT NULL THEN 1 ELSE NULL END)
/COUNT(CASE WHEN occurred_at_complete_signup IS NOT NULL THEN 1 ELSE NULL END)*100.0,2) AS complete_signup_to_homepage,
ROUND(COUNT(CASE WHEN occurred_at_like_message IS NOT NULL THEN 1 ELSE NULL END)
/COUNT(CASE WHEN occurred_at_home_page IS NOT NULL THEN 1 ELSE NULL END)*100.0, 2) AS homepage_to_like_message,
ROUND(COUNT(CASE WHEN occurred_at_like_message IS NOT NULL THEN 1 ELSE NULL END)
/COUNT(CASE WHEN occurred_at_complete_signup IS NOT NULL THEN 1 ELSE NULL END)*100.0,2) AS complete_signup_to_like_message
FROM create_user cu
LEFT JOIN complete_signup cs ON cu.user_id = cs.user_id
LEFT JOIN home_page hp ON cu.user_id = hp.user_id
LEFT JOIN like_message lm ON cu.user_id = lm.user_id
WHERE occurred_at_complete_signup IS NULL
OR occurred_at_home_page IS NULL
OR occurred_at_like_message IS NULL
OR TIMESTAMPDIFF(HOUR, occurred_at_create_user, occurred_at_like_message) <= 0.5;
[์ฌํ] ๊ณผ์ 4. ์ผ๋ณ๋ก ํ์ ๊ฐ์ ํ๋ก์ธ์ค์ ๊ฐ ๋จ๊ณ ์๋ฃ ํ๊ท ์๊ฐ
๐ ๋ชฉํ: ์ ์ ๊ฐ ๊ณ์ ์ ์์ฑํ ํ 30๋ถ ์ด๋ด์ ๊ฐ ๋จ๊ณ๋ณ ์ ํ์จ์ ๊ณ์ฐ.
- ๊ณ์ ์์ฑ (create_user) ํ ์ด๋ฉ์ผ ์ ๋ ฅ ํ๋ฉด ์ง์ (enter_email)๊น์ง.
- ์ด๋ฉ์ผ ์ ๋ ฅ ํ๋ฉด ์ง์ (enter_email) ํ ๊ฐ์ธ ์ ๋ณด ์ ๋ ฅ ํ๋ฉด ์ง์ (enter_info)๊น์ง.
- ๊ฐ์ธ ์ ๋ณด ์ ๋ ฅ ํ๋ฉด ์ง์ (enter_info) ํ ํ์๊ฐ์ ์๋ฃ(complete_signup)๊น์ง.
- ๊ณ์ ์์ฑ (create_user) ํ ํ์๊ฐ์ ์๋ฃ(complete_signup)๊น์ง.
๐ก ๊ฒฐ๊ณผ: ๊ฐ ๋จ๊ณ๋ณ ์ ํ์จ์ ๋ฐฑ๋ถ์จ๋ก ์ฐ์ถ.
- ์์ซ์ ๋์์ง๋ฆฌ๊น์ง ์ถ๋ ฅ
- ๋ชจ๋ ๋จ๊ณ๋ ์ฒ์ ์ ์ ๊ฐ ๊ณ์ ์ ์์ฑํ ํ 30๋ถ ์์ ์๋ฃ๋์์ด์ผ ํฉ๋๋ค.
- ๋ฐฑ๋ถ๋ฅ ๋ก ๊ตฌํด์ฃผ์ธ์.
![](https://blog.kakaocdn.net/dn/crfgqE/btsHGAMBcrG/nyJnuGrmTVhKNvScaOdDV0/img.png)
WITH create_user AS
(
SELECT location,
device,
user_id,
MIN(occurred_at) AS occurred_at_create_user
FROM Basic.yammer_events y1
WHERE event_name = 'create_user'
GROUP BY location, device, user_id
),
complete_signup AS
(
SELECT user_id, MIN(occurred_at) AS occurred_at_complete_signup
FROM Basic.yammer_events y2
WHERE event_name = 'complete_signup'
GROUP BY user_id),
enter_email AS
(
SELECT user_id, MIN(occurred_at) AS occurred_at_enter_email
FROM Basic.yammer_events y3
WHERE event_name = 'enter_email'
GROUP BY user_id),
enter_info AS
(
SELECT user_id, MIN(occurred_at) AS occurred_at_enter_info
FROM Basic.yammer_events y4
WHERE event_name = 'enter_info'
GROUP BY user_id)
SELECT ROUND(AVG(TIMESTAMPDIFF(SECOND, occurred_at_create_user,occurred_at_enter_email)),2) AS create_user_to_enter_email_AVG,
ROUND(AVG(TIMESTAMPDIFF(SECOND, occurred_at_enter_email,occurred_at_enter_info)),2) AS enter_email_to_enter_info_AVG,
ROUND(AVG(TIMESTAMPDIFF(SECOND, occurred_at_enter_info,occurred_at_complete_signup)),2)enter_info_to_complete_signup_AVG,
ROUND(AVG(TIMESTAMPDIFF(SECOND, occurred_at_create_user,occurred_at_complete_signup)),2)create_user_to_complete_signup_AVG
FROM create_user cu
LEFT JOIN enter_email ee ON cu.user_id = ee.user_id
LEFT JOIN enter_info ei ON cu.user_id = ei.user_id
LEFT JOIN complete_signup cs ON cu.user_id = cs.user_id
WHERE occurred_at_enter_email IS NULL
OR occurred_at_enter_info IS NULL
OR occurred_at_complete_signup IS NULL
OR DATEDIFF(occurred_at_complete_signup, occurred_at_create_user) <= 1;
Funnels with SQL: The good, the bad and the ugly way
Writing funnel queries in SQL is a complex task. There are multiple approaches one can take. However, each of them has its benefits and drawbacks. This post explores three approaches you may copy for your next data analytics project.
www.mitzu.io
'๐ Today I Learn > ๐ My SQL' ์นดํ ๊ณ ๋ฆฌ์ ๋ค๋ฅธ ๊ธ
SQL Challenge ์ธ์ #5 ํผ๋ฒํ ์ด๋ธ (0) | 2024.06.05 |
---|---|
SQL Challenge ์ธ์ #4 ๋ฆฌํ ์ , ์ฝํธํธ ๋ถ์ (0) | 2024.05.30 |
SQL Challenge ์ธ์ #2 ์ด๋ํ๊ท ๊ณผ ๋น์จ (0) | 2024.05.27 |
SQL Challenge ์ธ์ #1 ์๋์ฐ ํจ์, WITH์ (0) | 2024.05.27 |
Union๊ณผ Order by (0) | 2024.05.04 |