WEBVTT

00:00:04.880 --> 00:00:07.040
Here, we have a
spreadsheet in LibreOffice

00:00:07.040 --> 00:00:09.180
that contains all of our data.

00:00:09.180 --> 00:00:10.870
Up here, we have the
basic data, which

00:00:10.870 --> 00:00:14.350
are the price-per-click
and click-through-rate.

00:00:14.350 --> 00:00:17.480
Below, we have the
average price per display,

00:00:17.480 --> 00:00:18.820
which we will actually be using.

00:00:18.820 --> 00:00:21.040
Below it, we have the
budgets, and below

00:00:21.040 --> 00:00:23.240
that we have the
query estimates.

00:00:23.240 --> 00:00:25.910
After the data, we then
have the variables.

00:00:25.910 --> 00:00:30.380
So these are the x_A1s through
x_A3s, the x_T1s through x_T3s

00:00:30.380 --> 00:00:36.740
and the x_V1s through x_V3s that
we saw in the previous video.

00:00:36.740 --> 00:00:41.100
And below those, we have the
objective, the constraints,

00:00:41.100 --> 00:00:42.940
so that the budgets
are not exceeded

00:00:42.940 --> 00:00:46.330
and that the query
estimates are not exceeded.

00:00:46.330 --> 00:00:49.450
So we'll have to fill in all
of these expressions, which

00:00:49.450 --> 00:00:53.440
reflect, for example, how
many times query one is used

00:00:53.440 --> 00:00:56.960
in our advertising strategy,
how much of, for instance,

00:00:56.960 --> 00:00:59.970
T-Mobile's budget we use in
your advertising strategy,

00:00:59.970 --> 00:01:04.349
as well as the objective, which
is the total average revenue

00:01:04.349 --> 00:01:06.460
from our advertising strategy.

00:01:06.460 --> 00:01:10.900
Let's start by defining the
objective of our problem.

00:01:10.900 --> 00:01:13.789
To form the objective, we go
to the cell next to revenue,

00:01:13.789 --> 00:01:15.670
and we start by
writing, =SUMPRODUCT.

00:01:19.500 --> 00:01:22.470
The SUMPRODUCT function takes
two collections of cells,

00:01:22.470 --> 00:01:25.940
multiplies corresponding cells
together, and adds them up.

00:01:25.940 --> 00:01:28.980
Here we want to use SUMPRODUCT
to multiply the average prices

00:01:28.980 --> 00:01:30.950
per display with
the number of times

00:01:30.950 --> 00:01:33.870
we display each ad
with each query.

00:01:33.870 --> 00:01:38.560
Our variables are all the
cells between B35 and D37.

00:01:38.560 --> 00:01:40.430
And the average
price per display

00:01:40.430 --> 00:01:44.430
is contained in all the
cells between B17 and D19.

00:01:44.430 --> 00:01:46.990
So we need to input
these into the function.

00:01:46.990 --> 00:01:57.270
So we write B35:D37 comma,
so the comma indicates that

00:01:57.270 --> 00:02:00.380
we're moving on to a
new collection of cells.

00:02:00.380 --> 00:02:07.880
Then we include B17:D19.

00:02:07.880 --> 00:02:11.510
We press Enter, and so now
we have our expression,

00:02:11.510 --> 00:02:14.530
which reflects the objective
value for our advertising

00:02:14.530 --> 00:02:16.160
strategy.

00:02:16.160 --> 00:02:18.760
Now to compute how much of
each advertiser's budget

00:02:18.760 --> 00:02:21.750
we're using, we're going to use
the average price per display

00:02:21.750 --> 00:02:25.220
and the decision variables of
each individual advertiser.

00:02:25.220 --> 00:02:28.810
So to get, for example,
AT&T's budget usage,

00:02:28.810 --> 00:02:30.880
we would use SUMPRODUCT
again in the same way

00:02:30.880 --> 00:02:32.560
that we used it
for the objective,

00:02:32.560 --> 00:02:34.500
but this time we
would use it just

00:02:34.500 --> 00:02:37.960
for AT&T's decision variables
and AT&T's average prices

00:02:37.960 --> 00:02:39.520
per display.

00:02:39.520 --> 00:02:41.320
So we use SUMPRODUCT
again, as I mentioned.

00:02:41.320 --> 00:02:47.230
So we write =SUMPRODUCT, but
this time we select the cells

