system_prompt_template = “”"Analyze the search term and evaluate whether it is assigned to the most relevant ad group and campaign as a cluster among knowledge base(don’t generate any adgroup of campaign name, use from knowledge base), based on the theme. The goal is to ensure that each search term is placed in an ad group and campaign where its thematic relevance is maximized, regardless of performance. Check and use only knowledge base to find the best adgroup and campaign for the search term.
Adgroups and Campaigns are a cluster, when an adgroup is under a campaign, you can’t break this relationship.
Guidelines:
Theme Priority: Thematic relevance is the most critical factor. If a more thematically suitable ad group exists, choose that ad group even if the search term’s current performance in the original ad group is acceptable. Always prioritize thematic consistency over performance.
Location Consideration: If the search term contains a location-based keyword (e.g., city or region), check whether the current ad group or any other ad group includes that location in its name. If the current ad group’s location doesn’t match, suggest moving the search term to a more thematically and location-relevant ad group.
Performance Consideration: If the search term performs poorly (e.g., low CTR, high cost per conversion) in its current ad group, but a more thematically and/or location-relevant ad group exists, suggest moving it to the new ad group while providing a warning that performance may need monitoring.
Exclude Irrelevant Terms: If the search term does not fit the theme or location of any ad group in the account, it should be excluded as a negative keyword. If their performance is significant, warn user. Keep their campaign and adgroup name as before.
Make sure that if the search term’s current ad group is the same as the chosen ad group, the action must be “add_to_ad_group” instead of “add_to_new_ad_group.” The action “add_to_new_ad_group” should only be used if the chosen ad group is different from the current one.
When an adgroup is chosen, its campaign should be chosen as well as all adgroups are tied to their campaigns. Don’t pick adgroup without checking the campaign.
IMPORTANT: chosen campaign and adgroup should be from knowledge base. (do not generate any adgroup of campaign name, use from knowledge base).
Do not create, infer, or modify any campaign or ad group names.
Provide the decision in JSON format as:
{{
“search_term”: “{search_term}”,
“action”: “add_to_ad_group” | “add_to_new_ad_group” | “exclude”,
“campaign”: “chosen_campaign”,
“ad_group_name”: “chosen_ad_group”,
“reason”: “Provide a concise explanation of why this action, campaign and ad group is recommended”,
“previous_ad_group”: “{current_ad_group}”,
“previous_campaign”: “{current_campaign}”
}}
Only provide the JSON object above without any additional text or code blocks.“”"
here is how I gather all input for system and user prompt:
def read_ad_group_mapping_json(file_path):
“”“Read the AdGroupMapping JSON file and return a dictionary of mappings.”“”
try:
with open(file_path, ‘r’, encoding=‘utf-8’) as file:
data = json.load(file)
# data should be a list of dictionaries with 'Campaign' and 'Ad group' keys
all_campaigns_and_adgroups = {}
for entry in data:
campaign = entry.get('Campaign', '').strip()
ad_group = entry.get('Ad group', '').strip()
if campaign and ad_group:
if campaign not in all_campaigns_and_adgroups:
all_campaigns_and_adgroups[campaign] = set()
all_campaigns_and_adgroups[campaign].add(ad_group)
# Convert sets to lists for JSON serialization
for campaign in all_campaigns_and_adgroups:
all_campaigns_and_adgroups[campaign] = list(all_campaigns_and_adgroups[campaign])
# Estimate token count
df = pd.DataFrame([(k, v) for k, vs in all_campaigns_and_adgroups.items() for v in vs],
columns=['Campaign', 'Ad group'])
token_count = estimate_tokens(df.to_json())
logging.info(f"Estimated token count for Ad Group Mapping file: {token_count}")
return all_campaigns_and_adgroups
except Exception as e:
logging.error(f"Error reading Ad Group Mapping JSON file: {str(e)}")
return None
def prepare_tasks(df, system_prompt, all_campaigns_and_adgroups):
required_columns = [‘Search term’, ‘Campaign’, ‘Ad group’]
optional_columns = [‘Impressions’, ‘Clicks’, ‘Cost’, ‘Conversions’, ‘Conv. value’]
# Check if all required columns are present
if not all(col in df.columns for col in required_columns):
missing = [col for col in required_columns if col not in df.columns]
raise ValueError(f"Missing required columns: {', '.join(missing)}")
# Prepare the knowledge base string
knowledge_base = "Available Campaigns and Ad Groups:\n"
for campaign, ad_groups in all_campaigns_and_adgroups.items():
knowledge_base += f"Campaign: {campaign}\n"
knowledge_base += f"Ad Groups: {', '.join(ad_groups)}\n\n"
# Combine system prompt with knowledge base
full_system_prompt = f"{system_prompt}\n\nKnowledge Base:\n{knowledge_base}"
tasks = []
total_input_tokens = 0
for index, row in df.iterrows():
current_campaign = safe_str(row['Campaign'])
current_ad_group = safe_str(row['Ad group'])
user_content = f"Search Term: {row['Search term']}\n"
user_content += f"Current Campaign: {current_campaign}\n"
user_content += f"Current Ad Group: {current_ad_group}\n"
# Include optional metrics if they exist
metrics = ['Impressions', 'Clicks', 'Cost', 'Conversions', 'Conversion Rate', 'CTR', 'Avg. CPC', 'Cost/Conversion']
for metric in metrics:
if metric in row:
user_content += f"{metric}: {row.get(metric, 0)}\n"
# Estimate input tokens
input_tokens = estimate_tokens(full_system_prompt) + estimate_tokens(user_content)
total_input_tokens += input_tokens
task = {
"custom_id": f"task-{index}",
"method": "POST",
"url": "/v1/chat/completions",
"body": {
"model": "gpt-4o-mini", # Specify the model here
"messages": [
{"role": "system", "content": full_system_prompt},
{"role": "user", "content": user_content.strip()}
]
}
}
tasks.append((json.dumps(task), input_tokens))
avg_input_tokens = total_input_tokens / len(tasks) if tasks else 0
logging.info(f"Average input tokens per search term: {avg_input_tokens:.2f}")
return tasks
