2. 에어비앤비 (Airbnb) 이용자 구매전환율 분석 in SQL

0. Intro

[AirBnB CF]

1. 질문  구매전환율은 회원가입을 한 이용자 중 실제 예약으로 이어진 이용자의 비율을 의미합니다. 에어비앤비 ( AirBnB) 이용자의 첫여행국가별, 성별 구매전환율을 구하고 1) 실제 예약으로 이어진 이용자가 얼마나 되는지, 2) 어떤 성별이 더 높은 예약율을 보이는지, 3) 어떤 국가가 가장 예약될 가능성이 높은지를 알려주세요. 2. 참고data train_users_2.csv.zip (출처 : www.kaggle.com) 3. 테이블생성스크립트 create table air_user  (id  varchar2(50),   date_account_created  date,   timestamp_first_active  number,   date_first_booking   varchar2(50),   gender  varchar2(50),   age    number(10),   signup_method  varchar2(50),   signup_flow  number(10),   language  varchar(10),   affiliate_channel  varchar2(50),   affiliate_provider   varchar2(50),   first_affiliate_tracked  varchar2(50),   signup_app  varchar2(50),   first_device_type  varchar2(50),   first_browser  varchar2(50),   country_destination varchar2(10));  4. 분석쿼리 select 여행목적지, 남자, 여자, 알수없음, case when 여행목적지 is not null then 남자+여자+알수없음 end as TOTAL    from     (   select *     from      ( select a.여행목적지, a.gender, round( ( a.cnt_book / b.country_total )*100,2 ) as booking_ratio  -- booking_ratio : 첫 예약자의 여행목적지별 성별 전환율   from    (     select country_destination as "여행목적지", gender, count(*) as cnt_book   -- cnt_book : Airbnb 첫 예약자의 여행목적지별, 성별 예약자 수 from air_user where date_first_booking is not null   group by country_destination, gender   ) a   inner join   (     select country_destination as "여행목적지", count(country_destination) as country_total  -- country_total : Airbnb 첫 예약자의 여행목적지별 예약자 수 from air_user group by country_destination   ) b   on a.여행목적지 = b.여행목적지     )     pivot( sum(booking_ratio) for gender in ('MALE' as "남자",'FEMALE' as "여자",'-unknown-' as "알수없음")) union all select 'TOTAL' as 여행목적지,  round(sum(regexp_count(upper(gender), '^MALE')) / count(*)*100, 2) as 남자,  round(sum(regexp_count(upper(gender), 'FEMALE')) / count(*)*100, 2) as 여자,  round(sum(regexp_count(upper(gender), '-UNKNOWN-')) / count(*)*100, 2) as 알수없음     from air_user     where date_first_booking is not null ); 5. 결과화면 [그림 1. 국가별 성별 전환율] 6. 결론 1.1 에어비앤비 (AirBnB)에 가입하는 회원의 10명 중 최소 3명은 실제 예약으로 이어짐  남성의 경우 31.18%, 여성의 경우 35.98%가 그리고 성별을 알 수 없는 경우 32.64%의 이용자가 회원가입 이후 첫 예약까지 이어졌다. 기존의 전문 숙박업인 호텔, 모텔이 경쟁자로 있다는 것을 생각해 보았을 때 비전문적으로 운영되는 AirBnB의 숙박 서비스의 예약률은 상당히 높은 수치인 것으로 생각된다. 1.2 (성별 분석) 여성의 구매전환율이 남성의 구매전환율보다 약 4%가량 높게 나타남  '여행은 살아보는거야'. AirBnB의 마케팅 메시지인 '현지인 처럼 살아보는 경험'이 이용자들에게 제대로 전달이 되고 있다고 가정했을 때, 이러한 메시지는 남성보다 여성에게 더 잘 소구된다고 판단할 수 있다. 따라서, 차후 마케팅을 진행시에 여성을 타겟으로한 마케팅 진행시에 좀 더 높은 ROI를 달성할 수 있을 것으로 기대된다. 1.3 (국가별 분석) 남성의 경우 독일(DE)에서, 여성의 경우 프랑스(FR)에서 첫 AirBnB를 경험할 확률이 가장 높음  남성들의 경우 가장 매력을 느낀 여행 국가는 1. DE(독일) 2. NL(네덜란드) 3. AU (오스트레일리아) 순으로 나타났고, 여성들의 경우 가장 매력을 느낀 여행 국가는 1. FR(프랑스) 2. IT(이탈리아) 3. GB (영국) 순으로 나타났다. 이유를 wild guessing 했을 때, 독일의 경우 '맥주'문화가 남성들에게 어필하지 않았나 싶고 네덜란드의 경우 법적 규제가 약해 '마약'과 '성문화'가 용이한 점이 남성들에게 어필했을 것이라고 추측된다. 여성들의 경우 선호하는 국가가 패션, 뷰티로 대표되는 럭셔리 산업이 발전해있고 도시의 이미지가 세련되었다는 특징이 있다. 따라서, 쇼핑의 용이성과 도시가 주는 세련됨 자체가 여성들에게 어필했을 것이라고 추측해볼 수 있다.  차후 마케팅 진행시에, '독일 여행을 계획 중인 남성'과 '프랑스 여행을 계획 중인 여성'을 타겟으로 가장 먼저 진행했을 때 상대적으로 높은 ROI를 기대해볼 수 있을 것으로 기대된다. 7. 연습문제 airbnb_practice.csv 데이터를 사용하여 회원가입을 한 시점을 기준으로  첫 예약이 이루어지기 까지의 시간을 다음과 같은 구간으로 나누어서 유져 수를 구해주세요.   -1일 미만  ( 0 )  -1일 이상~2일 미만 ( 1 )  -2일 이상~3일 미만 ( 2 )  -3일 이상~4일 미만 ( 3 )  -4일 이상~5일 미만 ( 4 )  -5일 이상~6일 미만 ( 5 )  -6일 이상~7일 미만 ( 6 )  -7일 이상 ( 7 )  [data set]     air_practice.csv  [테이블 생성]  create table pra  (id  varchar2(100),   day_diff  number(20));   ** id : 회원고유 아이디   ** day_diff : 회원가입으로부터 구매까지 걸린 날짜수 ** Git Hub : https://github.com/JoeUnsung/airbnb_sql/blob/master/airbnb_convsersion_analysis.sql