Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Create data visuaization for the following: B ) Identifying Top Selling Courses To identify the top selling courses, we can analyze the data based on

Create data visuaization for the following:
B) Identifying Top Selling Courses
To identify the top selling courses, we can analyze the data based on the following metrics:
1. Total Revenue: Calculated as the sum of paid app counts across all UTMs for each program.
2. Conversion Rate: Calculated as the ratio of paid app count to total leads for each program-UTM combination.
3. Average Revenue per Lead: Calculated as the ratio of total revenue to total leads for each program.
Sql
-- Total Revenue
SELECT
p.program_name,
SUM(p.paid_app) AS total_revenue
FROM
prospect_id_course_lead_app_paid_app p
JOIN
campaign_utm_prospect_mapping c ON p.program_name = c.program_name
GROUP BY
p.program_name
ORDER BY
total_revenue DESC;
-- Conversion Rate
SELECT
p.program_name,
c.utm_campaign,
ROUND(SUM(p.paid_app)*1.0/ SUM(p.leads),2) AS conversion_rate
FROM
prospect_id_course_lead_app_paid_app p
JOIN
campaign_utm_prospect_mapping c ON p.program_name = c.program_name
GROUP BY
p.program_name, c.utm_campaign
ORDER BY
conversion_rate DESC;
-- Average Revenue per Lead
SELECT
p.program_name,
ROUND(SUM(p.paid_app)*1.0/ SUM(p.leads),2) AS avg_revenue_per_lead
FROM
prospect_id_course_lead_app_paid_app p
JOIN
campaign_utm_prospect_mapping c ON p.program_name = c.program_name
GROUP BY
p.program_name
ORDER BY
avg_revenue_per_lead DESC;
Explanation:
For total revenue, we sum the paid_app column grouped by program_name to get the total paid app count for each program, which represents the total revenue.
For conversion rate, we calculate the ratio of paid_app to leads for each program-UTM combination, rounding the result to 2 decimal places.
For average revenue per lead, we calculate the ratio of total revenue to total leads for each program, rounding the result to 2 decimal places.
Ordering the results descending allows us to identify the top performing programs based on each metric.
This is the raw data:
program_name_ic utm_campaign leads app paid_app
Program 1 UTM 1900
Program 2 UTM 26642
Program 3 UTM 3400
Program 4 UTM 4500
Program 5 UTM 51220
Program 6 UTM 63530
Program 7 UTM 7900
Program 8 UTM 8900
Program 9 UTM 98161
Program 10 UTM 103210
Program 11 UTM 115510
Program 12 UTM 121110
Program 13 UTM 132600
Program 14 UTM 14600
Program 15 UTM 151900
Program 16 UTM 16700
Program 17 UTM 17100
Program 18 UTM 181000
Program 19 UTM 19100
Program 20 UTM 205930
Program 1 UTM 21600
Program 2 UTM 221700
Program 3 UTM 23300
Program 4 UTM 241100
Program 5 UTM 251221
Program 6 UTM 263921
Program 7 UTM 273610
Program 8 UTM 281441
Program 9 UTM 293622
Program 10 UTM 304031
Program 11 UTM 311220
Program 12 UTM 321000
Program 13 UTM 33300
Program 14 UTM 342000
Program 15 UTM 351010
Program 16 UTM 3611751
Program 17 UTM 374794
Program 18 UTM 382910
Program 19 UTM 39800
Program 20 UTM 402200

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Database Design For Mere Mortals

Authors: Michael J Hernandez

4th Edition

978-0136788041

More Books

Students also viewed these Databases questions

Question

=+ (c) Show that the Bernoulli shift is mixing.

Answered: 1 week ago

Question

Determine miller indices of plane A Z a/2 X a/2 a/2 Y

Answered: 1 week ago

Question

Identify possible reasons for ineffective performance.

Answered: 1 week ago

Question

Describe the components of a needs assessment.

Answered: 1 week ago

Question

Describe the benefits of employee orientation.

Answered: 1 week ago