Script 165: Intraday Adj Google tCPA and Budget

Purpose:

The script adjusts the target CPA and daily budget of Google campaigns based on recent ROAS and spending data.

To Elaborate

The Python script is designed to dynamically adjust the target Cost Per Acquisition (tCPA) and daily budget of Google advertising campaigns. It uses recent data on Return on Advertising Spend (ROAS) and expenditure over a specified number of hours to make these adjustments. The script applies a set of predefined criteria to determine how the budget and tCPA should be adjusted for each campaign. These criteria are based on ranges of ROAS and spending thresholds, allowing for fine-tuned control over campaign performance. The goal is to optimize the allocation of advertising budgets in real-time, ensuring that campaigns are adjusted according to their performance metrics to maximize efficiency and return on investment.

Walking Through the Code

Define Adjustment Criteria:
- The script begins by defining several sets of criteria for adjusting campaign budgets and tCPA. Each set of criteria specifies ranges for ROAS and spending, along with corresponding adjustments to be made.
Initialize Temporary Columns:
- Temporary columns are created in the input DataFrame to store new budget and tCPA values. These columns are initially set to empty.
Calculate Spend Percentage:
- The script calculates the percentage of the daily budget that has been spent and stores this value in a temporary column.
Apply First ROAS Criteria:
- A copy of the input DataFrame is created, and the first set of ROAS criteria is applied. Campaigns meeting these criteria have their budgets adjusted accordingly.
Apply Second ROAS Criteria:
- Similar to the first criteria, the second set is applied to another copy of the input DataFrame. Adjustments are made based on the defined criteria.
Apply Third ROAS Criteria:
- The third set of criteria is applied, which includes adjustments to both budget and tCPA. Campaigns meeting these criteria are adjusted and stored in a new DataFrame.
Apply Fourth and Fifth ROAS Criteria:
- The fourth and fifth sets of criteria are applied in a similar manner, adjusting campaigns based on their respective conditions.
Merge Adjusted Campaigns:
- All adjusted campaigns from the different criteria are merged into a final output DataFrame. This DataFrame contains only the campaigns that have had their budgets or tCPA adjusted.
Final Adjustments and Output:
- A final check ensures that no tCPA values fall below a minimum threshold. The adjusted campaigns are then prepared for output.

Vitals

Script ID : 165
Client ID / Customer ID: 1306925431 / 60269477
Action Type: Bulk Upload (Preview)
Item Changed: Campaign
Output Columns: Account, Campaign, Publisher Target CPA, Daily Budget, Last Intraday Adj
Linked Datasource: M1 Report
Reference Datasource: None
Owner: Byron Porter (bporter@marinsoftware.com)
Created by Byron Porter on 2023-06-07 04:29
Last Updated by simon@rainmakeradventures.com on 2023-12-06 04:01

> See it in Action

Python Code

#
# Publisher Budget and Target CPA Adjustment - Intraday
#
#
# Author: Byron Porter
# Date: 2023-07-24
#


# define criteria for intraday campaign tCPA and Daily Budget adjustment
# note: MIN values are inclusive; MAX values are non-inclusive

first_campaign_adj_criteria = [
	# format: (min roas, max roas, min cost, budget adj),
	(.0, .25, 100.00, 25.00)
]

second_campaign_adj_criteria = [
	# format: (min roas, max roas, budget adj),
	(.25, .50, .50),
	(.5, .75, .30)
]

third_campaign_adj_criteria = [
	# format: (min roas, max roas, min custom (spend/daily cap), max custom (spend/daily cap), tCPA adj, budget adj),
	(1, 1.25, .50, 999999.0, 0, .10), 
	(1.25, 1.50, .0, .50, .10, 0),
	(1.25, 1.50, .50, 999999.0, 0, .20),
	(1.50, 2, .0, .50, .10, 0),
	(1.50, 2, .50, 999999.0, 0, .30),
	(2, 3, .0, .50, .10, 0), 
	(2, 3, .50, 999999.0, 0, .40)
]

