[기초 프로젝트] 데이팅 앱 지하철 광고 전략 (1)

1. 주제 선정

2030 타깃으로 한 데이팅 앱 지하철 PSD 광고 진행 시 최적의 역사 선정
데이팅 앱 타깃층 특성에 기반해 광고 효과를 효율적으로 낼 수 있는 역사 분석

상황 설정
B07팀은 데이팅 앱을 개발한 스타트업 기업의 마케팅 팀
해당 앱은 2030 청년층을 타깃으로 제작
'위피' 성공사례를 기반으로 여성과 남성 유저의 성비를 맞추는 것이 목표

2. 가설 설정

목표 : 2030 여성에게 최대 광고 노출

승하차 수가 많을수록 광고 효과가 높을 것이다.
청년 인구 비중이 높은 지역일수록 광고 효과가 높을 것이다.
청년 중 여성 인구 비중이 높은 지역이 목표 달성에 효과적일 것이다.

유동인구가 많은 홍대역, 강남역의 승하차 수가 많을 것이다.
신림역, 서울대입구역의 청년 인구 비중이 높을 것이다.
여대 인근 역(이대역, 숙명여대)의 여성 청년 인구 비중이 높을 것이다.

필자는 해당 프로젝트에서 지하철 승하차 수 분석을 담당하여 다음 과정을 진행했다.

3. 데이터 수집

서울 열린데이터 광장, "서울시 지하철호선별 역별 승하차 인원 정보",
https://data.seoul.go.kr/dataList/OA-12914/S/1/datasetView.do
서울시 교통정보 시스템 TOPIS, "대중교통 O/D 현황",
https://topis.seoul.go.kr/refRoom/openRefRoom_3_4.do

4. 데이터 가공 (SQL 활용)

1) 2023 역별 하루 평균 승객수 순위

월마다 테이블이 나눠져 있어서 12개의 데이터를 불러온 뒤 UNION을 사용해 1년 치 데이터를 하나의 테이블로 만들었다.

with total_subway as (
select 사용일자, 노선명, 역명, 승차총승객수, 하차총승객수
	from card_subway_jan
	union
	select 사용일자, 노선명, 역명, 승차총승객수, 하차총승객수
	from card_subway_feb
	union
	select 사용일자, 노선명, 역명, 승차총승객수, 하차총승객수
	from card_subway_mar
	union
	select 사용일자, 노선명, 역명, 승차총승객수, 하차총승객수
	from card_subway_apr
	union
	select 사용일자, 노선명, 역명, 승차총승객수, 하차총승객수
	from card_subway_may
	union
	select 사용일자, 노선명, 역명, 승차총승객수, 하차총승객수
	from card_subway_jun
	union
	select 사용일자, 노선명, 역명, 승차총승객수, 하차총승객수
	from card_subway_jul
	union
	select 사용일자, 노선명, 역명, 승차총승객수, 하차총승객수
	from card_subway_aug
	union
	select 사용일자, 노선명, 역명, 승차총승객수, 하차총승객수
	from card_subway_sep
	union
	select 사용일자, 노선명, 역명, 승차총승객수, 하차총승객수
	from card_subway_oct
	union
	select 사용일자, 노선명, 역명, 승차총승객수, 하차총승객수
	from card_subway_nov
	union
	select 사용일자, 노선명, 역명, 승차총승객수, 하차총승객수
	from card_subway_dec)
select DATE_FORMAT(사용일자, '%Y') "사용일자", 노선명, 역명, ROUND(avg(승차총승객수)+avg(하차총승객수)) "평균 승객수 총합", ROUND(avg(승차총승객수)) "평균 승차승객수", ROUND(avg(하차총승객수)) "평균 하차승객수",
	   RANK() OVER(order by ROUND(avg(승차총승객수)) DESC) "승차승객수 순위",
	   RANK() OVER(order by ROUND(avg(승차총승객수)+avg(하차총승객수)) DESC) "총승객수 순위"
