Purpose:

The Python script synchronizes campaign budget data from a primary data source with updates from a Google Sheets reference, ensuring daily budget allocations are current.

To Elaborate

The script is designed to manage and update campaign budget allocations by integrating data from two sources: a primary data source and a Google Sheets reference. It focuses on campaigns that are active on the current date, ensuring that the daily budget figures are up-to-date. By merging these datasets based on campaign and account identifiers, the script updates the daily budget allocations with the latest figures from Google Sheets. This process helps maintain accurate budget tracking and allocation for campaigns, ensuring that any changes made in the Google Sheets are reflected in the primary data source.

Walking Through the Code

1. Data Preparation:
   • The script begins by filtering both the primary data source (inputDf) and the Google Sheets data (gSheetsDf) to include only the rows where the date matches the current day. This ensures that only relevant data for the current date is processed.
2. Data Merging:
   • It then merges the filtered datasets on the Campaign and Account columns, aligning the data from both sources where the campaign and account identifiers match. This step is crucial for integrating the latest budget information from Google Sheets into the primary data source.
3. Output DataFrame Initialization:
   • An empty DataFrame (outputDf) is initialized with columns for account, campaign, daily budget, and campaign ID. This DataFrame will store the final output after processing.
4. Data Assignment:
   • The script assigns the daily budget values from the Google Sheets data to the corresponding columns in the output DataFrame. This ensures that the most recent budget figures are used.
5. Data Cleaning:
   • Rows with missing or blank daily budget values are removed from the output DataFrame. This step ensures that only complete and valid data is included in the final output.
6. Preview Output:
   • Finally, the script prints a preview of the first few rows of the output DataFrame, allowing users to verify the results of the data processing.

Vitals

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

> 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
##
## 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

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

# 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])

# 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]

# 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