[멋쟁이사자처럼 부트캠프 그로스 마케팅] 데이터 분석 개론 : Pandas - SQL 연동 (2)

 

 

학습목표

• Pandas와 SQL을 활용한 데이터 분석 프로젝트
• 웹 애플리케이션 적용

 

 

 

1. Pandas - SQL 응용 데이터 분석 프로젝트

마케팅 지표(CTR, CVR, ROAS) 계산을 테이블에 추가할 수 있다.

1. CTR (Click-Through Rate): clicks / impressions * 100 → 광고 클릭률 (%)
2. CVR (Conversion Rate): conversions / clicks * 100 → 전환율 (%)
3. ROAS (Return On Ad Spend): revenue / cost * 100 → 광고 투자 수익률 (%)

import mysql.connector
import pandas as pd

# 1. MariaDB 연결 설정
db_config = {
    "host": "localhost",      # MariaDB 서버 주소
    "user": "user",           # 사용자 이름
    "password": "1234",   # 비밀번호
    "database": "backend"     # 사용할 데이터베이스
}

# MariaDB 연결
conn = mysql.connector.connect(**db_config)
cursor = conn.cursor()

# 2. 기존 테이블 삭제 후 새로 생성
cursor.execute("DROP TABLE IF EXISTS growth_marketing;")
cursor.execute("""
CREATE TABLE growth_marketing (
    id INT AUTO_INCREMENT PRIMARY KEY,
    campaign_name VARCHAR(100),
    date DATE,
    impressions INT,
    clicks INT,
    conversions INT,
    cost DECIMAL(10,2),
    revenue DECIMAL(10,2)
);
""")

# 3. 샘플 데이터 생성 (pandas DataFrame 활용)
data = {
    "campaign_name": ["Google Ads", "Facebook Ads", "Instagram Ads", "YouTube Ads", "LinkedIn Ads"],
    "date": ["2025-02-01", "2025-02-02", "2025-02-03", "2025-02-04", "2025-02-05"],
    "impressions": [10000, 15000, 12000, 18000, 11000],
    "clicks": [500, 750, 600, 900, 550],
    "conversions": [50, 70, 60, 80, 55],
    "cost": [100000, 150000, 120000, 180000, 110000],
    "revenue": [500000, 750000, 600000, 900000, 550000]
}

df = pd.DataFrame(data)

# 4. 데이터 삽입
insert_query = """
INSERT INTO growth_marketing (campaign_name, date, impressions, clicks, conversions, cost, revenue)
VALUES (%s, %s, %s, %s, %s, %s, %s)
"""
values = [tuple(row) for row in df.to_numpy()] #2차원 배열로 변환하고 각행을 순차적으로 가져온 후 각행(row)을 튜플로 변환
cursor.executemany(insert_query, values)
conn.commit()

print("데이터 삽입 완료")

# 5. 데이터 조회 및 지표 계산
cursor.execute("SELECT * FROM growth_marketing")
rows = cursor.fetchall()

# 컬럼명 가져오기
column_names = [desc[0] for desc in cursor.description]
df_result = pd.DataFrame(rows, columns=column_names)

# 6. 그로스 마케팅 지표 계산 (CTR, CVR, ROAS)
df_result["CTR (%)"] = (df_result["clicks"] / df_result["impressions"]) * 100
df_result["CVR (%)"] = (df_result["conversions"] / df_result["clicks"]) * 100
df_result["ROAS (%)"] = (df_result["revenue"] / df_result["cost"]) * 100

# 7. 결과 출력 (pandas 기본 출력)
print("\n=== Growth Marketing Performance Metrics ===")
print(df_result[["campaign_name", "date", "CTR (%)", "CVR (%)", "ROAS (%)"]].to_string(index=False))

# CSV로 저장 (필요한 경우)
df_result.to_csv("growth_marketing_metrics.csv", index=False, encoding="utf-8")
print("지표 데이터를 'growth_marketing_metrics.csv' 파일로 저장하였습니다.")

# 연결 종료
cursor.close()
conn.close()

 

 encoding="utf-8-sig" 

: utf-8 인코딩 + BOM(문자 인코딩 식별)

특히 Windows 환경에서 CSV 저장 시 한글 인코딩 오류 방지에 유용하다.

 

업로드 결과

 

 