from total_subway
where 승차총승객수 is not NULL
group by 1, 2, 3
having ROUND(avg(승차총승객수)+avg(하차총승객수)) is not NULL;

2) 2023 역별 월별 하루 평균 총 승객수

select a.노선명, a.역명, 1월평균총승객수, 2월평균총승객수, 3월평균총승객수, 4월평균총승객수, 5월평균총승객수, 6월평균총승객수, 7월평균총승객수, 8월평균총승객수, 9월평균총승객수, 10월평균총승객수, 11월평균총승객수, 12월평균총승객수
from
(select DATE_FORMAT(사용일자, '%Y-%m') "사용일자", 노선명, 역명, ROUND(avg(승차총승객수)+avg(하차총승객수)) "1월평균총승객수"
from card_subway_jan
group by 1, 2, 3
having ROUND(avg(승차총승객수)+avg(하차총승객수)) is not NULL) a left join
(select DATE_FORMAT(사용일자, '%Y-%m') "사용일자", 노선명, 역명, ROUND(avg(승차총승객수)+avg(하차총승객수)) "2월평균총승객수"
from card_subway_feb
group by 1, 2, 3
having ROUND(avg(승차총승객수)+avg(하차총승객수)) is not null) b on a.노선명=b.노선명 and a.역명=b.역명 left join
(select DATE_FORMAT(사용일자, '%Y-%m') "사용일자", 노선명, 역명, ROUND(avg(승차총승객수)+avg(하차총승객수)) "3월평균총승객수"
from card_subway_mar
group by 1, 2, 3
having ROUND(avg(승차총승객수)+avg(하차총승객수)) is not null) c on b.노선명=c.노선명 and b.역명=c.역명 left join
(select DATE_FORMAT(사용일자, '%Y-%m') "사용일자", 노선명, 역명, ROUND(avg(승차총승객수)+avg(하차총승객수)) "4월평균총승객수"
from card_subway_apr
group by 1, 2, 3
having ROUND(avg(승차총승객수)+avg(하차총승객수)) is not null) d on c.노선명=d.노선명 and c.역명=d.역명 left join
(select DATE_FORMAT(사용일자, '%Y-%m') "사용일자", 노선명, 역명, ROUND(avg(승차총승객수)+avg(하차총승객수)) "5월평균총승객수"
from card_subway_may
group by 1, 2, 3
having ROUND(avg(승차총승객수)+avg(하차총승객수)) is not null) e on d.노선명=e.노선명 and d.역명=e.역명 left join
(select DATE_FORMAT(사용일자, '%Y-%m') "사용일자", 노선명, 역명, ROUND(avg(승차총승객수)+avg(하차총승객수)) "6월평균총승객수"
from card_subway_jun
group by 1, 2, 3
having ROUND(avg(승차총승객수)+avg(하차총승객수)) is not null) f on e.노선명=f.노선명 and f.역명=e.역명 left join 
(select DATE_FORMAT(사용일자, '%Y-%m') "사용일자", 노선명, 역명, ROUND(avg(승차총승객수)+avg(하차총승객수)) "7월평균총승객수"
from card_subway_jul
group by 1, 2, 3
having ROUND(avg(승차총승객수)+avg(하차총승객수)) is not null) g on f.노선명=g.노선명 and f.역명=g.역명 left join
(select DATE_FORMAT(사용일자, '%Y-%m') "사용일자", 노선명, 역명, ROUND(avg(승차총승객수)+avg(하차총승객수)) "8월평균총승객수"
from card_subway_aug
group by 1, 2, 3
having ROUND(avg(승차총승객수)+avg(하차총승객수)) is not null) h on h.노선명=g.노선명 and h.역명=g.역명 left join
(select DATE_FORMAT(사용일자, '%Y-%m') "사용일자", 노선명, 역명, ROUND(avg(승차총승객수)+avg(하차총승객수)) "9월평균총승객수"
from card_subway_sep
group by 1, 2, 3
having ROUND(avg(승차총승객수)+avg(하차총승객수)) is not null) i on h.노선명=i.노선명 and h.역명=i.역명 left join 
(select DATE_FORMAT(사용일자, '%Y-%m') "사용일자", 노선명, 역명, ROUND(avg(승차총승객수)+avg(하차총승객수)) "10월평균총승객수"
from card_subway_oct
group by 1, 2, 3
having ROUND(avg(승차총승객수)+avg(하차총승객수)) is not null) j on j.노선명=i.노선명 and j.역명=i.역명 left join 
(select DATE_FORMAT(사용일자, '%Y-%m') "사용일자", 노선명, 역명, ROUND(avg(승차총승객수)+avg(하차총승객수)) "11월평균총승객수"
from card_subway_nov
group by 1, 2, 3
having ROUND(avg(승차총승객수)+avg(하차총승객수)) is not null) k on j.노선명=k.노선명 and j.역명=k.역명 left join 
(select DATE_FORMAT(사용일자, '%Y-%m') "사용일자", 노선명, 역명, ROUND(avg(승차총승객수)+avg(하차총승객수)) "12월평균총승객수"
from card_subway_dec
group by 1, 2, 3
having ROUND(avg(승차총승객수)+avg(하차총승객수)) is not null) l on l.노선명=k.노선명 and l.역명=k.역명
order by 1, 2

