Purpose:

The Python script processes and merges campaign budget data from a primary data source and a Google Sheets reference to prepare a structured budget allocation for campaigns.

To Elaborate

The script is designed to manage and organize campaign budget data by merging information from two sources: a primary data source and a Google Sheets reference. It focuses on campaigns scheduled for the current date, filtering and aligning data based on campaign and account identifiers. The script ensures that the daily budget and any budget overrides specified in the Google Sheets are accurately reflected in the output. This process aids in structured budget allocation (SBA) by providing a consolidated view of campaign budgets, which can be used for further analysis or reporting. The script also removes any entries where the daily budget is not specified, ensuring that only complete and actionable data is included in the final output.

Walking Through the Code

1. Data Preparation
   • The script begins by filtering the primary data source (inputDf) and the Google Sheets data (gSheetsDf) to include only the rows where the date matches the current date. This ensures that only relevant data for the day is processed.
2. Data Merging
   • It merges the filtered data from the primary source and Google Sheets based on matching campaign and account identifiers. This step is crucial for aligning the data from both sources to ensure consistency in the budget allocation process.
3. Output DataFrame Initialization
   • An output DataFrame is initialized with specific columns to store the merged and processed data. This DataFrame will hold the final structured budget allocation information.
4. Data Assignment
   • The script assigns values from the merged DataFrame to the output DataFrame, specifically focusing on the daily budget and any budget overrides from the Google Sheets. This step ensures that the most up-to-date budget information is captured.
5. Data Cleaning
   • It removes any rows from the output DataFrame where the daily budget is not specified (i.e., blank or NaN). This ensures that the final output only contains complete and actionable budget data.
6. Output Preview
   • Finally, the script prints a preview of the first few rows of the output DataFrame, allowing users to verify the processed data.

Vitals

• Script ID : 1493
• Client ID / Customer ID: 1306927585 / 60270327
• Action Type: Bulk Upload (Preview)
• Item Changed: Campaign
• Output Columns: Account, Campaign, Daily Budget, Campaign Override
• Linked Datasource: M1 Report
• Reference Datasource: Google Sheets
• Owner: Grégory Pantaine (gpantaine@marinsoftware.com)
• Created by Grégory Pantaine on 2024-11-07 14:33
• Last Updated by Grégory Pantaine on 2024-11-07 14:38

> 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
##
## name: Budget Staging for Campaigns via GSheets - Roxtec
## description:
##  
## 
## author: Dana Waidhas and Anton Antonov
## created: 2024-09-30
## 

# Set timezone and today's date in the client's timezone
CLIENT_TIMEZONE = datetime.timezone(datetime.timedelta(hours=+8))
today = datetime.datetime.now(CLIENT_TIMEZONE).strftime('%Y-%m-%d')

# Primary data source and columns
inputDf = dataSourceDict["1"]
RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_DATE = 'Date'
RPT_COL_ACCOUNT = 'Account'
RPT_COL_DAILY_BUDGET = 'Daily Budget'
RPT_COL_CAMPAIGN_STATUS = 'Campaign Status'
RPT_CAMPAIGN_ID = 'Campaign ID'

# Reference data source (Google Sheets) and columns
gSheetsDf = dataSourceDict["2_1"]  # First sheet of gsheets dataframe
GSHEET_COL_DATE = 'A'  # Column A is the date in Google Sheets
GSHEET_COL_CAMPAIGN = 'B'  # Column B is the campaign name
GSHEET_COL_ACCOUNT = 'C'  # Column C is the account name
GSHEET_COL_DAILY_BUDGET = 'D'  # Column D is the daily budget
GSHEET_COL_BUDGET_OVERRIDE = 'E'  # Column E is the budget override

# Output columns
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_DAILY_BUDGET = 'Daily Budget'
BULK_COL_CAMPAIGN_ID = 'Campaign ID'
BULK_COL_BUDGET_OVERRIDE = 'Campaign Override'

# Filter inputDf and gSheetsDf for rows where the date matches today
inputDf_filtered = inputDf[inputDf[RPT_COL_DATE] == today]
gSheetsDf_filtered = gSheetsDf[gSheetsDf[GSHEET_COL_DATE] == today]

# Initialize output dataframe
outputDf = pd.DataFrame(columns=[BULK_COL_ACCOUNT, BULK_COL_CAMPAIGN, BULK_COL_DAILY_BUDGET, BULK_COL_CAMPAIGN_ID, BULK_COL_BUDGET_OVERRIDE])

# Merge inputDf with gSheetsDf based on Campaign and Account where dates match
mergedDf = pd.merge(inputDf_filtered, gSheetsDf_filtered, left_on=[RPT_COL_CAMPAIGN, RPT_COL_ACCOUNT],
                    right_on=[GSHEET_COL_CAMPAIGN, GSHEET_COL_ACCOUNT], how='left')

# Assign the Daily Budget from Google Sheets to the output
outputDf[BULK_COL_ACCOUNT] = mergedDf[RPT_COL_ACCOUNT]
outputDf[BULK_COL_CAMPAIGN] = mergedDf[RPT_COL_CAMPAIGN]
outputDf[BULK_COL_DAILY_BUDGET] = mergedDf[GSHEET_COL_DAILY_BUDGET]
outputDf[BULK_COL_CAMPAIGN_ID] = mergedDf[RPT_CAMPAIGN_ID]
outputDf[BULK_COL_BUDGET_OVERRIDE] = mergedDf[GSHEET_COL_BUDGET_OVERRIDE]

# Remove rows where the Daily Budget is blank (NaN or empty) 
outputDf = outputDf[outputDf[BULK_COL_DAILY_BUDGET].notna()] 

# Print the first few rows of the output as a preview
print(outputDf.head())

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