Template Google Sheets Quan Ly Chien Dich Email Marketing 2027: Theo Doi Open Rate va Conversion

Email marketing van la kenh ROI cao nhat trong digital marketing — moi 1 USD dau tu mang lai trung binh 42 USD doanh thu. Nhung de dat duoc dieu do, ban can theo doi chinh xac hieu qua tung chien dich: ai mo email, ai click, ai mua hang. Phan mem chuyen dung nhu HubSpot hay Mailchimp cung cap analytics nhung ban phai tra $50-400/thang. Template Google Sheets nay giup ban co analytics day du voi chi phi bang 0.

Phan 1: Van De Khi Khong Co He Thong Theo Doi Email Marketing

1.1 Gui Email Khong Biet Hieu Qua

Nhieu doanh nghiep nho gui email marketing ma khong biet nguoi nhan co mo email khong, co click vao link khong, co mua hang sau do khong. Ket qua la tiep tuc gui cac chien dich tuong tu du hieu qua thap, lang phi ngan sach va cong suc.

1.2 A/B Testing Khong Co He Thong

De biet tieu de email nao hap dan hon, gio gui nao co open rate cao hon, hay call-to-action nao hieu qua hon, ban can A/B testing. Nhung neu khong co he thong theo doi, ban chi dang oan so chu khong co du lieu thuc te.

1.3 Khong Do Luong ROI Email Marketing

Ngan sach email marketing bao nhieu la du? Khong ai biet neu khong do duoc doanh thu phat sinh tu email. Bo phan marketing khong co con so de bao cao cho giam doc, va giam doc khong biet co nen tiep tuc dau tu vao email hay khong.

1.4 Danh Sach Email Bi Phan Manh

Danh sach email bi luu o nhieu noi: Gmail exports, Excel files, CRM rieng biet. Khong co segmentation ro rang, gui nham doi tuong, tang ty le unsubscribe.

Phan 2: Kien Truc Template Email Marketing Analytics

2.1 Sheet 1: Email Campaign Calendar

Lich chien dich email toan nam, dam bao khong gui qua day hoac qua thu:

Bieu do Calendar view (thang):

Tao Calendar View bang QUERY:
=QUERY(A:K,
  "SELECT D, B, C, F, G
   WHERE MONTH(D)=" & MONTH(NOW()) & "
   AND YEAR(D)=" & YEAR(NOW()) & "
   ORDER BY D ASC",
  1
)

Dem so chien dich gui trong thang (tranh spam):
=COUNTIFS(
  D:D, ">="&DATE(YEAR(NOW()),MONTH(NOW()),1),
  D:D, "<="&EOMONTH(NOW(),0)
)

Canh bao gui qua day (tren 4 email/thang cho 1 segment):
=COUNTIFS(
  D:D, ">="&DATE(YEAR(NOW()),MONTH(NOW()),1),
  F:F, A2,
  I:I, "Sent"
)

2.2 Sheet 2: Campaign KPI Results

Ket qua cu the cua tung chien dich sau khi gui:

Conditional Formatting theo benchmark:

Open Rate:
- Do: F2 < 15% (duoi muc tot)
- Vang: F2 >= 15% AND F2 < 20% (trung binh)
- Xanh la: F2 >= 20% (tot)

CTR:
- Do: H2 < 1% (duoi muc tot)
- Vang: H2 >= 1% AND H2 < 2% (trung binh)
- Xanh la: H2 >= 2% (tot)

Unsubscribe Rate:
- Xanh la: O2 < 0.2% (rat tot)
- Vang: O2 >= 0.2% AND O2 < 0.5% (chap nhan duoc)
- Do: O2 >= 0.5% (can xem lai noi dung)

2.3 Sheet 3: A/B Test Results

Theo doi A/B testing cac yeu to email:

Cau truc A/B Test Tracker:
| Test ID | Campaign | Yeu to test | Phien ban A | Phien ban B | Size A | Size B | Open A | Open B | OR A% | OR B% | Ket qua |
|---------|---------|------------|------------|------------|--------|--------|--------|--------|-------|-------|---------|
| AB-001 | EM-001 | Subject line | "Giam 50% hom nay!" | "Tin nong: Uu dai khung!" | 2500 | 2500 | 625 | 750 | 25% | 30% | B win |
| AB-002 | EM-001 | Gio gui | 10:00 | 20:00 | 2500 | 2500 | 500 | 700 | 20% | 28% | B win |