3) 2023 역별 월별 하루 총 승객수 합계

select a.노선명, a.역명, 1월총승객수합계, 2월총승객수합계, 3월총승객수합계, 4월총승객수합계, 5월총승객수합계, 6월총승객수합계, 7월총승객수합계, 8월총승객수합계, 9월총승객수합계, 10월총승객수합계, 11월총승객수합계, 12월총승객수합계
from
(select DATE_FORMAT(사용일자, '%Y-%m') "사용일자", 노선명, 역명, SUM(승차총승객수)+SUM(하차총승객수) "1월총승객수합계"
from card_subway_jan
group by 1, 2, 3
having SUM(승차총승객수)+SUM(하차총승객수) is not NULL) a left join
(select DATE_FORMAT(사용일자, '%Y-%m') "사용일자", 노선명, 역명, SUM(승차총승객수)+SUM(하차총승객수) "2월총승객수합계"
from card_subway_feb
group by 1, 2, 3
having SUM(승차총승객수)+SUM(하차총승객수) is not null) b on a.노선명=b.노선명 and a.역명=b.역명 left join
(select DATE_FORMAT(사용일자, '%Y-%m') "사용일자", 노선명, 역명, SUM(승차총승객수)+SUM(하차총승객수) "3월총승객수합계"
from card_subway_mar
group by 1, 2, 3
having SUM(승차총승객수)+SUM(하차총승객수) is not null) c on b.노선명=c.노선명 and b.역명=c.역명 left join
(select DATE_FORMAT(사용일자, '%Y-%m') "사용일자", 노선명, 역명, SUM(승차총승객수)+SUM(하차총승객수) "4월총승객수합계"
from card_subway_apr
group by 1, 2, 3
having SUM(승차총승객수)+SUM(하차총승객수) is not null) d on c.노선명=d.노선명 and c.역명=d.역명 left join
(select DATE_FORMAT(사용일자, '%Y-%m') "사용일자", 노선명, 역명, SUM(승차총승객수)+SUM(하차총승객수) "5월총승객수합계"
from card_subway_may
group by 1, 2, 3
having SUM(승차총승객수)+SUM(하차총승객수) is not null) e on d.노선명=e.노선명 and d.역명=e.역명 left join
(select DATE_FORMAT(사용일자, '%Y-%m') "사용일자", 노선명, 역명, SUM(승차총승객수)+SUM(하차총승객수) "6월총승객수합계"
from card_subway_jun
group by 1, 2, 3
having SUM(승차총승객수)+SUM(하차총승객수) is not null) f on e.노선명=f.노선명 and f.역명=e.역명 left join 
(select DATE_FORMAT(사용일자, '%Y-%m') "사용일자", 노선명, 역명, SUM(승차총승객수)+SUM(하차총승객수) "7월총승객수합계"
from card_subway_jul
group by 1, 2, 3
having SUM(승차총승객수)+SUM(하차총승객수) is not null) g on f.노선명=g.노선명 and f.역명=g.역명 left join
(select DATE_FORMAT(사용일자, '%Y-%m') "사용일자", 노선명, 역명, SUM(승차총승객수)+SUM(하차총승객수) "8월총승객수합계"
from card_subway_aug
group by 1, 2, 3
having SUM(승차총승객수)+SUM(하차총승객수) is not null) h on h.노선명=g.노선명 and h.역명=g.역명 left join
(select DATE_FORMAT(사용일자, '%Y-%m') "사용일자", 노선명, 역명, SUM(승차총승객수)+SUM(하차총승객수) "9월총승객수합계"
from card_subway_sep
group by 1, 2, 3
having SUM(승차총승객수)+SUM(하차총승객수) is not null) i on h.노선명=i.노선명 and h.역명=i.역명 left join 
(select DATE_FORMAT(사용일자, '%Y-%m') "사용일자", 노선명, 역명, SUM(승차총승객수)+SUM(하차총승객수) "10월총승객수합계"
from card_subway_oct
group by 1, 2, 3
having SUM(승차총승객수)+SUM(하차총승객수) is not null) j on j.노선명=i.노선명 and j.역명=i.역명 left join 
(select DATE_FORMAT(사용일자, '%Y-%m') "사용일자", 노선명, 역명, SUM(승차총승객수)+SUM(하차총승객수) "11월총승객수합계"
from card_subway_nov
group by 1, 2, 3
having SUM(승차총승객수)+SUM(하차총승객수) is not null) k on j.노선명=k.노선명 and j.역명=k.역명 left join 
(select DATE_FORMAT(사용일자, '%Y-%m') "사용일자", 노선명, 역명, SUM(승차총승객수)+SUM(하차총승객수) "12월총승객수합계"
from card_subway_dec
group by 1, 2, 3
having SUM(승차총승객수)+SUM(하차총승객수) is not null) l on l.노선명=k.노선명 and l.역명=k.역명
order by 1, 2

