Purpose

The Python script processes and merges data from two sources to format email campaign information for Memira DK.

To Elaborate

The Python script is designed to handle and format data for email campaigns by merging information from two different data sources. It specifically focuses on aligning and renaming columns to match a predefined structure, ensuring that the data is consistent and ready for further processing or analysis. The script also includes logic to handle missing values and to determine the final destination URL based on specific conditions. This is particularly useful for marketing teams who need to ensure that their campaign data is accurate and follows a structured format for reporting or further use in marketing platforms.

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 consolidate the information.
2. Column Renaming and Data Cleaning
   • After merging, the script renames columns to match a predefined set of constants, ensuring consistency in the data structure.
   • It checks for NaN values in the ‘Destination URL’ and ‘Suggested URL’ columns and replaces them with empty strings to avoid errors in subsequent operations.
3. URL Processing and Finalization
   • The script identifies URLs containing ‘utm_marin’ and sets the ‘FinalDestURL’ based on this condition, defaulting to a specific URL if neither condition is met.
   • The ‘FinalDestURL’ column is then renamed to ‘Custom Parameters’, and any remaining NaN values are replaced with empty strings.
4. Output Preparation
   • The processed dataframe, mergedDf, is assigned to outputDf for further use or export.
   • Finally, the script prints the formatted table to display the results.

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