Statistical Significance (kiem dinh y nghia thong ke):
Kiem dinh chi-square so sanh hai ti le:

Chi-square = (Observed_A - Expected_A)^2 / Expected_A + ...

Don gian hon: dung formula nay:
Expected_A = (Open_A + Open_B) / (Size_A + Size_B) * Size_A
Chi2 = (Open_A - Expected_A)^2 / Expected_A +
       (Open_B - Expected_B)^2 / Expected_B

=CHISQ.DIST.RT(chi2_value, 1)  -- p-value

Neu p-value < 0.05: Ket qua co y nghia thong ke (co the tin tuong)
Neu p-value >= 0.05: Chenh lech co the la ngau nhien

Cong thuc trong Google Sheets:
Expected_A = (E2+F2)/(C2+D2)*C2  -- E2=OpenA, F2=OpenB, C2=SizeA, D2=SizeB
Expected_B = (E2+F2)/(C2+D2)*D2
Chi2 = (E2-Expected_A)^2/Expected_A + (F2-Expected_B)^2/Expected_B
P_value = CHISQ.DIST.RT(Chi2, 1)

=IF(P_value<0.05, "Co y nghia thong ke ✓", "Chua co y nghia - can them data")

2.4 Sheet 4: Audience Segment Tracker

Quan ly cac nhom doi tuong (segment) email:

Cac segment co ban:
| Segment ID | Ten segment | Mo ta | So nguoi | Nguon | Ngay tao | Cap nhat gan nhat |
|------------|------------|-------|---------|-------|---------|------------------|
| SEG-001 | Khach hang VIP | Mua tren 5 trieu/lan | 1,247 | CRM export | 01/01/2027 | 01/03/2027 |
| SEG-002 | Khach hang moi | Mua lan dau trong 30 ngay | 342 | Website | | |
| SEG-003 | Khach hang tiem nang | Da dang ky, chua mua | 5,823 | Landing page | | |
| SEG-004 | Khach hang ngui | Khong mo email 6 thang | 2,156 | Filter CRM | | |
| SEG-005 | B2B | Doanh nghiep (email cong ty) | 891 | Sales team | | |

Phan tich health cua tung segment:
Ty le hoat dong (mo email trong 3 thang gan):
=COUNTIFS(
  'Email Activity'!B:B, A2,  -- Segment ID
  'Email Activity'!D:D, ">="&DATE(YEAR(NOW()),MONTH(NOW())-3,1),
  'Email Activity'!E:E, "Opened"
) / C2 * 100  -- C2: tong so nguoi trong segment

Toc do tang truong segment:
=('Cuoi Ky'!C2 - 'Dau Ky'!C2) / 'Dau Ky'!C2 * 100

Ty le unsubscribe theo segment (canh bao neu cao):
=COUNTIFS(
  'Unsubscribe Log'!B:B, A2,
  'Unsubscribe Log'!C:C, ">="&DATE(YEAR(NOW()),MONTH(NOW()),1)
) / C2 * 100

2.5 Sheet 5: Email ROI Calculator

Tinh ROI chinh xac cho tung chien dich:

CHI PHI CHIEN DICH:
Chi phi nhan luc (viet content, thiet ke):
= So gio x Don gia gio

Chi phi cong cu (Mailchimp, SMTP...):
= Phi thang / So chien dich thang do

Chi phi quang cao (neu co Landing Page rieng):
= Chi phi qua PPC / So nguoi tu email

Tong chi phi = Nhan luc + Cong cu + Quang cao

DOANH THU TU CHIEN DICH:
Cach 1: UTM Tracking (chinh xac nhat):
- Gan UTM parameters vao moi link trong email
- utm_source=email&utm_medium=newsletter&utm_campaign=sale_33_2027
- Import data tu Google Analytics vao Google Sheets

