Purpose

The script identifies campaigns with significant discrepancies between the reported two-year publisher cost and the actual publisher cost over the last two years.

To Elaborate

The Python script is designed to analyze marketing campaign data by comparing the reported two-year publisher cost against the actual publisher cost incurred over the same period. The primary goal is to identify any significant discrepancies, specifically those exceeding a 15% difference, which are then flagged for further review. This process helps ensure the accuracy of financial reporting and budget allocation, allowing businesses to detect potential errors or misreporting in their campaign cost data. By automating this comparison, the script aids in maintaining financial integrity and supports decision-making processes related to marketing expenditures.

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 only the necessary columns for processing.
2. Discrepancy Calculation
   • A function calculate_pub_cost_discrepancy is defined to compute the percentage difference between the reported publisher cost and the actual publisher cost.
   • The function checks if the publisher cost is zero to avoid division errors and calculates the discrepancy percentage.
   • If the discrepancy exceeds 5% in either direction, it flags the entry as “COST DISCREPANCY DETECTED”; otherwise, it marks it as “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 2024-11-27 06:58:46 GMT