광고 좀 쳐본 마케터라면 나름의 규칙을 가지고 UTM을 만든다.
문득 남들은 어떤 규칙으로 UTM을 구성하는지 궁금해졌다.
(UTM 모르는 사람은 아래 글 참고)
https://blog.ab180.co/posts/utm-code-olbareuge-sayonghagi
UTM 코드 뜯어보기
마케터들의 GA 사용이 증가함에 따라, GA에서 사용하는 코드인 utm 코드를 통한 트래킹이 필수화 되고 있어 utm 코드에 대한 이해가 매우 중요합니다.
blog.ab180.co
물론 링크를 눈으로 보고 찾으면 보이겠지만
이걸 어느 세월에 눈으로 하나 하나 보고 찾나? 자동 추출기가 필요하다
역시 완성된 시트 링크를 먼저 던지고 시작한다
UTM parameter extract
공유용 랜딩 URL,utm_source,utm_medium,utm_campaign,utm_content,utm_term <a href="https://sjdb.tistory.com/132?utm_source=newjeans&utm_medium=ive&utm_campaign=lesserafim&utm_content=aespa">https://sjdb.tistory.com/132?utm_source=newjeans&utm_medium=ive
docs.google.com
이 작업의 목표는 다음과 같다.
UTM 정보를 포함하고 있는 URL이면, 그대로 붙여 넣으면 자동으로 utm 파라미터 별 값을 자동으로 추출한다.
일단 추출 대상이 될 URL을 대충 만들어봤다.
| https://sjdb.tistory.com/132?utm_source=newjeans&utm_medium=ive&utm_campaign=lesserafim&utm_content=aespa |
이 URL을 붙여넣었을 때, 자동으로
newjeans / ive / lesserafim / aespa 가 각각
utm source / medium / campaign / content의 값으로 추출되도록 할 것이다.
1. 추출할 값의 위치를 찾아서 앞부분 잘라내기
URL 내에서 'utm_source' 라는 텍스트의 위치를 찾아서, 그 위치를 기준으로 필요 없는 앞부분을 잘라낼 것이다.
find 함수는 주어진 텍스트에서 찾는 텍스트가 몇 번째 글자부터 시작되는지 숫자로 반환한다.
주어진 URL 에서 'utm_source'라는 텍스트는 30번째 텍스트부터 시작한다.
그러면 'newjeans' 는 어디서부터 시작할까?
'utm_source'라는 텍스트가 시작하는 지점으로부터 'utm_source'의 글자수+1 만큼 더 오른쪽에서 시작한다.
len 함수는 주어진 텍스트의 길이를 반환한다.
주어진 URL 에서 'utm_source='의 다음 글자는 41번째 텍스트부터 시작한다.
이제 mid 함수로 newjeans만 추출해 보자.
| =mid(B3,find("utm_source",B3)+len("utm_source")+1,8) |
이 수식은
주어진 URL에서, "utm_source"라는 텍스트가 끝나는 지점으로부터 한 글자 더 오른쪽으로 간 위치부터 8글자를 반환해 줘
라는 뜻.
짠, newjeans가 추출됐다. 하지만 위 수식은 한계가 있다. utm_source의 값이 8글자일 때만 제대로 워킹한다. utm_source 값의 글자수가 달라도 워킹하는 수식이 되어야 한다.
source 값이 100글자를 초과할 경우는 웬만하면 없을 거라고 판단하고, 최대 100글자까지 반환하도록 수식을 수정한 뒤 필요 없는 뒷부분을 잘라내는 방식으로 해야겠다.
| =mid(B3,find("utm_source",B3)+len("utm_source")+1,100) |
주어진 URL에서, "utm_source"라는 텍스트가 끝나는 지점으로부터 한 글자 더 오른쪽으로 간 위치부터 최대 100글자를 반환해 줘
주어진 URL에서 utm_source 뒷부분의 길이가 100글자가 안되기 때문에 뒷부분 전체가 반환됐다.
newjeans&utm_medium=ive&utm_campaign=lesserafim&utm_content=aespa
이렇게 1차적으로 추출된 값을 편의상 '1차 추출값'이라고 하겠다.
이제 1차 추출값에서 필요 없는 뒷부분을 잘라내겠다.
2. 1차 추출값에서 쓸데없는 뒷부분 잘라내기
UTM 파라미터는 URL의 마지막 부분이 아닌 이상, 무조건 & 를 기준으로 그다음 파라미터와 나뉘게 된다.
1차 추출값은 무조건 맨 왼쪽에서 newjeans로부터 시작하므로, 1차 추출값의 맨 왼쪽부터 첫 &가 나오기 전까지의 텍스트만 잘라내면 된다.
left 함수는 텍스트의 맨 왼쪽부터 주어진 숫자만큼의 글자를 반환한다.
주어진 숫자는 1차 추출값에서 & 가 시작되는 위치보다 1이 작으면 된다.
| =left(mid(B3,find("utm_source",B3)+len("utm_source")+1,100),find("&",mid(B3,find("utm_source",B3)+len("utm_source")+1,100))-1) |
1차 추출값의 맨 왼쪽부터, 1차 추출값 텍스트에서 '&'가 시작되는 위치에서 한 글자 앞까지의 글자를 반환해 줘
자 다시 newjeans가 반환됐다. 하지만 이 수식도 한계가 있다. 이 수식은 1차 추출값 내에 & 가 한 개도 없으면 오류가 뜬다.
주어진 URL을 https://sjdb.tistory.com/132?utm_source=newjeans로 짧게 바꿨더니 이렇게 오류가 나온다.
이러면 실제 UTM 사용법을 고려했을 때, 아래와 같은 두 가지 경우 문제가 생길 것으로 예상된다.
1. UTM 파라미터를 꼭 전부 쓰라는 법은 없다. 귀찮아서 utm 파라미터를 utm_source 한 가지만 썼을 경우 추출되지 않는다.
2. URL에 UTM 파라미터들을 넣을 때 사실 정해진 순서라는 게 없다. utm_source가 맨 마지막 파라미터일 경우 추출되지 않는다.
1차 추출값 내에 & 가 한 개도 없어도 제대로 추출되게 해야겠다.
left 함수로 맨 왼쪽부터 추출되는 '글자수'를 지정할 때, 1차 추출값 내 '&' 텍스트의 여부에 따라 다른 숫자가 나오도록 조건식을 걸면 된다.
수식이 기니까 잘라서 해석해 보자
| if(countif(mid(B3,find("utm_source",B3)+len("utm_source")+1,100),"*&*")>0,find("&",mid(B3,find("utm_source",B3)+len("utm_source")+1,100))-1,100) |
만약 1차 추출값 내에 '&'의 개수가 0개 초과이면, 1차 추출값에서 '&'가 시작되는 위치에서 1을 뺀 값을 반환하고, 1차 추출값에서 '&'의 개수가 0 이하이면 100을 반환해라
| =left(mid(B3,find("utm_source",B3)+len("utm_source")+1,100), 위 수식에서 반환된 값) |
1차 추출값의 왼쪽에서부터 위 수식에서 반환된 값만큼의 글자수를 반환해라
이렇게 하면 다시 문제없이 newjeans가 반환되는 것을 볼 수 있다.
utm_source 파라미터가 아예 없을 때 오류를 띄우지 않기 위해 iferror로 한 번 감싸주면 완성
| =iferror(left(mid(B3,find("utm_source",B3)+len("utm_source")+1,100),if(countif(mid(B3,find("utm_source",B3)+len("utm_source")+1,100),"*&*")>0,find("&",mid(B3,find("utm_source",B3)+len("utm_source")+1,100))-1,100)),"") |
위 최종수식을 굳이 풀버전으로 해석해 보면 다음과 같다.
주어진 URL에서 'utm_source'라는 텍스트의 위치로부터 'utm_source' 텍스트의 글자수+1 만큼 오른쪽으로 간 지점의 텍스트부터 최대 100글자를 추출한 뒤,추출한 그 텍스트의 왼쪽부터 다시 글자를 추출하는데, 만약 주어진 URL에서 'utm_source' 라는 텍스트의 위치로부터 'utm_source' 텍스트의 글자수+1 만큼 오른쪽으로 간 지점의 텍스트부터 최대 100글자를 추출한 그 텍스트 내에 '&'가 1개 이상 있을 경우 주어진 URL에서 'utm_source' 라는 텍스트의 위치로부터 'utm_source' 텍스트의 글자수+1 만큼 오른쪽으로 간 지점의 텍스트부터 최대 100글자를 추출한 그 텍스트에서 '&' 이 시작하는 위치에 1을 더한 숫자만큼의 글자수를 반환하고, 그렇지 않을 경우 100글자를 반환하되, 여태까지 서술한 이 모든 연산 과정에서 하나라도 오류가 있을 경우 공백을 반환해라
3. 모든 파라미터에 적용
여기까지 해서 완성한 수식은 사실 utm_source 파라미터에 대해서만 작동한다. 이제 utm_source 뿐만 아니라 아래와 같은 포맷에서, C열~G열의 모든 칸에서 각 열의 헤더에 해당하는 파라미터에 대해 똑같이 작동하는 수식을 만들고 싶다.
그러면 위의 최종수식에서, "utm_source"라는 텍스트가 직접 들어간 모든 부분을 "utm_source" 대신, 각 열의 헤더값이 참조되도록 바꾸면 된다.
(수식 복붙이 용이하도록 B3도 열고정되도록 $B3으로 바꿨다.)
| =iferror(left(mid($B3,find(C$2,$B3)+len(C$2)+1,100),if(countif(mid($B3,find(C$2,$B3)+len(C$2)+1,100),"*&*")>0,find("&",mid($B3,find(C$2,$B3)+len(C$2)+1,100))-1,100)),"") |
이제 수식을 칸칸이 복사만 해도 목적에 맞게 작동한다.
다른 URL을 갖고 와도 utm 파라미터별 값을 잘 추출한다.
참고로 GA4에서는 utm 파라미터의 종류가 더 많아지고, 아예 커스텀 파라미터를 넣을 수 있다는 소문도 있다. 하지만 위 수식은 커스텀으로 어떤 파라미터를 만들든 헤더값에 넣기만 하면 같은 수식을 복붙만 하면 똑같이 작동한다. 다행히 GA4의 시대에도 쓰일 수 있겠다.
끝.