Cach 2: Coupon Code Tracking:
- Moi chien dich email co coupon code rieng: SALE33-EMAIL
- Dem don hang dung code nay tu CRM/website

Cach 3: Uoc tinh (don gian nhat):
- Conversion Rate trung binh cua email: 2%
- So conversion tu chien dich: Open Rate * Conversion Rate * So email gui
- Doanh thu: So conversion * AOV (Gia tri don hang TB)

Cong thuc ROI trong Google Sheets:
Chi_phi = B2 * C2 + D2 / E2 + F2  -- NL: SoGio*DonGia + CC + QC
Doanh_thu = G2  -- nhap truc tiep tu he thong

ROI = (Doanh_thu - Chi_phi) / Chi_phi * 100
Revenue_per_email = Doanh_thu / Emails_sent

Payback Period (thoi gian hoan von):
=Chi_phi / (Doanh_thu / 30)  -- Tinh theo ngay

2.6 Sheet 6: List Growth va Unsubscribe Log

Theo doi su tang truong cua danh sach email:

List Growth Tracker (theo thang):
| Thang | Dau ky | Them moi | Unsubscribe | Hard Bounce | Cuoi ky | Tang truong % |
|-------|--------|---------|------------|------------|---------|--------------|
| T1/2027 | 10,000 | 450 | 23 | 12 | 10,415 | +4.15% |
| T2/2027 | 10,415 | 380 | 31 | 8 | 10,756 | +3.27% |
| T3/2027 | 10,756 | ? | ? | ? | ? | ? |

Cong thuc tang truong tinh:
=((Cuoi_ky - Dau_ky) / Dau_ky) * 100

Net List Growth Rate (tru cac mat mat):
=(Them_moi - Unsubscribe - Hard_bounce) / Dau_ky * 100

Nguon subscriber moi (de biet kenh nao hieu qua):
=COUNTIF(
  'Subscriber Log'!E:E, "Organic"
) -- Cac nguon: Organic/Facebook/Google/Popup/Referral

Unsubscribe Analysis:
=COUNTIF(
  'Unsubscribe Log'!C:C, "Noi dung khong lien quan"
) -- Ly do: Noi dung KLQ / Gui qua nhieu / Khong biet dang ky / Khac

Canh bao List Hygiene (can don dep danh sach):
=IF(
  COUNTIFS('Activity'!E:E,"No Open",
           'Activity'!D:D,"<"&DATE(YEAR(NOW()),MONTH(NOW())-6,1)) >
  COUNTA('Subscriber Log'!A:A) * 0.2,
  "20%+ subscribers bi ngu - can Win-back campaign!",
  "Danh sach khoe manh"
)

Phan 3: Tich Hop Voi Google Forms Cho Unsubscribe

Tao Google Form "Huy Dang Ky Nhan Email" the hien minh bach:
Cac truong:
1. Email cua ban (Short answer - pre-filled tu URL param)
2. Ly do huy dang ky (Multiple choice):
   - Nhan qua nhieu email
   - Noi dung khong con phu hop
   - Toi khong nho dang ky
   - Noi dung chat luong kem
   - Ly do khac
3. Ban co muon giam tan suat khong thay vi huy hoan toan? (Multiple choice):
   - Giu nguyen (huy hoan toan)
   - Giam xuong 1 email/tuan
   - Chi nhan email quan trong (khuyen mai lon, san pham moi)

Link unsubscribe: Them vao footer moi email:
https://forms.gle/YOUR_FORM_ID?entry.email={subscriber_email}

