알아서 타겟 맞춤 CRM 문자발송 자동화

📌 소개

왜 만들었나?

Salesforce, HubSpot, Channel Talk, Stibee...

CRM(고객관계관리) 하면 떠오르는 유명한 툴들이 많습니다. 이 툴들은 정말 강력하고, 대기업에서는 필수로 쓰고 있죠. 하지만 솔직히 말해서:

월 구독료가 부담됩니다 (유료 플랜 기준 월 수만원~수십만원)
기능이 너무 많아서 오히려 복잡합니다
우리 비즈니스에 딱 맞게 커스텀하기 어렵습니다

특히 1인 사업자, 소규모 팀, 스타트업 초기 단계에서는 "고객 명단 관리하고 + 가끔 문자나 이메일 보내는 것" 정도면 충분한 경우가 많거든요. 그런데 이것 때문에 월 10만원씩 내기엔... 좀 아깝잖아요?

그래서 생각했습니다.

"구글시트로 직접 만들면 되지 않을까?"

시작은 스터디 과제였습니다

저는 현재 "구글 앱스스크립트로 CRM 자동화" 라는 주제로 스터디를 진행하고 있습니다.

1주차: 단체 이메일 발송
2주차: 흩어진 고객 DB 한 곳에 모으기
3주차: ??? (이번 글의 주제!)
4주차: 대시보드/리포트 자동화

1~2주차에서 "발송 기능"과 "데이터 통합"을 다뤘으니, 3주차에는 이 둘을 합쳐서 뭔가 실용적인 걸 만들고 싶었어요.

그러다 문득 이런 생각이 들었습니다:

"내가 구글시트에 '3개월 이상 연락 안 한 고객한테 웰컴백 쿠폰 보내줘'라고 쓰면, AI가 알아서 해주면 안 되나?"

AI가 마케팅 문자도 예쁘게 써주고
고객 DB에서 조건에 맞는 사람만 자동으로 추려내고
문자까지 발송해주는 것

결론부터 말하면, 됩니다. 그것도 무료(문자비용 제외)로, 바이브코딩으로요.

뭘 만들었나? (결과물 미리보기)

완성된 시스템의 플로우를 한 문장으로 요약하면:

📱 "자연어로 타겟 조건 입력 → AI가 메시지 작성 + 고객 필터링 → 문자 발송"

구글시트 안에서 모든 게 해결됩니다.

📌 진행 방법

2-1. 전체 구조 설계

본격적으로 개발하기 전에, 전체 시스템 구조를 먼저 그려봤습니다. (깨지네요 흑)

사용한 기술 스택

구분

기술

역할

비용

인터페이스

구글시트

사용자 입력/결과 표시

무료

데이터 저장

구글시트

고객 DB

무료

프롬프트 저장

구글독스

AI 명령어 템플릿

무료

백엔드 로직

구글 앱스스크립트

전체 자동화 코드

무료

Gemini API

메시지 생성, 조건식 생성

무료 (일정 한도)

문자 발송

네이버 SENS API

SMS/LMS 발송

종량제 (건당 약 10원)

총 비용: 거의 0원 (문자 발송 비용만 실비)

2-2. 구글시트 인터페이스 만들기

시트 구조

두 개의 시트를 사용합니다:

1) CRM메세지발송 시트 - 사용자 인터페이스

행

B열 (라벨)

C열 (입력/결과)

진행상황

(실시간 로그 표시)

1. CRM 메세지

내가 대충 쓴 내용

(사용자가 초안 입력)

내용 작성 프롬프트

📎 스마트칩 (구글독스 연결)

AI가 다듬어준 내용 결과

(AI 출력)

AI가 추천하는 타겟

(AI 출력)

2. 타겟 고객

타겟 설명

(사용자가 자연어로 입력)

고객 DB

📎 스마트칩 (시트 연결)

시트명

(고객DB 시트 이름)

조건식 생성 프롬프트

📎 스마트칩 (구글독스 연결)

조건식

(AI가 생성한 JSON)

2) 고객DB 시트 - 고객 데이터

이메일

닉네임

성명

전화번호

관심사

처음

마지막

응답수

[email protected]

테스터

홍길동

01012345678

CRM, 업무자동화

2025-10-04

2025-11-07

여기서 핵심 컬럼은 세 가지입니다:

관심사: 쉼표로 구분된 관심 분야 (타겟팅에 활용)
처음: 첫 유입/가입 날짜 (신규 고객 판별)
마지막: 마지막 연락 날짜 (휴면 고객 판별)

스마트칩이 뭔가요?

구글시트에서 @를 누르면 다른 파일을 연결할 수 있는 기능입니다.

