/
ads_conversions_ROI.txt
160 lines (145 loc) · 4.68 KB
/
ads_conversions_ROI.txt
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
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
Ads
Here we have 2 tables
adv_info: advertiser_id | ad_id | spend: The Advertiser pay for this ad
ad_info: ad_id | user_id | price: The user spend through this ad (Assume all prices in this column >0)
CREATE TABLE adv_info (
advertiser_id int,
ad_id int,
spend float
);
INSERT INTO adv_info VALUES (10, 200,300);
INSERT INTO adv_info VALUES (11, 100,1000);
INSERT INTO adv_info VALUES (13, 400,3000);
INSERT INTO adv_info VALUES (14, 500,5000);
CREATE TABLE ad_info (
ad_id int,
user_id int,
price float
);
INSERT INTO ad_info VALUES (200, 4,30);
INSERT INTO ad_info VALUES (100, 4,100);
INSERT INTO ad_info VALUES (400, 4,300);
INSERT INTO ad_info VALUES (200, 10,31);
INSERT INTO ad_info VALUES (100, 10,110);
INSERT INTO ad_info VALUES (400, 10,310);
select *
from ad_info;
+-------+---------+-------+
| ad_id | user_id | price |
+-------+---------+-------+
| 200 | 4 | 30 |
| 100 | 4 | 100 |
| 400 | 4 | 300 |
| 200 | 10 | 31 |
| 100 | 10 | 110 |
| 400 | 10 | 310 |
+-------+---------+-------+
select *
from adv_info;
+---------------+-------+-------+
| advertiser_id | ad_id | spend |
+---------------+-------+-------+
| 10 | 200 | 300 |
| 11 | 100 | 1000 |
| 13 | 400 | 3000 |
| 14 | 500 | 5000 |
+---------------+-------+-------+
1) THE FRACTION OF ADVERTISER HAS ATLEAST 1 CONVERSION?
MYSQL
select cnt_adv /total coverstion_ratio
from (select count(distinct a.advertiser_id) cnt_adv
from adv_info a
join (select ad_id from ad_info) b
on a.ad_id=b.ad_id)
temp,
(select count(distinct a.advertiser_id) total
from adv_info a
) tmp2;
POSTGRESQL
NOTE: ABBREVATION IS NOT NEEDED AS THEY ARE UNIQUE
select cast(conv as float) /total res
from
( select count(distinct(a.ad_id)) conv
from ad_info a
join adv_info b
on a.ad_id=b.ad_id
)tmp,
(
select count(distinct(ad_id)) total
from adv_info
)tmp1;
# Another query to get the same results
select tmp1.cnv_cnt/tmp2.total_adv as fraction
from (select count(distinct a.advertiser_id) as cnv_cnt
from adv_info a join ad_info u on a.ad_id=u.ad_id
) tmp1 ,
(select count(distinct advertiser_id) as total_adv
from adv_info
)
tmp2;
+----------+
| fraction |
+----------+
| 0.7500 |
+----------+
2) WHAT METRICS WOULD YOU SHOW TO ADVERTISERS (ROI)?
REVENUE / SPENDING
select b.ad_id,b.advertiser_id , a.revenue/b.total roi
from
(select ad_id,sum(price) revenue
from ad_info
group by ad_id)a
,
(select ad_id
, advertiser_id
, sum(spend) as total
from adv_info
group by ad_id,advertiser_id
)b
where a.ad_id=b.ad_id;
+---------------+-------+---------------------+
| advertiser_id | ad_id | roi |
+---------------+-------+---------------------+
| 10 | 200 | 0.20333333333333334 |
| 11 | 100 | 0.21 |
| 13 | 400 | 0.20333333333333334 |
+---------------+-------+---------------------+
select tmp1.advertiser_id,tmp1.ad_id,(tmp2.sum_rev/tmp1.sum_spend)as ROI
from (select advertiser_id,ad_id,sum(spend) as sum_spend
from adv_info
group by advertiser_id,ad_id
) tmp1
join ( select u.ad_id,sum(u.price) as sum_rev
from ad_info u
join adv_info a on u.ad_id=a.ad_id
group by u.ad_id
) tmp2
on tmp1.ad_id=tmp2.ad_id;
+---------------+-------+---------------------+
| advertiser_id | ad_id | ROI |
+---------------+-------+---------------------+
| 10 | 200 | 0.20333333333333334 |
| 11 | 100 | 0.21 |
| 13 | 400 | 0.20333333333333334 |
+---------------+-------+---------------------+
CAST TO DECIMAL
select a.advertiser_id
, a.ad_id
, cast(b.ad_spend/a.spend as decimal(4,2)) ROI
from
(select advertiser_id
,ad_id
,spend
from adv_info) a
join (select ad_id
, sum(price) ad_spend
from ad_info
group by ad_id)b
on a.ad_id=b.ad_id
+---------------+-------+------+
| advertiser_id | ad_id | ROI |
+---------------+-------+------+
| 10 | 200 | 0.20 |
| 11 | 100 | 0.21 |
| 13 | 400 | 0.20 |
+---------------+-------+------+