데이터 엔지니어링 25일차 TIL

학습 내용

데이터 응용 유스케이스(특강)

요즘 시대의 마케팅은 ?
마케팅 = 디지털 마케팅 = 데이터 기반 마케팅
- 요즘 마케팅은 기본적으로 디지털미디어를 통해 이뤄지며 다양한 종류가 존재한다.
- 검색엔진, 온라인 비디오 광고, 이메일 광고, 디스플레이 광고, 소셜 미디어 광고, 리타겟팅(Re-targeting, 프라이버시 이슈가 존재) 등등

디지털 마케팅이다보니 사용자에 관한 다양하고 많은 정보들이 생긴다.(빅 데이터)
- 이러한 데이터를 수집해서 마케팅 성능을 측정하고 마케팅 방법을 개선하는 것이 가능해짐.
- 즉 마케팅 데이터의 수집과 분석이 중요해짐(사이클이 짧아짐)
  => 양질의 마케팅 데이터를 빠르게 수집 -> 마케팅 전략을 빠르게 시험/수정
SLA (Service Level Agreement)
B2C(Business 2 Customer) : 마켓팅이 중요(쇼핑몰) Marketing
B2B(Business 2 Business) : 회사와 거래(영업) Sales
B2G(Business 2 Governmnet) : 정부와 거래

요즘 시대의 마케팅은 ?
모바일 앱의 등장으로 이러한 데이터 수집은 더 복잡해짐
- 웹과 모바일 앱을 통합하는 마케팅 관련 데이터의 수집의 중요성 대두, 관련 서비스를 제공해주는 다수의 회사들이 존재
- 모바일 앱 설치와 관련된 마케팅도 중요해졌다.
- 데이터 인력이 많이 하는 일 중의 하나는 마케팅 관련 데이터 수집 !!!

마케팅 분석 필수 데이터 - 접점
접점 = 채널 = 광고 미디어
접점 데이터 수집 및 저장
- 고객은 다양한 경로를 통해 다양한 사이트들을 방문하면서 물건이나 서비스를 구매하거나 사이트에 회원가입등을 하게 됨
- 가격이 상대적으로 비싼 물건이나 서비스일수록 시간을 두고 여러 경로를 통해 같은 사이트를 여러번 방문하면서 리서치를 하게 되는데 이런 방문들과 방문에 기여한 채널들을 기록하는 것이 아주 중요해짐.

최종 전환기록(Macro-conversion) 기록
- 물건 구매나 회원가입 혹은 앱 설치처럼 마케팅의 목표
보조 전환(Micro-conversion) 기록
- 방문시 했던 행동들을 자세하게 기록하는 것이 도움이 됨. 특정 물건의 상세정보를 클릭 혹은 특정 물건을 쇼핑카트에 넣었다던지 등의 행동들을 기록하는 것.
- 이런 작은 행동들이 모여서 더 의미있는 행동(최종전환)이 발생. 보조전환은 최종전환의 징조가 되는 셈

사용자의 방문 정보들(접점)이 계속 추출되어야 함
- 이 정보들이 결국 내부 데이터베이스에 저장되어야 함
- 이런 데이터(접점과 보조/최종전환 이벤트)의 수집활동과 이런 데이터가 저장되는 데이터베이스(데이터웨어하우스)가 바로 디지털 마케팅 데이터 인프라의 핵심이 된다.

마케팅 필수 측정 데이터 - 채널기여도 측정
어느 마케팅 채널 혹은 플랫폼이 효과적인가?
- 사용자별로 접점과 최종이벤트 등을 기록한다면 마케팅 채널별 기여도 계산이 가능
- 마케팅 실험이 가능해짐
  - 마케팅의 목적에 따라 마케팅 예산을 일부 여러 채널에 집행
  - 가장 효과적인 채널들을 찾아서 남은 마케팅 예산을 계속해서 집          
     행하는것이 가능
  - 채널별 성능결과가 빨리 나온다면 더 많은 실험이 가능해짐

