Script 445: SBA inactive campaigns

Purpose:

The Python script identifies inactive campaigns based on publication costs and predicted user visits within a structured budget allocation framework.

To Elaborate

The Python script is designed to analyze marketing campaign data to identify inactive campaigns within a structured budget allocation (SBA) framework. It processes data to determine which campaigns have not incurred any publication costs over the last five days and evaluates their performance against predicted user visits. The script filters campaigns based on their end dates, calculates the maximum predicted user visit differences for each SBA bucket, and identifies campaigns with zero publication costs in the recent past. It then updates the status of these campaigns to ‘Inactive’ or ‘Active’ and assesses whether they are on target or not pacing based on their predicted user visit differences.

Walking Through the Code

  1. Data Preparation:
    • The script begins by converting relevant date columns to datetime objects for accurate date comparisons.
    • It filters the dataset to include only campaigns with end dates in the current month and year.
  2. Data Aggregation:
    • The script groups the data by SBA bucket name to find the maximum predicted user visit difference for each bucket.
    • It calculates a five-day window to assess publication costs and filters campaigns with zero costs during this period.
  3. Filtering and Merging:
    • The script identifies distinct SBA buckets with zero publication costs and filters the dataset accordingly.
    • It merges the filtered data with the maximum predicted user visit differences and five-day publication costs.
  4. Status and Pacing Evaluation:
    • The script updates the campaign status to ‘Inactive’ if the publication cost is zero, otherwise ‘Active’.
    • It evaluates the pacing of campaigns, marking them as ‘On target’ if the predicted user visit difference is above 95, otherwise ‘Not pacing’.
  5. Output Preparation:
    • The final dataset, containing campaign status and pacing information, is prepared for output.

Vitals

  • Script ID : 445
  • Client ID / Customer ID: 1306923673 / 60269245
  • Action Type: Email Report
  • Item Changed: None
  • Output Columns:
  • Linked Datasource: M1 Report
  • Reference Datasource: None
  • Owner: Jonathan Reichl (jreichl@marinsoftware.com)
  • Created by Jonathan Reichl on 2023-10-26 13:36
  • Last Updated by Stephen Malina on 2023-12-11 15:41
> 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
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88

##
## name: 
## description:
##  
## 
## author: undefined
## created: 2023-10-26
## 

DEVICE = {
  'MOBILE': 'm',
  'DESKTOP': 'c',
  'TABLET': 't',
}
today = datetime.datetime.now(CLIENT_TIMEZONE).date()
current_month = today.month
current_year = today.year

# primary data source and columns
inputDf = dataSourceDict["1"]
RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_DATE = 'Date'
RPT_COL_SBA_BUCKET_NAME = 'SBA Bucket Name'
RPT_COL_PUB_COST = 'Pub. Cost $'
RPT_COL_SBA_PREDICTED_UV_DIF = 'SBA Predicted UV Dif'
RPT_COL_SBA_PREDICTED = 'SBA Predicted UVs'
RPT_COL_CAMPAIGN_STATUS = 'Campaign Status'
RPT_COL_SBA_BUCKET_END_DATE = 'SBA Bucket End Date'
RPT_COL_SBA_MODEL_TARGET = 'SBA Budget Model Target'

# output columns and initial values
BULK_COL_ACCOUNT = 'Account'

#outputDf[BULK_COL_SOCIAL_PLAYPAUSE_UPDATE_STATUS] = "<<YOUR VALUE>>"

# user code start here
print(tableize(inputDf))
inputDf[RPT_COL_SBA_BUCKET_END_DATE] = pd.to_datetime(inputDf[RPT_COL_SBA_BUCKET_END_DATE])
inputDf[RPT_COL_DATE] = pd.to_datetime(inputDf[RPT_COL_DATE])

#only campaigns with end dates > today 
filtered_df = inputDf[(inputDf[RPT_COL_SBA_BUCKET_END_DATE].dt.month == current_month) & (inputDf[RPT_COL_SBA_BUCKET_END_DATE].dt.year == current_year)]

# code to find max predicted UV not needed as we will apply to all buckets
result_df = filtered_df.groupby(RPT_COL_SBA_BUCKET_NAME)[RPT_COL_SBA_PREDICTED_UV_DIF].max().reset_index()
# Rename the column to 'Bucket Name' and 'Conversions' (optional)
result_df.columns = [RPT_COL_SBA_BUCKET_NAME, RPT_COL_SBA_PREDICTED_UV_DIF]

## Calculate the date threshold (last 5 days)
start_date = pd.to_datetime(today - datetime.timedelta(days=5))
today = pd.to_datetime(today)

five_day_df = filtered_df[(filtered_df[RPT_COL_DATE] >= start_date) & (filtered_df[RPT_COL_DATE] <= today)]

# Group by 'RPT_COL_CAMPAIGN' and 'RPT_COL_SBA_BUCKET_NAME' and sum 'RPT_COL_PUB_COST'
five_day_df = five_day_df.groupby([RPT_COL_CAMPAIGN, RPT_COL_SBA_BUCKET_NAME])[RPT_COL_PUB_COST].sum().reset_index()

# Filter the DataFrame to include rows where 'RPT_COL_PUB_COST' is equal to 0
five_day_df_filter = five_day_df[five_day_df[RPT_COL_PUB_COST] == 0]

# Get a distinct list of 'RPT_COL_SBA_BUCKET_NAME'
distinct_buckets = five_day_df_filter[RPT_COL_SBA_BUCKET_NAME].unique()

# Update filtered_df to only include rows with 'RPT_COL_SBA_BUCKET_NAME' in distinct_buckets
filtered_df = filtered_df[filtered_df[RPT_COL_SBA_BUCKET_NAME].isin(distinct_buckets)]

workingdf = filtered_df

workingdf = workingdf[[RPT_COL_CAMPAIGN, RPT_COL_SBA_BUCKET_NAME]].drop_duplicates()

## add predicted target 
workingdf = workingdf.merge(result_df, on=RPT_COL_SBA_BUCKET_NAME, how='left')


## add 5 day cost 
workingdf = workingdf.merge(five_day_df[[RPT_COL_CAMPAIGN, RPT_COL_PUB_COST]], on=RPT_COL_CAMPAIGN, how='left')

# Update 'Status' column based on 'RPT_COL_PUB_COST'
workingdf['Status'] = workingdf[RPT_COL_PUB_COST].apply(lambda x: 'Inactive' if x == 0 else 'Active')

# Update 'Pacing' column based on 'RPT_COL_SBA_PREDICTED_UV_DIF'
workingdf['Pacing'] = workingdf[RPT_COL_SBA_PREDICTED_UV_DIF].apply(lambda x: 'On target' if x > 95 else 'Not pacing')


outputDf = workingdf

#outputDf = outputDf.sort_values(by=RPT_COL_SBA_BUCKET_NAME)

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

comments powered by Disqus