2. 웹 어플리케이션 적용 : 게시판 페이지 실습

[Step 1] Web Service 설정

Google Cloud : VPC 네트워크 - 방화벽 - 방화벽 규칙 만들기

 

✅ 방화벽 규칙 만드는 이유

• GCP는 기본적으로 보안을 위해 모든 외부 트래픽을 차단하기 때문에 Flask 웹 서버를 외부에서 접속 가능하게 하기 위해.
• 특정 포트( :5000, :5001 <-이거), 특정 IP(소스 IPv4 범위에서 지정), 혹은 모든 IP 허용(0.0.0.0/0)하도록 설정 가능

 

[Step 2] Web Integration

: 서버에 Flask 디렉토리 구조 만들기

# 디렉토리 경로
lab/site1
│
├── app.py
├── templates/
│   ├── index.html
│   ├── view.html
│   └── write.html
├── static/
│   ├── common.css
│   └── styles.css
│
└── uploads/

777 권한을 부여하면 리스트에 디렉토리 이름이 초록 배경으로 바뀜

 

 

 

[Step 3] 게시판 글을 저장하는 테이블 생성

웹 페이지에 글을 작성하면 요 테이블에 계속 저장됨

 

 

 

[Step 4] Flask 애플리케이션에서 사용할 코드 파일 생성

#app.py
from flask import Flask, render_template, request, redirect, url_for, send_from_directory
from werkzeug.utils import secure_filename
import os
import mysql.connector

app = Flask(__name__)
app.config['UPLOAD_FOLDER'] = 'uploads'
app.config['MAX_CONTENT_LENGTH'] = 16 * 1024 * 1024  # 16 MB

# Database connection configuration
DB_USER = 'user'
DB_PASSWORD = '1234'
DB_HOST = 'localhost'
DB_DATABASE = 'backend'

# Database connection
def get_db_connection():
    conn = mysql.connector.connect(
        user=DB_USER,
        password=DB_PASSWORD,
        host=DB_HOST,
        database=DB_DATABASE
    )
    return conn

@app.route('/')
def index():
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT id, title, author, created_at FROM posts")
    posts = cursor.fetchall()
    conn.close()
    return render_template('index.html', posts=posts)

@app.route('/write', methods=['GET', 'POST'])
def write():
    if request.method == 'POST':
        title = request.form['title']
        content = request.form['content']
        author = '관리자'  
        file = request.files['image']
        filename = None
        if file:
            filename = secure_filename(file.filename)
            file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
        
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute("INSERT INTO posts (title, content, author, image) VALUES (%s, %s, %s, %s)",
                       (title, content, author, filename))
        conn.commit()
        conn.close()
        return redirect(url_for('index'))
    return render_template('write.html')

@app.route('/view/<int:post_id>')
def view(post_id):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT title, content, author, created_at, image FROM posts WHERE id=%s", (post_id,))
    post = cursor.fetchone()
    conn.close()
    return render_template('view.html', post=post)

@app.route('/uploads/<filename>')
def uploaded_file(filename):
    return send_from_directory(app.config['UPLOAD_FOLDER'], filename)

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5002, debug=True)

-> Flask 애플리케이션이 데이터베이스와 연결되어 사용자의 요청을 받아 데이터를 CRUD(create, read, update, delete) 작업을 수행할 수 있도록 하는 핵심 파일

 

이하 웹 페이지를 구성하는 html 파일 생성

• templates/index.html: 게시글 목록을 보여주는 메인 페이지
• templates/view.html: 게시글 상세보기 페이지
• templates/write.html: 새 게시글 작성 페이지
• static/style.css: index.html 스타일을 위한 CSS 파일
• static/common.css: view.html, write.html 스타일을 위한 CSS 파일

 

 

[Step 5] 디렉토리에 업로드

 

각 파일을 경로에 맞는 디렉토리에 업로드하면 리스트도 수정됨

 

-> 웹 페이지 확인

http://34.64.80.51:5002/

(SSH 외부 ip 주소 : 포트 번호)

 

결과~

 

 

 

 

---

 

 

웹 페이지 조작하면 터널에 어쩌구저쩌구 로그 쌓이는 게 신기했다

근데 난 분명히 html을 배웠는데

배웠다는 것만 기억남

제길