Script 199: populate campaign category

Purpose

Python script to populate the “Campaign Category” column in a DataFrame based on specific rules.

To Elaborate

The Python script solves the problem of populating the “Campaign Category” column in a DataFrame based on specific rules. The rules are defined using string matching conditions on the “Campaign” and “Account” columns. The script creates a new temporary column, applies the rules to populate the temporary column, and then copies the values from the temporary column to the “Campaign Category” column. The script also filters the DataFrame to include only rows where the “Campaign Category” has changed.

Walking Through the Code

  1. Define column constants for “Campaign”, “Account”, and “Campaign Category”.
  2. Create a new temporary field in the DataFrame.
  3. Blank out the values in the temporary field.
  4. Apply string matching conditions to populate the temporary field based on the “Campaign” column values.
  5. Print the DataFrame with the temporary field populated.
  6. Check for duplicated index values in the DataFrame.
  7. Copy the values from the temporary field to the “Campaign Category” column in the output DataFrame.
  8. Filter the output DataFrame to include only rows where the “Campaign Category” has changed.

Vitals

  • Script ID : 199
  • Client ID / Customer ID: 3635161 / 13367
  • Action Type: Bulk Upload
  • Item Changed: Campaign
  • Output Columns: Account, Campaign, Campaign Category
  • Linked Datasource: M1 Report
  • Reference Datasource: None
  • Owner: Jonathan Reichl (jreichl@marinsoftware.com)
  • Created by Jonathan Reichl on 2023-06-16 08:52
  • Last Updated by Jonathan Reichl on 2023-12-06 04:01
> 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

RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_ACCOUNT = 'Account'
RPT_COL_CAMPAIGN_CATEGORY = 'Campaign Category'
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_CAMPAIGN_CATEGORY = 'Campaign Category'

#outputDf[BULK_COL_CAMPAIGN_CATEGORY] = "<<YOUR VALUE>>"

today = datetime.datetime.now(CLIENT_TIMEZONE).date()
print(tableize(inputDf))



TMP_FIELD = BULK_COL_CAMPAIGN_CATEGORY + '_new'
# blank out tmp field
inputDf[TMP_FIELD] = numpy.nan

