Script 1463: Predictive Revenue (Campaigns)

Purpose

The Python script calculates predictive revenue values at the campaign level using gross lead data and program-level revenue and conversion rates.

To Elaborate

The script is designed to generate predictive revenue figures for marketing campaigns by leveraging data on gross leads and various conversion rates at the program level. It processes input data to calculate potential revenue outcomes based on different stages of the lead conversion process, such as contacted, interviewed, and accepted leads. The script applies a series of calculations to estimate the revenue that can be expected from each campaign, considering the conversion rates at each stage and the associated program revenue. This allows businesses to forecast potential earnings from their marketing efforts, aiding in budget allocation and strategic planning.

Walking Through the Code

  1. Data Preparation:
    • The script begins by defining the primary data source and relevant columns from the input data frame, which includes campaign, program, and conversion rate details.
    • It selects specific columns necessary for revenue prediction calculations and creates a copy of this subset for further processing.
  2. Revenue Calculation Function:
    • A function named calculate_predicted_revenue is defined to compute the predicted revenue for each row of data. This function uses a formula that considers various conversion stages and their respective rates, multiplying these by the program revenue to estimate potential earnings.
  3. Data Processing and Output:
    • The script applies the calculate_predicted_revenue function to each row of the selected data frame, rounding the results to two decimal places and handling any missing values by filling them with zero.
    • It constructs a new output data frame containing the account, campaign, and calculated predicted revenue columns.
    • Finally, the script prints the output data frame, displaying the predicted revenue for each campaign.

Vitals

  • Script ID : 1463
  • Client ID / Customer ID: 1306926629 / 60270083
  • Action Type: Email Report
  • Item Changed: None
  • Output Columns:
  • Linked Datasource: M1 Report
  • Reference Datasource: None
  • Owner: Chris Jetton (cjetton@marinsoftware.com)
  • Created by Chris Jetton on 2024-10-29 18:03
  • Last Updated by Chris Jetton on 2024-10-29 19:34
> 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
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

##
## name: 
## description:
##  
## 
## author: 
## created: 2024-10-29
## 

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

# primary data source and columns
inputDf = dataSourceDict["1"]
RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_PROGRAM = 'Program'
RPT_COL_SCHOOL_PROGRAM = 'School_Program'
RPT_COL_ACCOUNT = 'Account'
RPT_COL_CAMPAIGN_ID = 'Campaign ID'
RPT_COL_PREDICTED_REVENUE__GROSS_LEAD_CONV = 'Predicted Revenue - Gross Lead Conv.'
RPT_COL_GROSS_LEAD_CONV = 'Gross Lead Conv.'
RPT_COL_PROGRAM_GROSS_LEAD_CONVERSION_RATE = 'Program Gross Lead Conversion Rate'
RPT_COL_PREDICTED_REVENUE__CONTACTED_CONV = 'Predicted Revenue - Contacted Conv.'
RPT_COL_CONTACTED_CONV = 'Contacted Conv.'
RPT_COL_PROGRAM_CONTACTED_CONVERSION_RATE = 'Program Contacted Conversion Rate'
RPT_COL_PREDICTED_REVENUE__INTERVIEW_CONV = 'Predicted Revenue - Interview Conv.'
RPT_COL_INTERVIEW_CONV = 'Interview Conv.'
RPT_COL_PROGRAM_INTERVIEW_CONVERSION_RATE = 'Program Interview Conversion Rate'
RPT_COL_PREDICTED_REVENUE__APPLICATION_IN_PROGRESS_CONV = 'Predicted Revenue - Application In Progress Conv.'
RPT_COL_APPLICATION_IN_PROGRESS_CONV = 'Application In Progress Conv.'
RPT_COL_PROGRAM_APPLICATION_IN_PROGRESS_CONVERSION_RATE = 'Program Application in Progress Conversion Rate'
RPT_COL_PREDICTED_REVENUE__APPLICATION_SUBMITTED_FOR_REVIEW_CONV = 'Predicted Revenue - Application Submitted for Review Conv.'
RPT_COL_APPLICATION_SUBMITTED_FOR_REVIEW_CONV = 'Application Submitted for Review Conv.'
RPT_COL_PROGRAM_APPLICATION_SUBMITTED_FOR_REVIEW_CONVERSION_RATE = 'Program Application Submitted for Review Conversion Rate'
RPT_COL_PREDICTED_REVENUE__ACCEPTED_CONV = 'Predicted Revenue - Accepted Conv.'
RPT_COL_ACCEPTED_CONV = 'Accepted Conv.'
RPT_COL_PROGRAM_ACCEPTED_CONVERSION_RATE = 'Program Accepted Conversion Rate'
RPT_COL_PREDICTED_REVENUE__ATTENDING_CONV = 'Predicted Revenue - Attending Conv.'
RPT_COL_ATTENDING_CONV = 'Attending Conv.'
RPT_COL_PROGRAM_REVENUE = 'Program Revenue'
RPT_COL_PUB_COST = 'Pub. Cost $'
RPT_COL_PREDICTED_REVENUE_LEADS = 'Predicted Revenue Leads'
RPT_COL_PREDICTED_REVENUE_ACCEPTED = 'Predicted Revenue Accepted'
RPT_COL_ACCOUNT_TRACKING_TEMPLATE = 'Account Tracking Template'
RPT_COL_CAMPAIGN_TRACKING_TEMPLATE = 'Campaign Tracking Template'
RPT_COL_CAMPAIGN_STATUS = 'Campaign Status'
RPT_COL_PUBLISHER = 'Publisher'
RPT_COL_TWO_YEAR_COST_PUBLISHER = 'Two Year Cost (Publisher)'
RPT_COL_TWO_YEAR_COST_PUBLISHER__LAST_UPDATED = 'Two Year Cost (Publisher) - Last Updated'

