Becker Media Hmw
Sean Arnold
4/5/2022
Introduction Of Data And Its Source
Hi! I’m Sean! You asked me to answer a few questions regarding the Budgeted Leads & Spends vs the Actual Leads & Spends for Texas colleges in January 2021. Below are my findings plus a few extra pieces of data I found interesting while examining the data. I hope you enjoy reading the following as much as I did working on it!
Data Cleaning
The provided data is from Becker Media recording Homework Google Sheets Assignment. The data contains two separate tables Budgeted Leads & Spend and Actual Leads & Spend. For ease of digestion, I will break down the details of each separately.
Budgeted Leads & Spend
The Budgeted Leads & Spend table contains 8 variables and 66 observations. Each row records the details of the predicted budget for each observation in the table. Each row of data includes the Campus, Vendor, Spend Goal, Lead Goal, and more.
The data reported in this table includes:
• Campus
• Program
• Channel (PPC (Pay per click ),PPL (People?), or Social)
• Vendor
• Lead Goal (The goal number of leads)
• Spend Goal (Amount of money wanted to be spent)
• Goal Cost Per Month
• Month (The month of the year)
Actual Leads & Spend
The Actual Leads & Spend table contains 10 variables and 1157 observations. Each row records the details of the actual Leads and Spend of each observation in the table. Each row of data includes a Date, Campus, Vendor, Program Name, and more.
The data reported in this table includes:
• Lead_ID
• Lead_timestamp_chicago (time stamp of lead Chicago time)
• Received Date
• Channel
• Vendor
• Campus
• Program Name (Name of program at campus)
• Valid Lead Status ( True False column informing if the lead is still active)
• Lead Milestone (What phase of the application process the are in EX: Applied)
• Cost
If you would like to learn more about Becker Media and the services they provide please check out their home page at https://www.beckermedia.net
Question 1
What was the lead volume and cost per lead (CPL) by campus? How did that compare to the client’s goals?
To best answer, this question I deiced a simple table visualization was in order I quickly threw together two tables to compare the number of leads, Total cost and the Average cost per lead for both the predicted and actual data sets. I felt compelled to create a bar graph to break down the leads by campus from the actual data just to see where Becker Media had the highest rate of leads.
| Campus | Actual Number of Leads | Total Cost | Average Cost per Lead |
|---|---|---|---|
| Austin | 126 | 7110.37 | 56.43 |
| Brownsville | 75 | 4010.55 | 53.47 |
| Corpus Christi | 103 | 4409.04 | 42.81 |
| Harlingen | 60 | 2748.12 | 45.80 |
| Not_Count | 107 | 5943.39 | 55.55 |
| Online | 49 | 1872.10 | 38.21 |
| Pharr | 111 | 5168.08 | 46.56 |
| SA North | 208 | 8553.75 | 41.12 |
| SA South | 101 | 4753.88 | 47.07 |
| Waco | 116 | 5216.14 | 44.97 |
| Campus | Expected Lead Volume | Expected Total Cost For Leads | Expected Average Cost Per Lead |
|---|---|---|---|
| All | 0 | 0 | NaN |
| Austin | 225 | 14603 | 64.90 |
| Brownsville | 148 | 13710 | 92.64 |
| Corpus Christi | 226 | 16110 | 71.28 |
| Harlingen | 136 | 10054 | 73.93 |
| Online | 50 | 1957 | 39.14 |
| Pharr | 259 | 17201 | 66.41 |
| SA North | 429 | 26741 | 62.33 |
| SA South | 186 | 10901 | 58.61 |
| Waco | 291 | 18283 | 62.83 |
Question 2
What were the most efficient Vendors, in terms of CPL?
The best way to answer this question is with another simple table. Based on the findings it appears that PPL Vendors were by far the most efficient in terms of CPL. The combined number of leads brought by PPL was 712. comparison to the remaining vendors only brought in a combined 344 leads. PPL vendors are the most efficient way to procure new leads.
But how many of those leads turn to enrollments?…
| Vendor | Average CPL | Number of Leads |
|---|---|---|
| PPL Online | 31.92 | 26 |
| PPL Harlingen | 32.32 | 53 |
| PPL Waco | 32.42 | 100 |
| PPL Corpus Christi | 32.78 | 81 |
| PPL Pharr | 32.81 | 99 |
| PPL Brownsville | 33.07 | 54 |
| PPL SA South | 33.79 | 66 |
| PPL SA North | 34.02 | 166 |
| PPL Austin | 34.03 | 67 |
| Archer Display | 50.81 | 105 |
| Bing Search | 53.84 | 27 |
| Google Search | 90.65 | 195 |
| Google Display | 99.31 | 17 |
Who Actually Enrolled?
It appears that Even though PPL Vendors were more successful in obtaining leads they were not as successful in obtaining enrollments. In fact, PPL collectively had 15 people enroll while search engine Vendors successfully had 30 leads enrolled.
| Vendor | Average CPL | Number of Leads | Number Enrolled | Percent Enrolled |
|---|---|---|---|---|
| PPL Online | 31.92 | 26 | 0 | 0.00 |
| PPL Harlingen | 32.32 | 53 | 0 | 0.00 |
| PPL Waco | 32.42 | 100 | 3 | 0.03 |
| PPL Corpus Christi | 32.78 | 81 | 0 | 0.00 |
| PPL Pharr | 32.81 | 99 | 3 | 0.03 |
| PPL Brownsville | 33.07 | 54 | 2 | 0.04 |
| PPL SA South | 33.79 | 66 | 2 | 0.03 |
| PPL SA North | 34.02 | 166 | 4 | 0.02 |
| PPL Austin | 34.03 | 67 | 1 | 0.01 |
| Archer Display | 50.81 | 105 | 8 | 0.08 |
| Bing Search | 53.84 | 27 | 0 | 0.00 |
| Google Search | 90.65 | 195 | 22 | 0.11 |
| Google Display | 99.31 | 17 | 0 | 0.00 |
Question 3
How does performance look by Campus and by Channel?
Based on the graphs below it seems that the PPL channel is more efficient in securing leads at all campuses. Interestingly Austins Campus had an almost even number of leads between PPL and PPC..
Question 4
How does performance vary by Requested Program by Campus and/or by Channel?
Based on the graphs below it seems that PPL is more effective in gaining leads in all programs Except for Software Development.
Below is a searchable table allowing you to find the campus or program name you are looking for.
Question 5
Are there any trends that seem to occur within the course of the month?
Based on this histogram it appears that there is a sharp decrease in the number of entries after January 9th. I would like to see if this is a monthly occurrence or if this month is just an exception.
Just For Fun
Just for fun, I wanted to see if any programs may have had a different distribution curve based on the day of the month. It is quite a lot of data to take in but I wanted to include it to show my thought process. It does not seem like there are any super interesting findings to pull from this but I wonder if it would look different if I could compare by month or if I had a larger pool of data…
For fun Prt 2
I just had to know… is there a certain program that has a distinctively higher number of leads grouped by campus… As it turns out Austin has a large portion of their bar filled by the medical assistant program.
Question 6
Evaluating performance by enrolls instead of leads (Milestone column),what are the most efficient drivers of enrollment?
Okay, so this is going to be a little messy. I wanted to show you the approach I took so here it is…
I created several different visualizations to see if any of them showed signs of being a more efficient driver of enrollment and based on these findings it seems that the program name is a good driver as well as the cost per lead. And of course, as mentioned earlier Vendors are an excellent driver of enrollment.
Vendors
Google Search is the top dog as far as obtaining enrollments. 22 enrollments of the 30 PPC come from Google Search.
Channels
Just for information Gathering. Not the most helpful visual…
Cost
It seems that the most frequent enrollment comes from leads that cost anywhere between 0 and $100. Further investigation is required to pull meaningful data from this…
Cost cont.
That’s more like it! It seems that $40 is the most effective pricing for obtaining enrollments.
Program Name
It seems that the Medical Assistant has the highest rate of leads leading to enrollment.
Question 7
What recommendations would you make to optimize the allocation of the media
plan (budgeted spend and goal leads) between channels/vendors for next month
assuming that the overall spend and campus-level lead goals remain constant,
and that there is no expected impact due to seasonality in order to maximize
the number of enrolls generated?
What a fantastic question! Based on the data I have collected so far, I would recommend focusing more of the budget on the Google search engine Vendor. They have successfully resulted in the highest number of enrollments per lead cost.
I would spend significant energy on finding what keywords will help boost Google search presences to try and increase the number of people who will see the ad. Which will hopefully increase the number of leads leading to enrollments.
Post Script
Hi, I just wanted to take a second and say thank you for the opportunity to show off my r studio skills. I know I can find so many more intriguing insights into this data but for now, I have to at least submit it. Hope you you have a wonderful rest of your day!