00:02:47.230 --> 00:02:49.900
between B35 and D35.

00:02:49.900 --> 00:02:53.530
So we write B35:D35.

00:02:53.530 --> 00:02:56.440
These are the cells that
correspond to AT&T's decision

00:02:56.440 --> 00:02:57.870
variables.

00:02:57.870 --> 00:02:59.630
For the second
collection of cells,

00:02:59.630 --> 00:03:05.860
we select the cells
between B17 and D17.

00:03:05.860 --> 00:03:07.320
These are the cells
that correspond

00:03:07.320 --> 00:03:10.810
to AT&T's average
prices per display.

00:03:10.810 --> 00:03:12.980
We can do the same thing
for T-Mobile and Verizon.

00:03:12.980 --> 00:03:16.440
In this case, we don't need to
enter the expressions again.

00:03:16.440 --> 00:03:19.510
We can just simply
drag these expressions,

00:03:19.510 --> 00:03:22.630
and LibreOffice
will conveniently

00:03:22.630 --> 00:03:25.890
fill the expressions in for us.

00:03:25.890 --> 00:03:28.930
And now we need to do a similar
thing for the number of times

00:03:28.930 --> 00:03:30.329
that we use each query.

00:03:30.329 --> 00:03:32.540
So, for example, to get the
number of times query one

00:03:32.540 --> 00:03:34.200
is used, we need
to add the cells

00:03:34.200 --> 00:03:36.480
corresponding to query one.

00:03:36.480 --> 00:03:38.690
In the spreadsheet, these
are the cells corresponding

00:03:38.690 --> 00:03:41.190
to B35 and B37.

00:03:41.190 --> 00:03:44.010
And in this case, we simply need
to add the decision variable

00:03:44.010 --> 00:03:46.260
cells -- we don't need to
multiply them with any other

00:03:46.260 --> 00:03:47.050
cells.

00:03:47.050 --> 00:03:48.860
So we just need to
use the sum function.

00:03:48.860 --> 00:04:01.330
So we write =SUM, and again,
we're using the cell's B35:B37.

00:04:01.330 --> 00:04:03.930
For query two, we
have to use SUM again.

00:04:03.930 --> 00:04:06.270
And we need to SUM the cells
corresponding to query two,

00:04:06.270 --> 00:04:14.870
so we write =SUM(C35:C37).

00:04:14.870 --> 00:04:17.029
And for query three, we
need to use SUM again,

00:04:17.029 --> 00:04:20.769
but this time we need to
use cells D35 through D37.

00:04:20.769 --> 00:04:26.470
So we write D35:D37.

00:04:26.470 --> 00:04:28.460
So we've now defined
all of the expressions

00:04:28.460 --> 00:04:30.440
that we'll need for our model.

00:04:30.440 --> 00:04:32.560
Now we need to input
the decision variables,

00:04:32.560 --> 00:04:34.690
the objective, and
the constraints

00:04:34.690 --> 00:04:36.830
into the LibreOffice Solver.

00:04:36.830 --> 00:04:39.190
So we need to first open
up the LibreOffice Solver.

00:04:39.190 --> 00:04:41.520
So we go to Tools.

00:04:41.520 --> 00:04:44.200
We open up Tools, and
we click on Solver.

00:04:48.490 --> 00:04:51.350
So, to do this now, we
need to specify, again

00:04:51.350 --> 00:04:53.690
as I mentioned, all the
pieces of the problem.

00:04:53.690 --> 00:04:56.490
So for "Target cell" --
so the target cell here is

00:04:56.490 --> 00:04:57.320
the objective cell.

00:04:57.320 --> 00:04:59.890
So we need to specify
our objective.

00:04:59.890 --> 00:05:03.600
So this cell was just B40.

00:05:03.600 --> 00:05:07.440
And we want to maximize this
as we're maximizing revenue.

00:05:07.440 --> 00:05:10.400
The "changing cells" here
are the decision variables.

00:05:10.400 --> 00:05:13.800
So this is just a collection
of cells, B35 through D37.

00:05:13.800 --> 00:05:20.620
So we write, B35:D37.

00:05:20.620 --> 00:05:23.370
Now, underneath, these
rows corresponding

00:05:23.370 --> 00:05:25.380
to the "Limiting
conditions", these

00:05:25.380 --> 00:05:27.720
are just the constraints
of the problem.