fourth_campaign_adj_criteria = [
	# format: (min roas, tCPA adj, budget adj),
	(3, .10, .50)
]

fifth_campaign_adj_criteria = [
	# format: (min roas, max roas, budget adj, tCPA adj),
	(.75, 1, .10, .05)
]

# define column parameters
RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_ACCOUNT = 'Account'
RPT_COL_STRATEGY = 'Strategy'
RPT_COL_CAMPAIGN_STATUS = 'Campaign Status'
RPT_COL_DAILY_BUDGET = 'Daily Budget'
RPT_COL_ROAS = 'CLICKS ROAS'
RPT_COL_PUBLISHER_TARGETCPA = 'Publisher Target CPA'
RPT_COL_PUB_COST = 'Pub. Cost $'
RPT_COL_CAMP_MATURITY = 'Campaign Maturity'
RPT_COL_INTRADAY_ADJUSTED = 'Last Intraday Adj'
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_DAILY_BUDGET = 'Daily Budget'
BULK_COL_PUBLISHER_TARGETCPA = 'Publisher Target CPA'
BULK_COL_INTRADAY_ADJUSTED = 'Last Intraday Adj'


# Assign current timestamp and day of the month to parameters
timestmp = datetime.datetime.now(CLIENT_TIMEZONE).strftime('%Y-%m-%d %I:%M:%S %p')
today = datetime.datetime.now(CLIENT_TIMEZONE)
day = today.day

# create temp columns to store new values and default to empty
TMP_BUDGET = RPT_COL_DAILY_BUDGET + '_'
inputDf[TMP_BUDGET] = np.nan

TMP_TARGETCPA = RPT_COL_PUBLISHER_TARGETCPA + '_'
inputDf[TMP_TARGETCPA] = np.nan

TMP_PERCENTOFBUDGET = RPT_COL_PUB_COST + '_'
inputDf[TMP_PERCENTOFBUDGET] = np.nan

# assign calculated spend percentage of daily budget to temp column
inputDf[TMP_PERCENTOFBUDGET] = inputDf[RPT_COL_PUB_COST] / inputDf[RPT_COL_DAILY_BUDGET]



######################## Changes for First ROAS Criteria ######################## 

# create DataFrame for campaigns that meet first ROAS criteria
firstDf = inputDf.copy()

# loop through first ROAS criteria
for (min_roas, max_roas, min_cost, budget_adj) in first_campaign_adj_criteria:

	print(f"Applying first adj criteria: min roas={min_roas}, max roas={max_roas}, min cost={min_cost}, budget adj={budget_adj}")

	matched_campaigns = (firstDf[RPT_COL_ROAS] >= min_roas) & \
						(firstDf[RPT_COL_ROAS] < max_roas) & \
						(firstDf[RPT_COL_PUB_COST] >= min_cost)

	if sum(matched_campaigns) > 0:
		print("matched campaigns: ", sum(matched_campaigns))
		
		print("new budget", budget_adj)

		firstDf.loc[matched_campaigns, TMP_BUDGET] = budget_adj

		print("adj applied", tableize(firstDf.loc[matched_campaigns]))

# define a new DataFrame to be populated with only the rows needed for upload, i.e there's a budget adjustment
changed_firstDf = pd.DataFrame()

# find campaigns in the firstDf DataFrame with an adjusted budget and copy them to a new DataFrame
changed = (firstDf[TMP_BUDGET].notnull() & (firstDf[RPT_COL_DAILY_BUDGET] != firstDf[TMP_BUDGET]))

