광고 캠페인 데이터 프로젝트 리뷰② - 파생변수 및 기술통계량 계산하기

지난 포스팅에 이어 광고 캠페인 데이터 프로젝트 리뷰 포스팅입니다. 지난 번에는 아이디 간 관계를 파악하는 과정에 대해 다루었습니다.

2024.02.23 - [프로젝트 리뷰/개인 프로젝트] - 광고 캠페인 데이터 프로젝트 리뷰① - 아이디 간 관계 파악하기

 

분석 내용 정하기

이후 도메인 지식을 갖춘 친구의 도움을 받아 분석 내용을 정하고, 각 분석에 필요한 지표(파생변수)를 정의하였습니다. 주요 분석 내용은 아래와 같습니다.

• 같은 대상을 타겟으로 삼는 광고 중 타게팅 옵션이 잘못 설정되었거나 매력도가 낮은 광고의 비율은 어느 정도인가
  • 사용 변수 : 성별, 연령대, 클릭률(파생), 전환율(파생)
  • 클릭률, CTR : 클릭수 / 노출수 * 100
  • 전환율, CVR : 구매수 / 클릭수 * 100
• 같은 대상을 타겟으로 삼는 광고 중 광고 단가가 지나치게 높은 광고의 비율 파악하기
  
  • 사용 변수 : 성별, 연령대, CPC(클릭당 비용, 파생), CPM(1000회 노출 비용, 파생)
  • 클릭당 비용, CPC : 광고료 / 클릭수
  • 1000회 노출 비용, CPM : 광고료 / 노출 수 * 1000

 

기술통계량 계산

주요 분석을 실시하기에 앞서서 기술통계량을 계산하였습니다. 데이터의 분포를 살펴봄으로써 변수의 대표값을 선정하기 위해서 실시했는데요, 계산 코드는 아래와 같습니다.

-- 노출 수
SELECT
  min(impressions),
  PERCENTILE_CONT(0.25) WITHIN GROUP (
    ORDER BY
      impressions
  ) AS Q1,
  PERCENTILE_CONT(0.5) WITHIN GROUP (
    ORDER BY
      impressions
  ) AS median,
	avg(impressions) as mean,
  PERCENTILE_CONT(0.75) WITHIN GROUP (
    ORDER BY
      impressions
  ) AS Q3,
  max(impressions)
FROM
  tutorial.kag_conversion_data

-- 클릭 수
SELECT
  min(clicks),
  PERCENTILE_CONT(0.25) WITHIN GROUP (
    ORDER BY
      clicks
  ) AS Q1,
  PERCENTILE_CONT(0.5) WITHIN GROUP (
    ORDER BY
      clicks
  ) AS median,
  avg(clicks) AS mean,
  PERCENTILE_CONT(0.75) WITHIN GROUP (
    ORDER BY
      clicks
  ) AS Q3,
  max(clicks)
FROM
  tutorial.kag_conversion_data

-- 광고료
SELECT
  min(spent),
  PERCENTILE_CONT(0.25) WITHIN GROUP (
    ORDER BY
      spent
  ) AS Q1,
  PERCENTILE_CONT(0.5) WITHIN GROUP (
    ORDER BY
      spent
  ) AS median,
  avg(spent) AS mean,
  PERCENTILE_CONT(0.75) WITHIN GROUP (
    ORDER BY
      spent
  ) AS Q3,
  max(spent)
FROM
  tutorial.kag_conversion_data

-- 문의 수
SELECT
  min(total_conversion),
  PERCENTILE_CONT(0.25) WITHIN GROUP (
    ORDER BY
      total_conversion
  ) AS Q1,
  PERCENTILE_CONT(0.5) WITHIN GROUP (
    ORDER BY
      total_conversion
  ) AS median,
  avg(total_conversion) AS mean,
  PERCENTILE_CONT(0.75) WITHIN GROUP (
    ORDER BY
      total_conversion
  ) AS Q3,
  max(total_conversion)
FROM
  tutorial.kag_conversion_data

-- 구매자 수
SELECT
  min(approved_conversion),
  PERCENTILE_CONT(0.25) WITHIN GROUP (
    ORDER BY
      approved_conversion
  ) AS Q1,
  PERCENTILE_CONT(0.5) WITHIN GROUP (
    ORDER BY
      approved_conversion
  ) AS median,
  avg(approved_conversion) AS mean,
  PERCENTILE_CONT(0.75) WITHIN GROUP (
    ORDER BY
      approved_conversion
  ) AS Q3,
  max(approved_conversion)
FROM
  tutorial.kag_conversion_data