# output columns and initial values
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_PREDICTED_REVENUE = 'Predicted Revenue'


# Function to calculate Predicted Revenue dynamically based on column names
def calculate_predicted_revenue(row):
    return (
        (row[RPT_COL_PREDICTED_REVENUE__ATTENDING_CONV] * row[RPT_COL_PROGRAM_REVENUE]) +
        ((row[RPT_COL_PREDICTED_REVENUE__ACCEPTED_CONV] - row[RPT_COL_PREDICTED_REVENUE__ATTENDING_CONV]) * row[RPT_COL_PROGRAM_ACCEPTED_CONVERSION_RATE] * row[RPT_COL_PROGRAM_REVENUE]) +
        ((row[RPT_COL_PREDICTED_REVENUE__APPLICATION_SUBMITTED_FOR_REVIEW_CONV] - row[RPT_COL_PREDICTED_REVENUE__ACCEPTED_CONV]) * row[RPT_COL_PROGRAM_APPLICATION_SUBMITTED_FOR_REVIEW_CONVERSION_RATE] * row[RPT_COL_PROGRAM_REVENUE]) +
        ((row[RPT_COL_PREDICTED_REVENUE__APPLICATION_IN_PROGRESS_CONV] - row[RPT_COL_PREDICTED_REVENUE__APPLICATION_SUBMITTED_FOR_REVIEW_CONV]) * row[RPT_COL_PROGRAM_APPLICATION_IN_PROGRESS_CONVERSION_RATE] * row[RPT_COL_PROGRAM_REVENUE]) +
        ((row[RPT_COL_PREDICTED_REVENUE__INTERVIEW_CONV] - row[RPT_COL_PREDICTED_REVENUE__APPLICATION_IN_PROGRESS_CONV]) * row[RPT_COL_PROGRAM_INTERVIEW_CONVERSION_RATE] * row[RPT_COL_PROGRAM_REVENUE]) +
        ((row[RPT_COL_PREDICTED_REVENUE__CONTACTED_CONV] - row[RPT_COL_PREDICTED_REVENUE__INTERVIEW_CONV]) * row[RPT_COL_PROGRAM_CONTACTED_CONVERSION_RATE] * row[RPT_COL_PROGRAM_REVENUE]) +
        ((row[RPT_COL_PREDICTED_REVENUE__GROSS_LEAD_CONV] - row[RPT_COL_PREDICTED_REVENUE__CONTACTED_CONV]) * row[RPT_COL_PROGRAM_GROSS_LEAD_CONVERSION_RATE] * row[RPT_COL_PROGRAM_REVENUE])
    )



# user code start here
print(tableize(inputDf.head()))

cols = [RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_PREDICTED_REVENUE__ATTENDING_CONV, RPT_COL_PREDICTED_REVENUE__ACCEPTED_CONV, RPT_COL_PREDICTED_REVENUE__APPLICATION_SUBMITTED_FOR_REVIEW_CONV, RPT_COL_PREDICTED_REVENUE__APPLICATION_IN_PROGRESS_CONV, RPT_COL_PREDICTED_REVENUE__INTERVIEW_CONV, RPT_COL_PREDICTED_REVENUE__CONTACTED_CONV, RPT_COL_PREDICTED_REVENUE__GROSS_LEAD_CONV, RPT_COL_PROGRAM_REVENUE, RPT_COL_PROGRAM_ACCEPTED_CONVERSION_RATE, RPT_COL_PROGRAM_APPLICATION_SUBMITTED_FOR_REVIEW_CONVERSION_RATE, RPT_COL_PROGRAM_APPLICATION_IN_PROGRESS_CONVERSION_RATE, RPT_COL_PROGRAM_INTERVIEW_CONVERSION_RATE, RPT_COL_PROGRAM_CONTACTED_CONVERSION_RATE, RPT_COL_PROGRAM_GROSS_LEAD_CONVERSION_RATE]
df = inputDf[cols].copy()
#outputDf[BULK_COL_ACCOUNT] = inputDf[RPT_COL_ACCOUNT]
#outputDf[BULK_COL_CAMPAIGN] = inputDf[RPT_COL_CAMPAIGN]
#outputDf[BULK_COL_PREDICTED_REVENUE] = df.apply(calculate_predicted_revenue, axis=1).round(2)

# Define the specified columns in outputDf
outputDf = pd.DataFrame({
    BULK_COL_ACCOUNT: inputDf[RPT_COL_ACCOUNT],
    BULK_COL_CAMPAIGN: inputDf[RPT_COL_CAMPAIGN],
    BULK_COL_PREDICTED_REVENUE: df.apply(calculate_predicted_revenue, axis=1).round(2).fillna(0)
})



outputDf[BULK_COL_PREDICTED_REVENUE] = outputDf[BULK_COL_PREDICTED_REVENUE].fillna(0)

# Print only the specified columns
#print(outputDf[[BULK_COL_ACCOUNT, BULK_COL_CAMPAIGN, BULK_COL_PREDICTED_REVENUE]])

# Print only the specified columns
print(outputDf)

Post generated on 2024-11-27 06:58:46 GMT

comments powered by Disqus