if sum(changed) > 0:

	print("== Campaigns with Budget Adjusted ==", tableize(firstDf.loc[changed]))

	# assign changed rows to a DataFrame that will be used in final outputDf
	cols = [RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, TMP_BUDGET, RPT_COL_PUBLISHER_TARGETCPA, RPT_COL_INTRADAY_ADJUSTED]
	changed_firstDf = firstDf.loc[changed, cols].copy() \
					.rename(columns = {TMP_BUDGET: BULK_COL_DAILY_BUDGET})

	# populate intraday adj dimension with timestamp
	changed_firstDf[RPT_COL_INTRADAY_ADJUSTED] = timestmp

	print("changed_firstDf", tableize(changed_firstDf))



######################## Changes for Second ROAS Criteria ######################## 

# create DataFrame for campaigns that meet second ROAS criteria
secondDf = inputDf.copy()

# loop through second ROAS criteria
for (min_roas, max_roas, budget_adj) in second_campaign_adj_criteria:

	print(f"Applying second adj criteria: min roas={min_roas}, max roas={max_roas}, budget adj={budget_adj}")

	matched_campaigns = (secondDf[RPT_COL_ROAS] >= min_roas) & \
						(secondDf[RPT_COL_ROAS] < max_roas)


	if sum(matched_campaigns) > 0:
		print("matched campaigns: ", sum(matched_campaigns))
		new_budget = (secondDf.loc[matched_campaigns, RPT_COL_PUB_COST] * budget_adj)
		
		print("new_budget", new_budget)

		secondDf.loc[matched_campaigns, TMP_BUDGET] = new_budget

		print("adj applied", tableize(secondDf.loc[matched_campaigns]))

# define a new DataFrame to be populated with only the rows needed for upload, i.e there's a budget adjustment
changed_secondDf = pd.DataFrame()

# find campaigns in the firstDf DataFrame with an adjusted budget and copy them to a new DataFrame
changed = (secondDf[TMP_BUDGET].notnull() & (secondDf[RPT_COL_DAILY_BUDGET] != secondDf[TMP_BUDGET]))

if sum(changed) > 0:

	print("== Campaigns with Budget Adjusted ==", tableize(secondDf.loc[changed]))

	# assign changed rows to a DataFrame that will be used in final outputDf
	cols = [RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, TMP_BUDGET, RPT_COL_PUBLISHER_TARGETCPA, RPT_COL_INTRADAY_ADJUSTED]
	changed_secondDf = secondDf.loc[changed, cols].copy() \
					.rename(columns = {TMP_BUDGET: BULK_COL_DAILY_BUDGET})

	# populate intraday adj dimension with timestamp
	changed_secondDf[RPT_COL_INTRADAY_ADJUSTED] = timestmp

else:

	print("Empty changed_secondDf")
	changed_secondDf = changed_secondDf.iloc[0:0]


######################## Changes for Third ROAS Criteria ######################## 

# create DataFrame for campaigns that meet second ROAS criteria
thirdDf = inputDf.copy()

# loop through second ROAS criteria
for (min_roas, max_roas, min_custom, max_custom, tcpa_adj, budget_adj) in third_campaign_adj_criteria:

	print(f"Applying third adj criteria: min roas={min_roas}, max roas={max_roas}, min custom={min_custom}, max custom={max_custom}, tcpa adj={tcpa_adj}, budget adj={budget_adj}")

	matched_campaigns = (thirdDf[RPT_COL_ROAS] >= min_roas) & \
						(thirdDf[RPT_COL_ROAS] < max_roas) & \
						(thirdDf[TMP_PERCENTOFBUDGET] >= min_custom) & \
						(thirdDf[TMP_PERCENTOFBUDGET] < max_custom) 


	if sum(matched_campaigns) > 0:
		print("matched campaigns: ", sum(matched_campaigns))
		new_budget = thirdDf.loc[matched_campaigns, RPT_COL_DAILY_BUDGET] * (1 + budget_adj)
		thirdDf.loc[matched_campaigns, TMP_BUDGET] = new_budget

		new_tcpa = thirdDf.loc[matched_campaigns, RPT_COL_PUBLISHER_TARGETCPA] * (1 + tcpa_adj)
		thirdDf.loc[matched_campaigns, TMP_TARGETCPA] = new_tcpa

		#print("adj applied", tableize(thirdDf.loc[matched_campaigns]))