Xu ly unsubscribe request (Apps Script):
function processUnsubscribeRequests() {
  const formSheet = SpreadsheetApp.openById('FORM_SPREADSHEET_ID')
    .getSheetByName('Form Responses 1');
  const subscriberSheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('Subscriber Log');
  
  const responses = formSheet.getDataRange().getValues();
  const subscribers = subscriberSheet.getDataRange().getValues();
  
  for (let i = 1; i < responses.length; i++) {
    const email = responses[i][1];  // Email
    const reason = responses[i][2]; // Ly do
    const preference = responses[i][3]; // Tuy chon
    
    // Tim subscriber trong danh sach
    for (let j = 1; j < subscribers.length; j++) {
      if (subscribers[j][2] === email) {  // Cot C: email
        if (preference === 'Giu nguyen (huy hoan toan)') {
          subscriberSheet.getRange(j+1, 5).setValue('Unsubscribed'); // Cot E: status
          subscriberSheet.getRange(j+1, 6).setValue(new Date()); // Ngay huy
          subscriberSheet.getRange(j+1, 7).setValue(reason); // Ly do
        } else if (preference === 'Chi nhan email quan trong') {
          subscriberSheet.getRange(j+1, 5).setValue('VIP Only');
        }
        break;
      }
    }
    
    // Danh dau da xu ly
    formSheet.getRange(i+1, 5).setValue('Processed');
  }
}

Phan 4: Automated Follow-up Sequence Tracker

Theo doi chuoi email tu dong (email sequences/drip campaigns):

Cau truc Email Sequence:
| Sequence ID | Ten | Mo ta | So email | Trigger | Trang thai |
|-------------|-----|-------|---------|---------|-----------|
| SEQ-001 | Welcome Series | Chao mung subscriber moi | 5 email | New subscriber | Active |
| SEQ-002 | Abandoned Cart | Nhac nho gio hang bi bo | 3 email | Gio hang bo qua 1h | Active |
| SEQ-003 | Win-back | Lay lai khach hang ngu | 4 email | Khong mo email 90 ngay | Active |
| SEQ-004 | Post-purchase | Sau khi mua hang | 3 email | Thanh toan thanh cong | Active |

Theo doi tu tung email trong sequence:
| Sequence | Email So | Tieu de | Delay | Emails Sent | Opens | CTR | Unsubscribes |
|---------|---------|--------|-------|------------|-------|-----|-------------|
| SEQ-001 | Email 1 | Chao mung! | 0h | 1,247 | 62% | 8% | 0.2% |
| SEQ-001 | Email 2 | Huong dan su dung | 24h | 1,087 | 48% | 12% | 0.3% |
| SEQ-001 | Email 3 | Template mien phi | 72h | 967 | 42% | 15% | 0.4% |

Phan tich drop-off rate (mat nguoi sau moi email):
=1 - (Emails_Sent_Email2 / Emails_Sent_Email1)

Best performing email trong sequence (cao nhat CTOR):
=ARRAYFORMULA(IF(I:I=MAX(I:I),"Best Email",""))

Conversion rate cua ca sequence:
=SUMIF(Conversions_By_Sequence, "SEQ-001", Conversion_Count) /
 MAX(Emails_Sent_By_Step) * 100  -- Max = email dau tien gui

Thoi gian trung binh den conversion:
=AVERAGEIFS(
  'Conversion Log'!D:D,  -- Ngay convert
  'Conversion Log'!B:B, "SEQ-001"
) - MIN('Sequence Calendar'!F:F)  -- Ngay bat dau sequence

Phan 5: Dashboard Email Marketing Tong Hop

Dashboard chinh hien thi tat ca KPIs quan trong:

=== HIEU SUAT THANG NAY ===
Tong chien dich da gui: =COUNTIFS(I:I,"Sent",D:D,">="&DATE(YEAR(NOW()),MONTH(NOW()),1))
Tong email da gui: =SUMIFS(G:G,I:I,"Sent",D:D,">="&DATE(YEAR(NOW()),MONTH(NOW()),1))
Open rate trung binh: =AVERAGEIFS('KPI Results'!F:F,'KPI Results'!D:D,">="&DATE...)
CTR trung binh: =AVERAGEIFS('KPI Results'!H:H,...)
Tong doanh thu tu email: =SUMIFS('KPI Results'!L:L,...)
ROI trung binh: =AVERAGEIFS('KPI Results'!Q:Q,...)

=== SO SANH VS THANG TRUOC ===
Open rate thang nay vs thang truoc:
=This_Month_OR - Last_Month_OR
Mui ten len/xuong: =IF(A2>0,"Tang "&TEXT(A2,"0.0%"),"Giam "&TEXT(ABS(A2),"0.0%"))