마케팅 기여도 분석(Marketing Attribution Analysis)
- 마케팅 채널의 기여도를 계산하는 방법
- 어떤 마케팅 채널에 돈을 쓰는 것이 최선인지 분석하는 방법
- 하나의 채널에 기여도를 몰아주는 방식과 다수의 채널에 기여도를 나눠주는 방식이 존재

마지막 터치(Last Touch) 모델
: 최종 구매 전의 마지막 채널에게 모든 성과를 부여하는 모델
마지막 비직접방문 터치(LastNon-Direct Touch) 모델

고객의 평생 가치 (Life Time Value)
- 처음엔 고객의 현재 가치를 계산
- 한단계 더 나아가 사용자의 초기행동을 보고 이 사용자가 미래에 가져다줄 수 있는 평생 가치를 예측

쿠키 : 온라인 광고 타켓팅을 가능하게 해주는 기술
- 리타겟팅 광고
- 쿠키 : 웹브라우저에 저장되는 유일식별자로 웹사이트로 하여금 방문자를 식별할 수 있게 해준다. 보통 만료일이 존재하고 개인정보는 저장되지 않는다. 
자사 쿠키(First Party Cookie)와 제3자 쿠키(Third Party Cookie)가 존재

UTM(Urchin Tracking Module) : 마케팅채널 기여도 계산 표준
- 기본적으로 사용자가 어떤 사이트를 방문하게 해준 채널이 무엇인지 알려줌.
  - 마케팅 채널별 효율 분석, 트래픽 유입의 원천을 파악
  - 웹 링크(URL)의 부가 파라미터로 추가됨.

디지털 헬스케어란?
: 건강 관련서비스와 의료IT가 융합된 종합의료서비스
- 인공지능과 개인 스마트 디바이스의 발전과 함께 앞으로 더 큰 발전과 변화가 예상되는 분야
  - 인공지능의 발전으로 일부 분야는 기계가 의사의 역할을 대신함.(방사선 이미지 분석)
  - 다른 분야에 비해 아직 의료분야에서는 인공지능이 널리 사용되고 있지 않지만 어느분야보다 임팩트가 더 클 것으로 예상

 

SQL

WITH A AS(), B AS(),C AS()
: A와 B와 C라는 임시 테이블을 생성

ROW_NUMBER() OVER(PARTITION BY A)
: 윈도우 함수중 하나로 결과 집합의 행 일련번호를 매겨주는 함수
  1부터 차례대로 넘버링 된다.


숙제1 : 사용자별로 처음 채널과 마지막 채널 알아내기
방법 1 - CTE를 빌딩블록으로 사용하는 방법

%%sql

WITH first AS (
   SELECT userid, ts, channel, ROW_NUMBER() OVER(PARTITION BY userid ORDER BY ts) seq
   FROM raw_data.user_session_channel usc
   JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
), last AS (
   SELECT userid, ts, channel, ROW_NUMBER() OVER(PARTITION BY userid ORDER BY ts DESC) seq
   FROM raw_data.user_session_channel usc
   JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
)
SELECT first.userid AS userid, first.channel AS first_channel, last.channel AS last_channel
FROM first
JOIN last ON first.userid = last.userid and last.seq = 1
WHERE first.seq = 1;

방법 2 - JOIN 방법

%%sql

SELECT first.userid AS userid, first.channel AS first_channel, last.channel AS last_channel
FROM (
  SELECT userid, ts, channel, ROW_NUMBER() OVER(PARTITION BY userid ORDER BY ts) seq
  FROM raw_data.user_session_channel usc
  JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
) first
JOIN (
  SELECT userid, ts, channel, ROW_NUMBER() OVER(PARTITION BY userid ORDER BY ts DESC) seq
  FROM raw_data.user_session_channel usc
  JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
)  last ON first.userid = last.userid and last.seq = 1
WHERE first.seq = 1;

방법 3 - GROUP BY 방법

%%sql

SELECT userid,
 MAX(CASE WHEN rn1 = 1 THEN channel END) first_touch,
 MAX(CASE WHEN rn2 = 1 THEN channel END) last_touch