예를 들어 C7 셀에 @마케팅 프롬프트라고 입력하면, 해당 구글독스가 칩(chip) 형태로 연결됩니다. 클릭하면 바로 그 문서로 이동할 수 있고, 코드에서도 이 연결 정보를 읽어올 수 있어요.

왜 스마트칩을 썼냐면:

프롬프트를 독스에서 편하게 수정할 수 있음
여러 프롬프트를 만들어두고 필요할 때 바꿔 끼울 수 있음
시트가 지저분해지지 않음

2-3. 1단계 - AI 마케팅 메시지 생성

흐름

C6 (내가 대충 쓴 초안)
        ↓
C7 스마트칩에서 프롬프트 가져오기
        ↓
    Gemini API 호출
        ↓
C8 (AI가 다듬어준 메시지)

사용자가 C6에 이렇게 입력합니다:

3개월동안 안오신분들한테 20프로 할인해준다고 보내고싶어

1단계 버튼을 누르면, AI가 이렇게 다듬어줍니다:

[웰컴백] 오랜만에 인사드려요 :) 돌아오시는 고객님께 20% 할인쿠폰을 드립니다. 쿠폰코드: WELCOME20

프롬프트 핵심 포인트 (메시지 다듬기용)

📎 전체 프롬프트는 [여기 링크]에서 확인하세요

프롬프트 설계할 때 신경 쓴 부분들:

1. 역할 부여

당신은 전문 마케팅 카피라이터입니다.

2. 제약 조건 명시 - SMS 90바이트 제한, 스팸처럼 보이면 안 됨

- SMS: 90바이트(한글 약 45자) 이내 권장
- 스팸성 문구 (무료, 공짜, 대박 등 과용) 피하기

3. 예시 포함 - 입력-출력 예시를 보여주면 AI가 형식을 잘 따라함

### 입력
"3개월동안 안오신분들한테 20프로 할인해준다고 보내고싶어"

### 출력
[웰컴백] 오랜만에 인사드려요 :) 돌아오시는 고객님께 20% 할인쿠폰을 드립니다.

4. 출력 형식 지정 - 설명 없이 메시지만 달라고 명시

다듬어진 메시지만 출력하세요. 설명이나 부가 텍스트 없이.

진행상황 로그

실행 중에 C2 셀에 실시간으로 진행상황이 표시됩니다:

[17:45:01] 🚀 1단계 시작: 마케팅 메시지 생성
[17:45:02] 📝 C6에서 초안 메시지 읽는 중...
[17:45:02] 🔗 C7에서 프롬프트 문서 링크 읽는 중...
[17:45:03] 📄 구글 문서에서 프롬프트 내용 가져오는 중...
[17:45:04] 🤖 Gemini API 호출 중... (메시지 다듬기)
[17:45:08] 💾 C8에 결과 저장 중...
[17:45:08] ✅ 1단계 완료! C8에서 다듬어진 메시지를 확인하세요.

이렇게 로그를 찍어두면 어디서 오류가 났는지 바로 알 수 있어서, 디버깅할 때 정말 편합니다.

2-4. 2단계 - AI 조건식 생성 + 고객 필터링

여기가 이 프로젝트의 핵심입니다.

문제 상황

"3개월 이상 연락 안 한 CRM 관심 고객"을 추려내려면, 원래는 이런 조건식을 직접 작성해야 합니다:

customers.filter(c => 
  (new Date() - new Date(c.마지막)) / (1000*60*60*24) >= 90 &&
  c.관심사.includes('CRM')
)

비개발자 입장에서 이거 직접 쓰라고 하면... 막막하죠.

해결책: Gemini Structured Output

Gemini API에는 Structured Output이라는 기능이 있습니다. 쉽게 말해:

"AI야, 대답할 때 이 형식(JSON)에 맞춰서 대답해"

라고 미리 틀을 정해주는 겁니다.

예를 들어 AI한테 "3개월 이상 연락 안 한 CRM 관심 고객"이라고 물으면, 아무 형식으로 대답하는 게 아니라:

{
  "conditions": [
    {"field": "마지막", "operator": "older_than_days", "value": "90"},
    {"field": "관심사", "operator": "contains", "value": "CRM"}
  ],
  "logic": "AND"
}

이렇게 정해진 JSON 형식으로 대답하게 됩니다. 이러면 코드에서 바로 파싱해서 사용할 수 있죠!

프롬프트 핵심 포인트 (조건식 생성용)

📎 전체 프롬프트는 [여기 링크]에서 확인하세요

1. 컬럼 정보를 상세히 제공 - AI가 어떤 필드가 있는지 알아야 조건을 만들 수 있음

| 컬럼명 | 타입    | 설명                |
| 관심사 | String | 쉼표로 구분된 관심 분야 |
| 마지막 | Date   | 마지막 응답 날짜       |

