Purpose:

The script identifies conflicting keywords between current and negative keyword lists in an account.

To Elaborate

The Python script is designed to identify conflicts between current keywords and negative keywords within an account. In digital advertising, keywords are used to target specific search queries, while negative keywords are used to prevent ads from showing for certain queries. A conflict arises when a keyword that is intended to trigger an ad is also listed as a negative keyword, which would prevent the ad from being shown. This script compares the two lists of keywords to find any such conflicts, ensuring that the advertising strategy is not inadvertently undermined by these contradictions.

Walking Through the Code

1. Data Source Initialization:
   • The script begins by defining the primary data source (inputDf) for current keywords and the reference data source (reportDf) for negative keywords. These data sources are expected to be provided in a dictionary format (dataSourceDict).
2. Dataframe Setup:
   • An empty DataFrame outputDf is initialized with columns to store the results of the conflict check, including account, campaign, group, keyword, match type, and a flag for conflicting keywords.
3. Merging DataFrames:
   • The script merges inputDf and reportDf on account, campaign, group, keyword, and match type to identify rows where the same keyword appears in both the current and negative keyword lists.
4. Conflict Identification:
   • A new DataFrame conflictsDf is created from the merged DataFrame, containing only the columns necessary to identify conflicts. A new column, Conflicting Keyword, is added with a value of ‘yes’ to indicate a conflict.
5. Output Preparation:
   • The columns in conflictsDf are renamed to match the output requirements, and the results are appended to outputDf.
6. Result Display:
   • Finally, the script prints the outputDf, which contains all identified keyword conflicts.

Vitals

• Script ID : 1191
• Client ID / Customer ID: 1306927757 / 60270153
• Action Type: Bulk Upload (Preview)
• Item Changed: Keyword
• Output Columns: Account, Campaign, Group, Keyword, Match Type, Conflicting Keyword
• Linked Datasource: M1 Report
• Reference Datasource: M1 Report
• Owner: dwaidhas@marinsoftware.com (dwaidhas@marinsoftware.com)
• Created by dwaidhas@marinsoftware.com on 2024-06-17 21:43
• Last Updated by dwaidhas@marinsoftware.com on 2024-06-17 21:46

> See it in Action

Python Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
## 
## name: Conflicting Keywords 
## description:
##  This script compares the current keywords in the account with the negative keywords to see if there are any conflicting keywords 
## 
## author: 
## created: 2024-06-17
## 

MATCH_TYPE = {
    'EXACT': 'exact',
    'PHRASE': 'phrase',
    'BROAD': 'broad',
}
today = datetime.datetime.now().date()


# Primary data source and columns (current keywords)
inputDf = dataSourceDict["1"]
RPT_COL_KEYWORD = 'Keyword'
RPT_COL_ACCOUNT = 'Account'
RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_GROUP = 'Group'
RPT_COL_MATCH_TYPE = 'Match Type'
RPT_COL_STATUS = 'Status'

# Reference data source and columns (negative keywords)
reportDf = dataSourceDict["2"]
NEG_COL_KEYWORD = 'Keyword'
NEG_COL_ACCOUNT = 'Account'
NEG_COL_CAMPAIGN = 'Campaign'
NEG_COL_GROUP = 'Group'
NEG_COL_MATCH_TYPE = 'Match Type'

# Output columns and initial values
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_GROUP = 'Group'
BULK_COL_KEYWORD = 'Keyword'
BULK_COL_MATCH_TYPE = 'Match Type'
BULK_COL_CONFLICTING_KEYWORD = 'Conflicting Keyword'

outputDf = pd.DataFrame(columns=[
    BULK_COL_ACCOUNT, BULK_COL_CAMPAIGN, BULK_COL_GROUP, 
    BULK_COL_KEYWORD, BULK_COL_MATCH_TYPE, BULK_COL_CONFLICTING_KEYWORD
])

# Merging the dataframes to find conflicts
mergedDf = pd.merge(
    inputDf, reportDf, 
    left_on=[RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_GROUP, RPT_COL_KEYWORD, RPT_COL_MATCH_TYPE], 
    right_on=[NEG_COL_ACCOUNT, NEG_COL_CAMPAIGN, NEG_COL_GROUP, NEG_COL_KEYWORD, NEG_COL_MATCH_TYPE], 
    suffixes=('_current', '_negative')
)

# Identifying the conflicts
conflictsDf = mergedDf[
    [RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_GROUP, RPT_COL_KEYWORD, RPT_COL_MATCH_TYPE]
].copy()
conflictsDf[BULK_COL_CONFLICTING_KEYWORD] = 'yes'

# Renaming columns to match output requirements
conflictsDf.columns = [
    BULK_COL_ACCOUNT, BULK_COL_CAMPAIGN, BULK_COL_GROUP, 
    BULK_COL_KEYWORD, BULK_COL_MATCH_TYPE, BULK_COL_CONFLICTING_KEYWORD
]

# Storing the result in output dataframe
outputDf = pd.concat([outputDf, conflictsDf], ignore_index=True)

# Displaying the output
print(outputDf)

Post generated on 2025-03-11 01:25:51 GMT