Script 1493: Budget Staging for Campaigns via GSheets with Override Roxtec

Purpose

The script automates the process of merging campaign budget data from a primary data source with override data from Google Sheets for daily budget allocation.

To Elaborate

The Python script is designed to streamline the process of managing campaign budgets by integrating data from two sources: a primary data source and a Google Sheets document. The script filters both data sources to include only the rows that correspond to the current date. It then merges these filtered datasets based on matching campaign and account names. The merged data is used to create an output that includes the daily budget and any budget overrides specified in the Google Sheets. This ensures that the most up-to-date budget information is used for campaign management, allowing for efficient and accurate budget allocation.

Walking Through the Code

  1. Data Preparation
    • The script begins by defining the primary data source (inputDf) and the reference data source from Google Sheets (gSheetsDf).
    • It filters both datasets to include only the rows where the date matches the current date.
  2. Data Merging and Output Preparation
    • The script merges the filtered datasets based on matching campaign and account names.
    • It initializes an output DataFrame with specified columns to store the merged data.
    • The daily budget and any budget overrides from the Google Sheets are assigned to the output DataFrame.
    • Rows with missing daily budget values are removed to ensure data integrity.
  3. Output Preview
    • Finally, the script prints a preview of the first few rows of the output DataFrame to verify the results.

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

comments powered by Disqus