# define a new DataFrame to be populated with only the rows needed for upload, i.e there's a budget adjustment
changed_thirdDf = pd.DataFrame()

# find campaigns in the firstDf DataFrame with an adjusted budget and copy them to a new DataFrame
changed = (thirdDf[TMP_BUDGET].notnull() & (thirdDf[RPT_COL_DAILY_BUDGET] != thirdDf[TMP_BUDGET])) | \
		  (thirdDf[TMP_TARGETCPA].notnull() & (thirdDf[RPT_COL_PUBLISHER_TARGETCPA] != thirdDf[TMP_TARGETCPA]))

if sum(changed) > 0:

	print("== Campaigns with Budget Adjusted By Third Criteria ==", tableize(thirdDf.loc[changed]))

	# assign changed rows to a DataFrame that will be used in final outputDf
	cols = [RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, TMP_BUDGET, TMP_TARGETCPA, RPT_COL_INTRADAY_ADJUSTED]
	changed_thirdDf = thirdDf.loc[changed, cols].copy() \
					.rename(columns = { \
						TMP_BUDGET: BULK_COL_DAILY_BUDGET, \
						TMP_TARGETCPA: BULK_COL_PUBLISHER_TARGETCPA \
						})

	# populate intraday adj dimension with timestamp
	changed_thirdDf[RPT_COL_INTRADAY_ADJUSTED] = timestmp

	print("== Campaigns with Budget Adjusted By Third Criteria ==", tableize(changed_thirdDf))

else:

	print("Empty changed_thirdDf - no third criteria campaign budget changes for")
	changed_thirdDf = changed_thirdDf.iloc[0:0]


######################## Changes for Fourth ROAS Criteria ######################## 

# create DataFrame for campaigns that meet fourth ROAS criteria
fourthDf = inputDf.copy()

# loop through fourth ROAS criteria
for (min_roas, tcpa_adj, budget_adj) in fourth_campaign_adj_criteria:

	print(f"Applying fourth adj criteria: min roas={min_roas}, tcpa adj={tcpa_adj}, budget adj={budget_adj}")

	matched_campaigns = (fourthDf[RPT_COL_ROAS] >= min_roas)

	if sum(matched_campaigns) > 0:
		print("matched campaigns: ", sum(matched_campaigns))
		
		new_budget = fourthDf.loc[matched_campaigns, RPT_COL_DAILY_BUDGET] * (1 + budget_adj)
		fourthDf.loc[matched_campaigns, TMP_BUDGET] = new_budget

		new_tcpa = fourthDf.loc[matched_campaigns, RPT_COL_PUBLISHER_TARGETCPA] * (1 + tcpa_adj)
		fourthDf.loc[matched_campaigns, TMP_TARGETCPA] = new_tcpa

		print("adj applied", tableize(fourthDf.loc[matched_campaigns]))

# define a new DataFrame to be populated with only the rows needed for upload, i.e there's a budget adjustment
changed_fourthDf = pd.DataFrame()

# find campaigns in the firstDf DataFrame with an adjusted budget and copy them to a new DataFrame
changed = (fourthDf[TMP_BUDGET].notnull() & (fourthDf[RPT_COL_DAILY_BUDGET] != fourthDf[TMP_BUDGET]))

if sum(changed) > 0:

	print("== Campaigns with Budget Adjusted ==", tableize(fourthDf.loc[changed]))

	# assign changed rows to a DataFrame that will be used in final outputDf
	cols = [RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, TMP_BUDGET, TMP_TARGETCPA, RPT_COL_INTRADAY_ADJUSTED]
	changed_fourthDf = fourthDf.loc[changed, cols].copy() \
					.rename(columns = { \
						TMP_BUDGET: BULK_COL_DAILY_BUDGET, \
						TMP_TARGETCPA: BULK_COL_PUBLISHER_TARGETCPA \
						})

	# populate intraday adj dimension with timestamp
	changed_fourthDf[RPT_COL_INTRADAY_ADJUSTED] = timestmp

	print("changed_firstDf", tableize(changed_fourthDf))



