Purpose

The Python script synchronizes daily budget allocations for marketing campaigns between a primary data source and a Google Sheets reference, ensuring up-to-date budget information.

To Elaborate

The script is designed to manage and update daily budget allocations for marketing campaigns by integrating data from a primary data source and a Google Sheets reference. It filters both data sources to include only the records relevant to the current date, ensuring that the budget information is current. The script then merges these filtered datasets based on campaign and account identifiers, allowing it to update the daily budget allocations in the primary data source with the values specified in the Google Sheets. This process ensures that the budget allocations are consistent and up-to-date, facilitating effective budget management for marketing campaigns.

Walking Through the Code

1. Data Preparation
   • The script begins by filtering the primary data source (inputDf) and the Google Sheets reference (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 datasets based on the campaign and account identifiers. This step aligns the data from both sources, allowing the script to update the daily budget allocations accurately.
3. Output DataFrame Initialization and Assignment
   • An output DataFrame is initialized with specific columns for account, campaign, daily budget, and campaign ID. The script assigns the daily budget from the Google Sheets to this output DataFrame, ensuring that the most recent budget information is used.
4. Data Cleaning
   • The script removes any rows from the output DataFrame where the daily budget is not available (i.e., NaN or empty), ensuring that only complete and valid data is retained.
5. Preview Output
   • Finally, the script prints a preview of the first few rows of the output DataFrame, providing a quick overview of the updated budget allocations.

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