Script 781: Set Pacing Start Date & Pacing End Date

Purpose

The Python script extracts and updates pacing start and end dates from campaign names in a dataset.

To Elaborate

The script is designed to parse campaign names in a dataset to extract start and end dates, which are then formatted and stored in a structured manner. This is particularly useful for marketing or advertising campaigns where the campaign duration is embedded within the campaign name itself. The script identifies these dates using regular expressions and converts them into a standardized ISO format (YYYY-MM-DD). It then compares these extracted dates with existing data to identify any changes. If changes are detected, the script updates the dataset accordingly, ensuring that the pacing start and end dates are accurately reflected. This process helps maintain consistency and accuracy in campaign scheduling and reporting.

Walking Through the Code

  1. Initialization and Setup
    • The script begins by defining constants for column names used in the input and output dataframes.
    • Temporary columns are created in the input dataframe to store the parsed start and end dates.
  2. Date Extraction Function
    • A function get_dates_from_campaign_name is defined to extract dates from the campaign name using regular expressions.
    • The function attempts to parse dates in both four-digit and two-digit year formats, returning them in ISO format or NaN if parsing fails.
  3. Testing the Date Extraction
    • A test function test_get_dates_from_campaign_name is provided to validate the date extraction logic against various campaign name formats.
  4. Parsing and Updating Data
    • The script iterates over each row in the input dataframe, applying the date extraction function to populate the temporary columns with parsed dates.
    • It identifies rows where the parsed dates differ from existing data, marking these as changed.
  5. Output Preparation
    • The script filters the input dataframe to include only rows with changed dates, renaming columns for output consistency.
    • If changes are detected, the updated dataframe is printed; otherwise, an empty dataframe is initialized for output.

Vitals

  • Script ID : 781
  • Client ID / Customer ID: 1306927189 / 60270139
  • Action Type: Bulk Upload
  • Item Changed: Campaign
  • Output Columns: Account, Campaign, Pacing - End Date, Pacing - Start Date
  • Linked Datasource: M1 Report
  • Reference Datasource: None
  • Owner: ascott@marinsoftware.com (ascott@marinsoftware.com)
  • Created by ascott@marinsoftware.com on 2024-03-08 20:34
  • Last Updated by ascott@marinsoftware.com on 2024-07-16 14:15
> 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
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108

# Add Dimensions Tag based on Campaign Name
# * `Pacing - Start Date`
# * `Pacing - End Date`
# Example: `Search - Display Campaign & Google Ad Words (2/1/2023 - 1/31/2024)`
#
# Author: Michael S. Huang
# Created: 2023-11-25
#

RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_ACCOUNT = 'Account'
RPT_COL_PACING_START_DATE = 'Pacing - Start Date'
RPT_COL_PACING_END_DATE = 'Pacing - End Date'
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_PACING_START_DATE = 'Pacing - Start Date'
BULK_COL_PACING_END_DATE = 'Pacing - End Date'

outputDf[BULK_COL_PACING_START_DATE] = "<<YOUR VALUE>>"
outputDf[BULK_COL_PACING_END_DATE] = "<<YOUR VALUE>>"

# create temp column to store new dim tag and default to empty
TMP_START_DATE = RPT_COL_PACING_START_DATE + '_'
TMP_END_DATE = RPT_COL_PACING_END_DATE + '_'
inputDf[TMP_START_DATE] = np.nan
inputDf[TMP_END_DATE] = np.nan

