Purpose

The script identifies conflicting keywords between current and negative keywords 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 audiences, while negative keywords are used to exclude certain search terms to prevent ads from appearing in irrelevant searches. The script compares these two sets of keywords to find any overlaps, which could lead to ads being unintentionally blocked. By identifying these conflicts, the script helps ensure that the advertising strategy is optimized and that ads are shown to the intended audience without unnecessary exclusions.

Walking Through the Code

1. Data Preparation
   • The script begins by defining a dictionary MATCH_TYPE to map different match types such as ‘EXACT’, ‘PHRASE’, and ‘BROAD’.
   • It then retrieves the primary data source (inputDf) containing current keywords and the reference data source (reportDf) containing negative keywords from a dictionary dataSourceDict.
2. Data Merging
   • The script merges inputDf and reportDf using a common set of columns: Account, Campaign, Group, Keyword, and Match Type. This merge operation helps identify rows where the current keywords match the negative keywords, indicating a conflict.
3. Conflict Identification
   • After merging, the script creates a new DataFrame conflictsDf to store only the conflicting keywords. It adds a new column Conflicting Keyword with a value of ‘yes’ to indicate a conflict.
4. Output Preparation
   • The script renames the columns in conflictsDf to match the output requirements and concatenates it with an initially empty DataFrame outputDf.
   • Finally, it prints the outputDf to display the results, showing accounts, campaigns, groups, keywords, match types, and whether a conflict exists.

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 2024-11-27 06:58:46 GMT