import pandas as pd
from itertools import product

# 1. 定义维度
cities = ["广州", "深圳", "杭州", "武汉", "成都", "南京", "苏州", "青岛"]
services = ["Google Ads 代运营", "SEO 优化", "网站建设", "数字营销咨询"]
intents = ["公司", "服务", "价格", "哪家好", "推荐", "怎么样"]

# 2. 生成关键词矩阵
keywords = []
for city, service, intent in product(cities, services, intents):
    keyword = f"{city}{service}{intent}"
    keywords.append({
        'city': city,
        'service': service,
        'intent': intent,
        'keyword': keyword,
        'match_type': 'PHRASE',  # 短语匹配
        'bid': 2.0  # 默认出价
    })

df = pd.DataFrame(keywords)
print(f"共生成 {len(df)} 个地域关键词")

# 3. 导出 CSV（可直接导入 Google Ads）
df.to_csv("geo_keywords.csv", index=False, encoding='utf-8-sig')
print("已导出: geo_keywords.csv")
print(df.head(10))

def score_keywords(df, search_volume_df, competition_df):
    """关键词优先级评分"""
    # 合并搜索量和竞争度数据
    df = df.merge(search_volume_df, on='keyword', how='left')
    df = df.merge(competition_df, on='keyword', how='left')
    
    # 归一化
    df['sv_norm'] = df['search_volume'] / df['search_volume'].max()
    df['comp_norm'] = 1 - (df['competition'] / df['competition'].max())  # 竞争度越低分越高
    
    # 综合评分
    df['score'] = df['sv_norm'] * 0.4 + df['comp_norm'] * 0.3 + 0.2 + 0.1
    
    # 筛选
    df['priority'] = pd.cut(df['score'], 
                            bins=[0, 0.3, 0.5, 0.7, 1.0],
                            labels=['低', '中', '高', '核心'])
    
    return df.sort_values('score', ascending=False)

# 筛选核心关键词
# scored = score_keywords(df, sv_data, comp_data)
# core_keywords = scored[scored['priority'] == '核心']