4) 2023 요금별(연령대별) 승객 비율

➡️연령대 기준은 지하철 요금 종류로 파악

어린이 : 만 6세 ~ 만 12세
청소년 : 만 13세 ~ 만 18세
경로 : 만 65세 이상

➡️ 로우데이터에는 대학생 구분이 있었으나 데이터 값이 모두 0이라서 제외함

➡️ 원본 데이터 값이 한 달마다 하루 값밖에 없어서 사용할 수 없는 데이터지만 분석은 시도해 봄

with total_subway_users as (
	select DATE_FORMAT(기준일자, '%Y-%m-%d') "기준일자", 승차_호선, 승차_역, sum(총_승객수) "총승객수", SUM(일반_승객수) "일반승객수", SUM(어린이_승객수) "어린이승객수", SUM(청소년_승객수) "청소년승객수", SUM(경로_승객수) "경로승객수", SUM(대학생_승객수+장애인_승객수+국가유공자_승객수+직원_승객수+외국인경로_승객수) "기타승객수"
	from 지하철_역별od_jan
	group by 1, 2, 3
	union
	select DATE_FORMAT(기준일자, '%Y-%m-%d') "기준일자", 승차_호선, 승차_역, sum(총_승객수) "총승객수", SUM(일반_승객수) "일반승객수", SUM(어린이_승객수) "어린이승객수", SUM(청소년_승객수) "청소년승객수", SUM(경로_승객수) "경로승객수", SUM(대학생_승객수+장애인_승객수+국가유공자_승객수+직원_승객수+외국인경로_승객수) "기타승객수"
	from 지하철_역별od_feb
	group by 1, 2, 3
	union
	select DATE_FORMAT(기준일자, '%Y-%m-%d') "기준일자", 승차_호선, 승차_역, sum(총_승객수) "총승객수", SUM(일반_승객수) "일반승객수", SUM(어린이_승객수) "어린이승객수", SUM(청소년_승객수) "청소년승객수", SUM(경로_승객수) "경로승객수", SUM(대학생_승객수+장애인_승객수+국가유공자_승객수+직원_승객수+외국인경로_승객수) "기타승객수"
	from 지하철_역별od_mar
	group by 1, 2, 3
	union
	select DATE_FORMAT(기준일자, '%Y-%m-%d') "기준일자", 승차_호선, 승차_역, sum(총_승객수) "총승객수", SUM(일반_승객수) "일반승객수", SUM(어린이_승객수) "어린이승객수", SUM(청소년_승객수) "청소년승객수", SUM(경로_승객수) "경로승객수", SUM(대학생_승객수+장애인_승객수+국가유공자_승객수+직원_승객수+외국인경로_승객수) "기타승객수"
	from 지하철_역별od_apr
	group by 1, 2, 3
	union
	select DATE_FORMAT(기준일자, '%Y-%m-%d') "기준일자", 승차_호선, 승차_역, sum(총_승객수) "총승객수", SUM(일반_승객수) "일반승객수", SUM(어린이_승객수) "어린이승객수", SUM(청소년_승객수) "청소년승객수", SUM(경로_승객수) "경로승객수", SUM(대학생_승객수+장애인_승객수+국가유공자_승객수+직원_승객수+외국인경로_승객수) "기타승객수"
	from 지하철_역별od_may
	group by 1, 2, 3
	union
	select DATE_FORMAT(기준일자, '%Y-%m-%d') "기준일자", 승차_호선, 승차_역, sum(총_승객수) "총승객수", SUM(일반_승객수) "일반승객수", SUM(어린이_승객수) "어린이승객수", SUM(청소년_승객수) "청소년승객수", SUM(경로_승객수) "경로승객수", SUM(대학생_승객수+장애인_승객수+국가유공자_승객수+직원_승객수+외국인경로_승객수) "기타승객수"
	from 지하철_역별od_jun
	group by 1, 2, 3
	union
	select DATE_FORMAT(기준일자, '%Y-%m-%d') "기준일자", 승차_호선, 승차_역, sum(총_승객수) "총승객수", SUM(일반_승객수) "일반승객수", SUM(어린이_승객수) "어린이승객수", SUM(청소년_승객수) "청소년승객수", SUM(경로_승객수) "경로승객수", SUM(대학생_승객수+장애인_승객수+국가유공자_승객수+직원_승객수+외국인경로_승객수) "기타승객수"
	from 지하철_역별od_jul
	group by 1, 2, 3
	union
	select DATE_FORMAT(기준일자, '%Y-%m-%d') "기준일자", 승차_호선, 승차_역, sum(총_승객수) "총승객수", SUM(일반_승객수) "일반승객수", SUM(어린이_승객수) "어린이승객수", SUM(청소년_승객수) "청소년승객수", SUM(경로_승객수) "경로승객수", SUM(대학생_승객수+장애인_승객수+국가유공자_승객수+직원_승객수+외국인경로_승객수) "기타승객수"
	from 지하철_역별od_aug
	group by 1, 2, 3
	union
	select DATE_FORMAT(기준일자, '%Y-%m-%d') "기준일자", 승차_호선, 승차_역, sum(총_승객수) "총승객수", SUM(일반_승객수) "일반승객수", SUM(어린이_승객수) "어린이승객수", SUM(청소년_승객수) "청소년승객수", SUM(경로_승객수) "경로승객수", SUM(대학생_승객수+장애인_승객수+국가유공자_승객수+직원_승객수+외국인경로_승객수) "기타승객수"
	from 지하철_역별od_sep
	group by 1, 2, 3
	union
	select DATE_FORMAT(기준일자, '%Y-%m-%d') "기준일자", 승차_호선, 승차_역, sum(총_승객수) "총승객수", SUM(일반_승객수) "일반승객수", SUM(어린이_승객수) "어린이승객수", SUM(청소년_승객수) "청소년승객수", SUM(경로_승객수) "경로승객수", SUM(대학생_승객수+장애인_승객수+국가유공자_승객수+직원_승객수+외국인경로_승객수) "기타승객수"
	from 지하철_역별od_oct
	group by 1, 2, 3
	union
	select DATE_FORMAT(기준일자, '%Y-%m-%d') "기준일자", 승차_호선, 승차_역, sum(총_승객수) "총승객수", SUM(일반_승객수) "일반승객수", SUM(어린이_승객수) "어린이승객수", SUM(청소년_승객수) "청소년승객수", SUM(경로_승객수) "경로승객수", SUM(대학생_승객수+장애인_승객수+국가유공자_승객수+직원_승객수+외국인경로_승객수) "기타승객수"
	from 지하철_역별od_nov
	group by 1, 2, 3
	union
	select DATE_FORMAT(기준일자, '%Y-%m-%d') "기준일자", 승차_호선, 승차_역, sum(총_승객수) "총승객수", SUM(일반_승객수) "일반승객수", SUM(어린이_승객수) "어린이승객수", SUM(청소년_승객수) "청소년승객수", SUM(경로_승객수) "경로승객수", SUM(대학생_승객수+장애인_승객수+국가유공자_승객수+직원_승객수+외국인경로_승객수) "기타승객수"
	from 지하철_역별od_dec
	group by 1, 2, 3)
