Script 1615: Script Budgets from G Sheet Brand Name TBC

Purpose

The script processes and merges campaign budget data from a primary data source and Google Sheets to update daily budgets for campaigns.

To Elaborate

The Python script is designed to manage and update campaign budgets by integrating data from two sources: a primary data source and a Google Sheets document. It filters both datasets to include only the entries relevant to the current date, ensuring that the budget allocations are up-to-date. The script then merges these datasets based on matching campaign and account names, allowing it to update the daily budget for each campaign with the values specified in the Google Sheets. This process ensures that the budget allocations are consistent and reflect any changes made in the Google Sheets, which serves as a reference for budget adjustments. The final output is a cleaned dataset that excludes any entries with missing budget information, providing a clear and actionable list of campaigns with their updated daily budgets.

Walking Through the Code

  1. Data Preparation
    • The script begins by defining the primary and reference data sources, which include a primary dataset and a Google Sheets document. It specifies the relevant columns for campaigns, accounts, and daily budgets in both datasets.
  2. Data Filtering
    • It filters both the primary dataset and the Google Sheets data to include only the rows where the date matches the current date. This ensures that only relevant and timely data is processed.
  3. Data Merging
    • The script merges the filtered datasets based on matching campaign and account names. This step aligns the data from both sources, allowing the script to update the daily budgets with the values from the Google Sheets.
  4. Output Preparation
    • An output DataFrame is initialized to store the merged data. The script assigns the updated daily budgets from the Google Sheets to this output DataFrame, ensuring that only complete and accurate budget information is included.
  5. Data Cleaning
    • The script removes any rows from the output DataFrame where the daily budget is missing or blank. This step ensures that the final output is clean and ready for use.
  6. Preview Output
    • Finally, the script prints a preview of the first few rows of the output DataFrame, providing a quick overview of the updated campaign budgets.

Vitals

  • Script ID : 1615
  • Client ID / Customer ID: 1306922279 / 60268737
  • Action Type: Bulk Upload (Preview)
  • Item Changed: Campaign
  • Output Columns: Account, Campaign, Daily Budget
  • Linked Datasource: M1 Report
  • Reference Datasource: Google Sheets
  • Owner: Grégory Pantaine (gpantaine@marinsoftware.com)
  • Created by Grégory Pantaine on 2025-01-13 20:36
  • Last Updated by Grégory Pantaine on 2025-01-13 20:57
> 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 - Paddy Power Betfair
## description:
##  
## 
## author: Dana Waidhas and Anton Antonov - Copied by Gregory Pantaine
## created: 2025-01-13
## 

# Set timezone and today's date in the client's timezone
CLIENT_TIMEZONE = datetime.timezone(datetime.timedelta(hours=+1))
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-02-21 10:25:25 GMT

comments powered by Disqus