1
00:00:04,880 --> 00:00:07,040
Here, we have a
spreadsheet in LibreOffice

2
00:00:07,040 --> 00:00:09,180
that contains all of our data.

3
00:00:09,180 --> 00:00:10,870
Up here, we have the
basic data, which

4
00:00:10,870 --> 00:00:14,350
are the price-per-click
and click-through-rate.

5
00:00:14,350 --> 00:00:17,480
Below, we have the
average price per display,

6
00:00:17,480 --> 00:00:18,820
which we will actually be using.

7
00:00:18,820 --> 00:00:21,040
Below it, we have the
budgets, and below

8
00:00:21,040 --> 00:00:23,240
that we have the
query estimates.

9
00:00:23,240 --> 00:00:25,910
After the data, we then
have the variables.

10
00:00:25,910 --> 00:00:30,380
So these are the x_A1s through
x_A3s, the x_T1s through x_T3s

11
00:00:30,380 --> 00:00:36,740
and the x_V1s through x_V3s that
we saw in the previous video.

12
00:00:36,740 --> 00:00:41,100
And below those, we have the
objective, the constraints,

13
00:00:41,100 --> 00:00:42,940
so that the budgets
are not exceeded

14
00:00:42,940 --> 00:00:46,330
and that the query
estimates are not exceeded.

15
00:00:46,330 --> 00:00:49,450
So we'll have to fill in all
of these expressions, which

16
00:00:49,450 --> 00:00:53,440
reflect, for example, how
many times query one is used

17
00:00:53,440 --> 00:00:56,960
in our advertising strategy,
how much of, for instance,

18
00:00:56,960 --> 00:00:59,970
T-Mobile's budget we use in
your advertising strategy,

19
00:00:59,970 --> 00:01:04,349
as well as the objective, which
is the total average revenue

20
00:01:04,349 --> 00:01:06,460
from our advertising strategy.

21
00:01:06,460 --> 00:01:10,900
Let's start by defining the
objective of our problem.

22
00:01:10,900 --> 00:01:13,789
To form the objective, we go
to the cell next to revenue,

23
00:01:13,789 --> 00:01:15,670
and we start by
writing, =SUMPRODUCT.

24
00:01:19,500 --> 00:01:22,470
The SUMPRODUCT function takes
two collections of cells,

25
00:01:22,470 --> 00:01:25,940
multiplies corresponding cells
together, and adds them up.

26
00:01:25,940 --> 00:01:28,980
Here we want to use SUMPRODUCT
to multiply the average prices

27
00:01:28,980 --> 00:01:30,950
per display with
the number of times

28
00:01:30,950 --> 00:01:33,870
we display each ad
with each query.

29
00:01:33,870 --> 00:01:38,560
Our variables are all the
cells between B35 and D37.

30
00:01:38,560 --> 00:01:40,430
And the average
price per display

31
00:01:40,430 --> 00:01:44,430
is contained in all the
cells between B17 and D19.

32
00:01:44,430 --> 00:01:46,990
So we need to input
these into the function.

33
00:01:46,990 --> 00:01:57,270
So we write B35:D37 comma,
so the comma indicates that

34
00:01:57,270 --> 00:02:00,380
we're moving on to a
new collection of cells.

35
00:02:00,380 --> 00:02:07,880
Then we include B17:D19.

36
00:02:07,880 --> 00:02:11,510
We press Enter, and so now
we have our expression,

37
00:02:11,510 --> 00:02:14,530
which reflects the objective
value for our advertising

38
00:02:14,530 --> 00:02:16,160
strategy.

39
00:02:16,160 --> 00:02:18,760
Now to compute how much of
each advertiser's budget

40
00:02:18,760 --> 00:02:21,750
we're using, we're going to use
the average price per display

41
00:02:21,750 --> 00:02:25,220
and the decision variables of
each individual advertiser.

42
00:02:25,220 --> 00:02:28,810
So to get, for example,
AT&T's budget usage,

43
00:02:28,810 --> 00:02:30,880
we would use SUMPRODUCT
again in the same way

44
00:02:30,880 --> 00:02:32,560
that we used it
for the objective,

45
00:02:32,560 --> 00:02:34,500
but this time we
would use it just

46
00:02:34,500 --> 00:02:37,960
for AT&T's decision variables
and AT&T's average prices

47
00:02:37,960 --> 00:02:39,520
per display.

48
00:02:39,520 --> 00:02:41,320
So we use SUMPRODUCT
again, as I mentioned.

49
00:02:41,320 --> 00:02:47,230
So we write =SUMPRODUCT, but
this time we select the cells