2. 연산자를 미리 정의 - 코드에서 처리할 수 있는 것만 사용하게 제한

| 연산자     | 설명                  |
| contains | 포함 (부분 일치)         |
| older_than_days | N일 이전 (오래된) |

3. 변환 예시를 충분히 - 다양한 케이스를 보여주면 정확도가 올라감

입력: "3개월 이상 연락 안한 CRM 관심 고객"
출력:
{
  "conditions": [
    {"field": "마지막", "operator": "older_than_days", "value": "90"},
    {"field": "관심사", "operator": "contains", "value": "CRM"}
  ],
  "logic": "AND"
}

4. 주의사항 명시 - "3개월=90일"처럼 변환 규칙을 알려줌

사이드바 미리보기

2단계 실행이 완료되면 사이드바가 열립니다:

📝 발송 메시지: 1단계에서 만든 메시지 (수정 가능)
📞 발신번호: 기본값 설정, 변경 가능
🎯 발송 대상: 조건에 맞게 필터링된 고객 목록
- 체크박스로 선택/해제
- X 버튼으로 개별 삭제
- 수동으로 추가도 가능

2-5. 3단계 - 사이드바에서 문자 발송

사이드바에서 최종 확인 후 "문자 발송하기" 버튼을 누르면:

체크된 고객만 필터링
전화번호에서 하이픈(-) 제거
메시지 길이에 따라 SMS(90바이트 이하) 또는 LMS 자동 선택
네이버 SENS API 호출
발송 완료 표시

네이버 SENS API 설정

네이버 클라우드 플랫폼에서 SENS(Simple & Easy Notification Service)를 사용합니다.

필요한 정보:

Access Key
Secret Key
Service ID
발신번호 (사전 등록 필요)

이 정보들은 구글 앱스스크립트의 "스크립트 속성"에 저장해서 보안을 유지합니다.

2-6. 핵심 코드 설명

📎 전체 코드는 [여기 링크]에서 확인하세요

코드 구성

파일

역할

Code.gs

메인 로직 (1단계, 2단계, API 호출 등)

sidebar.html

사이드바 UI (미리보기, 발송 버튼)

핵심 1: 셀 위치를 CONFIG로 관리

셀 위치가 바뀌면 코드 여기저기 수정해야 하는데, CONFIG에 모아두면 한 곳만 바꾸면 됩니다.

const CONFIG = {
  INTERFACE_SHEET: 'CRM메세지발송',
  CELLS: {
    STATUS: 'C2',           // 진행상황 로그
    RAW_MESSAGE: 'C6',      // 초안 메시지
    MESSAGE_PROMPT: 'C7',   // 프롬프트 (스마트칩)
    REFINED_MESSAGE: 'C8',  // AI 결과
    // ...
  }
};

핵심 2: 실시간 진행상황 로그

SpreadsheetApp.flush()를 쓰면 셀에 즉시 반영됩니다. 디버깅할 때 정말 유용해요.

function updateStatus(message) {
  const timestamp = Utilities.formatDate(new Date(), 'Asia/Seoul', 'HH:mm:ss');
  sheet.getRange('C2').setValue(`[${timestamp}] ${message}`);
  SpreadsheetApp.flush(); // 즉시 반영!
}

핵심 3: 스마트칩에서 URL 추출

구글시트의 스마트칩(연결된 문서)에서 URL을 가져오려면 Sheets API를 써야 합니다.

function getSmartChipUrl(sheet, cellAddress) {
  const obj = Sheets.Spreadsheets.get(spreadsheetId, {
    ranges: [cellAddress],
    fields: "sheets(data(rowData(values(chipRuns))))"
  });
  // chipRuns에서 richLinkProperties.uri 추출
}

핵심 4: Gemini Structured Output 호출

일반 텍스트가 아니라 정해진 JSON 형식으로 응답받으려면 responseSchema를 설정합니다.

const payload = {
  contents: [{ parts: [{ text: prompt }] }],
  generationConfig: {
    responseMimeType: "application/json",
    responseSchema: {
      type: "object",
      properties: {
        conditions: { type: "array", items: { ... } },
        logic: { type: "string", enum: ["AND", "OR"] }
      }
    }
  }
};

핵심 5: 조건식으로 고객 필터링

AI가 만들어준 JSON 조건식을 파싱해서 실제 필터링합니다.