FROM (
  SELECT userid,
    channel,
    (ROW_NUMBER() OVER (PARTITION BY usc.userid ORDER BY  st.ts asc)) AS rn1,
    (ROW_NUMBER() OVER (PARTITION BY usc.userid ORDER BY  st.ts desc)) AS rn2
  FROM raw_data.user_session_channel usc
  JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
)
GROUP BY 1;

방법 4 - FIRST_VALUE/LAST_VALUE

%%sql

SELECT DISTINCT
    A.userid,
    FIRST_VALUE(A.channel) over(partition by A.userid order by B.ts
rows between unbounded preceding and unbounded following) AS First_Channel,
    LAST_VALUE(A.channel) over(partition by A.userid order by B.ts
rows between unbounded preceding and unbounded following) AS Last_Channel
FROM raw_data.user_session_channel A
LEFT JOIN raw_data.session_timestamp B ON A.sessionid = B.sessionid
ORDER BY 1;

FIRST_VALUE/LAST_VALUE함수
: 처음값/마지막값을 리턴해주는 함수

숙제 2 : Gross Revenue가 가장 큰 UserID 10개 찾기
방법 1 : GROUP BY

%%sql

SELECT
    userID,
    SUM(amount)
FROM raw_data.session_transaction st
LEFT JOIN raw_data.user_session_channel usc ON st.sessionid = usc.sessionid
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10;

방법 2 : SUM OVER

%%sql

SELECT DISTINCT
    usc.userid,
    SUM(amount) OVER(PARTITION BY usc.userid)
FROM raw_data.user_session_channel AS usc
JOIN raw_data.session_transaction AS revenue ON revenue.sessionid = usc.sessionid
ORDER BY 2 DESC
LIMIT 10;

숙제 3 : raw_data.nps 테이블을 바탕으로 월별 NPS
방법 1

%%sql

SELECT month,
  ROUND((promoters-detractors)::float/total_count*100, 2) AS overall_nps
FROM (
  SELECT LEFT(created_at, 7) AS month,
    COUNT(CASE WHEN score >= 9 THEN 1 END) AS promoters,
    COUNT(CASE WHEN score <= 6 THEN 1 END) AS detractors,
    COUNT(CASE WHEN score > 6 AND score < 9 THEN 1 END) As passives,
    COUNT(1) AS total_count
  FROM raw_data.nps
  GROUP BY 1
  ORDER BY 1
);

방법 2

%%sql

SELECT LEFT(created_at, 7) AS month,
  ROUND(SUM(CASE
    WHEN score >= 9 THEN 1
    WHEN score <= 6 THEN -1 END)::float*100/COUNT(1), 2)
FROM raw_data.nps
GROUP BY 1
ORDER BY 1;

트랜잭션이란?
: Atomic(동시에 성공하거나 동시에 실패해야 함)하게 실행되어야 하는 SQL들을 묶어서 하나의 작업처럼 처리하는 방법
- DDL이나 DML중 레코드를 수정/추가/삭제한 것에만 의미가 있다.
- SELECT에는 트랜잭션을 사용할 이유가 없다.
- BEGIN과 END 혹은 BEGIN과 COMMIT 사이에 해당 SQL들을 사용
- ROLLBACK에 사용
ex) 은행 계좌 이체

트랜잭션 커밋 모드 : autocommit
autocommit = True
- 모든 레코드 수정/삭제/추가 작업이 기본적으로 바로 데이터베이스에 쓰여짐. 이를 커밋(Commit)된다고 한다.
- 만일 특정 작업을 트랜잭션으로 묶고 싶다면 BEGIN과 END(COMMIT)/ROLLBACK으로 처리

autocommit = False 
- 모든 레코드 수정/삭제/추가 작업이 COMMIT 호출될때까지 커밋되지 않음

Google Colab의 트랜잭션
- 기본적으로 모든 SQL statement가 바로 커밋됨(autocommit=True)
- 바꾸고 싶다면 BEGIN;END; 혹은 BEGIN;COMMIT을 사용(혹은 ROLLBACK;)

