Script 165: Intraday Adj Google tCPA and Budget

Purpose

The script adjusts the target Cost Per Acquisition (tCPA) and daily budget of Google campaigns based on Return on Advertising Spend (ROAS) and spend over the previous hours.

To Elaborate

The Python script is designed to optimize Google advertising campaigns by adjusting the target Cost Per Acquisition (tCPA) and daily budget based on specific criteria related to Return on Advertising Spend (ROAS) and spend over a defined period. The script evaluates campaigns against multiple sets of criteria, each with different thresholds for ROAS and spend, to determine the necessary adjustments. The adjustments aim to improve campaign performance by increasing or decreasing budgets and tCPA targets based on how well the campaigns are performing relative to the defined criteria. This process is intended to be executed intraday, allowing for more responsive and dynamic budget management in response to real-time campaign performance data.

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 spend, along with corresponding budget and tCPA adjustments.
Initialize Temporary Columns
- Temporary columns are created in the input DataFrame to store new budget and tCPA values. These columns are initialized with empty values.
First ROAS Criteria Adjustments
- A copy of the input DataFrame is created for campaigns meeting the first set of ROAS criteria. The script loops through the criteria, identifying campaigns that match and applying the specified budget adjustments.
Second ROAS Criteria Adjustments
- Similar to the first criteria, a new DataFrame is created for the second set of criteria. The script identifies matching campaigns and applies budget adjustments based on the criteria.
Third ROAS Criteria Adjustments
- The script processes the third set of criteria, which includes both budget and tCPA adjustments. Campaigns that meet the criteria have their budgets and tCPA adjusted accordingly.
Fourth and Fifth ROAS Criteria Adjustments
- The script continues to process the fourth and fifth sets of criteria, applying the specified adjustments to campaigns that meet the criteria.
Compile Adjusted Campaigns
- After processing all criteria, the script compiles the adjusted campaigns into a final DataFrame for output. It ensures that only campaigns with changes are included.
Final Adjustments and Output
- The script applies a final check to ensure that no campaign’s tCPA falls below a minimum threshold. The resulting DataFrame is prepared for output, containing all necessary adjustments.

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

07 Jun 2023

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

MarinOne Scripts Creator's Corner