Purpose

The Python script processes and merges data from two sources to generate a structured dataset for advertising campaigns, ensuring URLs are correctly formatted and populated.

To Elaborate

The Python script is designed to merge and process data from two different data sources, specifically focusing on advertising campaign information. It aligns data from a Google Sheets document and another input source by matching specific columns. The script ensures that URLs within the dataset are correctly formatted, checking for specific tracking parameters and setting default URLs when necessary. The final output is a structured dataset that can be used for further analysis or reporting on advertising campaigns, with particular attention to the correct formatting of URLs and campaign identifiers.

Walking Through the Code

1. Data Input and Preparation
   • The script begins by loading data from two sources into dataframes: one from a Google Sheets document and another from a different input source.
   • It merges these dataframes based on specific columns (‘Ad’ and ‘Keyword’), ensuring that relevant data from both sources are combined.
2. Data Renaming and Cleaning
   • The merged dataframe’s columns are renamed to more descriptive names using predefined constants.
   • The script checks for missing values in URL-related columns and replaces them with empty strings to avoid errors in subsequent processing.
3. URL Processing
   • It checks if the ‘Destination URL’ or ‘Suggested URL’ contains a specific tracking parameter (‘utm_marin’).
   • Based on the presence of this parameter, it sets a ‘FinalDestURL’ column, defaulting to a specific URL if neither contains the parameter.
4. Final Adjustments and Output
   • The ‘FinalDestURL’ column is renamed to a constant representing custom parameters.
   • Any remaining NaN values in this column are replaced with empty strings.
   • The processed dataframe is then prepared for output, ready for further use or analysis.

Vitals

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

> 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.no/'))

# 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