######################## Changes for Fifth ROAS Criteria ######################## 

# create DataFrame for campaigns that meet fourth ROAS criteria
fifthDf = inputDf.copy()

# loop through fourth ROAS criteria
for (min_roas, max_roas, budget_adj, tcpa_adj) in fifth_campaign_adj_criteria:

	print(f"Applying fifth adj criteria: min roas={min_roas}, max roas={max_roas}, budget adj={budget_adj}, tcpa adj={tcpa_adj}")

	matched_campaigns = (fifthDf[RPT_COL_ROAS] >= min_roas) & \
						(fifthDf[RPT_COL_ROAS] < max_roas)

	if sum(matched_campaigns) > 0:
		print("matched campaigns: ", sum(matched_campaigns))
		
		new_budget = fifthDf.loc[matched_campaigns, RPT_COL_DAILY_BUDGET] * (1 + budget_adj)
		fifthDf.loc[matched_campaigns, TMP_BUDGET] = new_budget

		new_tcpa = fifthDf.loc[matched_campaigns, RPT_COL_PUBLISHER_TARGETCPA] * (1 + tcpa_adj)
		fifthDf.loc[matched_campaigns, TMP_TARGETCPA] = new_tcpa

		print("adj applied", tableize(fifthDf.loc[matched_campaigns]))

# define a new DataFrame to be populated with only the rows needed for upload, i.e there's a budget adjustment
changed_fifthDf = pd.DataFrame()

# find campaigns in the fifthDf DataFrame with an adjusted budget and copy them to a new DataFrame
changed = (fifthDf[TMP_BUDGET].notnull() & (fifthDf[RPT_COL_DAILY_BUDGET] != fifthDf[TMP_BUDGET]))

if sum(changed) > 0:

	print("== Campaigns with Budget Adjusted ==", tableize(fifthDf.loc[changed]))

	# assign changed rows to a DataFrame that will be used in final outputDf
	cols = [RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, TMP_BUDGET, TMP_TARGETCPA, RPT_COL_INTRADAY_ADJUSTED]
	changed_fifthDf = fifthDf.loc[changed, cols].copy() \
					.rename(columns = { \
						TMP_BUDGET: BULK_COL_DAILY_BUDGET, \
						TMP_TARGETCPA: BULK_COL_PUBLISHER_TARGETCPA \
						})

	# populate intraday adj dimension with timestamp
	changed_fifthDf[RPT_COL_INTRADAY_ADJUSTED] = timestmp

	print("changed_fifthDf", tableize(changed_fifthDf))	


# Merge defined data, exlcuding those that are empty, and print the resulting outputDf
dataframes = [changed_firstDf, changed_secondDf, changed_thirdDf, changed_fourthDf, changed_fifthDf]
non_empty_dataframes = [df for df in dataframes if not df.empty]

if non_empty_dataframes:
	outputDf = pd.concat(non_empty_dataframes)
	
else:
	print("Empty outputDf")
	outputDf = outputDf.iloc[0:0]

if (outputDf[BULK_COL_PUBLISHER_TARGETCPA] < 2.50).any():
    outputDf.loc[outputDf[BULK_COL_PUBLISHER_TARGETCPA] < 2.50, BULK_COL_PUBLISHER_TARGETCPA] = 2.50
    print("outputDf:", tableize(outputDf))
else:
    print("outputDf", tableize(outputDf))

Post generated on 2025-03-11 01:25:51 GMT

07 Jun 2023

« Script 163: Mature Campaigns Pause Low ROAS Script 167: Campaign Code »

MarinOne Scripts Creator's Corner