50
00:02:47,230 --> 00:02:49,900
between B35 and D35.

51
00:02:49,900 --> 00:02:53,530
So we write B35:D35.

52
00:02:53,530 --> 00:02:56,440
These are the cells that
correspond to AT&T's decision

53
00:02:56,440 --> 00:02:57,870
variables.

54
00:02:57,870 --> 00:02:59,630
For the second
collection of cells,

55
00:02:59,630 --> 00:03:05,860
we select the cells
between B17 and D17.

56
00:03:05,860 --> 00:03:07,320
These are the cells
that correspond

57
00:03:07,320 --> 00:03:10,810
to AT&T's average
prices per display.

58
00:03:10,810 --> 00:03:12,980
We can do the same thing
for T-Mobile and Verizon.

59
00:03:12,980 --> 00:03:16,440
In this case, we don't need to
enter the expressions again.

60
00:03:16,440 --> 00:03:19,510
We can just simply
drag these expressions,

61
00:03:19,510 --> 00:03:22,630
and LibreOffice
will conveniently

62
00:03:22,630 --> 00:03:25,890
fill the expressions in for us.

63
00:03:25,890 --> 00:03:28,930
And now we need to do a similar
thing for the number of times

64
00:03:28,930 --> 00:03:30,329
that we use each query.

65
00:03:30,329 --> 00:03:32,540
So, for example, to get the
number of times query one

66
00:03:32,540 --> 00:03:34,200
is used, we need
to add the cells

67
00:03:34,200 --> 00:03:36,480
corresponding to query one.

68
00:03:36,480 --> 00:03:38,690
In the spreadsheet, these
are the cells corresponding

69
00:03:38,690 --> 00:03:41,190
to B35 and B37.

70
00:03:41,190 --> 00:03:44,010
And in this case, we simply need
to add the decision variable

71
00:03:44,010 --> 00:03:46,260
cells -- we don't need to
multiply them with any other

72
00:03:46,260 --> 00:03:47,050
cells.

73
00:03:47,050 --> 00:03:48,860
So we just need to
use the sum function.

74
00:03:48,860 --> 00:04:01,330
So we write =SUM, and again,
we're using the cell's B35:B37.

75
00:04:01,330 --> 00:04:03,930
For query two, we
have to use SUM again.

76
00:04:03,930 --> 00:04:06,270
And we need to SUM the cells
corresponding to query two,

77
00:04:06,270 --> 00:04:14,870
so we write =SUM(C35:C37).

78
00:04:14,870 --> 00:04:17,029
And for query three, we
need to use SUM again,

79
00:04:17,029 --> 00:04:20,769
but this time we need to
use cells D35 through D37.

80
00:04:20,769 --> 00:04:26,470
So we write D35:D37.

81
00:04:26,470 --> 00:04:28,460
So we've now defined
all of the expressions

82
00:04:28,460 --> 00:04:30,440
that we'll need for our model.

83
00:04:30,440 --> 00:04:32,560
Now we need to input
the decision variables,

84
00:04:32,560 --> 00:04:34,690
the objective, and
the constraints

85
00:04:34,690 --> 00:04:36,830
into the LibreOffice Solver.

86
00:04:36,830 --> 00:04:39,190
So we need to first open
up the LibreOffice Solver.

87
00:04:39,190 --> 00:04:41,520
So we go to Tools.

88
00:04:41,520 --> 00:04:44,200
We open up Tools, and
we click on Solver.

89
00:04:48,490 --> 00:04:51,350
So, to do this now, we
need to specify, again

90
00:04:51,350 --> 00:04:53,690
as I mentioned, all the
pieces of the problem.

91
00:04:53,690 --> 00:04:56,490
So for "Target cell" --
so the target cell here is

92
00:04:56,490 --> 00:04:57,320
the objective cell.

93
00:04:57,320 --> 00:04:59,890
So we need to specify
our objective.

94
00:04:59,890 --> 00:05:03,600
So this cell was just B40.

95
00:05:03,600 --> 00:05:07,440
And we want to maximize this
as we're maximizing revenue.

96
00:05:07,440 --> 00:05:10,400
The "changing cells" here
are the decision variables.

97
00:05:10,400 --> 00:05:13,800
So this is just a collection
of cells, B35 through D37.

98
00:05:13,800 --> 00:05:20,620
So we write, B35:D37.

99
00:05:20,620 --> 00:05:23,370
Now, underneath, these
rows corresponding

100
00:05:23,370 --> 00:05:25,380
to the "Limiting
conditions", these

101
00:05:25,380 --> 00:05:27,720
are just the constraints
of the problem.

