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
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
##
## name: ABO Budget Assignment - Groups
## description:
##  assign values to the Meta ABO Budget dimension value at the group level for active Meta Ad sets only, removing any ABO Budget values previously assigned at the campaign level
## 
## author: 
## created: 2024-01-03
## 

# Define a function to set the ABO Budget value to 0 if Ad Set Status is not equal to 'Play'
def set_meta_abo_budget(row):
    if row['Ad Set Status'] != 'Play':
        return 0
    else:
        return row['Meta ABO Budget']

# Define a function to determine the Account based on Ad account value
def get_account(ad_account):
    if 'South Carolina 22' in ad_account:
        return 'South Carolina 22 - Meta'
    elif 'Engage WI' in ad_account:
        return 'Engage WI - Meta'
    elif 'Indiana22' in ad_account:
        return 'Indiana 22 - Meta'
    elif 'Engage NH' in ad_account:
        return 'NH - Meta'
    elif 'Graduation Alliance' in ad_account:
        return 'Graduation Alliance - Meta'
    elif 'Kansas 20' in ad_account:
        return 'KANSAS 20 - Meta'
    elif 'Arizona21' in ad_account:
        return 'Arizona 21 - Meta'
    elif 'Michigan23' in ad_account:
        return 'Michigan23+ - Meta'
    elif 'Arkansas 21' in ad_account:
        return 'Arkansas 21+ - Meta'
    elif 'AchievePoint' in ad_account:
        return 'AchievePoint Career Academy - Meta'
    elif 'American Academy' in ad_account:
        return 'The American Academy (new) - Meta'
    elif 'Engage NM' in ad_account:
        return 'Engage NM - Meta'
    elif 'Engage SC' in ad_account:
        return 'Engage SC - Meta'
    elif 'Engage MI' in ad_account:
        return 'Engage MI - Meta'
    elif 'Engage AZ' in ad_account:
        return 'Engage AZ - Meta'
    elif 'Engage GA' in ad_account:
        return 'Engage GA - Meta'
    elif 'Engage OH' in ad_account:
        return 'Engage OH - Meta'
    elif 'Missouri 21' in ad_account:
        return 'Missouri 21+ - Meta'
    else:
        return 'Unknown'

today = datetime.datetime.now(CLIENT_TIMEZONE).date()

# primary data source and columns
inputDf = dataSourceDict["1"]
RPT_COL_AD_SET_NAME = 'Ad Set Name'
RPT_COL_PUBLISHER = 'Publisher'
RPT_COL_OBJECTIVE = 'Objective'
RPT_COL_PUBLISHER_NAME = 'Publisher Name'
RPT_COL_AD_ACCOUNT = 'Ad Account'
RPT_COL_CAMPAIGN_NAME = 'Campaign Name'
RPT_COL_CAMPAIGN_STATUS = 'Campaign Status'
RPT_COL_CAMPAIGN_END_DATE = 'Campaign End Date'
RPT_COL_CAMPAIGN_START_DATE = 'Campaign Start Date'
RPT_COL_CAMPAIGN_BUDGET_TYPE = 'Campaign Budget Type'
RPT_COL_CAMPAIGN_LIFETIME_BUDGET = 'Campaign Lifetime Budget $'
RPT_COL_CAMPAIGN_DAILY_BUDGET = 'Campaign Daily Budget $'
RPT_COL_AD_SET_STATUS = 'Ad Set Status'
RPT_COL_AD_SET_START_DATE = 'Ad Set Start Date'
RPT_COL_AD_SET_END_DATE = 'Ad Set End Date'
RPT_COL_AD_SET_BUDGET_TYPE = 'Ad Set Budget Type'
RPT_COL_AD_SET_DAILY_BUDGET = 'Ad Set Daily Budget $'
RPT_COL_AD_SET_LIFETIME_BUDGET = 'Ad Set Lifetime Budget $'
RPT_COL_IMPR = 'Impr.'
RPT_COL_IMPR_CHG = 'Impr. Chg.'
RPT_COL_IMPR_CHG = 'Impr. Chg. %'
RPT_COL_MAIN_KPI = 'Main KPI'
RPT_COL_MAIN_KPI_CHG = 'Main KPI Chg.'
RPT_COL_PUB_COST = 'Pub. Cost $'
RPT_COL_MAIN_KPI_CHG = 'Main KPI Chg. %'
RPT_COL_PUB_COST__CHG = 'Pub. Cost $ Chg. %'
RPT_COL_PUB_COST__CHG = 'Pub. Cost $ Chg.'

# output columns and initial values
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_GROUP = 'Group'
BULK_COL_META_ABO_BUDGET = 'Meta ABO Budget'

# user code start here
# Create a new column 'Account' using the get_account function
inputDf['Account'] = inputDf['Ad Account'].apply(get_account)

# Assuming 'Ad Set Status' column is present in the inputDf
inputDf['Meta ABO Budget'] = inputDf.apply(lambda row: '0.0' if row['Ad Set Status'] != 'Play' else row['Ad Set Daily Budget $'], axis=1)

# Rename columns as per your requirements
outputDf.rename(columns={
    'Campaign Name': 'Campaign',
    'Ad Set Name': 'Group',
  # 'Publisher Campaign ID': 'Campaign',
  # 'Account': 'Account',
    'Ad Set Daily Budget $': 'Meta ABO Budget'
}, inplace=True)

outputDf['Group'] = inputDf['Ad Set Name'].copy()
outputDf['Campaign'] = inputDf['Campaign Name'].copy()
outputDf['Meta ABO Budget'] = inputDf['Meta ABO Budget'].copy()

# Set the 'Account' column in outputDf to have the same values as inputDf['Account']
outputDf['Account'] = inputDf['Account'].copy()

# Filter out rows with Account = 'Unknown'
outputDf = outputDf[outputDf['Account'] != 'Unknown']

# Filter out rows where 'Ad Set Budget Type' is not equal to 'Daily'
outputDf = outputDf[inputDf['Ad Set Budget Type'] == 'Daily']

# Filter out rows where 'Ad Set Start Date' is later than 1/1/22
outputDf = outputDf[inputDf['Ad Set Start Date'] > '1/1/2022']