Answered step by step
Verified Expert Solution
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:
Total Revenue: Calculated as the sum of paid app counts across all UTMs for each program.
Conversion Rate: Calculated as the ratio of paid app count to total leads for each programUTM combination.
Average Revenue per Lead: Calculated as the ratio of total revenue to total leads for each program.
Sql
Total Revenue
SELECT
pprogramname,
SUMppaidapp AS totalrevenue
FROM
prospectidcourseleadapppaidapp p
JOIN
campaignutmprospectmapping c ON pprogramname cprogramname
GROUP BY
pprogramname
ORDER BY
totalrevenue DESC;
Conversion Rate
SELECT
pprogramname,
cutmcampaign,
ROUNDSUMppaidapp SUMpleads AS conversionrate
FROM
prospectidcourseleadapppaidapp p
JOIN
campaignutmprospectmapping c ON pprogramname cprogramname
GROUP BY
pprogramname, cutmcampaign
ORDER BY
conversionrate DESC;
Average Revenue per Lead
SELECT
pprogramname,
ROUNDSUMppaidapp SUMpleads AS avgrevenueperlead
FROM
prospectidcourseleadapppaidapp p
JOIN
campaignutmprospectmapping c ON pprogramname cprogramname
GROUP BY
pprogramname
ORDER BY
avgrevenueperlead DESC;
Explanation:
For total revenue, we sum the paidapp column grouped by programname to get the total paid app count for each program, which represents the total revenue.
For conversion rate, we calculate the ratio of paidapp to leads for each programUTM combination, rounding the result to decimal places.
For average revenue per lead, we calculate the ratio of total revenue to total leads for each program, rounding the result to decimal places.
Ordering the results descending allows us to identify the top performing programs based on each metric.
This is the raw data:
programnameic utmcampaign leads app paidapp
Program UTM
Program UTM
Program UTM
Program UTM
Program UTM
Program UTM
Program UTM
Program UTM
Program UTM
Program UTM
Program UTM
Program UTM
Program UTM
Program UTM
Program UTM
Program UTM
Program UTM
Program UTM
Program UTM
Program UTM
Program UTM
Program UTM
Program UTM
Program UTM
Program UTM
Program UTM
Program UTM
Program UTM
Program UTM
Program UTM
Program UTM
Program UTM
Program UTM
Program UTM
Program UTM
Program UTM
Program UTM
Program UTM
Program UTM
Program UTM
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started