변수별로 최소값, 제1사분위수, 중앙값, 평균, 제3사분위수, 최대값을 계산하는 코드입니다. 중앙값의 경우, percentile_cont 함수를 사용했습니다. percentile_cont는 연속형 변수의 분포를 가정하고 백분위수를 계산하는 함수입니다. 변수가 이산형인 경우, percentile_disc를 사용할 수 있지만 제가 사용한 데이터의 경우, 두 함수의 결과값이 동일했습니다. 기술통계량 계산 결과는 아래와 같습니다.

	min	Q1	Q2	mean	Q3	max
노출수	87	6503.5	51509	186732.1330	221769	3052003
클릭수	0	1	8	33.3902	37.5	421
광고료	0	1.48	12.37	51.3607	60.025	639.95
문의수	0	1	1	2.8556	3	60
구매자수	0	0	1	0.9440	1	21

구매자 수 외의 변수는 중앙값보다 평균이 큰 positive skew 분포를 보이고 있습니다. 이러한 분포를 고려해서 평균을 대표값으로 선정하였습니다.

파생변수도 동일한 방식으로 기술 통계량을 계산하였습니다. 다만 파생변수를 저장해둘 수 없으니 각 함수 안의 인자로 수식을 적어주었습니다.

-- CTR
SELECT
  min(clicks / impressions * 100),
  PERCENTILE_CONT(0.25) WITHIN GROUP (
    ORDER BY
      clicks / impressions * 100
  ) AS Q1,
  PERCENTILE_CONT(0.5) WITHIN GROUP (
    ORDER BY
      clicks / impressions * 100
  ) AS median,
  avg(clicks / impressions * 100) AS mean,
  PERCENTILE_CONT(0.75) WITHIN GROUP (
    ORDER BY
      clicks / impressions * 100
  ) AS Q3,
  max(clicks / impressions * 100)
FROM
  tutorial.kag_conversion_data

-- CVR
SELECT
  min(approved_conversion / clicks * 100),
  PERCENTILE_CONT(0.25) WITHIN GROUP (
    ORDER BY
      approved_conversion / clicks * 100
  ) AS Q1,
  PERCENTILE_CONT(0.5) WITHIN GROUP (
    ORDER BY
      approved_conversion / clicks * 100
  ) AS median,
  avg(approved_conversion / clicks * 100) AS mean,
  PERCENTILE_CONT(0.75) WITHIN GROUP (
    ORDER BY
      approved_conversion / clicks * 100
  ) AS Q3,
  max(approved_conversion / clicks * 100)
FROM
  (select * from tutorial.kag_conversion_data where clicks != 0) a

-- CPC
SELECT
  min(spent / clicks),
  PERCENTILE_CONT(0.25) WITHIN GROUP (
    ORDER BY
      spent / clicks
  ) AS Q1,
  PERCENTILE_CONT(0.5) WITHIN GROUP (
    ORDER BY
      spent / clicks
  ) AS median,
  avg(spent / clicks) AS mean,
  PERCENTILE_CONT(0.75) WITHIN GROUP (
    ORDER BY
      spent / clicks
  ) AS Q3,
  max(spent / clicks)
FROM
  (select * from tutorial.kag_conversion_data where clicks != 0) a

-- CPM
SELECT
  min(spent / impressions * 1000),
  PERCENTILE_CONT(0.25) WITHIN GROUP (
    ORDER BY
      spent / impressions * 1000
  ) AS Q1,
  PERCENTILE_CONT(0.5) WITHIN GROUP (
    ORDER BY
      spent / impressions * 1000
  ) AS median,
  avg(spent / impressions * 1000) AS mean,
  PERCENTILE_CONT(0.75) WITHIN GROUP (
    ORDER BY
      spent / impressions * 1000
  ) AS Q3,
  max(spent / impressions * 1000)
FROM
  tutorial.kag_conversion_data

기술통계량 계산 결과는 아래와 같습니다.

	min	Q1	Q2	mean	Q3	max
CTR	0	0.01002	0.01598	0.01642	0.02336	0.1059
CVR	0	0	1.2539	10.9030	6.6667	200
CPC	0.180	1.390	1.4983	1.4993	1.6444	2.212
CPM	0	0.1487	0.2488	0.2394	0.3327	1.5042

CPM 외의 변수는 중앙값보다 평균이 큰 positive skew 분포를 보이고 있습니다. 이러한 분포를 고려해서 평균을 대표값으로 선정하였습니다.

---

다음 포스팅에서는 주요 분석에 대해 리뷰해보겠습니다. mode의 mysql에서는 view를 사용하기 어렵기 때문에 매번 서브쿼리를 사용했는데, 그러다보니 코드가 좀 길어지더라구요. 그래서 주요 분석은 두 파트로 나누어서 포스팅할 예정입니다. 다음에는 '같은 대상을 타겟으로 삼는 광고 중 타게팅 옵션이 잘못 설정되었거나 매력도가 낮은 광고의 비율은 어느 정도인지' 파악하는 포스팅으로 돌아오겠습니다.