102
00:05:27,720 --> 00:05:31,010
In the first row here, we'll
handle the budget constraints.

103
00:05:31,010 --> 00:05:34,580
So under "Cell reference" we'll
input the budget expression,

104
00:05:34,580 --> 00:05:38,900
and these are contained
in cells B45 through B47.

105
00:05:38,900 --> 00:05:44,300
So we can enter these or we can
just click on the input button

106
00:05:44,300 --> 00:05:46,810
and just select
them in this way.

107
00:05:46,810 --> 00:05:48,790
And then we click on
the shrink button here.

108
00:05:51,950 --> 00:05:53,920
Under "Operator", we
want to select less than

109
00:05:53,920 --> 00:05:56,460
or equal to because we want to
ensure that the amount that we

110
00:05:56,460 --> 00:05:58,640
use of each budget is
less than the total budget

111
00:05:58,640 --> 00:06:00,970
of the advertiser.

112
00:06:00,970 --> 00:06:03,280
And here under
"Value", we're going

113
00:06:03,280 --> 00:06:05,900
to select the actual
budget amounts, which

114
00:06:05,900 --> 00:06:08,050
are on the right hand
side of these less than

115
00:06:08,050 --> 00:06:09,390
or equal to signs.

116
00:06:09,390 --> 00:06:14,250
So we select them,
and we put them in.

117
00:06:14,250 --> 00:06:16,920
So that handles the
budget constraints.

118
00:06:16,920 --> 00:06:18,630
And in the second
row here, we're

119
00:06:18,630 --> 00:06:21,220
going to handle the query
estimate constraints.

120
00:06:21,220 --> 00:06:24,180
So here again, under
cell reference,

121
00:06:24,180 --> 00:06:26,900
we're going to specify the
expressions that correspond

122
00:06:26,900 --> 00:06:30,320
to how much we use each query
in our advertising strategy.

123
00:06:30,320 --> 00:06:34,280
And so these are just the
cells, B50 through B52,

124
00:06:34,280 --> 00:06:38,730
which we select, and we
input them into the solver.

125
00:06:38,730 --> 00:06:40,840
Under operator, we want
to keep it as less than

126
00:06:40,840 --> 00:06:43,650
or equal to, because we want to
ensure that the amount that we

127
00:06:43,650 --> 00:06:46,950
use each query is less than or
equal to the expected number

128
00:06:46,950 --> 00:06:51,120
of times that we
estimate for that query.

129
00:06:51,120 --> 00:06:54,420
And under value, we want to
input the query estimates,

130
00:06:54,420 --> 00:06:57,530
which are just the
cells, D50 through D52.

131
00:06:57,530 --> 00:07:02,010
So, we can just select them
in this way, and input them.

132
00:07:02,010 --> 00:07:04,470
So, at this point, it
might be tempting to think

133
00:07:04,470 --> 00:07:07,710
that we are done, but we have
two more things we need to do.

134
00:07:07,710 --> 00:07:09,740
First, we need to
tell the solver

135
00:07:09,740 --> 00:07:13,140
to explicitly treat this as a
linear optimization problem.

136
00:07:13,140 --> 00:07:16,370
Second, we need to include
another set of constraints.

137
00:07:16,370 --> 00:07:18,990
This set of constraints
requires each decision variable

138
00:07:18,990 --> 00:07:20,540
to be greater than
or equal to zero,

139
00:07:20,540 --> 00:07:22,170
since it does not
make sense to display

140
00:07:22,170 --> 00:07:25,600
an advertiser with a certain
query some negative number

141
00:07:25,600 --> 00:07:26,900
of times.

142
00:07:26,900 --> 00:07:29,190
Now, while we could include
these constraints here,

143
00:07:29,190 --> 00:07:32,130
these types of constraints are
very common and very typical

144
00:07:32,130 --> 00:07:33,600
in linear optimization models.

145
00:07:33,600 --> 00:07:35,010
They come up all the time.

146
00:07:35,010 --> 00:07:36,510
And, in fact, they
come up so often,

147
00:07:36,510 --> 00:07:39,190
that solvers often have an
option that you can toggle,

148
00:07:39,190 --> 00:07:41,830
that incorporates these
constraints automatically.

149
00:07:41,830 --> 00:07:44,430
So to handle both of
these considerations.

150
00:07:44,430 --> 00:07:47,110
Let's just click on Options.

151
00:07:47,110 --> 00:07:49,810
And under options, where
we have the drop down

152
00:07:49,810 --> 00:07:52,950
menu for "Solver engine",
we'll click on there,

