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
##
## name: SBA Campaigns Watcher
## description:
##  
## 
## author: 
## created: 2023-12-19
## 

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

# primary data source and columns
inputDf = dataSourceDict["1"]
RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_PUBLISHER = 'Publisher'
RPT_COL_ACCOUNT = 'Account'
RPT_COL_CAMPAIGN_STATUS = 'Campaign Status'
RPT_COL_CAMPAIGN_TYPE = 'Campaign Type'
RPT_COL_GOAL = 'Goal'
RPT_COL_TARGET_IMPR_PER_SPENDVIEWS = 'Target (Impr/Spend/Views)'
RPT_COL_PACING__MONTHLY_BUDGET = 'Pacing - Monthly Budget'
RPT_COL_SBA_PAUSE_DATE = 'SBA Pause Date'
RPT_COL_SBA_TRAFFIC = 'SBA Traffic'
RPT_COL_PUB_COST = 'Pub. Cost $'
RPT_COL_CLICKS = 'Clicks'
RPT_COL_IMPR = 'Impr.'

# output columns and initial values
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_STATUS = 'Status'
BULK_COL_SBA___PROJECTED_2HR_TOTAL = 'SBA -  projected 2hr total'  # Corrected variable name
BULK_COL_PACING__MONTHLY_BUDGET = 'Pacing - Monthly Budget'
BULK_COL_SBA_PAUSE_DATE = 'SBA Pause Date'
BULK_COL_SBA_WATCHER_RECOMMENDED_STATUS = 'SBA Watcher Recommended Status'
outputDf[BULK_COL_STATUS] = "<<YOUR VALUE>>"
outputDf[BULK_COL_SBA___PROJECTED_2HR_TOTAL] = "<<YOUR VALUE>>"  # Corrected usage
outputDf[BULK_COL_SBA_PAUSE_DATE] = "<<YOUR VALUE>>"
outputDf[BULK_COL_SBA_WATCHER_RECOMMENDED_STATUS] = "<<YOUR VALUE>>"
outputDf[BULK_COL_PACING__MONTHLY_BUDGET] = "<<YOUR VALUE>>"

# Define column data types
col_dtypes = {
    'Campaign': str,
    'Publisher': str,
    'Account': str,
    'Campaign Status': str,
    'Campaign Type': str,
    'Pacing - Budget Allocation Goal': str,
    'Pacing - Monthly Budget': str,
    'Pub. Cost $': str,
    'SBA Traffic': str,
    'Impr.': str,
    'Clicks': str
}


df = inputDf.copy()

# List of columns that need to be converted to numeric
numeric_cols = [RPT_COL_PUB_COST, RPT_COL_IMPR, RPT_COL_CLICKS]

for col in numeric_cols:
    # Convert the column to string type before replacing commas
    df[col] = pd.to_numeric(df[col].astype(str).str.replace(',', ''), errors='coerce')

# Calculation steps
#  for each campaign, figure out how much will spend in the next two hours
#  make a total that adds what has been spent with the projection
#  if this exceeds the Monthly Budget then
#    output Status of Pause and output the timestamp in the SBA Pause Date
#    otherwise do not output row

# 1. Calculate projected spend
now = datetime.datetime.now()
start_of_month = datetime.datetime(now.year, now.month, 1)
total_hours_elapsed = math.floor((now - start_of_month).total_seconds() / 3600)

df['hourly_avg_spend'] = df[RPT_COL_PUB_COST] / total_hours_elapsed
df['SBA - projected 2hr total'] = df['hourly_avg_spend'] * 2

# 2. Make a total that adds what has been spent with the projected next two-hour spend
df['projected_total'] = df[RPT_COL_PUB_COST] + df['SBA - projected 2hr total']

# 3. Calculate the Monthly Budget by multiplying the cost by the number of impressions
df[BULK_COL_PACING__MONTHLY_BUDGET] = df[RPT_COL_PUB_COST] * df[RPT_COL_IMPR].max()

# Check if the projected total exceeds the Monthly Budget and recommend Pausing
df['SBA Watcher Recommended Status'] = np.where(df['projected_total'] >= df[BULK_COL_PACING__MONTHLY_BUDGET], 'Paused', df[RPT_COL_CAMPAIGN_STATUS])

# Assign the recommended status to the Status column only if SBA Traffic == 1, otherwise keep the original Status
df[RPT_COL_CAMPAIGN_STATUS] = np.where((df['SBA Traffic'] == '1') & (df['SBA Watcher Recommended Status'] == 'Paused'), df['SBA Watcher Recommended Status'], df[RPT_COL_CAMPAIGN_STATUS])

# Only assign SBA Pause Date if SBA Traffic equals 1 and the recommended status is 'Paused'
current_date = pd.Timestamp.now()
df['SBA Pause Date'] = np.where((df['SBA Traffic'] == '1') & (df['SBA Watcher Recommended Status'] == 'Paused') & (df[RPT_COL_CAMPAIGN_STATUS] == 'Active'), current_date, df['SBA Pause Date'])

# Write df to a CSV file with UTF-16LE encoding
#with open(inputfile + '.out.txt', 'w', encoding='utf-16le') as f:
    #df.to_csv(f, sep='\t', index=False)

# Filter rows where SBA Watcher Recommended Status is Paused
outputDf = pd.DataFrame(columns=[BULK_COL_ACCOUNT, BULK_COL_CAMPAIGN, BULK_COL_STATUS, BULK_COL_PACING__MONTHLY_BUDGET, BULK_COL_SBA___PROJECTED_2HR_TOTAL, BULK_COL_SBA_PAUSE_DATE, BULK_COL_SBA_WATCHER_RECOMMENDED_STATUS])

print(outputDf)

# Save to BulkSheet.txt
#outputDf.to_csv('WatcherBulkSheet.txt', sep='\t', index=False)

#print(tableize(inputDf))