=== TOP CHIEN DICH ===
Open Rate cao nhat: =MAXIFS('KPI Results'!F:F,...)
Doanh thu cao nhat: =MAXIFS('KPI Results'!L:L,...)
ROI cao nhat: =MAXIFS('KPI Results'!Q:Q,...)

=== CANH BAO ===
Chien dich open rate duoi 15%:
=FILTER('KPI Results'!B:B, 'KPI Results'!F:F<15, 'KPI Results'!I:I="Sent")

Segment unsubscribe rate cao:
=FILTER('Segment Tracker'!B:B, 'Segment Tracker'!Unsub:Unsub>=0.5)

Phan 6: Best Practices Email Marketing Viet Nam

6.1 Gio Gui Email Tot Nhat (Du Lieu Thuc Te VN)

Ket qua A/B testing gio gui email B2C Viet Nam:
| Gio gui | Open Rate TB | Dac diem |
|---------|-------------|---------|
| 7:00-8:00 | 24% | Doc email sang soi, truoc di lam |
| 10:00-11:00 | 22% | Break buoi sang |
| 12:00-13:00 | 18% | An trua, doc dien thoai |
| 20:00-21:00 | 26% | Thu gian buoi toi - TOT NHAT |
| 21:00-22:00 | 23% | Truoc khi ngu |
| Cuoi tuan | +15% | Nhieu thoi gian ranh hon |

Gio gui tot nhat: 20:00-21:00 (toi)
Ngay gui tot nhat: Thu 3, Thu 5 (cho B2B); Cuoi tuan (cho B2C)

Tan suat gui khuyen nghi:
B2C (giam gia, san pham tieu dung): 1-2 lan/tuan
B2B (thong tin, giao duc): 1 lan/tuan
Newsletter: 2 lan/thang

6.2 Tieu De Email Hieu Qua (A/B Test Insights)

Cac dang tieu de co open rate cao (da kiem chung):
1. Co so cu the: "Giam 50% - Chi con 24h"
2. Cau hoi: "Ban da xem qua san pham nay chua?"
3. Tao urgency: "Chi con 10 xuat - Dat ngay!"
4. Ca nhan hoa: "[Ten], qua tang dac biet danh rieng cho ban"
5. Emoji (khong lam dung): "Flash Sale: Giam 50% hom nay!"

Cac dang tieu de co open rate thap:
- Qua dai (>60 ky tu, bi cat tren mobile)
- Chu hoa toan bo: "GIAM GIA 50% HOC NGAY"
- Spam trigger words: "Mien phi", "Click ngay", "Kha nang han che"
- Mieu ta kho hieu: "Su kien thang 3 nam 2027 tai cong ty chung toi"

Checker tieu de email:
Do dai tieu de:
=LEN(B2)
=IF(LEN(B2)>60, "Qua dai - cat tren mobile", IF(LEN(B2)<30, "Qua ngan", "OK"))

Kiem tra spam words:
=IF(OR(
  ISNUMBER(SEARCH("mien phi", LOWER(B2))),
  ISNUMBER(SEARCH("click ngay", LOWER(B2))),
  ISNUMBER(SEARCH("100%", B2))
), "Co the bi vao spam - kiem tra lai", "OK")

6.3 Phap Ly Email Marketing Viet Nam

Theo Nghi dinh 13/2023/ND-CP (PDPA - Luat bao ve du lieu ca nhan VN):
- PHAI co su dong y ro rang truoc khi gui email marketing
- PHAI co nut unsubscribe trong moi email
- PHAI xu ly yeu cau huy dang ky trong vong 10 ngay
- KHONG duoc mua/ban danh sach email khi khong co su dong y

Theo doi compliance trong Google Sheets:
| Subscriber | Nguon dong y | Ngay dong y | Loai dong y | Chung co |
|-----------|-------------|------------|-------------|---------|
| a@b.com | Website popup | 01/01/2027 | Double opt-in | Log #001 |

Double opt-in flow (khuyen nghi):
1. Nguoi dung nhap email vao form
2. He thong gui email xac nhan
3. Nguoi dung click link xac nhan trong email
4. He thong ghi nhan dong y (co timestamp, IP, nguon)
5. Chi khi do moi them vao danh sach chinh thuc

