Owner
Tags
Last edited time
Oct 5, 2023 3:01 PM
This python script currently will check whether a set of important terms are blocked by either a negative phrase or negative exact term at both the campaign and ad group level. It takes an excel file called “Campaign Check.xls” and then outputs a new excel file with the results as “Campaign Check - Done.xls”
This is useful to identify issues where an important set of terms for a product are either blocked by negative targets and confirming that these terms can actually get bid on within the campaign.
Things I will add in later:
- Coverage checker component to the script later which will confirm that there is a target in the campaign that can actually bid on the specific customer search term, i.e. it has “coverage” in the campaign.
- A way to enter in a group of campaigns vs just one campaign as some people have separate campaigns for each match type vs having separate ad groups within a campaign for match types so in this case they want to confirm that a group of campaigns has coverage for a group of important search terms.
Here is the Python code
import pandas as pd
import re
def check_negative_keywords(negatives_df, queries_df):
# Initialize new columns with default values and data types
queries_df['Is Blocked'] = False
queries_df['Blocking Keyword'] = None
queries_df['Blocking Keyword'] = queries_df['Blocking Keyword'].astype('object')
queries_df['Match Type'] = None
queries_df['Match Type'] = queries_df['Match Type'].astype('object')
# Loop through each query
for i, query_row in queries_df.iterrows():
query = query_row['Query'].lower() # Convert query to lowercase for case-insensitive match
is_blocked = False
blocking_keyword = None
match_type = None
# First, check for "Negative phrase" match type
for j, negative_row in negatives_df.iterrows():
if negative_row['Match Type'].lower() == 'negative phrase': # Convert to lowercase for case-insensitive match
keyword = str(negative_row['Keyword']).lower() # Convert keyword to string and lowercase for case-insensitive match
if re.search(rf'\b{re.escape(keyword)}\b', query):
is_blocked = True
blocking_keyword = keyword
match_type = 'Negative phrase'
break
# If not blocked by "Negative phrase", check for "Negative exact" match type
if not is_blocked:
for j, negative_row in negatives_df.iterrows():
if negative_row['Match Type'].lower() == 'negative exact': # Convert to lowercase for case-insensitive match
keyword = str(negative_row['Keyword']).lower() # Convert keyword to string and lowercase for case-insensitive match
if keyword == query:
is_blocked = True
blocking_keyword = keyword
match_type = 'Negative exact'
break
# Update the DataFrame with the result
queries_df.at[i, 'Is Blocked'] = is_blocked
queries_df.at[i, 'Blocking Keyword'] = blocking_keyword
queries_df.at[i, 'Match Type'] = match_type
return queries_df
if __name__ == "__main__":
# Read the Excel file
negatives_df = pd.read_excel('Campaign Check.xlsx', sheet_name='Negatives')
queries_df = pd.read_excel('Campaign Check.xlsx', sheet_name='KWs to check')
# Check if queries are blocked by negative keywords
result_df = check_negative_keywords(negatives_df, queries_df)
# Save the updated DataFrame to a new Excel file
with pd.ExcelWriter('Campaign Check - Done.xlsx', engine='openpyxl') as writer:
result_df.to_excel(writer, sheet_name='KWs to check', index=False)