Purpose:

The script automates the process of setting daily budgets for campaigns by merging data from a primary data source with a Google Sheets reference.

To Elaborate

The Python script is designed to streamline the process of updating daily budgets for marketing campaigns. It achieves this by integrating data from two sources: a primary data source containing campaign details and a Google Sheets document that specifies the desired daily budgets. The script filters both datasets to include only the entries relevant to the current date, ensuring that the budget updates are timely. It then merges these datasets based on matching campaign and account identifiers, allowing for the transfer of budget information from the Google Sheets to the primary dataset. The result is a consolidated output that reflects the updated daily budgets for each campaign, ready for further processing or implementation.

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 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 on the Campaign and Account columns, aligning them based on these identifiers. This step is crucial for associating the correct budget information from the Google Sheets with the corresponding campaign data.
3. Budget Assignment
   • The script assigns the daily budget values from the Google Sheets to the output dataframe. It ensures that each campaign’s budget is updated according to the latest information provided in the Google Sheets.
4. Data Cleaning
   • Rows with missing budget values are removed from the output dataframe. This step ensures that only complete and actionable data is retained for further use.
5. Output Preview
   • Finally, the script provides a preview of the first few rows of the output dataframe, allowing users to verify the results of the budget allocation process.

Vitals

• Script ID : 1549
• Client ID / Customer ID: 1306918645 / 60268158
• 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 2024-11-28 20:09
• Last Updated by Grégory Pantaine on 2024-11-29 10:44

> 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
##
## name: Budget Staging for Campaigns via GSheets - Sky Vegas
## 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=+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 = 'C'  # Column C is the campaign name
GSHEET_COL_ACCOUNT = 'B'  # Column B 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