00:05:27.720 --> 00:05:31.010
In the first row here, we'll
handle the budget constraints.

00:05:31.010 --> 00:05:34.580
So under "Cell reference" we'll
input the budget expression,

00:05:34.580 --> 00:05:38.900
and these are contained
in cells B45 through B47.

00:05:38.900 --> 00:05:44.300
So we can enter these or we can
just click on the input button

00:05:44.300 --> 00:05:46.810
and just select
them in this way.

00:05:46.810 --> 00:05:48.790
And then we click on
the shrink button here.

00:05:51.950 --> 00:05:53.920
Under "Operator", we
want to select less than

00:05:53.920 --> 00:05:56.460
or equal to because we want to
ensure that the amount that we

00:05:56.460 --> 00:05:58.640
use of each budget is
less than the total budget

00:05:58.640 --> 00:06:00.970
of the advertiser.

00:06:00.970 --> 00:06:03.280
And here under
"Value", we're going

00:06:03.280 --> 00:06:05.900
to select the actual
budget amounts, which

00:06:05.900 --> 00:06:08.050
are on the right hand
side of these less than

00:06:08.050 --> 00:06:09.390
or equal to signs.

00:06:09.390 --> 00:06:14.250
So we select them,
and we put them in.

00:06:14.250 --> 00:06:16.920
So that handles the
budget constraints.

00:06:16.920 --> 00:06:18.630
And in the second
row here, we're

00:06:18.630 --> 00:06:21.220
going to handle the query
estimate constraints.

00:06:21.220 --> 00:06:24.180
So here again, under
cell reference,

00:06:24.180 --> 00:06:26.900
we're going to specify the
expressions that correspond

00:06:26.900 --> 00:06:30.320
to how much we use each query
in our advertising strategy.

00:06:30.320 --> 00:06:34.280
And so these are just the
cells, B50 through B52,

00:06:34.280 --> 00:06:38.730
which we select, and we
input them into the solver.

00:06:38.730 --> 00:06:40.840
Under operator, we want
to keep it as less than

00:06:40.840 --> 00:06:43.650
or equal to, because we want to
ensure that the amount that we

00:06:43.650 --> 00:06:46.950
use each query is less than or
equal to the expected number

00:06:46.950 --> 00:06:51.120
of times that we
estimate for that query.

00:06:51.120 --> 00:06:54.420
And under value, we want to
input the query estimates,

00:06:54.420 --> 00:06:57.530
which are just the
cells, D50 through D52.

00:06:57.530 --> 00:07:02.010
So, we can just select them
in this way, and input them.

00:07:02.010 --> 00:07:04.470
So, at this point, it
might be tempting to think

00:07:04.470 --> 00:07:07.710
that we are done, but we have
two more things we need to do.

00:07:07.710 --> 00:07:09.740
First, we need to
tell the solver

00:07:09.740 --> 00:07:13.140
to explicitly treat this as a
linear optimization problem.

00:07:13.140 --> 00:07:16.370
Second, we need to include
another set of constraints.

00:07:16.370 --> 00:07:18.990
This set of constraints
requires each decision variable

00:07:18.990 --> 00:07:20.540
to be greater than
or equal to zero,

00:07:20.540 --> 00:07:22.170
since it does not
make sense to display

00:07:22.170 --> 00:07:25.600
an advertiser with a certain
query some negative number

00:07:25.600 --> 00:07:26.900
of times.

00:07:26.900 --> 00:07:29.190
Now, while we could include
these constraints here,

00:07:29.190 --> 00:07:32.130
these types of constraints are
very common and very typical

00:07:32.130 --> 00:07:33.600
in linear optimization models.

00:07:33.600 --> 00:07:35.010
They come up all the time.

00:07:35.010 --> 00:07:36.510
And, in fact, they
come up so often,

00:07:36.510 --> 00:07:39.190
that solvers often have an
option that you can toggle,

00:07:39.190 --> 00:07:41.830
that incorporates these
constraints automatically.

00:07:41.830 --> 00:07:44.430
So to handle both of
these considerations.

00:07:44.430 --> 00:07:47.110
Let's just click on Options.

00:07:47.110 --> 00:07:49.810
And under options, where
we have the drop down

00:07:49.810 --> 00:07:52.950
menu for "Solver engine",
we'll click on there,