select DATE_FORMAT(기준일자, '%Y') "기준일자", 승차_호선, 승차_역, 
	   ROUND(AVG(총승객수)) "총승객수 평균", 
	   ROUND((AVG(일반승객수)/AVG(총승객수))*100,1) "일반승객수 비율", 
	   ROUND((AVG(어린이승객수)/AVG(총승객수))*100,1) "어린이승객수 비율", 
	   ROUND((AVG(청소년승객수)/AVG(총승객수))*100,1) "청소년승객수 비율", 
	   ROUND((AVG(경로승객수)/AVG(총승객수))*100,1) "경로승객수 비율", 
	   ROUND((AVG(기타승객수)/AVG(총승객수))*100,1) "기타승객수 비율"
from total_subway_users
group by 1, 2, 3
order by 4 DESC;

>> 지하철 승하차 수 데이터만으론 연령대와 성별에 대한 정보를 얻기 어려우므로 다른 팀원이 분석한 동별 청년(여성) 비중 데이터와 함께 각 데이터를 점수화하여 통합 점수를 바탕으로 최종 역사 후보지를 선정했다.

+ 프로젝트가 끝난 시점에서 느낀 부족함/아쉬움

승하차수 하루 평균값 외에 한 달 평균값 계산도 있었으면 좋았을 것 같다.
광고 단가를 평균값으로 나눠서 계산해 보면 새로운 결과가 도출되었을 것 같다

'스파르타 코딩클럽 > [프로젝트] 내일배움캠프 데이터분석 1기' 카테고리의 다른 글

[기초 프로젝트] 데이팅 앱 지하철 광고 전략 (2) - 데이터 시각화 및 결론 (0)	2024.01.19
[미니 프로젝트] Netflix 데이터 분석 (2) - EDA 및 결론 (1)	2023.12.21
[미니 프로젝트] Netflix 데이터 분석 (1) - 데이터 확인 (0)	2023.12.21

길 잃은 취준생이 뭐라도 해보는 블로그

[기초 프로젝트] 데이팅 앱 지하철 광고 전략 (1) - 데이터 수집 및 가공

'스파르타 코딩클럽 > [프로젝트] 내일배움캠프 데이터분석 1기' 카테고리의 다른 글

티스토리툴바

[기초 프로젝트] 데이팅 앱 지하철 광고 전략 (1) - 데이터 수집 및 가공

'스파르타 코딩클럽 > [프로젝트] 내일배움캠프 데이터분석 1기' 카테고리의 다른 글

'스파르타 코딩클럽/[프로젝트] 내일배움캠프 데이터분석 1기' Related Articles

티스토리툴바