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\mathrm{.}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\mathrm{.}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$