Purpose:

The script identifies campaigns with a significant discrepancy between the assigned “Two Year Cost (Publisher)” and the actual publisher cost over the last two years.

To Elaborate

The Python script is designed to review marketing campaigns by comparing the “Two Year Cost (Publisher)” with the actual publisher costs incurred over the last two years. The primary goal is to identify any significant discrepancies, specifically those exceeding a 15% difference. This is crucial for ensuring accurate financial reporting and budget allocation. The script processes data from a specified data source, calculates the percentage difference for each campaign, and flags those with discrepancies. This helps in maintaining financial integrity and aids in the decision-making process for budget adjustments.

Walking Through the Code

1. Data Preparation
   • The script begins by defining the primary data source and relevant columns, such as ‘Campaign’, ‘Account’, ‘Two Year Cost (Publisher)’, and ‘Pub. Cost $’.
   • It creates a copy of the input data frame with these columns for further processing.
2. Discrepancy Calculation
   • A function calculate_pub_cost_discrepancy is defined to compute the percentage difference between the “Two Year Cost (Publisher)” and the actual publisher cost.
   • If the publisher cost is zero, it returns a specific message indicating this condition.
   • The function checks if the discrepancy exceeds 5% in either direction and flags it as “COST DISCREPANCY DETECTED” if true; otherwise, it returns “NO COST DISCREPANCY”.
3. Output Generation
   • The script applies the discrepancy calculation function to each row of the data frame.
   • It populates the output data frame with the account, campaign, and the calculated discrepancy status for each campaign.

Vitals

• Script ID : 1001
• Client ID / Customer ID: 1306926629 / 60270083
• Action Type: Bulk Upload
• Item Changed: Campaign
• Output Columns: Account, Campaign, Percentage Two Year Cost Difference
• Linked Datasource: M1 Report
• Reference Datasource: None
• Owner: Chris Jetton (cjetton@marinsoftware.com)
• Created by Chris Jetton on 2024-04-24 22:02
• Last Updated by Chris Jetton on 2024-05-03 15:13

> 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
##
## name: Campaign Two Year Pub Cost Discrepancy Percentage
## description:
##  
## 
## author: 
## created: 2024-04-24
## 

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

# primary data source and columns
inputDf = dataSourceDict["1"]
RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_ACCOUNT = 'Account'
RPT_COL_TWO_YEAR_COST_PUBLISHER = 'Two Year Cost (Publisher)'
RPT_COL_PUB_COST = 'Pub. Cost $'

# output columns and initial values
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_PERCENTAGE_TWO_YEAR_COST_DIFFERENCE = 'Percentage Two Year Cost Difference'
#outputDf[BULK_COL_PERCENTAGE_TWO_YEAR_COST_DIFFERENCE] = "<<YOUR VALUE>>"

# user code start here
print(tableize(inputDf.head()))
def calculate_pub_cost_discrepancy(row):
    publisher_cost = row[RPT_COL_TWO_YEAR_COST_PUBLISHER]
    marin_cost = row[RPT_COL_PUB_COST]
    print('Publisher Cost:')
    print(publisher_cost)
    print('Marin Cost:')
    print(marin_cost)

    if publisher_cost == 0:
        return "Publisher cost is zero"
        
    cost_discrepancy = ((marin_cost - publisher_cost) / publisher_cost) * 100
    
    if cost_discrepancy > 5.0 or cost_discrepancy < -5.0:
        return "COST DISCREPANCY DETECTED"
    else:
        return "NO COST DISCREPANCY"


cols = [RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_TWO_YEAR_COST_PUBLISHER, RPT_COL_PUB_COST]
df = inputDf[cols].copy()
outputDf[BULK_COL_ACCOUNT] = inputDf[RPT_COL_ACCOUNT]
outputDf[BULK_COL_CAMPAIGN] = inputDf[RPT_COL_CAMPAIGN]
outputDf[BULK_COL_PERCENTAGE_TWO_YEAR_COST_DIFFERENCE] = df.apply(calculate_pub_cost_discrepancy, axis=1)

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