'_'은 일반적으로 문자열로 사용되지 않음 따라서, escape문을 통해 문자열로 따로 빼줘야함.

SELECT DISTINCT contents FROM data WHERE contents NOT LIKE '%_%' escape '';

• escape문의 기호는 변경가능 %*_%' escape '*'

• 퍼널(funnel) :

  • user가 특정 목적을 달성하기 위해 거쳐야 하는 단계
  • 특정 목적에 가까운 단계일 수록 사용자 수가 줄어들어 깔데기 모양(Funnel)을 띔

  (출처 - 데이터리안 : https://datarian.io/blog/funnel-analysis)

• 퍼널분석 :

  • 퍼널별로 이탈율을 계산하고, 이탈원인을 분석 및 해결법을 제시하는 분석 방법

  • 퍼널분석을 통해** 이탈율을 감소하고, 전환율을 높임**

1️⃣ 이탈율과 전환율 구하기

• 이탈율(drop_off rate) :
  • 다음 퍼널로 넘어갈 때, 이탈하는 비율
  • 이탈율 = ((이전 퍼널 사용자 수 - 현재 퍼널 사용자수) / 이전 퍼널 사용자 수 )*100
• 전환율 (conversion rate) :
  • 다음 퍼널로 넘어가는 비율 (전환conversion하는 비율)
  • 전환율 = 1- (이탈율/100)

2️⃣ 전환율 높이기

• 퍼널 간 이탈율, 전환율을 구하면, 유난히 전환율이 낮은 전환이 있다.
• 퍼널 분석에서는 이 부분의 전환율을 높이기 위해서 어떤 방법을 취할 것 인지 해결방법을 제시해야 한다.
• 대표적인 방법으로는 UX/UI 최적화 (속도 개선, 입력 필드 최소화, 자동입력기능제공, 디자인 개선 및 크기 조정), 인센티브 제공(쿠폰, 포인트, 회원가입시 혜택, 장바구니 리마인드, 긴급성강도, 무료체험기간), A/B 테스트& 데이터기반 최적화&CTA(call to Action : 유도 메시지), 신뢰도 향상 (불안요소 해결: 결제 보안 표시, 리뷰평점 노출, 브랜드 인증, 정책 명확화)

📌 AARRR (Pirate Metric)

• AARRR : 퍼널분석의 대표적인 종류
  • 사용자 행동 흐름을 추적
  • Acquisition (획득), Activity (활성화), Retention(유지), Revenue(수익), Referral(추천) 5단계로 나뉨
  • 이탈율과 전환율에 집중하기 보다, 각 단계를 세밀하게 분석 (출처 - 요즘 IT : https://yozm.wishket.com/magazine/detail/1071/)

    AARRR의 5단계

• Acquisision(흭득)
  • user가 서비스를 처음 접하는 단계
  • 광고, SNS, SEO
  • 지표 : 고객 획득 비용(CAC: Customer Acquisition Cost, 한 명의 고객을 획득하기 위해 지출되는 비용), 일간/월간, 주간 활성 유저(DAU: Daily Active User, WAU: Weeknedly , MAU : Monthly)
• Activity (활성화)
  • 사용자가 서비스를 처음 이용하거나 행복한 경험 happy experience *를 느끼는 단계 *(가치)
  • 회원가입, 첫구매
  • 페이지뷰(PV : page view, 둘러본 페이지수, 다양한 페이지를 방문한다는 뜻), 체류시간(DT: Duration Time), 아하모먼트(Aha moment, 제품의 핵심 가치를 고객이 경험하는 결정적인 순간)
• Retention(유지)
  • 사용자가 일정기간 지속적으로 서비스를 반복하여 사용하는 단계
  • 재방문, 재구매
  • 리텐션율(retention rate, 고객이 유지되는 비율), 이탈율 (churn rate)
• Revenue (수익)
  • 사용자가 서비스를 통해 수익 발생
  • 유료결제, 구독
  • 고객 생애가치 (LTV : Life Time Value, 고객 한 명이 자신의 일생동안 기업에 가져다주는 이익의 합), 결제 유저(pu : paying user, 실제로 결제하는 고객), 유저 당 평균 수익(ARPU : averahe revenue per user, 전체수익/전체회원 값)
• Referral (추천)
  • 다른 사람에게 추천하는 단계. 입소문
  • 친구초대, 리뷰작성
  • 사용자 언급 댓글수, SNS 공유수, 레퍼럴 트래픽(referral traffic, GA용어, 다양한 채널에서 웹사이트로 유입된 트래픽)

📌 SQL 쿼리

count if, count distinct , max case

• 검색해보니까 여러 방식으로 퍼널을 구현할 수 있었다.
• 어떤 방식이 좋을 지 ChatGPT에 비교를 요청했더니 다음과 같았다.
• 📌 목표에 따라 추천하는 방식이 다름!
  • 단순 퍼널 분석, 유저 수만 반환하여 이탈률 및 단계별 분석 추적이 어려울 수도! → COUNT(DISTINCT user_id) 사용
  • 유저별 세부 분석 필요 → MAX(CASE WHEN ... THEN 1 ELSE 0 END) 사용
    • MySQL/PostgreSQL에서 성능 최적화 필요, 가독성이 좋음 → COUNT(IF(...)) 사용

count case를 사용

SELECT 
    COUNT(DISTINCT CASE WHEN event_name = 'visit' THEN user_id END) AS visit_users,
    COUNT(DISTINCT CASE WHEN event_name = 'view_product' THEN user_id END) AS view_product_users,
    COUNT(DISTINCT CASE WHEN event_name = 'add_to_cart' THEN user_id END) AS add_to_cart_users,
    COUNT(DISTINCT CASE WHEN event_name = 'checkout' THEN user_id END) AS checkout_users,
    COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_id END) AS purchase_users
FROM events;

max case를 사용

WITH funnel AS (
    SELECT 
        user_id,
        MAX(CASE WHEN event_name = 'visit' THEN 1 ELSE 0 END) AS visit,
        MAX(CASE WHEN event_name = 'view_product' THEN 1 ELSE 0 END) AS view_product,
        MAX(CASE WHEN event_name = 'add_to_cart' THEN 1 ELSE 0 END) AS add_to_cart,
        MAX(CASE WHEN event_name = 'checkout' THEN 1 ELSE 0 END) AS checkout,
        MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS purchase
    FROM events
    GROUP BY user_id
)
SELECT
    COUNT(*) AS total_users,
    SUM(visit) AS visit_users,
    SUM(view_product) AS view_product_users,
    SUM(add_to_cart) AS add_to_cart_users,
    SUM(checkout) AS checkout_users,
    SUM(purchase) AS purchase_users
FROM funnel;

count if를 사용

SELECT 
    COUNT(IF(event_name = 'visit', user_id, NULL)) AS visit_users,
    COUNT(IF(event_name = 'view_product', user_id, NULL)) AS view_product_users,
    COUNT(IF(event_name = 'add_to_cart', user_id, NULL)) AS add_to_cart_users,
    COUNT(IF(event_name = 'checkout', user_id, NULL)) AS checkout_users,
    COUNT(IF(event_name = 'purchase', user_id, NULL)) AS purchase_users
FROM events;

참고

• 데이터리안 : 퍼널 분석 (https://datarian.io/blog/funnel-analysis)
• 요즘 IT : 고객 여정에 따른 'AARRR' 마케팅 용어 알아보기 (https://yozm.wishket.com/magazine/detail/1071/)
• Joe : 퍼널분석(Funnel Analysis), 개념잡기 (https://brunch.co.kr/@joeflow/8)

• date()를 사용하는데, 인상 깊은 것은 'YYYY-MM-DD'형식의 경우, 따로 지정해주지 않아도 date()를 사용하면 기본적으로 적용이 된다.
• 참고로, date()에서 'YYYY-MM-DD'를 따로 적용하니까, 아무것도 안 나온다

  date()

• Yammer회사의 문제상황을 파악하고, 분석
• 회사에서는 특정 구간에 사용자의 서비스 이용에 대한 분석을 진행하고 이에 따라 전략을 세움.

1. 문제 파악하기

• WAU 감소 원인 및 해결책 제시.
• engagement는 user와 product 간의 모든 interaction으로 server call을 한번이라도 실행 시킨 것으로 간주.
• 데이터 핸들링 전에 possible caues에 대한 list를 생성하여 이 가정에 대하여 테스트를 해보기.

2. 분석하기

1. 신규가입자 분석
2. 가입 코호트별 WAU 분석
3. 디바이스별 WAU 분석
4. 이메일 인게이지먼트 분석

1. 신규가입자 분석 (리텐션)

• 특정 기간 동안 활동한 사용자 수를 지표로 표현

• growth를 확인할 수 있는 지표.

• 상용 DB에서는 전체 기간이 아닌 특정 기간을 정하여 쿼리를 작성하는 것을 추천!

• DAU, WAU, MAU가 있음

  • DAU (Daily Active User) : 일간 활성 사용자 수
  • WAU (Weekly Active User) : 주간 활성 사용자 수
  • MAU (Monthly Active User) : 월간 활성 사용자 수

가설

• 가설을 세우고 해결하는 것이 중요
• 가설이 없으면 중구난방으로 데이터를 건드리게 됨
• 본 프로젝트에서는 다음과 같은 이유를 감소의 원인으로 가설을 세움.
• 1. hoilday*
• 2. broken feature* : 서비스가 고장났을 때, 사용자 수가 감소할 수 있음. device 별로 고장 확인하는 것이 좋음
• 3. broken tracking code* : 코드 트래킹, 서버 다운으로 log가 찾아지 않을 경우.
• 4. Traffic anomaties from boots*: 서비스 변경(버튼의 내용 및 디자인 변경 등)으로 봇에 의한 활동이 활동 저하에 영향
• 5. Traffic shutdown to your site* : 외부 유입(검색서비스, 서버)이 블락된 경우
• 6. Marketing event*: 마케팅 이벤트가 일시적으로 수를 증가하나, 이후 하락. 참고로 친구로 유입된 사람이 서비스를 더 오래 쓸 확률이 높음.
• 7. Bad date*:QA와 일반 유저간의 분리가 안되어, QA의 일반적이지 않은 활동로그가 섞임.
• 8. Search crawler changes*: 엔진검색 변경으로 랭킹 및 노출 횟수가 감소

• 원인이 다양한 경우 (a lot of possibilities), 우선순위를 세워 가설 확인. 이때, 아래와 같은 요소들을 고려하는 것이 좋음.

  • 경험 (Experience) : 비슷한 문제 해결 경험을 가진 사람의 경우 빠르게 해결.
  • *의사소통 (Comunication) * : 마케팅 부서 등 모르는 부분이 있으면 물어보기. 가장 중요. 분석팀에서 모든 운영과 프로젝트를 알 수 없어 타부서와 커뮤니케이션이 중요.
  • *속도 (Speed) * : 전처리가 잘되어 있거나, 쿼리가 짜져 있거나 하는 경우
  • ** 의존성 (Dependency)** : 하나 확인하고 쉽게 확인할 수 있는 것은 빠르게 확인

2. 코호트 분석

• 코흐트 : 가입한 시기로 유저를 특정 기준으로 쪼갬. (서비스에 따라 가입 말고 다른 이벤트로 해도 되는 듯!)
• 가입시기로 두었을 경우, 마케팅으로 인한 유입이 효과적이었는지 확인할 수 있음.

3. 디바이스별 분석

• 모바일앱 자체 문제가 아니어도, 특정 OS에서의 모바일앱에 대한 불편 확인할 수 있다.
• 시기에 따라 업데이트를 확인하고 사용자에 어떤 버전에서 어떤 문제 때문에 사용자 경험의 질이 하락하였는지 확인.

4. 이메일 분석 (Engagement 방법 분석)

• product의 engagement가 어디서 비롯되는 지 생각해보기
• 예를 들어 듀오링고는 푸쉬 메시지를 센스있게 보내서 user engagement를 생산
• 이메일 일 경우, 링크에 문제가 있지 않을까 확인
• engagement 이벤트 이후 user engagement까지 얼마나 시간이 걸리는지 확인해보기. 이를 통해 현재 사용하고 있는 방법이 효율적인지 확인해볼 수 있음

참고

• mode - Investigating a Drop in User Engagement : https://mode.com/sql-tutorial/a-drop-in-user-engagement
• 데이터리안 - 리텐션 (4) DAU, WAU, MAU 그리고 Stickiness : https://datarian.io/blog/stickiness
• 인프런 (데이터리안) : https://www.inflearn.com/course/lecture?courseSlug=%EB%8D%B0%EC%9D%B4%ED%84%B0-%EB%B6%84%EC%84%9D-sql-%EC%8B%A4%EC%A0%84&unitId=72151&tab=curriculum

빅쿼리 강의를 완강하다

원래 맘에 드는 강의를 넣어두고 필요한 부분만 그때그때 듣거나 듣다가 완강을 못하는 경우가 많았다.

인프런에서도 그렇게 수납된 강의가 완강한 강의보다 몇배로 많을 정도였는데, 이번에 완강을 하게 되어 너무 뿌듯했다🎉

솔직히 리텐션 분석 과제가 주어진 데이터에서 직접 분석해보는 거라, 몇 일 미루는 기간이 있긴 했는데, 어떻게든 이번에는 완강하고 싶어서 이판저판으로 끝까지 들었던 것 같다.

기존에 SQL 데이터 분석 학습을 하면, 보통 기초 ~ 중급문법에서 끝나는 경우가 많아서 현업에서 어떻게 SQL을 사용하는지 감이 안 잡힐 뿐더러, 내가 하고 있는 학습의 방향성에 대해 의구심을 갖다 끝내는 적이 많았다. 학교 강의에서 SQL을 잠깐 배운 적은 있지만, 거의 독학해야 했었기 때문에 항상 어떤 부분을 어떻게 학습할 것인가에 대한 혼란스러움이 있었다. 대부분의 강의는 SQL문법에 대하여 가르치지만, 과연 현업에서 필요로 하는 부분인가에 대한 의문과 이에 대한 갈증이 존재했다.

하지만, 이번에는 현업 경험을 중심으로 커리큘럼이 짜여져 강의의 방향성에 대하여 신뢰가 생기고, 본 학습이 의미있는 시간이 될 것이라는 확신이 생겨 끝까지 포기하지 않을 수 있었던 것 같다.

무엇보다, 이번에 카일님이 직접 피드백을 개개인 마다 잘 주시기도 했고, 슬랙에 공지가 주기적으로 올라와 내 나태한 모습을 다시 다잡을 수 있어 완강할 수 있었던 것 같다. 평소 같았으면, 어려운 부분에서 멈추거나 좌절하기 마련이었을 텐데, 확실히 누군가가 관리를 해주니 나 스스로 학습할 수 있는 환경과 지지자(supporter)가 생겨 힘을 얻을 수 있었다.

이번 강의를 통해 '나'에 대해서 깨달은 점

이번 강의를 통해서 나 스스로 어떤 사람인지 깨달은 점이 있었다.

🥰 첫번째는 나는 학습의 방향성과 가치가 중요한 사람이라는 것이다.

지식을 그냥 외우기도 하지만, 나는 천상 N이라서 인과관계와 '왜?'라는 근본적인 의문을 항상 생각하는 사람이다. 그래서 기존 강의를 들었을 때 '결국 현업에서 요구하는 것은 문법이 아닌데 왜 하고 있지?' 하는 생각이 들고, '기본 문법만 배우는 강의가 왜 필요할까?'에 대해서 고민했던 것 같다.

주변에 현업 데이터 분석가들이 거의 없기도 했고, 회사에 코딩이란 것을 할 수 있는 사람은 나 밖에 없어서 학습에 방향성에 대하여 항상 혼란과 갈증이 존재했었다. 그러다보니, 나 스스로 배움과 거리가 먼 사람이 아닐까하는 생각에 좌절이 되고 자신감이 하락했었다.

하지만, 이번 스터디를 통해 방향성과 목표가 잘 잡혀 있으면 나도 열심히 할 수 있는 사람이구나에 대하여 깨닫게 되어 조금은 자기효능감을 회복할 수 있었던 시기인 것 같다.

목표가 없거나 가치를 못 느낄 때, 인내하며 그 자체로 즐기는 부분은 내가 더 개발해나가야 하는 부분이지만, 성격은 특정 그 자체로 장단점을 띄니까, 목표지향적이고 가치를 중시하는 내 장점을 알게 된 것이 뿌듯한 일이라는 생각이 든다.

🥰 두번째로 시스템과 환경, 사회적 지지는 중요한 요소라는 깨달음을 알게 되었다.

이번에 스터디를 참여하면서 동일한 목표를 가진 사람들, 특정 분야에 대하여 지식을 가진 사람들, 나보다 경험이 많은 사람들과 함께 하면서, 시스템과 환경의 중요성에 대하여 다시 한번 더 느끼게 되었다. 완강이 쉽다면 쉬울 수 있는데, 어려운 부분이 나오면 주춤하게 되고 회피하고 싶어지는 것이 사람 심리지 않을까 싶다.

이 때, 시스템과 환경의 중요성이 비로소 드러나게 되는 것 같다. 혼자 학습을 하면서 어려운 부분이 나오면 쉽게 포기하게 되거나, 해결법을 몰라 끙끙거리다 포기하게 되기 일쑤였다. 하지만, 내가 질문할 수 있는 대상이 존재하고, 함께 학습하는 사람이 있으며, 서로의 존재만으로 학습의 힘이 되는 환경에 있다는 것이 은근 많은 도움을 주었던 것 같다.

무엇보다, 나의 지식 중 어떤 부분이 중요한 것인지, 부족함을 채우기 위해서는 어떤 사이트와 자료를 참고해야 하는 지 가이드해 줄 사람이 있다는 것이 한 사람이 성장하고 가치를 느끼는데 얼마나 큰 도움이 되는지 느끼게 되었다. 도움을 요청할 곳이 없는 사회초년생에게 정말 큰 big break였던 것 같다.

앞으로의 계획 & 마음가짐

그동안, 회사에서 갈팡지팡하면서 시간을 보냈었는데, 스터디를 완주하니 방향성에 대해서도 알게 됐고, 스스로 자신감도 생긴 것 같아 다시 도전할 힘을 얻게 된 것 같다.

✅** 더 좋은 시스템과 문화(분위기)가 있는 환경 조성하기** 회사에 나를 지지해주는 고마우신 분들도 많고, 잘 하지 못해도 할 수 있다고 말씀해주시는 분들도 많지만, 데이터 분석가 사회초년생으로서 역량을 쌓아가기에 부족함을 느끼는 중이다. 나는 사람도 중요하지만, 성장을 중시하는 사람이라 더욱 그렇게 느끼는 것 같다. 또, 회사 방향도 데이터분석이 아닌, 개발역량을 더욱 필요로 하는 상황에서 어쩔 수 없는 상황인 것도 있는 것 같다. 이러한 상황에서 좀 더 내가 전문가로서 성장할 수 있도록 조언을 듣고, 경험을 쌓을 수 있는 회사 또는 공동체를 만나는 것이 목표다. 어떤 곳이 잘 맞을지는 모르겠지만, 차츰 알아보면 어디든 찾지 않을까? 시작이 반이니까 말이다!

✅** 데이터 분석가로서의 역량 쌓아가기** 데이터 분석가로 살아가기 위해서는 역량을 쌓아야 하는데, 아직 부족한 점이 많다. 이 부분을 보완하기 위해서 더 많은 학습과 시도가 필요하다.

우선은 인프런에 데이터리안에서 제공하는 '[백문이불여일타] 데이터 분석을 위한 SQL 실전편 (무료 미니 코스)'를 수강하면서 현업과 비슷한 상황에서 SQL이 어떻게 이용되는지 학습할 예정이다. 이 학습이 종료되면 리텐션, AB테스트, 가설검증, 코호트 분석 등 데이터 분석의 기반이 되는 부분에 대해 더 익숙해지지 않을까?

다음으로 BI 툴에 대해 학습할 예정이다. 일단 알아본 바로 power BI와 태블로가 많이 사용되는 것 같아, 이 툴에 대한 간단한 사용법을 익혀 볼 예정이다. 인프런의 '[입문자를 위한] Power BI로 시작하는 데이터 시각화'와 네이버 boostcourse의 '데이터 시각화를 위한 태블로'를 중점으로 학습할 예정이다. 인프런에도 태블로 강의가 있던데, 간단하게 훑어보고 필요하면 더 수강해봐야 겠다.

추가적으로 PM으로서의 역량을 키울 예정이다. 카일스쿨님이 이야기해주신 부분 중 인상 깊었던 것은 많은 회사들이 데이터 기반으로 의사결정을 하고 있다고 하지만 그렇지 않는 곳도 많다는 것이었다. 그래서 데이터 분석을 하시던 분들이 PM이 되는 경우도 많다고 들었는데, 혹시 미래를 대비하여 PM으로서의 데이터 리터러시 역량을 키우는 것도 가치있을 것 같다는 생각이 들었다. 인프런의 'PM을 위한 데이터 리터러시(프로덕트 데이터 분석)' 강의를 들어볼 예정이다. 마침 스터디 종료 기념?으로 30% 할인 쿠폰도 주셨고, 이번 스터디 우수 수강생으로 뽑혀서 인프런 포인트를 받게 되어 저렴하게 구매할 수 있게 되었다. 양질의 강의를 무료로 들을 수 있다니 기분 좋다🥰

✅** 미니 프로젝트 해보기 ** 가장 중요한 것은 현업에 잘 녹아들 수 있는 경험을 쌓았는가 라고 생각한다. 이 부분을 개발하기 위해 스터디 마지막 강의처럼 데이터를 직접 분석해보는 연습이 필요한 것 같다. 연습하다 보면, 스스로 역량도 개발학 포트폴리오도 자연스럽게 작성되지 않을까? 거창한 건 모르겠고 일단, 실제 데이터를 가공해서 나름 분석해보는 걸로 시작하면 어떨까 하는 막연한 생각중이다

✅ 완벽이 아닌 완료를 목표로 삼자 완벽을 중심으로 해내려 하다보니, 정작 쉽게 완료하지 못하거나 내 계획보다 완료 시점이 늦어지는 경우가 있었다. 이번에 3주차 과제하면서도 이 부분 때문에 완주를 못할 뻔 했는데, "완벽한 것이 중요한게 아니라 완료가 중요해요." 라는 카일님의 메시지에 시선을 고쳐 먹고 다시 도전할 수 있었다. '부족해도 괜찮아! 처음 배우는 거니까 그럴 수 있어!'하고 스스로 지지해주다 보니, 남은 3주차 4주차 과제도 모두 잘 제출할 수 있었다✌ 또, 나 스스로 너무 마음의 여유를 주지 않았나 반성할 수 있었다. 앞으로는 나에게도 친절한 내가 되도록 더욱 노력해야 겠다.

매일매일 노력하다 보면, 조금씩 자라고 있지 않을까 기대해 본다🌳

1. 스케쥴 쿼리
2. 절차적 언어
3. VIEW
4. UDF
5. JSON 함수
6. Gemini를 사용한 빅쿼리

1. 스케쥴 쿼리

• 지속적, 또는 일정 시간에 쿼리를 사용해야 하는 경우.
• 연산이 오래 걸려 (3분 이상) 미리 BigQuery Table로 저장하는 경우에 사용
• BigQuery Data Transfer Service API를 사용

2. 절차적 언어

• 쿼리문에서 절차형 프로그래밍을 적용할 때 활용
• 변수선언, 조건문, 반복문.

1. 변수선언

declare : 변수타입, 값 정의 set : 변수에 값을 할당

declear a INT;
SET a=13;

2. 조건문(if, while)

while문 예시

label_1: LOOP
  WHILE x < 1 DO
    IF y < 1 THEN
      CONTINUE label_1;
    ELSE
      BREAK label_1;
  END WHILE;
END LOOP label_1

3. 동적 쿼리 실행 EXCUTE IMMEDIATE

• 쿼리결과를 인자로 넣어 다시 실행하고 싶을 때. 특정값을 Table에서 SELECT할 수 있음.
• 사용자 입력 혹은 외부 변수를 사용하여 쿼리를 작성할 때 사용.
• 가변적인 것을 이용하고 싶을 때!

*4. 오류 * raise 기능!

3. VIEW

• Table에 데이터를 저장하지 않고 쿼리를 저장
• 복잡한 쿼리를 단순화하거나 쿼리 변경이 잦을 경우, 쿼리를 재사용하고 싶은 경우

1. 생성법

1) 쿼리 실행 후, 뷰 저장 ** **2) CREATE문 사용

• 기본문법

  CREATE OR REPLACE VIEW AS
  SELECT
  col
  FROM table

• 예시

  CREATE OR REPLACE VIEW `my_project.my_dataset.my_view` AS
  SELECT name, age, department
  FROM `my_project.my_dataset.employees`
  WHERE age > 30;
  
  

-- my_project.my_dataset.my_view 경로에 저장됨.

### 2. WITH과 VIEW의 차이
- **VIEW**는 재사용 가능, 다양한 쿼리에서 반복적으로 사용되는 복잡한 쿼리를 저장. DB에 저장됨. 속도가 상대적으로 느림.
- **WITH**은 하나의 쿼리 내에서 사용.
![](https://velog.velcdn.com/images/tett_77/post/8d19b737-d1ec-4cf4-9a8c-d1132701b563/image.png)
## 4. UDF(User Defined Function)
- 사용자 정의 함수 
- 반복 작업, 공용함수, SQL로 실행이 어려운 함수의 경우 사용
- SQL, JS로 UDF를 생성할 수 있음. JS는 속도 이슈가 있음. 

**1. 임시 UDF** : 쿼리문에서만 UDF
**2. 영구 UDF** : 데이터셋에 저장되는 UDF



### UDF 참고 사이트 
**1)  bqutil **
https://github.com/GoogleCloudPlatform/bigquery-utils/blob/master/udfs/community/README.md
- bqutil.fn.함수이름으로 실행

SELECT bqutil.fn.t_test([1.2,3.0,4.4], [3.5,4.7,8.4]) AS test_outcome

``` *2) BigFunctions *

• upload_table_to_gsheet, upload_to_gsheet, 각종 Transform geo data 함수, array 함수 등 각종 함수가 존재...

  5. JSON 함수

• JSON : value, key로 구성된 데이터

• json을 사용하면 특정 데이터를 추출하는 데 용이하다.

• JSON_QUERY (JSON형태로 데이터 반환), JSON_QUERY_ARRAY, JSON_VALUE (스칼라로 반환), JSON_VALUE_ARRAY 등의 함수가 존재

• JSON으로 반환하면 데이터에 항상 ''가 존재, 스칼라는 ''없음

• $: root, 배열 사용

• JSON path Finder(https://jsonpathfinder.com/) 를 사용하여 json 형태로 쉽게 표현할 수 있다.

6. Gemini 사용하기

• 빅쿼리 UI의 우측의 연필 아이콘을 사용하여 실행할 수 있음!
• 주석(#)에서 입력 후 엔터치고 기다리면 실행됨.
• 쿼리 포멧이나 성능 부분에서 부족한 점이 있지만, 추후에 업데이트될 수 있음!
• 쿼리 검증 용도로 사용. 쿼리 기본 지식이 없는 사람에게 사용할만 함

GA(Google Analytics)와 Firebase Analytics

• GA : 웹 데이터 수집 및 분석을 위한 도구

• firebase Analytics : 앱 데이터 수집 및 분석을 위한 도구

• 빅쿼리와 연동할 수 있음!!

• 개발 필요없이 데이터 export하면 자동으로 하루 1번 빅쿼리에 데이터가 저장 (하루에 event 100만개 제한)

모바일 앱 생애주기

• foreground : 앱이 화면에 보이고 사용자와 상호작용. 직접사용.

• backgroend : 앱이 화면에 안 보이나 작동중. 사용자가 앱 실행 우 홈버튼을 누른 경우.

• 개발 환경에 따라 백그라운드 후 다시 포그라운드로 올 경우 확인하는 방법이 있음 : event, window함수 사용...

참고

• BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석) : https://www.inflearn.com/course/bigquery-%ED%99%9C%EC%9A%A9%ED%8E%B8/dashboard

• 빅쿼리 공식문서 (구글) : https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language

• WITH, VIEW차이 : ChatGPT

📌 리텐션

• 시간에 따른 제품 사용을 측정하는 지표. customer가 제품을 재이용하는 단계.

• 기존 고객의 유지하는 것이 신규고객의 구매유도 보다 최대 25배의 비용을 줄일 수 있다. [하버드 비즈니스 인터뷰] (https://hbr.org/2014/10/the-value-of-keeping-the-right-customers) 특히, 고객유치가 되면, 입소문이 나게 되고 신규고객을 위한 마케팅 비용을 줄일 수 있다는 장점!

👉 함께 보면 좋은 글 : Market Fir Lab Solution

리텐션 분석

• 제품 특성(자주 사용하는 제품이 있고, 1회성으로 사용하는 제품/ 유료, 무료 인 제품)에 따라 리텐션을 분석할 때, 고려해야하는 부분이 달라짐. 예를 들어, 1회성으로 사용할 경우 리텐션이 적음

• PMF(Product Market Fit) : 제품과 시장의 적합성. 시장의 요구와 고객의 필요를 충족시키는 정도. 리텐션 커브에서 평평한 구간!. 어떻게 하면 PMF을 유지할 수 있을 지 생각해보는 것이 리텐션의 목표!

• 리텐션 분석시 생각해보면 좋은 부분들!

1. 리텐션 정의 : CORE를 접속으로 볼 것인가? 다른 EVENT로 볼 것인가?
2. 주기에 따라 관점이 달라진다. : 여러 페르소나가 있는 경우, 주기에 따라 관점이 달라짐
3. 예상 주기가 실제와 동일한 지 파악 : 예상과 실제와 다를 경우, 잘 이야기하는 능력. 결과 지표가 많을 경우, 구체적으로 잘게 쪼개서 작업.
4. 고객의 입장에서 재방문 요소 생각해보기
5. 가설을 업데이트하기

• 처음 리텐션 접근시, 접속기록 > 핵심 이벤트처럼 점점 구체적으로 파악하는 것이 좋음.

• 히스토그램을 그리면 제품의 사용주기 확인이 쉬움 : 오른쪽일 수록 긴 주기

  리텐션 지표

• 1. CLASSIC 리텐션(N DAY 리텐션)* : 유저가 최조로 참여한 DAY0 이후에 DAY N 별로 참여했는 지 계산. 낮은 값이 나올 수 있음. 보수적. 정기적이거나 하루단위

• 2. RANGE 리텐션(BRACKER 리텐션)* : 특정 RANGE 내에서 또 참여했는지 계산. 유용하고 실용적. 주차별.

• 3. ROLLING 리텐션(UNBOUNDED 리텐션)* : DAY N 이후 한 번이라도 참여했을 경우 그 동안 모두 참여한 것으로 간주. 높은 값이 나올 수 있음. 유저의 최근 참여에 따라 데이터 결과가 달라짐. 장기적, 사용주기가 긴경우

• 4. 리텐션 커브 (Retention Curve)* : 코호트 리텐션을 차트로 시각화한 것. 시각적으로 표현해서 직관적.

• 5. 코호트 리텐션(Cohert Retention)* : 동질적 특성을 가진 사람들의 집합. 보통, 동일한 시기에 가입한 사용자들을 지칭하는 용어로 사용됨.

👉 함께 보면 좋은 글 : 사용자 '리텐션'을 측정하는 5가지 지표 (요즘 IT)

리텐션 커브

• 커브를 전체덕으로 위로 올리되, 평평한 그래프가 좋음. (customer가 유지되고 있다는 뜻)
• 평평한 리텐션은 언제 생기는지 확인
• 리텐션을 면적관점으로 생각하기

  ⭐ 리텐션 SQL로 구현하기

👉 함께 보면 좋은 글 데이터 분석가의 SQL 최적화 일기 : 코호트 리텐션 Batch Query 만들기

1. CLASSIC 리텐션(N DAY 리텐션)

• n day retention은 특정 날짜, n일 뒤에도 서비스를 사용했는 지 확인

• 쿼리 구성

• 1. 가입 또는 첫 이용일 계산* : min()을 사용하여 최초일 계산

• 2. n일 뒤에도 이용 중인지 계산* : where을 통해 특정 기간의 데이터 중

• 3. 재이용까지 걸린 날짜 계산* : join, datediff로 재이용 또는 재접속 까지 걸린 기간을 계산

• 예시 : n을 7일으로 뒀을 경우의 예시

  WITH first_activity AS (
    SELECT
        user_id,
        MIN(activity_date) AS first_activity_date
    FROM
        user_activity
    GROUP BY
        user_id
  )
  SELECT
    COUNT(DISTINCT user_id) AS day_7_retention
  FROM
    first_activity f
  JOIN
    user_activity a ON f.user_id = a.user_id
  WHERE
    DATEDIFF(a.activity_date, f.first_activity_date) = 7;
  

## 2. RANGE 리텐션(BRACKER 리텐션)
- 특정 범위내에서 사용자가 활동했는지 확인

- 쿼리 구성
**1. 가입 또는 첫 이용일 계산**
: min()을 사용하여 최초일 계산
**2. 특정 기간에서의 사용자 수 계산**
: count, case절, datediff를 통해 특정 기간애 서비스를 이용한 사용자의 수 계산

- 예시 
: 1~3, 4~7일인 두 경우에 대한 계산

WITH first_activity AS ( SELECT user_id, MIN(activity_date) AS first_activity_date FROM user_activity GROUP BY user_id ) SELECT f.user_id, f.first_activity_date, -- 첫 이용 이후 1일3일 범위에 활동한 사용자 COUNT(CASE WHEN DATEDIFF(a.activity_date, f.first_activity_date) BETWEEN 1 AND 3 THEN 1 END) AS day_1_to_3_retention, -- 첫 이용 이후 4일7일 범위에 활동한 사용자 COUNT(CASE WHEN DATEDIFF(a.activity_date, f.first_activity_date) BETWEEN 4 AND 7 THEN 1 END) AS day_4_to_7_retention FROM first_activity f JOIN user_activity a ON f.user_id = a.user_id GROUP BY f.user_id, f.first_activity_date

## 3. ROLLING 리텐션(UNBOUNDED 리텐션)
- 첫 활동 이후 재접속의 지속성 추적. 재접속을 했는지에 대한 여부 (서비스 이용의 지속성)
- range와 달리, 날짜에 제한이 없고, 다시 돌아오는지 추적
- 첫 접속이후, 3일 7일 15일 등 특정 기간에 **최소 한 번** 재접속했는지 확인 
- 사용자가 얼마나 빨리 돌아오는지, 자주 오는 지 측정

- 쿼리 구성 
**1. 가입 또는 첫 이용일 계산**
: min()을 사용하여 최초일 계산
**2. 특정 기간에서의 사용자 수 계산**
: count, case절, datediff를 통해 특정 기간애 서비스를 이용한 사용자의 수 계산

WITH first_activity AS ( SELECT user_id, MIN(activity_date) AS first_activity_date FROM user_activity GROUP BY user_id ) SELECT f.user_id, f.first_activity_date, -- 첫 이용 이후 1일 이내에 활동한 사용자 수 COUNT(CASE WHEN DATEDIFF(a.activity_date, f.first_activity_date) <= 1 THEN 1 END) AS day_1_retention, -- 첫 이용 이후 7일 이내에 활동한 사용자 수 COUNT(CASE WHEN DATEDIFF(a.activity_date, f.first_activity_date) <= 7 THEN 1 END) AS day_7_retention, -- 첫 이용 이후 30일 이내에 활동한 사용자 수 COUNT(CASE WHEN DATEDIFF(a.activity_date, f.first_activity_date) <= 30 THEN 1 END) AS day_30_retention FROM first_activity f JOIN user_activity a ON f.user_id = a.user_id GROUP BY f.user_id, f.first_activity_date

```

RANGE와 ROLLING의 차이

• range와 rolling은 sql 쿼리적으로 동일할 수 있으나, 측정하는 관점에 따라 나뉨
• range는 정해진 날짜 범위에 활동을 했는 지 여부. 단 고정된 날짜 범위 내에서 각각의 기간이 끝날 때, 그 날짜를 기준으로 활동한 사용자 수 계산
• rolling은 정해진 날짜 범위에 사용자가 기간 내에 들어왔는 지 누적적으로 계산

예로 1일, 7일, 30일에 다시 접속했는지 여부를 추적

• Range Retention 사용자 A: 첫 사용일 2024-10-01 1일 리텐션: 2024-10-02에 다시 접속 7일 리텐션: 2024-10-08에 다시 접속 30일 리텐션: 2024-10-31에 다시 접속

• Rolling Retention 사용자 A: 첫 사용일 2024-10-01 1일 리텐션: 첫 사용일 이후 1일 동안 재접속한 사용자 7일 리텐션: 첫 사용일 이후 7일 이내 재접속한 사용자 30일 리텐션: 첫 사용일 이후 30일 이내 재접속한 사용자

코호트 (Cohert) 리텐션

• 코흐트 : 같은 특성을 갖는 사람들의 집합. 주로, 같은 날에 가입한 사람을 일컬음.
• n day, range, rolling 은 개별로 활동 추이를 볼 수 있으나, 코호트는 집단 단위로 활동을 확인

코호트(Cohert)와 세그먼트(Segmant) 차이

• 코호트는 "시간"을 기준으로 그룸을 나눔. 시간 흐름에 따라 변화 확인.
• 세그먼트는 "특성"을 기준으로 그룹을 나눔.

Retention User

• 사용자를 유지율(retention)을 기준으로 new, current, resurrected, dormant로 나눌 수 있음.

1. New User

• 신규 사용자. 서비스를 처음 이용하는 사용자를 의미.
• 첫 가입. 첫 로그인 기준

2. Current User

• 현재 사용자. 현재 서비스를 이용하는 사용자. 최근 특정 기간 동안 서비스 사용. 지속적으로 활동한 사용자.
• 특정기간은 각 서비스 및 회사에 따라 다르나 일반적으로 최근 7일. 30일 등을 활동 기준으로 구분

3. Resurrected User

• 부활한 사용자. 특정 기간 동안 활동이 없었으나 다시 서비스를 이용한 사용자.

4. Dormant User

• 휴면 사용자. 특정 기간동안 서비스를 이용하지 않은 경우. 일반적으로 30일, 60일이상 서비스를 사용하지 않는 경우.

참고

• BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석): https://www.inflearn.com/course/bigquery-%ED%99%9C%EC%9A%A9%ED%8E%B8/dashboard
• Market Fir Lab Solution: https://mixpanel.mfitlab.com/blog/retention-meaning-and-how-to-use
• 사용자 '리텐션'을 측정하는 5가지 지표 (요즘 IT): https://yozm.wishket.com/magazine/detail/2143/
• n day, range, rolling 리텐션의 쿼리 예시, range와 rolling 차이 : ChatGPT
• retention user를 new, current, resurrected, dormant user로 나누는 기준과 용어의 뜻 : ChatGPT
• 코호트 vs 세그먼트: https://www.inflearn.com/community/questions/622393/%EC%BD%94%ED%98%B8%ED%8A%B8-vs-%EC%84%B8%EA%B7%B8%EB%A8%BC%ED%8A%B8?srsltid=AfmBOor901V5HvFFvJPUt2HRAA65f39cEF5VkLj3hNbj15AFDWnooofx

• ORACLE에서는 분석 함수(Analytics)라고도 불림
• 창을 통해 풍경의 일부분을 볼 수 있듯이, 윈도우 함수를 통해 일부분을 계산할 수 있도록 돕는 함수
• 그룹 내 집계함수, 순위 등을 계산할 때 사용

**윈도우함수 OVER(PARTITION BY 그룹을 나눌 칼럼 ORDER BY 정렬 칼럼) AS 별칭**

SUM() OVER(PARTITION BY col ORDER BY col2 ) AS name

⭐ 윈도우 함수 종류

윈도우 함수는 크게 탐색함수, 순위함수, 집계분석함수로 나뉨.

1. 탐색함수

• LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()
• PARTITION으로 나눈 특정 그룹에서 이전행의 값, 후속행의 값, 첫번째 값, 마지막값을 볼 수 있음
• LAG, LEAD의 경우 column 뿐 아니라, 번호를 지정하여 몇 번째 값을 볼 것인지 지정할 수 있음

SELECT 
    user_id,
    date,
    cnt,
    LAG(cnt, 2) OVER (PARTITION BY user_id ORDER BY date) AS cnt_lag_2,
    LEAD(cnt, 2) OVER (PARTITION BY user_id ORDER BY date) AS lead_cnt_2,
    FIRST_VALUE(cnt) OVER (PARTITION BY user_id ORDER BY date) AS first_cnt,
    LAST_VALUE(cnt) OVER (PARTITION BY user_id ORDER BY date 
                          ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_cnt
FROM 
    your_table;

2. 순위함수

• ROW_NUMBER(), RANK(), DENSE_RANK()가 있음
• 데이터의 순위를 매겨주는 함수

SELECT 
    user_id,
    score,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY score DESC) AS row_num,
    RANK() OVER (PARTITION BY user_id ORDER BY score DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY user_id ORDER BY score DESC) AS dense_rank
FROM 
    your_table;

• ROW_NUMER는 중복 순위 값에 랜덤으로 값 부여. 중복되는 순위값이 없음
• RANK는 중복 순위값에 중복값 부여. 다음 점수는 이전 순위 수 만큼 건너 뛰고 순위 부여 (이전순위 데이터 갯수+1)
• DENSE_RANK는 중복 순위값에 중복값 부여. 다음점수는 그 다음 순위 부여

3. 집계분석함수

• AVG, SUM, COUNT, MIN,MAX
• 집계함수와 기능은 동일하나, 집계함수는 값만 도출해내는 반면,** 집계분석함수는 새로운 COLUMN에 값을 도출**

  SELECT 
    user_id,
    score,
    SUM(score) OVER (PARTITION BY user_id ORDER BY date) AS cumulative_score,
    AVG(score) OVER (PARTITION BY user_id ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_average,
    COUNT(score) OVER (PARTITION BY user_id ORDER BY date) AS cumulative_count,
    MIN(score) OVER (PARTITION BY user_id ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS min_score,
    MAX(score) OVER (PARTITION BY user_id ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS max_score
  FROM 
    your_dataset.your_table;
  

# 📌 FRAME 설정
- 윈도우 함수와 사용되며, **범위**를 지정해주는 역할 

## FRAME 종류
- 크게 **ROW, RANGE**로 종류가 나뉨
**1. ROW**
- 데이터의 **행 수**를 기준으로 범위 설정 
- OVER() 내부에서 **ROWS BETWEEN A AND B** (A,B는 범위를 나타내는 구문)
- 아래 예시는 현재 행을 기준으로 1이전행을 포함(1이전행 ~ 현재행)

SELECT employee_id, salary, SUM(salary) OVER (ORDER BY employee_id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS running_total FROM employees;

**2. RANGE**
- **값의 범위**를 기준으로 범위 설정 
- OVER()안에 **RANGE BETWEEN A AND B** (A,B는 범위를 나타내는 구문)
- 아래 예시는 현재 날짜를 기준으로 이전 하루 동안의 모든 행을 포함(현재날짜 기준 하루 전~현재날짜)
- 수치와 날짜 데이터에 주로 사용됨 

SELECT order_id, order_date, SUM(amount) OVER (ORDER BY order_date RANGE BETWEEN INTERVAL '1 DAY' PRECEDING AND CURRENT ROW) AS total_sales FROM orders;

## ⭐ FRAME 범위 설정 구문
- ROW, RANGE의 범위 설정 구문은 거의 동일
1. **UNBOUNDED PRECEDING**: 현재 행을 포함하여, 모든 이전 행. (모든 이전행 <=현재)
2. **n PRECEDING**: 현재 행의 n개 이전 행. (이전 N행<현재)
3. **CURRENT ROW**: 현재 행.
4. **UNBOUNDED FOLLOWING**: 현재 행 이후의 모든 행.(현재<=모든 이후행)
5. **n FOLLOWING**: 현재 행의 n개 이후 행.(현재<=이후 N 행)


- `INTERVAL`은 RANGE절에서 주로 사용됨
- 아래 예시는 이전 7일 범위 내의 모든 행 포함. 현재 값이 14이면 7~14인 모든 데이터.

SELECT order_id, order_date, SUM(order_amount) OVER ( ORDER BY order_date RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW ) AS weekly_sales FROM orders;

# ⭐📌 QUALIFY
- 윈도우 함수로 나온 값을 필터링
- WHERE는 데이터 실행 전 필터링 실행. 
- QUALIFY는 윈도우 함수 사용시 적용되며, 윈도우 함수 결과를 바탕으로 필터링되는 차이점

- 급여 랭크가 1인 사람 조회하는 예시. RANK를 사용한 윈도우 함수 이후에 QUALIFY문이 실행됨.

SELECT employee_id, department_id, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank FROM employees QUALIFY salary_rank = 1;

```

⭐ 중요 개념 및 함수

• 윈도우 함수
• FRAME
• QULIFY

  ⭐ 습관들이기

• ORDER BY를 사용하여 특정시점까지의 누적값을 구할 수 있다.
• FRAME에는 순서가 중요하다
• USER_ID와 USER_PSEUDO_ID 차이 유의
• CTE를 사용하는 것도 좋지만 간단한 코드는 서브쿼리를 사용해 보자
• IF문은 행 단위로 작동, SUM은 특정 파티션에 대한 누적합을 계산
• QUALIFY 로 조건설정을 하여 두 값이 같은 지 비교가능
• COUNT의 윈도우 함수 대신 GROUP BY를 사용하여 간략하게 코드를 작성할 수 있다.

참고

• ChatGPT : 각 함수 예시, ROW와 RANGE 예시, INTERVAL
• BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석) : https://www.inflearn.com/course/bigquery-%ED%99%9C%EC%9A%A9%ED%8E%B8

No matching signature for function DATE_DIFF for argument types: INT64, INT64, DATE_TIME_PART. Supported signatures: DATE_DIFF(DATE, DATE, DATE_TIME_PART); DATE_DIFF(DATETIME, DATETIME, DATE_TIME_PART); DATE_DIFF(TIMESTAMP, TIMESTAMP, DATE_TIME_PART)

상황

기존 날짜 데이터가 있는 칼럼과 LAG를 통해 생성된 새로운 날짜 칼럼 간의 차이를 구하는 과정에서 발생 (DATA_DIFF)

원인

두 데이터의 날짜 자료형이 일치하지 않음

해결

두 데이터의 자료형을 일치시켜주기!

이 값을 TIMESTAMP로 변환하려면 TIMESTAMP_MICROS함수를 사용해야 한다.

SELECT
    TIMESTAMP_MICROS(INTEGER TYPE DATA)

실행시에 UTC기준의 날짜와 시간이 나타난다.

Subquery returns more than 1 row

상황

WHERE COL =(서브쿼리)를 사용했을 경우 발생

원인

WHERE 절에서 COL은 하나인데, 값은 서브쿼리가 RETURN 하는 값이 여러개일 경우 발생

해결

WHERE COL = ()를 WHERE COL IN ()로 변경

• 여러개의 동일한 타입을 하나의 칼럼에 저장하는 데이터 타입

Array 생성하는 방법

1. 대괄호([])를 사용

• 간단한 배열 생성시 사용
• Bigquery, PostgreSQL에서 사용가능한 방법!

SELECT 
  [1, 2, 3, 4] AS numbers,
  ['apple', 'banana', 'cherry'] AS fruits;

2. ARRAY 함수 사용

• 대괄호보다 더 다양한 상황의 배열생성 (서브쿼리 결과)

• Bigquery, PostgreSQL에서 사용가능한 방법!

• MySQL에서는 JSON_ARRAY를 사용하며, Oracle에서는 PL/SQL에서 컬랙션 타입을 사용.

• ARRAY[value,...]

  SELECT 
    ARRAY[1, 2, 3, 4] AS numbers,
    ARRAY['apple', 'banana', 'cherry'] AS fruits;

3. 배열 생성 함수 GENERATE_DATE_ARRAY 사용

• Bigquery에서 사용가능한 방법!
• 주어진 시작 및 끝 날짜 조건에 맞는 배열을 생성하는 함수
• 날짜 범위 생성에 사용
• GENERATE_DATE_ARRAY(start_date, end_date[, step])

  --- 2일 간격으로 20240101부터 20240110까지의 날짜 배열 생성
  -- 결과는 ["2024-01-01", "2024-01-03", "2024-01-05", "2024-01-07", "2024-01-09"] 배열 생성
  
  

SELECT GENERATE_DATE_ARRAY('2024-01-01', '2024-01-10', INTERVAL 2 DAY) AS date_array;

### 4. ARRAY_AGG
- 그룹화된 결과를 배열로 집계하는데 사용하는 함수 
- 여러 행의 값을 배열로 집계하여 데이터분석 및 보고서 작성에 유용
- **Bigquery, PostgreSQL**에서 사용가능한 방법!

- SQL server에서도 동일한 함수를 사용하나, 문자열 형태로 반환! XML, JSON으로 배열과 비슷한 형태로 반환하게 끔은 할 수 있다고 함. 

SELECT user_id, ARRAY_AGG(order_id) AS orders FROM orders GROUP BY user_id;

## ARRAY 값에 접근하기 
 ** Bigquery**에서 배열의 element에 접근하는데 ``OFFSET``, ``ORDINAL``, ``SAFE``가 사용된다.
  (Bigquery에서만 사용하는 기능!)
###   1. OFFSET
  - 0부터 시작하는 인덱스로 접근
  - 즉, 1번째 element는 ``offset(0)``을 사용. 

SELECT my_array[OFFSET(0)] AS first_element FROM (SELECT [10, 20, 30] AS my_array);

### 2. ORDINAL
- 1부터 시작하는 인덱스를 사용
- 1번째 element 접근시, ``ORDINAL(1)``사용

SELECT my_array[ORDINAL(1)] AS first_element -- 첫 번째 요소 FROM (SELECT [10, 20, 30] AS my_array);

### 3. SAFE 
- 배열 인덱스에 접근시, 유효하지 않은 인덱스 오류 방지
- ``SAFE_OFFSET``,``SAFE_ORDINAL``사용
- 존재하지 않는 인덱스 접근시, **NULL** 반환

SELECT my_array[SAFE_OFFSET(3)] AS fourth_element -- 배열의 길이가 3일 경우 NULL 반환 FROM (SELECT [10, 20, 30] AS my_array);

SELECT my_array[SAFE_ORDINAL(4)] AS fourth_element -- 배열의 길이가 3일 경우 NULL 반환 FROM (SELECT [10, 20, 30] AS my_array);

## **⭐ UNNEST**
  - 배열의 element를 table로 변환
  - 중첩된 셀에서 하나의 데이터 셀로 변환 
  - 평면화 Flatten
     ```
      SELECT 
          *
      FROM table_A
      ,UNNEST(col) AS name
      ```
  또는 
       ```
      SELECT 
          *
      FROM table_A
      CROSS JOIN UNNEST(col) AS name
      ```
---

# 📌 STRUCT
  - ARRAY와 달리 자료형이 다른 데이터들의 집합 
## STRUCT 생성
  1.  ()를 사용하여 생성
  2. STRUCT<type>(val,...)
---

# 📌  PIVOT
  - 엑셀의 피봇과 동일한 개념
  - PIVOT : 회전하다. 축을 돌리다 -> 행과 열을 변환
  -> ROW 수를 줄여 효율적으로 쿼리작동
  ![](https://velog.velcdn.com/images/tett_77/post/faa829b7-b209-4b32-8c3b-fabf67fb64ec/image.png)
  피봇 후 
![](https://velog.velcdn.com/images/tett_77/post/830d50c8-6949-4f39-a401-f9bbb341ba1c/image.png)
## 1. ⭐ MAX, IF, GROUP BY를 사용하기
  - BIGQUEY는 PIVOT()을 지원하지 않음. 
  - PIVOT시 모든 값이 같을 경우 MAX 혹은 ANY_VALUE를 사용
  - 집계함수를 사용하기 때문에 GROUP BY 사용해주기 

SELECT MONTH, MAX(IF(PRODUCT = 'A', SALESAMOUNT, NULL)) AS A, MAX(IF(PRODUCT = 'B', SALESAMOUNT, NULL)) AS B FROM SALES GROUP BY MONTH;

  ## 2. PIVOT()을 사용하기 
- ** BigQuery에서도 지원!!** 👉[BigQuery PIVOT 사용법(Pivot Operator):어쩐지 오늘은(카일스쿨)](https://zzsza.github.io/gcp/2021/05/16/bigquery-pivot/)
- MYSQL, ORACLE에서 사용. POSTGRESQL은 CROSSTAB 사용 

SELECT * FROM ( SELECT Month, Product, SalesAmount FROM Sales ) AS SourceTable PIVOT ( SUM(SalesAmount) FOR Product IN ([A], [B]) ) AS PivotTable;

# 📌 퍼널 분석(FUNNEL) 
 - 퍼널 : 사용자의 행동 흐름을 시각적으로 표현할 때 사용되는 용어. (예: 방문, 회원가입, 장바구니, 결제..)
 - 세션, 대표적으로 유저, 페이지뷰 단위로 퍼널 분석
 - 이탈, 전환률을 분석하여 전략을 수립할 수 있음

## 퍼널 종류 
  1. OPEN 퍼널 : 순서없이 진행됨
  2. CLOSED 퍼널 : 순서대로 퍼널이 진행되어야 함

👉 함께 읽으면 좋은 글 
 - [퍼널 최적화 전략](https://medium.com/delightroom/%ED%8D%BC%EB%84%90-%EC%B5%9C%EC%A0%81%ED%99%94-%EC%A0%84%EB%9E%B5-a9511086e3c7)
 - [퍼널은 줄일수록 좋은걸까?](https://medium.com/delightroom/%ED%8D%BC%EB%84%90%EC%9D%80-%EC%A4%84%EC%9D%BC%EC%88%98%EB%A1%9D-%EC%A2%8B%EC%9D%80%EA%B1%B8%EA%B9%8C-652280c76e2d)

  ---
# ⭐ 중요 개념 및 함수 
- UNNEST
- PIVOT (MAX, IF, GROUP BY)
- 퍼널 분석 
# ⭐ 습관들이기  
  1. 쿼리작성시 예약어 대문자, \n 해주는 습관들이기 
  2. UNNEST시에 AS 까먹지 말기
  3. PIVOT시에 집계함수를 사용하니 꼭 GROUP BY 해주기!!!
  4. 퍼널 분석 익숙해지기! 

---
참고
--
- [BigQuery PIVOT 사용법(Pivot Operator):어쩐지 오늘은(카일스쿨)](https://zzsza.github.io/gcp/2021/05/16/bigquery-pivot/)
- [BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석):인프런(카일스쿨)] (https://www.inflearn.com/course/bigquery-%ED%99%9C%EC%9A%A9%ED%8E%B8/dashboard)
- ChatGPT: PIVOT 그림

Cannot access field key on a value with type ARRAY<STRUCT<key STRING, value STRUCT<string_value STRING, int_value INT64>>> at

상황

UNNEST한 칼럼을 불러올 때 발생

원인

UNNEST(my_array) AS item은 배열을 행으로 평탄화하여 key, string_value, int_value 필드에 직접 접근할 수 있게 해야하는데, AS를 사용하지 않아 UNNEST한 데이터의 별칭을 지정할 수 없어서 오류가 발생.

해결

UNNEST한 데이터에 AS로 별칭을 지정

나 데이터 분석가 맞아..?🤷‍♂️

분명 데이터 분석가로 들어왔는데, 파이썬 개발을 하고 있는 내 모습을 보면서 현타가 올 때가 많았다. 프로그래밍에 대하여 아는 사람도 없어서 그냥 거의 통으로 개발 프로젝트를 혼자 담당하는데, PM, 개발자, 데이터분석 등의 모든 역할을 하고 있다. 이거 맞나..?

정작 데이터 분석 업무는 0에 수렴하는 상황을 보면서 데이터 분석가로서 데이터 속에서 인사이트를 발견하고 데이터를 핸들링할 수 있는 능력을 키우고 싶다는 데이터분석가 초년생으로서의 목표가 사라지는 듯한 느낌이었다.

머릿속에서 잊혀지는 SQL 심폐소생하기

조금이나마 점점 내 뇌리 속에서 잊혀지는 SQL을 끄집어 내기 위해 카일스쿨님의 '초보자를 위한 BigQuery(SQL) 입문' 강의를 수강하게 되었는데, 내가 인생에서 들어본 SQL 강의 중 가장 명강이라 인상 깊었었다. 그 동안의 SQL 강의는 잘 사용하지 않는 고전적이고 기본적인 쿼리에 대해서도 가르쳐서 어떤 쿼리가 핵심인지, 현직에서 자주 사용하는지 알기가 어려웠다.

하지만, 카일 스쿨의 빅쿼리 기초편(초보자를 위한 빅쿼리(SQL) 입문)은 핵심 내용, 쉽게 헷갈리거나 어려운 부분, 실무에서 자주 사용하는 부분, 쿼리를 짜는 흐름, 데이터 검증과 같은 부분을 중심으로 알려주어 너무 알차다는 느낌이 들었다. 약간 책보고 1-10까지 그냥 외우는 느낌에서 쪽집개 강의를 듣는 느낌이어서 어떤 부분에 더 힘을 쏟아야 할 지 알 수 있었다. (핵심강의 + 노하우 전수) 또한, 중간중간에 연습문제도 있어 내가 학습을 제대로 완료했는지 확인할 수 있어 너무 좋았다! 심지어 이런 명강이 ✨무료✨라니 보석을 찾은 기분이었다💎

(https://www.inflearn.com/course/초보자를-위한-빅쿼리-sql-입문)

빅쿼리 활용편 강의도 들어보려고 했는데, 살짝 비용이 부담되었다. 추석할인때 살지 엄청 고민했었는데, 결국 여유가 안나 다음으로 미루게 되었다😥

빅쿼리 스터디가 있다구?

우연히 인프런에서 빅쿼리 스터디를 모집한다는 메시지를 받게 되어 홀린듯 구경하러 갔다.

스터디 내용을 확인해 보니, 4주 안에 데이터분석가가 되기 위한 기틀을 마련할 수 있는 기회처럼 느껴졌다.

기본적인 SQL은 할 수 있지만, 윈도우 함수와 같은 심화문법 및 분석기법, 실무 액션 아이템 도출까지는 잘 몰랐는데, 이 스터디를 통해 이 모든 것을 배울 수 있다는 것이 매력적이었다.

무엇보다 스터디에 참여하면 내가 고민하던 'BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)' 강의를 30%나 할인해준다는 것이 가장 구미가 당겼다! 추석할인보다 더 할인해주는 건 흔치 않은데, 이건 나를 위한 기회라는 생각이 들었다😭

심지어 강의자가 깔끔한 정리와 설명으로 감동을 주신 카일스쿨님이니, 신청 안할 이유가 없었던 것 같다.

왜 그동안 끝까지 SQL을 파지 못했을까?

당연하게도 SQL의 경우 혼자 학습을 했었는데, 정작 sqld 자격증을 취득하고 나서는 길을 잃었던 것 같다. 기본문법 외 어떤 분야에서 sql을 많이 사용하고, 어떻게 학습하는 것이 좋은지를 몰라 길을 잃었다. 그러다가 윈도우와 같은 고급문법을 공부하다 흥미를 잃었던 것 같다. 당시에는 SQL를 응용할만한 일이 없어 더 그랬던 것이 아닐까 생각해본다.

SQL과 별개로 다양한 플랫폼에서 운영하는 스터디 및 강의를 수강했었는데, 수강자들의 배경지식이 너무 다르다 보니 강의자도 강의의 난이도를 조절하는 것이 어려워지는 경우가 있었다. 그러다 보니, 내가 중점적으로 알고자 하는 내용에 대한 학습보다 전반적인 수박 겉핥기식의 학습이 되버리는 경우가 많았던 것 같다.

이번에는 수박 겉핥기가 아니라, 수박 안쪽까지 먹어보자!🍉

(pixabay : JillWellington에 의해 생성된 이미지)

이번 스터디에는** 혼자가 아닌 여러명에서 수강을 하기도 하고 **짜임새있는 강의와 간결하게 핵심만 알려주는 커리큘럼으로 sql의 왕도를 완주할 수 있지 않을까 기대가 된다.

또한, 기본편 강의를 들은 사람들을 대상으로 하기 때문에 내가** 배우고자 하는 부분에 집중하여 효율적으로 학습**을 할 수 있을 것 같아 여느 스터디보다 열심을 낼 수 있지 않을까 생각된다.

이번에는 수박 안쪽 빨간 부분까지 야무지게 먹어보자 😋🍉

참고

• 빅쿼리 빠짝 스터디 with. 카일스쿨 : https://www.inflearn.com/course/offline/카일스쿨-챌린지
• 초보자를 위한 BigQuery(SQL) 입문 : https://www.inflearn.com/course/초보자를-위한-빅쿼리-sql-입문
• BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석) : https://www.inflearn.com/course/bigquery-활용편/dashboard
• JillWellington에 의해 생성된 이미지 : pixabay

unrecognized configuration parameter "lc_collate"

상황

새로운 버전의 postgresql을 추가

원인

postgresql과 pgadmin4의 호환성이 안 맞는 문제

해결

pgadmin4를 최신버전으로 업데이트

참고

• https://stackoverflow.com/questions/77975676/i-am-getting-an-error-in-pgadmin-as-unrecognized-configuration-parameter-lc-co

📌퍼널(Funnel) : 고객이 구매하기 까지의 단계를 시각화한 모델. 인지 --> 관심-->결정 -->구매 및 충성도 📌리텐션(Retention): 고객이 제품이나 서비스를 지속적으로 사용하는 비율. (다시 서비스를 시용하는 비율)

데이터 기반 의사결정

비즈니스 파악 -> 지표 확인 -> 지표를 기반으로 가설 세우기! -> 가설검증 -> 결과 분석 -> 결론 도출

퍼널분석

퍼널 분석

• 퍼널 분석 : 제품에서 사용자들이 퍼널을 지나는 지 분석. 사용자가 특성 목표와 행동을 달성하기 위해 거치는 여러 단계를 분석
• 예를 들어, 방문 -> 회원가입 -> 상품조회 -> 장바구니 -> 결제
• 뒤에서 앞으로 개선하는 것이 좋음 (결제할 사람이 결제하도록!)

AARRR

• AARRR: 퍼널 분석의 한 형태. 스타트업이나 성장하는 기업이 사용. 사용자 행동을 추적하고 개선하기 위해 사용.

• AARRR 단계 1. Acquisition (획득) : 사용자가 어떻게 웹사이트나 서비스에 방문하게 되었는지를 분석합니다. 예를 들어, 마케팅 캠페인, 소셜 미디어, 검색엔진 등이 있을 수 있습니다. 2. Activation (활성화) : 사용자가 제품이나 서비스에서 첫 번째 긍정적인 경험을 한 순간을 분석합니다. 예를 들어, 앱을 설치하고 첫 사용을 완료한 순간입니다. 3. Retention (유지) : 사용자가 일정 기간 후에도 계속해서 서비스를 사용하는지, 즉 지속적인 사용을 분석합니다.

• 4. Revenue (수익)* : 사용자가 실제로 결제나 구매를 하여 수익이 발생하는 단계를 분석합니다.

• 5. Referral (추천)* : 사용자가 다른 사람을 초대하거나 추천하는 단계를 분석합니다.

• 유입을 늘리기 위해 퍼포먼스 마케팅을 활성화. 대신, 회사 사정이 어려워지면 마케팅 비용이 줄어듦

RARRA

• RARRA 단계
• 1 Retention* : 다시 서비스를 이용하는 지
• 2. Activation* : 고객이 최조로 긍정적인 경험을 하는지
• 3. Referral* : 다른 사람에게 공유 및 초대 하는지
• • 4. Revenue** : 수익이 나는지
• 5. Acquisition* : 제품에 얼마나 접근하는지

• 제품 초기에는 고객이 없어 RARRA가 성립될 수 없음 (retention)

참고

• PM을 위한 데이터 리터러시(프로덕트 데이터 분석) : https://www.inflearn.com/course/lecture?courseSlug=pm-%EB%8D%B0%EC%9D%B4%ED%84%B0-%EB%A6%AC%ED%84%B0%EB%9F%AC%EC%8B%9C&unitId=145142&tab=curriculum&subtitleLanguage=ko

Operand should contain 1 column(s)

상황

WHERE 절에서 서브쿼리 사용시 발생

원인

=를 기준으로 양측의 갯수가 일치하지 않아 발생

• 문제예시 SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE (CAR_ID, START_DATE) IN ( SELECT CAR_ID, END_DATE FROM OTHER_TABLE );

• 해결방안 SELECT CR.CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY CR WHERE EXISTS ( SELECT 1 FROM OTHER_TABLE OT WHERE OT.CAR_ID = CR.CAR_ID

  AND OT.END_DATE = CR.START_DATE

  );

해결

=를 기준으로 양 측의 갯수를 일치시켜 줌

참고

• CHATGPT : Operand should contain 1 column(s) 검색

데이터 베이스 : 실시간 트랜잭션 중심, OLTP, 정규화, 현재 중심의 데이터를 저장 데이터 웨어하우스 : 데이터 분석과 비즈니스 인텔리전스(BI) 중심, OLAP, 비정규화, 과거부터 현재까지의 데이터 저장

데이터 베이스에서 데이터를 분석 및 조작할 경우, 서비스 장애가 발생할 수 있음 이 것을 방지하기 위하여 데이터 웨어하우스로 데이터를 저장

서비스를 위해 데이터베이스를 사용, 대용량 데이터 분석 및 처리를 위해서는 데이터웨어하우스를 사용.

데이터 웨어하우스와 ETL

데이터 웨어하우스는 데이터를 추출, 변환, 불러와서 분석에 사용한다.

• 추출 extract : source에서 데이터를 수집 (운영 DB, log, API)
• 변환 transform : 필요한 형태로 데이터를 변환 (정제, 집계, 통합)
• 로드 load : DB, DW에 데이터를 적재. DW에서 데이터를 불러와 분석

데이터 마트

DW만 사용할 경우, 시간 비용, 데이터 손상, 쿼리의 복잡성 향상. 이를 해결하기 위하여 1)DM 구축 및 2)SQL Join 결과를 Batch로 Table에 저장

데이터 마트 DM : DW의 한 부분. 특정 분야 및 부서 기능 전용 데이터 저장소. 사용자의 필요에 따라 최적화된 상태.

정리

데이터 엔지니어는 다음과 같은 역할을 수행

1. DW에 DB 데이터를 적재(DW)
2. DA를 위한 인프라 구축 (DM, BI도구 )
3. Data Product 개발(AB 테스트, 데이터기반 서비스, GA, Amplitude , 로그 시스템, 머신러닝 딥러닝 서비스)

데이터 엔지니어 역량

• 데이터베이스, 네트워크, 백엔드 개발
• shell, python, scala, java
• 클라우드 서비스, 대용량 처리, kubernetes, docker
• 데이터 아키텍쳐 설계

데이터 엔지니어링 라이브러리 spark, kafka, airflow

참고

• : https://www.youtube.com/watch?v=1Viu7Ce632s
• : https://github.com/Team-Neighborhood/I-want-to-study-Data-Science/wiki/%EB%8D%B0%EC%9D%B4%ED%84%B0-%EC%97%94%EC%A7%80%EB%8B%88%EC%96%B4

SELECT list expression references column which is neither grouped nor aggregated at

원인

해당 칼럼이 groupby 혹은 집계함수에 있어야 함

참고

• https://zzsza.github.io/bigquery/error.html

• 기본 문법

  CASE
  WHEN 조건 
  THEN 결과 
  WHEN 조건 
  THEN 결과 
  WHEN 조건 
  THEN 결과 
  END AS 별칭
  

```

• CASE문은 순서가 중요하다! 위에 있을수록 우선적으로 실행된다 if - elif -else와 동일!