Tracking compliance:
Ty le double opt-in (phai >50%):
=COUNTIF('Subscriber Log'!G:G, "Double Opt-in") /
 COUNTA('Subscriber Log'!A:A) * 100

Phan 7: Tich Hop Voi He Thong Ben Ngoai

Nhap du lieu Analytics tu Mailchimp/SMTP2GO:

Mailchimp dat file CSV bao cao hang tuan:
File: campaign_report_YYYY-MM.csv
Cac cot: Campaign, Sent, Opens, Clicks, Bounces, Unsubscribes

Import vao Google Sheets:
1. Import manual: File > Import > Upload
2. Import tu URL (Mailchimp API):
   =IMPORTDATA("https://api.mailchimp.com/3.0/reports?api_key=YOUR_KEY")
3. Dung Apps Script de tu dong import theo lich:
   function fetchMailchimpData() {
     const apiKey = 'your-api-key';
     const response = UrlFetchApp.fetch(
       'https://api.mailchimp.com/3.0/reports',
       { headers: { 'Authorization': 'apikey ' + apiKey } }
     );
     const data = JSON.parse(response.getContentText());
     // Xu ly va ghi vao sheet...
   }

Tich hop voi Google Analytics 4 qua IMPORTDATA:
=IMPORTDATA("https://analyticsdata.googleapis.com/v1beta/...")

Su dung UTM Builder sheet de tao link tracking:
Base URL: https://sheetstore.vn
Source: =IF(B2="Newsletter","email","email")
Medium: =IF(C2="Newsletter","newsletter",C2)
Campaign: =LOWER(SUBSTITUTE(D2," ","-"))
UTM URL: =A2&"?utm_source="&E2&"&utm_medium="&F2&"&utm_campaign="&G2

Phan 8: FAQ - Cau Hoi Thuong Gap

Q: Template nay co the thay the hoan toan Mailchimp/ActiveCampaign khong?

Template thay the duoc phan analytics va tracking cua Mailchimp. Tuy nhien, ban van can mot cong cu gui email (Mailchimp free tier, Brevo mien phi den 300 email/ngay, SMTP2GO...). Template nay giup ban phan tich hieu qua chien dich chi tiet hon, khong phu thuoc vao dashboard cua cong cu gui.

Q: Lam sao do luong doanh thu tu email neu website khong co GA integration?

Su dung coupon code rieng cho moi chien dich email (VD: EMAIL-MAR27). Trong Google Sheets, nhap so coupon duoc dung va gia tri don hang tuong ung tu he thong ban hang (Shopify, WooCommerce, MISA...). Cach nay don gian hon UTM tracking nhung van chinh xac cao.

Q: Lam sao phat hien nguoi unsubscribe do noi dung hay do gui qua nhieu?

Phan tich unsubscribe theo tan suat: neu unsubscribe tang cao sau cac chien dich gui nhieu (3+ email/tuan) — van de la frequency. Neu unsubscribe cao bat ke frequency — van de la noi dung hoac relevance. Tracking ly do unsubscribe qua Google Form la cach chinh xac nhat.

Q: Co the tu dong hoa reporting hang tuan khong?

Co, dung Apps Script voi time-driven trigger chay moi thu hai sang. Script tu dong tong hop KPIs tuan truoc, tao bao cao PDF hoac Google Slides va gui email cho marketing manager. Code mau co trong template (sheet "Automation Scripts").

Ket Luan

Email marketing hieu qua dua tren du lieu, khong phai cam giac. Voi template Google Sheets nay, ban co the:

• Theo doi chinh xac open rate, CTR, conversion va ROI cua tung chien dich
• Thuc hien A/B testing co y nghia thong ke, khong phai oan so
• Phan loai doi tuong chinh xac, gui dung tin den dung nguoi
• Tinh ROI email marketing de bao cao voi ban lanh dao
• Dam bao tuan thu phap ly PDPA ve bao ve du lieu ca nhan

Tai template mien phi tai SheetStore va bien email marketing thanh kenh doanh thu co the do luong ngay hom nay!