psycopg2의 트랜잭션
- autocommit이라는 파라미터로 조절가능
- autocommit=True가 되면 기본적으로 PostgreSQL의 커밋모드와 동일
- autocommit=False가 되면 커넥션 객체의 .commit()과 .rollback()함수로 트랜잭션 조절 가능

DELETE FROM과 TRUNCATE의 차이
- DELETE FROM table_name(not DELETE* FROM)
  - 테이블에서 모든 레코드를 삭제
  - where 사용해 특정 레코드만 삭제 가능
- TRUNCATE table_name도 테이블에서 모든 레코들 삭제
  - DELETE FROM보다 속도가 빠르다
  - 특정 레코드만 삭제 불가(WHERE을 지원하지 않는다)
  - 복구 불가능 = Rollback불가능 (Transaction을 지원하지 않는다.)

UNION(합집합)
- 여러개의 테이블들이나 SELECT 결과를 하나의 결과로 합쳐준다.
- 각 SELECT문의 필드들의 수와 타입이 동일해야한다.
- UNION(중복 존재X) vs UNION ALL(중복 존재 가능)
  - UNION은 중복을 제거함.
- EXCEPT(MINUS, 차집합)
  : 하나의 SELECT결과에서 다른 SELECT결과를 빼주는 것이 가능하다.
- INTERSECT(교집합)
  : 여러개의 SELECT문에서 같은 레코드들만 찾아줌.

COALESCE(A1,A2,...)
: 첫번째 A1부터 값이 NULL이 아닌 것이 나오면 그 값을 리턴하고 모두 NULL이면 NULL을 리턴한다.
-NULL값을 다른 값으로 바꾸고 싶을 때 사용한다.
NULLIF(A1,A2)
: A1과 A2의 값이 같으면 NULL을 리턴한다. 다를 경우 그대로 리턴

LISTAGG
: 컬럼의 레코드들을 전체 그대로, 또는 그룹별로 1개 칸 속에 나열한다.
- GROUP BY 에서 사용되는 Aggregate함수 중의 하나
LISTAGG([합칠 컬럼명], [구분자]) WITHIN GROUP(ORDER BY [정렬 컬럼명]) 

WINDOW - LAG 함수
: 이전 행의 값을 리턴
LAG(A,m,n) OVER(ORDER BY B)
- 컬럼B를 기준으로 레코드를 정렬한 뒤
- 컬럼A의 값을 기준으로,
- 정렬된 레코드 중 직전 m번째 레코드 값 출력
- 출력할 값이 없으면(컬럼B 기준 정렬 시 첫번째 행) n 출력

WINDOW - LEAD 함수(LAG의 ORDER BY DESC와 같음)
: 다음 행의 값을 리턴
LEAD(A,m,n) OVER(ORDER BY B)
- 컬럼B를 기준으로 레코드를 정렬한 뒤
- 컬럼A의 값을 기준으로,
- 정렬된 레코드 중 직후 m번째 레코드 값 출력
- 출력할 값이 없으면(컬럼B 기준 정렬 시 첫번째 행) n 출력

JSON Parsing Functions
: JSON의 포맷을 이미 아는 상황에서만 사용가능한 함수
ex)JSON_EXTRACT_PATH_TEXT
  - JSON String을 입력으로 받아 특정 필드의 값을 추출가능(nested 구조 지원)

느낀 점

특강을 통해 빅데이터가 마케팅과 관련이 깊다는 것을 알 수 있었고, 마케팅분야로 진출하거나 마케팅분야에서 빅데이터분야로 넘어오는 사람들도 종종 있다는 것을 알았다.

sql강의 후 항상 숙제를 내주시는데 풀려고 할 땐 엄청 어려웠다. 풀이를 보니 여러가지 풀이 방법이 존재했고, 풀이를 순서대로 차근 차근 진행해나가면 생각보다 어렵지 않았다는 것을 알았다. 주어진 것을 그대로 활용하여 순서대로 하나씩 생각해보는 자세를 배웠다.