IDO GREEN: Hello, and welcome to another episode of GDL-IL,
GDL Israel.
Today we're going to have quite a fun time with showing
the power of both App Script and Compute Engine.
And in order to show the flexibility and ability to run
interesting test cases on both platforms, I decided just to
take this fun subject of Monte Carlo simulation and see how
we could utilize those two different products in order to
test it and see if we could get some interesting results
to real programs that we have under our hands.
So without further ado, let me just share my screen, and
we'll jump together into the code.
I find it very useful in most of the cases just to talk less
and see code.
So let's move this one so we won't have any problems, and
we'll open this little doc.
I guess the fonts are quite large, but if we have a
problem, just feel free to ping me on the Hangout, and
we'll be able to increase the font size.
Actually, I could do it right now.
So what are we having here?
Basically, what we want to test is to show the power of
App Script, and we took the fun world of Monte Carlo
simulations.
Because when we have problems that are happening in the real
world, usually there are quite a lot of outcomes that could
come for each and every scenario.
And what we're doing in those simulations, basically we're
trying to identify the different variables that we
have under our hands, and then run it many times with some
kind of random distribution that allow us in end of the
tests to come up with numbers that harness us with the
ability to fine tune and to get an answer.
And in our little case, we just choose the option to help
a good friend of mine who is a real a rock star salesperson,
and we'll try to find what are the things that are
constraining him and how we could have been.
How we'll do it?
Well, we'll use first App Script just to show the power
of Google Sheets and App Script, and how you could do
things quickly and share them with others.
And then we'll take it for the real world, and we'll show
together how we could run it-- the same code, basically, the
same JavaScript code--
on Node.js, inside Compute Engine, which is a very
efficient way to do it.
What is our problem?
Well, basically we have this brilliant salesperson, and
they could get big deals that pay, let's say, x amount of
dollars or smaller deals that pay y amount of dollars.
And of course, the level of effort for a bigger deal is
much higher than a smaller deal.
So let's take the scenario of the end of the quarter, and
our salesperson got 30 days to make the most out of her time.
What she could do?
She should choose just to close big deals, or just
smaller deals, or what is the perfect plan?
And this is basically what we'll try to do here.
I'll share all those links at the end of the
show, so feel free.
A much better explanation than the one that I gave you can
find on Wikipedia, then if you want to check out what it's
all about with Node.js and how cool it is to run your
JavaScript code inside it very efficiently, of course.
Node.js, but you need some machine to do it, so Compute
Engine is here to help, and we'll touch quickly on how you
could set your own Compute Engine.
Since we did it last episode of GDL-IL, I won't touch on
all the basic boilerplate steps that you need to do in
order to have your own virtual machines, but I'll just take
it a step further.
After you have the machine, you installed the Node.js, now
what you could do with it.
And of course, App Script, there's just a link to all the
details and documentation that you could enjoy there.
Let's go to our case, and first
let's define the problem.
So in our world-- let me just open it so it will be a bit
wider, and we'll try to squeeze everything to the page
so you'll be able to see it nicely.
Let's take it like this.
That's much better.
The chart here is just to give us a sense of how the data
look like, so don't focus too much on it.
Basically, what we have here is a test case.
Let's remove all the data that we have right now and look at
it together.
If you noticed, I have this new sheet that I created, and
for this GDL show, I just created a new menu that would
allow us to choose several things.
Let's try with the most basic way to just run simulations
that basically you could see are just filling now the
details, and the chart is updating in real time.
And what we have here is a very naive approach.
We're just coming with x amount of big deals and y
amount of small deals.
In our case, it's the same amount--
25.
And we have a number of percentage that we set in
advance that says something like in 30% of the deals that
she will approach that are big deals, she will be able to
close them successfully.
And let's say on smaller deals, it's much easier
because of lots of different reasons, and their percentage
there is 50%.
You could see here on 10 cases, more or less, those are
the numbers.
And we could have a slight feel for the numbers here,
what is the max, min, standard deviation, and the median.
And a nice aspect here will be probably to take the
conditional formatting.
I'm going to the cells that I want to apply this conditional
formatting.
In the conditional formatting, we say something like, OK,
everything that is above $250,000, I just
want to make bold.
And in our case, the median is 265, so let's correct it.
We go here conditional formatting.
And I could see now quite quickly where more or less I'm
getting the best results.
But this is, of course, not mimicking reality, because we
won't be able to do the same things, because probably a
bigger deal is taking more effort from us.
So we'll got the next phase of running more control deals.
And what will happen here, you'll see, is we enforce the
assumption that a bigger deal is taking actually twice the
time of the smaller deal.
So now the script is done.
You could see that I got this nice dialog box, telling me
what was the max amount that we are able to achieve.
And I just confirm it.
And here you could see quite clearly that if we were able
to do one big deal, then from the 50 deals that we could
achieve if we would focus just on a smaller deal, now we
could have only 48, and then it's going down.
And here it's much interesting aspect because we know that in
the real world, it will be much closer to this.
We'll spend more time on the bigger deals.
And here the case when the median is around 180,000, and
the average is 200.
Let's see all the cells that are above that
average quite quickly.
Conditional formatting, and above,
let's say, just 200,000.
And you could see nicely that it's more concentrated at the
beginning of everything.
Now, let's take it for the next step to show what the
code is doing.
Here you could see this--
let me make [AUDIO LOST]
that actually running everything.
So basically, we just took the assumption of how easy or how
hard it will be to close a big deal.
And in our case, it was 20%, and then 50%
for a smaller deal.
The amount of executions that we could allow ourselves to do
on smaller deals and bigger deals, and this is the returns
on each deal.
And of course, you could play with those parameters, and
those are the parameters that will drive all the simulation.
Later, what we're doing is just running this scenario.
And we're running this scenario basically on all the
amount of big deals because this is what's driving us or
constraining us from making all the smaller ones.
So what we will do, we'll call this function of running
"blending," and this is that function.
And we're passing the right line, so we'll be able to
update our sheet quite easily.
And this is just bottom line for all the parameters that we
want, all the variables that we want to keep.
And now we're calling the loop of the big deals.
And we're calling this simple function that's returning the
randomization, or basically the answer, if we were
successful with approaching the deal and
closing it or not.
And what this function does, basically, it's very simple.
We're just taking the random function-- it will be a number
between 0 and 1--
and we're trying to see if it was 20%.
And if the number that we got is lower, we were
able to win the deal.
And if not, it's just false.
And of course, the same with the smaller deal, so it's
very, very simple.
And when we're coming here, and we want to test if we want
the deal, we're just going on the deals, and it will start
with one deal, and then increase it.
And if we were successful and it's true, we're just adding
one to the deals that we were able, and we're adding the
money that this big deal will put into our account.
And of course, the same will be done with the smaller deal,
so the same simple code.
The smaller deal, however, the magic is here.
We have two constraints here.
One is that simple constraint of smaller deal should be
always lower than the max amount of smaller deals that
we could do.
But then we want to enforce the ability of, hey, a bigger
deal is twice as hard-- that's why it's 2 here-- than a
smaller deal.
And the combination of two can't exceed the max amount of
deals that we could allow ourselves to small and big.
After we do that, very easily, we're just updating the lines
with all these different data.
And those are these lines, basically here.
And very nicely, we could get some nice aspect of what's
going on here.
So you saw that with the current parameters--
and let's have a look at them again--
of 20% and 50%, and those are the returns, it might be worth
to just focus maybe on this range of deals, so to focus
on, let's say, something between 6 to 12, more or less.
But if we're changing the odds just a little bit, let's say
that we are more successful with a bigger
deal, and it's 0.4.
And we're coming again here.
Let's remove all the data so we start clean.
And we're going again here and running it, we'll get the
results quite quickly.
But as you can see here, we're waiting a bit, and that's
basically one of the things that I wanted to note.
If you need to run simulations, especially for
the real world, App Script might not be the tool for you.
Here we're playing with it, and it's a nice way to have a
feel for some aspects of the data, but wait with me.
Bear with me just two or three minutes, and you'll see how
you could do it for real with utilizing Compute Engine.
So here we got some different results, and you could see
here that the median is higher--
270.
And if we'll go here [AUDIO LOST]
use the conditional formatting,
and we'll drive here.
Basically, it should be 270.
You see that it's tending a big more to here, but it's not
very clear what's going on.
And of course, if we'll play the scenario again and again
multiple times, we'll be able to identify if it's driving
more to this side and aspect of the data or more to the
lower number of deals.
Now, of course, here you could play and run it multiple
times, and we could definitely do that again if we want.
One thing to note here is that you have full ability to use,
of course, the message box, but also the logger itself.
So you have the ability to go to View, click on Logs, and
getting the output here, very useful when you
want to debug things.
But one of the even nicer thing is just the ability to
put a break point on each function and each line that
you wish, and then select the right function
that we want to run.
In our case, it's Run Scenario Blending.
Click on the Debug, and you'll see in a minute that I'm
opening the debugger and getting a full view of the all
the variables.
I have this object containing all the
variables that I have here.
And now on this line, basically I could see and go
above or get into and stepping into the functions.
Let's close it.
We don't need it anymore.
We have the results back.
Let me just refresh it so it will be much quicker, and
we'll get the results in a second.
And as you can see now, it's maybe a bit clearer that we
want or wish to end to this range of the aspect.
Now, all this is quite cool.
But the ability to run it for real is basically coming very
handy when we're taking Compute Engine into our hands.
So I took the code from App Script.
Basically, you could see it here.
[AUDIO LOST]
changes into it.
First and foremost, since you are going to run in Node.js,
we could utilize some models.
NPM is your source, and there are so many, that it's quite
funny to look sometimes for the best ones.
In our cases, since we're working with a lot of data
now, we get to get out of the logger or, in our Node.js
world, Console Log.
We want to work with files, and here I just choose the
ability to work with text files and basically build CSV
files that later on I could upload and analyze in Google
Sheets again.
So in our case, we'll try to run, let's say, 1,000 times.
We could do actually 10,000 times.
It will be very, very efficient and fast, and you'll
see it in minute.
In terms of percentage and all the aspects that we talked
about, very similar to what we saw--
20% chance to close a big deal, 50% for a smaller one.
And those are the limitations that we put to ourselves, and
these are the returns.
Now we're just putting some boilerplate code to run our
blending scenario x amount of times, and each time we'll get
the maximum, and we'll want to output
everything to a CSV file.
By the way, this is all I need in order to
export it to a CSV file.
I'm using outData as a string object, and I'm just making
sure that I'm putting commas and end of line in the end of
everything.
And as you could see here, very straightforward and easy.
Now when we'll come here to our Compute Engine--
let me make it [AUDIO LOST].
we could run this little, nice application.
And for 10,000 runs, we got the results back after 451
milliseconds, so roughly half a second.
And you could see here that we were able to achieve the best
results with a higher number of big deals.
So probably the best recommendation that we could
make in this case with these assumptions--
these are the ones--
is that we want to focus on more bigger deals
than smaller deals.
Now you might notice that it's not black and white, and it's
not that we have one sort of thing.
And I guess it's very similar to the real world where
usually the answers are quite gray.
You need to find the golden path.
And in most of the cases, it's not very clear
where they will be.
And I guess that one of most important aspects to note is
that your assumptions will drive the simulation.
So it's very useful to have those type of simulations when
you feel that you have some control or some assumptions
that are close to reality.
And in our case, we could definitely say something like
our brilliant salesgirl could close a bigger
deal 30% of the times.
Why?
Because for every 10 deals that she approaches, three are
done deals.
And with those assumptions in hand, we could definitely go
and see how she or he could utilize their
time in the best way.
So these were just fun little things about how to utilize
Node.js, App Script, and run some Monte Carlo simulations.
I hope in the near future to show some cool things that you
could do in App Engine as well.
And all the code, of course, will be on my GitApp
repository, and I'll put a full transcript of all this
show in the footnotes so you'll be able to track it.
And until next time, be strong and have fun.
Bye-bye.