153
00:07:52,950 --> 00:07:55,960
and then we'll select
LibreOffice Linear Solver.

154
00:07:55,960 --> 00:07:58,830
This indicates to LibreOffice
to use the linear optimization

155
00:07:58,830 --> 00:08:01,330
solver for this problem.

156
00:08:01,330 --> 00:08:03,670
And under the settings
here, one of the settings,

157
00:08:03,670 --> 00:08:07,630
is to assume that the
variables are non-negative.

158
00:08:07,630 --> 00:08:10,730
We'll just activate
that option and hit OK.

159
00:08:10,730 --> 00:08:12,790
And now we're ready
to solve the problem.

160
00:08:12,790 --> 00:08:16,970
So if we hit solve, we
get this dialogue that

161
00:08:16,970 --> 00:08:19,070
says that solving
successfully finished,

162
00:08:19,070 --> 00:08:20,830
and that our result,
in this case,

163
00:08:20,830 --> 00:08:23,950
this is the objective
function, was 428.

164
00:08:23,950 --> 00:08:26,440
So we have an
advertising strategy

165
00:08:26,440 --> 00:08:30,660
that achieves an
average revenue of $428.

166
00:08:30,660 --> 00:08:34,700
Let's just hit here, Keep
Result, and just take a look.

167
00:08:37,730 --> 00:08:40,280
The cells that we specified
as the decision variables

168
00:08:40,280 --> 00:08:43,850
have been populated with
their optimal values.

169
00:08:43,850 --> 00:08:47,270
So our optimal strategy, based
on this linear optimization

170
00:08:47,270 --> 00:08:49,150
solution is the
following: so we're

171
00:08:49,150 --> 00:08:53,670
going to show AT&T with
query one 40 times;

172
00:08:53,670 --> 00:08:57,760
we're going to show AT&T
with query two also 40 times;

173
00:08:57,760 --> 00:09:01,220
we're going to show AT&T
with query three 80 times;

174
00:09:01,220 --> 00:09:03,950
for T-Mobile, we'll show
T-Mobile's ad with query one

175
00:09:03,950 --> 00:09:07,500
100 times; and we're only
going to show Verizon's

176
00:09:07,500 --> 00:09:10,650
ad with query two, and we're
going to show it 40 times.

177
00:09:10,650 --> 00:09:13,970
So this specifies, completely,
the advertising strategy

178
00:09:13,970 --> 00:09:15,250
that Google should use.

179
00:09:15,250 --> 00:09:17,190
And this strategy,
as we just saw,

180
00:09:17,190 --> 00:09:21,820
achieves an average
revenue of $428.

181
00:09:21,820 --> 00:09:23,320
Let's double check
that the solution

182
00:09:23,320 --> 00:09:25,610
is feasible by looking at
the budgets and the query

183
00:09:25,610 --> 00:09:26,620
estimates.

184
00:09:26,620 --> 00:09:28,760
So if we scroll
down here, we see

185
00:09:28,760 --> 00:09:34,640
that AT&T's budget that
we use here is $168.

186
00:09:34,640 --> 00:09:39,920
AT&T's actual budget is $170.

187
00:09:39,920 --> 00:09:42,200
We can see that for the
other two advertisers,

188
00:09:42,200 --> 00:09:44,950
that we are in the
clear for both of them.

189
00:09:44,950 --> 00:09:46,460
Similarly, with
the query estimates

190
00:09:46,460 --> 00:09:49,180
we can see that
for all the queries

191
00:09:49,180 --> 00:09:51,340
that we are considering
here, we do not

192
00:09:51,340 --> 00:09:53,660
use any query more
than the estimate

193
00:09:53,660 --> 00:09:56,370
for the number of times that
we expect to see that query.

194
00:09:56,370 --> 00:10:00,780
And finally, as one last check,
all the decision variable

195
00:10:00,780 --> 00:10:04,380
values that we see here are all
greater than or equal to zero.

196
00:10:04,380 --> 00:10:07,020
So we're not using
any advertiser

197
00:10:07,020 --> 00:10:09,050
with any query a negative
number times, which

198
00:10:09,050 --> 00:10:11,060
obviously would not make sense.

199
00:10:11,060 --> 00:10:15,450
So, the solution as a whole
is a feasible solution.

200
00:10:15,450 --> 00:10:17,280
In the next video,
we'll show how

201
00:10:17,280 --> 00:10:19,720
to solve the problem using
a greedy common sense

202
00:10:19,720 --> 00:10:23,120
approach, where we will allocate
ads to queries by prioritizing

203
00:10:23,120 --> 00:10:26,090
them by their average
price per display.