inputDf.loc[ (inputDf[RPT_COL_CAMPAIGN].str.contains('Home - Product', case=False)) & (~inputDf[RPT_COL_CAMPAIGN].str.contains('Brand', case=False)) , TMP_FIELD ] = 'Home Generics'
inputDf.loc[ (inputDf[RPT_COL_CAMPAIGN].str.contains('Home - Product - Brand', case=False)) , TMP_FIELD ] = 'Home Brand'
inputDf.loc[ (inputDf[RPT_COL_CAMPAIGN].str.contains('Home - Competitor', case=False)) , TMP_FIELD ] = 'Home - Competitor'
inputDf.loc[ (inputDf[RPT_COL_CAMPAIGN].str.contains('Motor - Product - Car', case=False)) , TMP_FIELD ] = 'Motor Generics'
inputDf.loc[ (inputDf[RPT_COL_CAMPAIGN].str.contains('Brand - Product - Car', case=False)) , TMP_FIELD ] = 'Branded Motor'
inputDf.loc[ (inputDf[RPT_COL_CAMPAIGN].str.contains('Competitors - Product', case=False)) & ( (inputDf[RPT_COL_CAMPAIGN].str.contains('Car', case=False)) | (inputDf[RPT_COL_CAMPAIGN].str.contains('Motor', case=False)) ), TMP_FIELD ] = 'Competitor Motor'
inputDf.loc[ (inputDf[RPT_COL_CAMPAIGN].str.contains('Competitors', case=False)) & (~inputDf[RPT_COL_CAMPAIGN].str.contains('Product', case=False)) , TMP_FIELD ] = 'Competitor Pure Brand'
inputDf.loc[ (inputDf[RPT_COL_CAMPAIGN].str.contains('Brand', case=False)) & (inputDf[RPT_COL_ACCOUNT].str.contains('_Brand', case=False)) & ((~inputDf[RPT_COL_CAMPAIGN].str.contains('Motor', case=False))|(~inputDf[RPT_COL_CAMPAIGN].str.contains('Car', case=False))|(~inputDf[RPT_COL_CAMPAIGN].str.contains('Product', case=False))|(~inputDf[RPT_COL_CAMPAIGN].str.contains('Travel', case=False))) , TMP_FIELD ] = 'Pure Brand'
inputDf.loc[ (inputDf[RPT_COL_CAMPAIGN].str.contains('Landlord', case=False)) & ((~inputDf[RPT_COL_CAMPAIGN].str.contains('Brand', case=False))|(~inputDf[RPT_COL_CAMPAIGN].str.contains('Competitor', case=False))) , TMP_FIELD ] = 'Landlord Generics'
inputDf.loc[ (inputDf[RPT_COL_CAMPAIGN].str.contains('Landlord', case=False)) & (inputDf[RPT_COL_CAMPAIGN].str.contains('Brand', case=False)) , TMP_FIELD ] = 'Landlord Brand'
inputDf.loc[ (inputDf[RPT_COL_CAMPAIGN].str.contains('Landlord', case=False)) & (inputDf[RPT_COL_CAMPAIGN].str.contains('Competitor', case=False)) , TMP_FIELD ] = 'Landlord Competitor'
inputDf.loc[ (inputDf[RPT_COL_CAMPAIGN].str.contains('Motorhome', case=False)) & ((~inputDf[RPT_COL_CAMPAIGN].str.contains('Brand', case=False))|(~inputDf[RPT_COL_CAMPAIGN].str.contains('Competitor', case=False))) , TMP_FIELD ] = 'Motorhome Generics'
inputDf.loc[ (inputDf[RPT_COL_CAMPAIGN].str.contains('Motorhome', case=False)) & (inputDf[RPT_COL_CAMPAIGN].str.contains('Brand', case=False)) , TMP_FIELD ] = 'Motorhome Brand'
inputDf.loc[ (inputDf[RPT_COL_CAMPAIGN].str.contains('Motorhome', case=False)) & (inputDf[RPT_COL_CAMPAIGN].str.contains('Competitor', case=False)) , TMP_FIELD ] = 'Motorhome Competitor'
inputDf.loc[ (inputDf[RPT_COL_CAMPAIGN].str.contains('Temporary', case=False)) & ((~inputDf[RPT_COL_CAMPAIGN].str.contains('Brand', case=False))|(~inputDf[RPT_COL_CAMPAIGN].str.contains('Competitor', case=False))) , TMP_FIELD ] = 'Temp Generics'
inputDf.loc[ (inputDf[RPT_COL_CAMPAIGN].str.contains('Temporary', case=False)) & (inputDf[RPT_COL_CAMPAIGN].str.contains('Brand', case=False)) , TMP_FIELD ] = 'Temp Brand'
inputDf.loc[ (inputDf[RPT_COL_CAMPAIGN].str.contains('Temporary', case=False)) & (inputDf[RPT_COL_CAMPAIGN].str.contains('Competitor', case=False)) , TMP_FIELD ] = 'Temp Competitor'
inputDf.loc[ (inputDf[RPT_COL_CAMPAIGN].str.contains('Travel', case=False)) & ((~inputDf[RPT_COL_CAMPAIGN].str.contains('Brand', case=False))|(~inputDf[RPT_COL_CAMPAIGN].str.contains('Competitor', case=False))) , TMP_FIELD ] = 'Travel Generics'
inputDf.loc[ (inputDf[RPT_COL_CAMPAIGN].str.contains('Travel', case=False)) & (inputDf[RPT_COL_CAMPAIGN].str.contains('Brand', case=False)) , TMP_FIELD ] = 'Travel Brand'
inputDf.loc[ (inputDf[RPT_COL_CAMPAIGN].str.contains('Travel', case=False)) & (inputDf[RPT_COL_CAMPAIGN].str.contains('Competitor', case=False)) , TMP_FIELD ] = 'Travel Competitor'
inputDf.loc[ (inputDf[RPT_COL_CAMPAIGN].str.contains('Life', case=False)) & ((~inputDf[RPT_COL_CAMPAIGN].str.contains('Brand', case=False))|(~inputDf[RPT_COL_CAMPAIGN].str.contains('Competitor', case=False))|(~inputDf[RPT_COL_CAMPAIGN].str.contains('Lifetime', case=False))) , TMP_FIELD ] = 'Life Generics'
inputDf.loc[ (inputDf[RPT_COL_CAMPAIGN].str.contains('Life', case=False)) & (inputDf[RPT_COL_CAMPAIGN].str.contains('Brand', case=False)) & (~inputDf[RPT_COL_CAMPAIGN].str.contains('Lifetime', case=False)) , TMP_FIELD ] = 'Life Brand'
inputDf.loc[ (inputDf[RPT_COL_CAMPAIGN].str.contains('Life', case=False)) & (inputDf[RPT_COL_CAMPAIGN].str.contains('Competitor', case=False)) & (~inputDf[RPT_COL_CAMPAIGN].str.contains('Lifetime', case=False)) , TMP_FIELD ] = 'Life Competitor'



print(tableize(inputDf))

print(inputDf.index.duplicated())

# copy new strategy to output
outputDf.loc[:,BULK_COL_CAMPAIGN_CATEGORY] = inputDf.loc[:, TMP_FIELD]

# only include campaigns with changed strategy in bulk file
outputDf = outputDf[ inputDf[TMP_FIELD].notnull() & (inputDf[BULK_COL_CAMPAIGN_CATEGORY] != inputDf[TMP_FIELD]) ]

Post generated on 2024-03-10 06:34:12 GMT

comments powered by Disqus