Script 1463: Predictive Revenue (Campaigns)

Purpose:

The Python script calculates predictive revenue values for campaigns using gross lead data and program-level revenue and conversion rates.

To Elaborate

The script is designed to generate predictive revenue values at the campaign level by utilizing gross lead data and various program-level metrics. It processes input data to calculate predicted revenue based on conversion rates at different stages of the lead funnel, such as attending, accepted, application submitted for review, application in progress, interview, contacted, and gross lead conversion. The calculation involves multiplying the conversion rates by the program revenue and adjusting for the differences between stages. This approach allows businesses to forecast potential revenue from campaigns by analyzing how leads progress through different stages and their associated conversion rates.

Walking Through the Code

  1. Data Preparation:
    • The script begins by defining the primary data source and selecting relevant columns from the input data frame (inputDf).
    • It copies these columns into a new data frame (df) for further processing.
  2. Revenue Calculation:
    • A function calculate_predicted_revenue is defined to compute the predicted revenue for each row based on conversion rates and program revenue.
    • This function is applied to each row of the data frame to calculate the predicted revenue values.
  3. Output Generation:
    • An output data frame (outputDf) is created to store the results, including account, campaign, and predicted revenue columns.
    • The predicted revenue values are rounded to two decimal places and filled with zeros where necessary to handle missing data.
    • Finally, the script prints the output data frame, displaying the calculated predictive 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 2025-03-11 01:25:51 GMT

comments powered by Disqus