Search Term Blocker Checker Script

Owner

Marc Pfeiffer

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)