# define function to parse out Start Date and End Date from `Campaign` column of row
# output should be in ISO format YYYY-MM-DD
def get_dates_from_campaign_name(row):
    campaign_name = row['Campaign']
    # Updated regex to handle optional spaces, parentheses, and missing closing parenthesis
    date_pattern_full = r'(\d{1,2}/\d{1,2}/\d{2,4})\s*-\s*?(\d{1,2}/\d{1,2}/\d{2,4})'
    match_full = re.search(date_pattern_full, campaign_name)
    if match_full:
        start_date_str, end_date_str = match_full.groups()
        # Try parsing with four-digit year format
        try:
            start_date = datetime.datetime.strptime(start_date_str, '%m/%d/%Y').strftime('%Y-%m-%d')
            end_date = datetime.datetime.strptime(end_date_str, '%m/%d/%Y').strftime('%Y-%m-%d')
        except ValueError:
            # If it fails, try parsing with two-digit year format
            try:
                start_date = datetime.datetime.strptime(start_date_str, '%m/%d/%y').strftime('%Y-%m-%d')
                end_date = datetime.datetime.strptime(end_date_str, '%m/%d/%y').strftime('%Y-%m-%d')
            except ValueError:
                # If both fail, set to NaN
                start_date, end_date = np.nan, np.nan
    else:
        start_date, end_date = np.nan, np.nan
    return start_date, end_date

def test_get_dates_from_campaign_name():
    campaign_formats = [
        "Traffic_Array Variety Show_3/1/2024-3/31/2024",
        "Traffic_Array Variety Show_(3/1/2024-3/31/2024)",
        "Traffic_Array Variety Show_(3/1/2024-3/31/2024",
        "Traffic_Array Variety Show_3/1/2024-3/31/2024)",
        "Traffic_Array Variety Show_3/1/2024 - 3/31/2024",
        "Traffic_Array Variety Show_(3/1/2024 - 3/31/2024)",
        "Traffic_Array Variety Show_(3/1/2024 - 3/31/2024",
        "Traffic_Array Variety Show_3/1/2024 - 3/31/2024)",
        "Traffic_Array Variety Show_3/1/2024- 3/31/2024",
        "Traffic_Array Variety Show_(3/1/2024- 3/31/2024)",
        "Traffic_Array Variety Show_(3/1/2024- 3/31/2024",
        "Traffic_Array Variety Show_3/1/2024- 3/31/2024)",
        "Traffic_Array Variety Show_3/1/2024 -3/31/2024",
        "Traffic_Array Variety Show_(3/1/2024 -3/31/2024)",
        "Traffic_Array Variety Show_(3/1/2024 -3/31/2024",
        "Traffic_Array Variety Show_3/1/2024 -3/31/2024)"
    ]

    expected_result = ('2024-03-01', '2024-03-31')
    for campaign in campaign_formats:
        row = pd.Series({'Campaign': campaign})
        assert get_dates_from_campaign_name(row) == expected_result, f"Test failed for format: {campaign}"

# Run the unit test
test_get_dates_from_campaign_name()

# parse out Start/End Date from Campaign name
for index, row in inputDf.iterrows():
    start_date, end_date = get_dates_from_campaign_name(row)
    inputDf.at[index, TMP_START_DATE] = start_date
    inputDf.at[index, TMP_END_DATE] = end_date

print("inputDf with parsed tags", tableize(inputDf))

# find changed campaigns
changed = ((inputDf[TMP_START_DATE].notnull() & (inputDf[RPT_COL_PACING_START_DATE] != inputDf[TMP_START_DATE])) | \
          (inputDf[TMP_END_DATE].notnull() & (inputDf[RPT_COL_PACING_END_DATE] != inputDf[TMP_END_DATE])))

print("== Campaigns with Changed Tag ==", tableize(inputDf.loc[changed]))

# only select changed rows
cols = [RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, TMP_START_DATE, TMP_END_DATE]
outputDf = inputDf.loc[ changed, cols ].copy() \
                  .rename(columns = { \
                    TMP_START_DATE: BULK_COL_PACING_START_DATE, \
                    TMP_END_DATE: BULK_COL_PACING_END_DATE \
                  })

if not outputDf.empty:
  print("outputDf", tableize(outputDf))
else:
  print("Empty outputDf")
  outputDf = pd.DataFrame(columns=[BULK_COL_ACCOUNT, BULK_COL_CAMPAIGN, BULK_COL_PACING_START_DATE, BULK_COL_PACING_END_DATE])

Post generated on 2024-11-27 06:58:46 GMT

comments powered by Disqus