function filterCustomers(customers, headers, filterConditions) {
  return customers.filter(row => {
    const results = filterConditions.conditions.map(cond => {
      const cellValue = row[headers.indexOf(cond.field)];
      
      switch(cond.operator) {
        case 'contains': 
          return String(cellValue).includes(cond.value);
        case 'older_than_days':
          const days = (new Date() - new Date(cellValue)) / (1000*60*60*24);
          return days >= Number(cond.value);
        // ...
      }
    });
    
    return filterConditions.logic === 'AND' 
      ? results.every(r => r) 
      : results.some(r => r);
  });
}

📌 결과와 배운 점

3-1. 시행착오 모음

개발하면서 겪었던 에러, 여러분은 이런 실수 안 하시길 바라며 공유합니다.

1) 스마트칩 URL 형식 문제

문제: 스마트칩에서 URL을 추출했는데, 문서 ID 파싱이 안 됨

원인: 구글독스 URL이 두 가지 형식이 있었음

일반: https://docs.google.com/document/d/문서ID/edit
로그인: https://docs.google.com/document/u/0/d/문서ID/edit

로그인 상태에서 생성된 스마트칩은 /u/0/ 가 포함되어 있어서, 기존 정규식으로는 파싱이 안 됐습니다.

해결: 정규식 수정

// Before
url.match(/\/document\/d\/([a-zA-Z0-9_-]+)/)

// After (u/0 패턴 처리 추가)
url.match(/\/document\/(?:u\/\d+\/)?d\/([a-zA-Z0-9_-]+)/)

2) 빈 고객 데이터가 사이드바에 표시됨

문제: 고객 목록에 이름/전화번호가 비어있는 항목이 보임

원인: 고객 DB에 빈 행이 있었음

해결: 필터 추가

.filter(c => c.name && c.phone)  // 이름과 전화번호 있는 것만

3) 에러 메시지만 나오고 위치를 모름

문제: "잘못된 인수: id" 에러가 나는데 어디서 난 건지 모름

해결: 에러 로그에 줄 번호 표시 추가

const stack = error.stack || '';
const lineMatch = stack.match(/:(\d+):/);
const lineNum = lineMatch ? lineMatch[1] : '?';
updateStatus('❌ 오류 발생 (Line ' + lineNum + '): ' + error.message);

4) 셀 위치 바꿨는데 코드 수정 안 함

문제: 인터페이스 레이아웃을 수정했는데 코드가 안 돌아감

원인: CONFIG의 CELLS 값을 안 바꿈

교훈: 셀 위치는 CONFIG에서 한 번에 관리하자!

3-2. 배운 점 & 꿀팁

꿀팁 1: Gemini Structured Output 활용법

자연어를 정형 데이터로 바꿀 때 정말 유용합니다. 핵심은:

스키마를 명확히 정의할 것
프롬프트에 예시를 충분히 넣을 것
enum으로 선택지를 제한할 것 (operator 같은 거)

꿀팁 2: 진행상황 로그는 필수

SpreadsheetApp.flush()를 쓰면 실시간으로 셀에 로그가 찍힙니다. 디버깅할 때 정말 편해요.

꿀팁 3: 바이브코딩할 때 Claude한테 요청하는 법

이번 프로젝트는 대부분 Claude한테 시켰습니다. 잘 시키려면:

전체 구조를 먼저 설명해주기
이미 가지고 있는 코드가 있으면 공유하기
에러나면 에러 메시지 그대로 복붙하기
수정 요청은 구체적으로: "C2에 로그 찍어줘" 처럼

3-3. 한계점 & 앞으로의 계획

현재 한계

SENS API 한도 (대량 발송 시 제한)
발송 결과 추적 기능 없음 (성공/실패 여부)
예약 발송 미지원

개선 계획

발송 로그 시트 추가 (누구한테, 언제, 뭘 보냈는지)
예약 발송 기능
카카오 알림톡 연동
대시보드 연결해서 발송 통계 시각화

📌 도움 받은 자료

📌 마무리

처음엔 "이게 쉽게 될까?" 싶었는데, 막상 해보니 금방 됩니다. 😄

핵심은 text 2 SQL. 자연어로 디비 쿼리를 만드는 부분입니다. 자연어를 코드가 이해할 수 있는 JSON으로 변환해주니까, 비개발자도 "3개월 이상 연락 안 한 고객"이라고만 쓰면 AI가 알아서 조건식을 만들어주거든요.

유료 CRM 툴 없이도, 구글시트 + API 조합으로 충분히 실용적인 자동화가 가능하니, 프리랜서 분들인 1인 사업자, 소규모 회사에서는 구현해보시면 좋겠습니다!

🎁 AI를 활용해 나에게 필요한 자동화를 만드는데 관심있으시다면, 같이 이야기 나눠요!
오픈카톡방 입장

📮 4만 5천 명이 받는 AI 뉴스레터 구독하기

알아서 타겟 맞춤 CRM 문자발송 자동화 - 구글 Appscript