Script 445: SBA inactive campaigns

Purpose

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

To Elaborate

The script processes campaign data to identify inactive campaigns within a structured budget allocation (SBA) framework. It filters campaigns with end dates in the current month and year, then calculates the maximum predicted user visit difference for each SBA bucket. It further narrows down the data to campaigns with zero publication costs over the last five days. The script then identifies distinct SBA buckets associated with these inactive campaigns. Finally, it updates the campaign status to ‘Inactive’ if the publication cost is zero and evaluates the pacing status based on the predicted user visit difference, marking it as ‘On target’ if the difference exceeds 95.

Walking Through the Code

  1. Data Preparation
    • The script begins by converting date columns to datetime objects for accurate date comparisons.
    • It filters the data to include only campaigns with end dates in the current month and year.
  2. Data Aggregation
    • It groups the filtered data by SBA bucket name to find the maximum predicted user visit difference for each bucket.
    • A five-day window is established to sum publication costs for each campaign and SBA bucket.
  3. Filtering Inactive Campaigns
    • The script identifies campaigns with zero publication costs over the last five days.
    • It extracts distinct SBA buckets associated with these inactive campaigns.
  4. Data Merging and Status Update
    • The script merges the filtered data with the maximum predicted user visit difference and five-day publication costs.
    • It updates the campaign status to ‘Inactive’ if the publication cost is zero and evaluates the pacing status based on the predicted user visit difference.

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

comments powered by Disqus