Purpose:

The Python script processes and merges data from two sources to prepare a structured dataset for email marketing campaigns.

To Elaborate

The Python script is designed to handle and process data for email marketing campaigns by merging information from two different data sources. It focuses on aligning and renaming columns to match specific requirements, ensuring that URLs are correctly formatted and contain necessary tracking parameters. The script checks for the presence of specific tracking parameters in URLs and assigns a default URL if they are missing. This ensures that the final dataset is structured and ready for further use in marketing activities, such as importing into email marketing platforms or generating reports.

Walking Through the Code

1. Data Import and Initial Setup:
   • The script begins by importing data from two sources into dataframes: inputDf and gSheetsDf.
   • These dataframes are merged based on specific columns (‘Ad’ and ‘Keyword’) to create a unified dataset, mergedDf.
2. Column Renaming:
   • The merged dataframe’s columns are renamed to match predefined constants, ensuring consistency and clarity in the dataset.
3. Handling Missing Values:
   • The script replaces any NaN values in the ‘Destination URL’ and ‘Suggested URL’ columns with empty strings to prevent errors in subsequent operations.
4. URL Processing:
   • It checks if the ‘Destination URL’ or ‘Suggested URL’ contains the ‘utm_marin’ parameter.
   • Based on this check, it sets the ‘FinalDestURL’ to either the ‘Destination URL’, ‘Suggested URL’, or a default URL (‘https://www.memira.dk/’).
5. Final Adjustments:
   • The ‘FinalDestURL’ column is renamed to match the BULK_COL_CUSTOM_PARAMETERS constant.
   • Any remaining NaN values in this column are replaced with empty strings to ensure data integrity.
6. Output:
   • The processed dataframe, outputDf, is prepared for further use, and its contents are printed in a tabular format.

Vitals

• Script ID : 503
• Client ID / Customer ID: 1306925579 / 60269545
• Action Type: Bulk Upload
• Item Changed: Ad
• Output Columns: Account, Campaign, Group, Creative ID, Display URL, Media sub-type, Media Type, Unique ID, Custom Parameters, Status
• Linked Datasource: M1 Report
• Reference Datasource: Google Sheets
• Owner: Tom McCaughey (tmccaughey@marinsoftware.com)
• Created by Tom McCaughey on 2023-11-07 13:06
• Last Updated by Tom McCaughey on 2024-01-10 14:36

> 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
#Email format script# Define the column names as constants
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_GROUP = 'Group'
BULK_COL_CREATIVE_ID = 'Creative ID'
BULK_COL_MEDIA_SUBTYPE = 'Media sub-type'
BULK_COL_MEDIA_TYPE = 'Media Type'
BULK_COL_DISPLAY_URL = 'Display URL'
BULK_COL_CUSTOM_PARAMETERS = 'Custom Parameters'
BULK_COL_UNIQUE_ID = 'Unique ID'
BULK_COL_STATUS = 'Status'

inputDf = dataSourceDict["1"]
gSheetsDf = dataSourceDict["2_1"]  # gsheets dataframe (first sheet)

# Merge dataframes based on 'Ad' and 'Keyword'
mergedDf = pd.merge(gSheetsDf, inputDf, left_on='F', right_on='Keyword', how='left')

mergedDf = mergedDf.rename(columns={
    'A': BULK_COL_UNIQUE_ID,
    'B': BULK_COL_CAMPAIGN,
    'E': BULK_COL_GROUP,
    'F': BULK_COL_CREATIVE_ID,
    'D': BULK_COL_MEDIA_SUBTYPE,
    'C': BULK_COL_MEDIA_TYPE,
    'G': BULK_COL_DISPLAY_URL,
    'H': BULK_COL_STATUS
})

# If you want to replace NaN values with an empty string in the URL columns
mergedDf['Destination URL'].fillna('', inplace=True)
mergedDf['Suggested URL'].fillna('', inplace=True)

DestURL_contains_utm_marin = mergedDf['Destination URL'].str.contains('utm_marin', case=False, na=False)
SuggestURL_contains_utm_marin = mergedDf['Suggested URL'].str.contains('utm_marin', case=False, na=False)

# Set FinalDestURL based on conditions
mergedDf['FinalDestURL'] = np.where(DestURL_contains_utm_marin, mergedDf['Destination URL'],
                                    np.where(SuggestURL_contains_utm_marin, mergedDf['Suggested URL'], 'https://www.memira.dk/'))

# Rename the FinalDestURL column to BULK_COL_CUSTOM_PARAMETERS
mergedDf = mergedDf.rename(columns={'FinalDestURL': BULK_COL_CUSTOM_PARAMETERS})

# If you want to replace NaN values with an empty string in the BULK_COL_CUSTOM_PARAMETERS column
mergedDf[BULK_COL_CUSTOM_PARAMETERS].fillna('', inplace=True)

outputDf = mergedDf

print(tableize(outputDf))

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