signaturehelper.py
import hashlib
import hmac
import base64
class Signature:
@staticmethod
def generate(timestamp, method, uri, secret_key):
message = “{}.{}.{}”.format(timestamp, method, uri)
hash = hmac.new(bytes(secret_key, “utf-8”), bytes(message, “utf-8”), hashlib.sha256)
hash.hexdigest()
return base64.b64encode(hash.digest())
naver_stat.py
import time
import requests
import json
import signaturehelper # 기존에 사용하시던 파일이 있어야 합니다.
def get_header(method, uri, api_key, secret_key, customer_id):
timestamp = str(round(time.time() * 1000))
signature = signaturehelper.Signature.generate(timestamp, method, uri, secret_key)
return {
‘Content-Type’: ‘application/json; charset=UTF-8’,
‘X-Timestamp’: timestamp,
‘X-API-KEY’: api_key,
‘X-Customer’: str(customer_id),
‘X-Signature’: signature
}
BASE_URL = ‘https://api.searchad.naver.com’
API_KEY = ‘0100000000b762fc1623971fdfdc6bd721762379d545ec0620b9c44b3150fff8591918ef4c’
SECRET_KEY = ‘AQAAAACrg1MV1zsIdwSDxAXGlRut5XIiGOQmUeXjkIM3oew7jg==’
CUSTOMER_ID = ‘2017471’
#### [특정 날짜를 조회할 때] #####
# 1. 설정값 준비
uri = ‘/stats’
method = ‘GET’
entity_id = ‘cmp-a001-01-000000008695213’ # 실제 조회할 ID 입력
# 조회 필드 설정
fields = [“impCnt”, “clkCnt”, “salesAmt”, “ctr”, “cpc”]
# 2. 파라미터 구성 (기간 직접 지정)
params = {
‘id’: entity_id,
‘fields’: json.dumps(fields),
# datePreset 대신 timeRange를 사용합니다.
‘timeRange’: json.dumps({“since”: “2026-01-01”, “until”: “2026-01-06”}),
‘timeIncrement’: ‘allDays’ # 해당 기간 전체 합계를 가져올 경우
}
# 3. 요청 및 결과 확인
headers = get_header(method, uri, API_KEY, SECRET_KEY, CUSTOMER_ID)
r = requests.get(BASE_URL + uri, params=params, headers=headers)
print(“#response body = “, json.dumps(r.json(), indent=4, ensure_ascii=False))
##### [최근 7일 조회할 때] #####
# # 1. 설정값 준비
# uri = ‘/stats’
# method = ‘GET’
# # 조회할 엔티티 ID (캠페인ID나 광고그룹ID 등을 입력하세요)
# entity_id = ‘cmp-a001-01-000000008695213’
# # 조회하고 싶은 필드들 (노출수, 클릭수, 비용, 클릭률, 평균클릭비용)
# fields = [“impCnt”, “clkCnt”, “salesAmt”, “ctr”, “cpc”]
# # 2. 파라미터 구성 (Query String)
# params = {
# ‘id’: entity_id,
# ‘fields’: json.dumps(fields), # 리스트를 JSON 문자열로 변환
# ‘datePreset’: ‘last7days’, # 최근 7일 (today, yesterday 등으로 변경 가능)
# ‘timeIncrement’: ‘allDays’ # 기간 전체 합계를 가져올 때 (일별은 ‘1’)
# }
# # 중요: GET 방식의 Signature 생성 시 uri에는 파라미터가 포함되지 않은 순수 경로(‘/stats’)만 들어갑니다.
# headers = get_header(method, uri, API_KEY, SECRET_KEY, CUSTOMER_ID)
# # 3. 요청 보내기
# r = requests.get(BASE_URL + uri, params=params, headers=headers)
# # 4. 결과 출력
# print(“#response status_code = {}”.format(r.status_code))
# if r.status_code == 200:
# print(“#response body = “, json.dumps(r.json(), indent=4, ensure_ascii=False))
# else:
# print(“Error Message: “, r.text)
ad_management_sample.py
# import time
# import random
# import requests
# import signaturehelper
# def get_header(method, uri, api_key, secret_key, customer_id):
# timestamp = str(round(time.time() * 1000))
# signature = signaturehelper.Signature.generate(timestamp, method, uri, SECRET_KEY)
# return {‘Content-Type’: ‘application/json; charset=UTF-8’, ‘X-Timestamp’: timestamp, ‘X-API-KEY’: API_KEY, ‘X-Customer’: str(CUSTOMER_ID), ‘X-Signature’: signature}
# BASE_URL = ‘https://api.searchad.naver.com’
# API_KEY = ‘0100000000b762fc1623971fdfdc6bd721762379d545ec0620b9c44b3150fff8591918ef4c’
# SECRET_KEY = ‘AQAAAACrg1MV1zsIdwSDxAXGlRut5XIiGOQmUeXjkIM3oew7jg==’
# CUSTOMER_ID = ‘2017471’
# # 평균 입찰가
# uri = ‘/estimate/average-position-bid/keyword’
# method = ‘POST’
# r = requests.post(BASE_URL + uri, json={‘device’: ‘PC’, ‘items’: [{‘key’: ‘강남성형’, ‘position’: 1}, {‘key’: ‘게스트하우스’, ‘position’: 2}, {‘key’: ‘자전거여행’, ‘position’: 3}]}, headers=get_header(method, uri, API_KEY, SECRET_KEY, CUSTOMER_ID))
# print(“#response status_code = {}”.format(r.status_code))
# print(“#response body = {}”.format(r.json()))
################260116 업데이트######################
import time
import requests
import signaturehelper
# 설정 정보
BASE_URL = ‘https://api.searchad.naver.com’
API_KEY = ‘0100000000b762fc1623971fdfdc6bd721762379d545ec0620b9c44b3150fff8591918ef4c’
SECRET_KEY = ‘AQAAAACrg1MV1zsIdwSDxAXGlRut5XIiGOQmUeXjkIM3oew7jg==’
CUSTOMER_ID = ‘2017471’
def get_header(method, uri, api_key, secret_key, customer_id):
timestamp = str(round(time.time() * 1000))
signature = signaturehelper.Signature.generate(timestamp, method, uri, secret_key)
return {
‘Content-Type’: ‘application/json; charset=UTF-8’,
‘X-Timestamp’: timestamp,
‘X-API-KEY’: api_key,
‘X-Customer’: str(customer_id),
‘X-Signature’: signature
}
# 평균 입찰가 조회 요청
uri = ‘/estimate/average-position-bid/keyword’
method = ‘POST’
payload = {
‘device’: ‘PC’,
‘items’: [
{‘key’: ‘강남성형’, ‘position’: 1},
{‘key’: ‘게스트하우스’, ‘position’: 2},
{‘key’: ‘자전거여행’, ‘position’: 3}
]
}
r = requests.post(
BASE_URL + uri,
json=payload,
headers=get_header(method, uri, API_KEY, SECRET_KEY, CUSTOMER_ID)
)
print(f”#response status_code = {r.status_code}”)
print(f”#response body = {r.json()}”)
bigquery_key.json
{
“type”: “service_account”,
“project_id”: “api-practice-20260103”,
“private_key_id”: “4e54e760d73e785832fc5202852142f36471fb7b”,
“private_key”: “—–BEGIN PRIVATE KEY—–\nMIIEvwIBADANBgkqhkiG9w0BAQEFAASCBKkwggSlAgEAAoIBAQCiaPYye31aEYGl\nXIwahmCh/0N5YV1bUNfzJQvjQn4xWD77p4K2w/6n5r73H72TEb10c7XJGfMa/8jI\nxUukzbTPzh+3vPN3UjOHQSmWI0CELrTV6fjh7zGTZPaGXWBC8TyIwA5wzj+jviCA\nIy4FSPjIFQGh9HuwPolySIvsj9zI5D9dh6HDm4AMMV0rlp9pASYInil3s65VJlpP\nTrbDcDvfv19ueVNsrdD9VHnYLd6la+3GA7Bq4NfBXcWms1G6+WY03YKWC9BLAeus\nChV8Jm8nBXgif8bx1ZZTj5CKw6zclk6TIkPIhnEdvgLMbg2GAybrUEvswTT+a7qq\nzPlWeIclAgMBAAECggEAGa+aCH/pYl5Ub8/ERMmt7NEPXdodRrzxen4IdEfm52z6\ngRiMHtW+p9VhrHnzpJfwq8NFI+obvdBodABoJ0d1akxCTe/vXZa9mY2fwyQOJxmv\n9gkMGMeYwqP8LWIFQHEgzsv5PLQR8pa2Uy29MI7K8fIIkRG2ojgxysZYgvB5zbey\n/NRv+M87hDmM6TNT7SUQQjGaB+joz/2k1gw0s51IgNvLGIc102aGuyk08l4d1Unq\ntQxJjJX3f33tTCmhY6nwFNnag1D4Z8HUaLY2P/1Cw2fMae5SRGqukDSpOoJTCclp\nFOcTpn+xAZ0TnsBHDQBG1cxgYjYVd1GQZrYhGKGl3QKBgQDaIO9HxRuaFkKDMngm\ndZC63FLjT/I+ZE+wBcGJ5DkgT38noXKyk8vY+FQymHHrxiO31TI63UmMjkgG8olw\nCex4sBRNJS/7Ah7Yr4Yj6fHTeZTrzNHLCbN+NJB0WTW3nBHhDDPbK8tro5TNK5oR\nzUv3+SSg/Jav5LldHJ81rv2XrwKBgQC+m4aS0b2pQ+E9PhiXUYQX6HNg/WyBh1C0\na9tNhx229oUgv838Y7RMTG5CEqIVnOxhu2U9g+vPWZRUeSkghG/aAxiBtKTpGYJs\nwIg3qRahV3mXoOr1sHB2i9ZYzKwToOE5Lubi+qaIqwmKTIja9uX3Za4lOwpYgneQ\nsv26/a8vawKBgQCDis0B+QoWOEpQGEWA2/E13txVnMa3fYLY4f+/ClSw+X/zBFfI\n/Xw3EjqkcG5zAwBTQkySVxSdoMgq+GSNnk5u0bDMfRqcQsEbZ257hf+BypZWRSRX\nTzhhaY2bvJi6a3IUx2kWSSjZdsKroBm/lpCzsBm4w9qTpFlsn3AbzbW8nQKBgQCY\nGy6yPXRX/lZhdAtVvfRmy9Y/QR105FIQGL5CSR/6YRHuB4JO9hXsiVW3nn0AOGEe\nwNfS1lK4/mhzXNjoHSfRV4UYBNtRNU0ykhZiLKpliJP836qA9BNbVs5vwK1VYJ46\n7uXLnnCFQdB0MA1Kh5sO6XKmPU5NQZg+vcfjkAxcDwKBgQCk9hBpafo2NLaPUl7g\ncmtw7RO0BeOHflbHAYFtdilYLDhglsLd6wvrqvsLExdaXJPNQHUP4JUQ1VK0e4yT\nUulWT0pKoW2iwhiKKrXikdM8MzSQraYITniITYiDO+CaZoLJcpbu6Te7VAhuC6sw\n7WioeKk55NzPWaL/xx+ZSXzH7Q==\n—–END PRIVATE KEY—–\n”,
“client_email”: “id-api-practice-20260103@api-practice-20260103.iam.gserviceaccount.com”,
“client_id”: “112353028205533869479”,
“auth_uri”: “https://accounts.google.com/o/oauth2/auth”,
“token_uri”: “https://oauth2.googleapis.com/token”,
“auth_provider_x509_cert_url”: “https://www.googleapis.com/oauth2/v1/certs”,
“client_x509_cert_url”: “https://www.googleapis.com/robot/v1/metadata/x509/id-api-practice-20260103%40api-practice-20260103.iam.gserviceaccount.com”,
“universe_domain”: “googleapis.com”
}
bigquery_naver_api.py
import time
import json
import requests
import pandas as pd
from google.cloud import bigquery
from google.oauth2 import service_account
import signaturehelper
# — [1] 설정 정보 (기존과 동일) —
BASE_URL = ‘https://api.searchad.naver.com’
API_KEY = ‘0100000000b762fc1623971fdfdc6bd721762379d545ec0620b9c44b3150fff8591918ef4c’
SECRET_KEY = ‘AQAAAACrg1MV1zsIdwSDxAXGlRut5XIiGOQmUeXjkIM3oew7jg==’
CUSTOMER_ID = ‘2017471’
KEY_PATH = “bigquery_key.json”
PROJECT_ID = “api-practice-20260103”
TABLE_ID = f”{PROJECT_ID}.naver_ads.daily_performance”
ENTITY_ID = ‘cmp-a001-01-000000008695213’
FIELDS = [“impCnt”, “clkCnt”, “salesAmt”, “ctr”, “cpc”]
DATE_RANGE = {“since”: “2026-01-01”, “until”: “2026-01-11”}
# — [2] 함수 정의 —
def get_header(method, uri, api_key, secret_key, customer_id):
timestamp = str(round(time.time() * 1000))
signature = signaturehelper.Signature.generate(timestamp, method, uri, secret_key)
return {
‘Content-Type’: ‘application/json; charset=UTF-8’,
‘X-Timestamp’: timestamp,
‘X-API-KEY’: api_key,
‘X-Customer’: str(customer_id),
‘X-Signature’: signature
}
def fetch_and_upload_naver_stats():
uri = ‘/stats’
method = ‘GET’
params = {
‘id’: ENTITY_ID,
‘fields’: json.dumps(FIELDS),
‘timeRange’: json.dumps(DATE_RANGE),
‘timeIncrement’: ‘1’
}
headers = get_header(method, uri, API_KEY, SECRET_KEY, CUSTOMER_ID)
print(“1. 네이버 API 요청 중…”)
r = requests.get(BASE_URL + uri, params=params, headers=headers)
if r.status_code != 200:
raise Exception(f”API Error: {r.text}”)
data_json = r.json()
if not data_json.get(‘data’):
print(“>> 데이터가 없습니다.”)
return
# 3. 데이터프레임 변환 및 컬럼명 수정
df = pd.DataFrame(data_json[‘data’])
if ‘dateStart’ in df.columns:
df = df.rename(columns={‘dateStart’: ‘date’})
# — [핵심 수정: 데이터 타입 변환] —
# 1) date 컬럼을 문자열에서 datetime64[ns] 타입으로 변환 (BigQuery DATE 타입과 호환)
df[‘date’] = pd.to_datetime(df[‘date’]).dt.date
# 2) 숫자형 컬럼 변환
numeric_cols = [‘impCnt’, ‘clkCnt’, ‘salesAmt’, ‘cpc’, ‘ctr’]
for col in numeric_cols:
if col in df.columns:
df[col] = pd.to_numeric(df[col], errors=’coerce’).fillna(0)
df[‘entity_id’] = ENTITY_ID
# 필요한 컬럼만 필터링
target_columns = [‘date’, ‘impCnt’, ‘clkCnt’, ‘salesAmt’, ‘ctr’, ‘cpc’, ‘entity_id’]
df = df[target_columns]
# 5. BigQuery 적재
print(“2. BigQuery 데이터 적재 시작…”)
credentials = service_account.Credentials.from_service_account_file(KEY_PATH)
client = bigquery.Client(credentials=credentials, project=credentials.project_id)
job_config = bigquery.LoadJobConfig(
schema=[
bigquery.SchemaField(“date”, “DATE”),
bigquery.SchemaField(“impCnt”, “INTEGER”),
bigquery.SchemaField(“clkCnt”, “INTEGER”),
bigquery.SchemaField(“salesAmt”, “INTEGER”),
bigquery.SchemaField(“ctr”, “FLOAT”),
bigquery.SchemaField(“cpc”, “FLOAT”),
bigquery.SchemaField(“entity_id”, “STRING”),
],
write_disposition=”WRITE_APPEND”,
)
try:
# 데이터프레임을 BigQuery에 로드
job = client.load_table_from_dataframe(df, TABLE_ID, job_config=job_config)
job.result()
print(f”✅ 성공! {len(df)}건의 데이터가 {TABLE_ID}에 적재되었습니다.”)
except Exception as e:
print(f”❌ BigQuery 적재 실패: {e}”)
if __name__ == “__main__”:
fetch_and_upload_naver_stats()
답글 남기기