00:07:52.950 --> 00:07:55.960
and then we'll select
LibreOffice Linear Solver.

00:07:55.960 --> 00:07:58.830
This indicates to LibreOffice
to use the linear optimization

00:07:58.830 --> 00:08:01.330
solver for this problem.

00:08:01.330 --> 00:08:03.670
And under the settings
here, one of the settings,

00:08:03.670 --> 00:08:07.630
is to assume that the
variables are non-negative.

00:08:07.630 --> 00:08:10.730
We'll just activate
that option and hit OK.

00:08:10.730 --> 00:08:12.790
And now we're ready
to solve the problem.

00:08:12.790 --> 00:08:16.970
So if we hit solve, we
get this dialogue that

00:08:16.970 --> 00:08:19.070
says that solving
successfully finished,

00:08:19.070 --> 00:08:20.830
and that our result,
in this case,

00:08:20.830 --> 00:08:23.950
this is the objective
function, was 428.

00:08:23.950 --> 00:08:26.440
So we have an
advertising strategy

00:08:26.440 --> 00:08:30.660
that achieves an
average revenue of $428.

00:08:30.660 --> 00:08:34.700
Let's just hit here, Keep
Result, and just take a look.

00:08:37.730 --> 00:08:40.280
The cells that we specified
as the decision variables

00:08:40.280 --> 00:08:43.850
have been populated with
their optimal values.

00:08:43.850 --> 00:08:47.270
So our optimal strategy, based
on this linear optimization

00:08:47.270 --> 00:08:49.150
solution is the
following: so we're

00:08:49.150 --> 00:08:53.670
going to show AT&T with
query one 40 times;

00:08:53.670 --> 00:08:57.760
we're going to show AT&T
with query two also 40 times;

00:08:57.760 --> 00:09:01.220
we're going to show AT&T
with query three 80 times;

00:09:01.220 --> 00:09:03.950
for T-Mobile, we'll show
T-Mobile's ad with query one

00:09:03.950 --> 00:09:07.500
100 times; and we're only
going to show Verizon's

00:09:07.500 --> 00:09:10.650
ad with query two, and we're
going to show it 40 times.

00:09:10.650 --> 00:09:13.970
So this specifies, completely,
the advertising strategy

00:09:13.970 --> 00:09:15.250
that Google should use.

00:09:15.250 --> 00:09:17.190
And this strategy,
as we just saw,

00:09:17.190 --> 00:09:21.820
achieves an average
revenue of $428.

00:09:21.820 --> 00:09:23.320
Let's double check
that the solution

00:09:23.320 --> 00:09:25.610
is feasible by looking at
the budgets and the query

00:09:25.610 --> 00:09:26.620
estimates.

00:09:26.620 --> 00:09:28.760
So if we scroll
down here, we see

00:09:28.760 --> 00:09:34.640
that AT&T's budget that
we use here is $168.

00:09:34.640 --> 00:09:39.920
AT&T's actual budget is $170.

00:09:39.920 --> 00:09:42.200
We can see that for the
other two advertisers,

00:09:42.200 --> 00:09:44.950
that we are in the
clear for both of them.

00:09:44.950 --> 00:09:46.460
Similarly, with
the query estimates

00:09:46.460 --> 00:09:49.180
we can see that
for all the queries

00:09:49.180 --> 00:09:51.340
that we are considering
here, we do not

00:09:51.340 --> 00:09:53.660
use any query more
than the estimate

00:09:53.660 --> 00:09:56.370
for the number of times that
we expect to see that query.

00:09:56.370 --> 00:10:00.780
And finally, as one last check,
all the decision variable

00:10:00.780 --> 00:10:04.380
values that we see here are all
greater than or equal to zero.

00:10:04.380 --> 00:10:07.020
So we're not using
any advertiser

00:10:07.020 --> 00:10:09.050
with any query a negative
number times, which

00:10:09.050 --> 00:10:11.060
obviously would not make sense.

00:10:11.060 --> 00:10:15.450
So, the solution as a whole
is a feasible solution.

00:10:15.450 --> 00:10:17.280
In the next video,
we'll show how

00:10:17.280 --> 00:10:19.720
to solve the problem using
a greedy common sense

00:10:19.720 --> 00:10:23.120
approach, where we will allocate
ads to queries by prioritizing

00:10:23.120 --> 00:10:26.090
them by their average
price per display.