Script 165: Intraday Adj Google tCPA and Budget

Purpose

Adjust the tCPA and Daily Budget of Google Campaigns based on the ROAS and spend over a specific time period.

To Elaborate

This Python script adjusts the tCPA (target cost per acquisition) and daily budget of Google Campaigns based on the ROAS (return on ad spend) and spend over a specific time period. The script applies different adjustment criteria to determine the new tCPA and budget for each campaign. The goal is to optimize campaign performance by adjusting the bidding strategy and budget allocation.

The script uses predefined criteria for each campaign to determine the adjustments. The criteria include ranges for ROAS, spend, and custom metrics. The script loops through each criteria and applies the corresponding adjustments to the campaigns that meet the criteria. The adjusted campaigns are then stored in separate dataframes for further processing.

The script also checks if the adjusted tCPA is below a minimum threshold and adjusts it to the threshold if necessary. Finally, the script merges the adjusted campaigns from all dataframes into a single output dataframe.

Overall, this script automates the process of adjusting tCPA and daily budget for Google Campaigns based on performance metrics, allowing for more efficient campaign management and optimization.

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-05-15 07:44:05 GMT

07 Jun 2023

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

MarinOne Scripts Creator's Corner