Purpose

Script will review all campaigns with a value assigned to the “Two Year Cost (Publisher)” dimension and compare that total to the total publisher cost over the last two years. If there is more than a 15% discrepancy, it will be noted in the “Percentage Two Year Cost Difference” dimension.

To Elaborate

The Python script aims to identify campaigns that have a significant discrepancy between the “Two Year Cost (Publisher)” dimension and the total publisher cost over the last two years. It calculates the percentage difference between the two values and flags campaigns with a discrepancy greater than 15%. The script helps identify potential issues or discrepancies in the cost allocation for campaigns.

Walking Through the Code

1. The script starts by importing the necessary libraries and defining the required constants.
2. It retrieves the primary data source and assigns it to the inputDf variable.
3. The script defines the columns used in the analysis, such as the campaign name, account, two-year cost from the publisher, and publisher cost.
4. It defines the output columns and their initial values.
5. The user-defined code starts with printing the first few rows of the input data frame.
6. The script defines a function calculate_pub_cost_discrepancy that calculates the cost discrepancy between the publisher cost and the two-year cost.
7. The function takes a row from the input data frame and extracts the publisher cost and Marin cost.
8. It checks if the publisher cost is zero and returns a message if it is.
9. The function calculates the cost discrepancy as a percentage using the formula: ((Marin cost - Publisher cost) / Publisher cost) * 100.
10. If the cost discrepancy is greater than 5% or less than -5%, the function returns “COST DISCREPANCY DETECTED”. Otherwise, it returns “NO COST DISCREPANCY”.
11. The script selects the required columns from the input data frame and assigns them to the df variable.
12. It assigns the account and campaign columns from the input data frame to the corresponding columns in the output data frame.
13. The script applies the calculate_pub_cost_discrepancy function to each row in the df data frame using the apply method and assigns the results to the “Percentage Two Year Cost Difference” column in the output data frame.

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-05-15 07:44:05 GMT