0:05

All right, so we're going to look at the Kiwi Bubbles data set.

It was a panel of customers, and what was monitored was when those customers

purchased for the first time a new drink product codenamed Kiwi Bubbles.

That's if they acquired the product during our observation period.

So we're going to look at using timing models, a geometric model, as well as

a model with a linear time trend, to model the adoption of this particular product.

And then we're going to modify those models to add in this idea

of the hardcore never buyers, the HCNB segment, to recognize that just some

customers in the panel, no matter how long we wait, aren't going to be interested.

So four different modeling specifications that we're going to take a look at.

Now you'll find a general description

of the approach to the problem that we're going to take up on the course website.

1:04

So the raw data that we have here you see is for the product Kiwi Bubbles.

There are 1,499 panelists in total.

And what we have are, the cumulative number of triers from that panel.

So in the first week that this product was launched, only 8 customers purchased it,

by the second week, we had 14 customers who had purchased it.

By the third week, 16 who had purchased this.

That's cumulative.

All right, so we're going to start by estimating the geometric model.

One that assumes a constant probability of trying the product each week.

So the time until it option is assumed to follow a geometric process.

So let's start there.

And we're going to use default value to get us started,

let's assume that the probability is .5.

All right, so the same data that we had previously,

we'll zoom in a little bit to be able to see this more clearly.

So let's start by constructing the number of incremental triers,

because what we need to do is come up with what's the probability that

2:20

We can multiply, that's going to be the likelihood associated with

each of the incremental triers.

So just to walk through the approach that we're going to be taking,

we're going to first calculate out what those incremental triers,

how many there are, that will be straight forward for us.

In column D, we're going to calculate that survival probability.

So what's the probability that someone has not yet

tried the product by the beginning of that week?

We're going to then calculate the incremental probability

of trying it in a particular week in column E.

In column F, we then going to combine the number of incremental triers we have with

that probability of try on a particular week to produce our log likelihood.

We're also going to have to take into account the likelihood associated with

the number of people who have not yet tried the product.

And then what you see in columns G and H is we're going to predict how many people,

according to our model, should have tried the product by a particular week.

That is, what's the expectations?

Now see, we provided up the data for 52 weeks,

but we're not going to use necessarily that full dataset.

What we may want to look at, for example, is splitting that data so

that we have a portion to be used for forecasting.

The alternative, we could use that full dataset and

make our forecast based on that.

Now what you'll see later in this worksheet,

I've also put in completed versions of what we're going to be working through,

so you can go back and look at the videos the steps that we are taking.

You can also refer to the completed sheet of the corresponding module, all right.

So what we are going to do in building this up is,

we are going to try to build the model using the first 26 weeks of data.

And then we are going to see how well the model does at testing the rest of

this data.

All right, so to get started,

let's calculate out the number of incremental triers.

Well that's going to be the difference between how many people tried it,

in the first week, it's just going to be the number of triers that we have,

that's the first week it's offered.

In subsequent weeks,

it's going to be the difference between the cumulative triers in adjacent weeks.

So cell C7, that's going to be the difference between how many

people tried it in week 2, and how many people tried it in week 1.

5:01

And so if we look at this, if we look at the sum, it's 139 incremental triers and

that matches the total number of triers that we have in our data.

So no problems there.

Next, in cell D6, what we want to calculate is what's the probability that

someone hasn't tried it by the beginning of this particular week?

Well, this is a survival probability.

5:25

Prior to the first week, it's guaranteed that they haven't tried it, so

we're going to start at one.

Now, if we're assuming a geometric model, we can say that there is a constant

probability each period of people trying the product.

And so what's the probability that someone by week two,

has not yet tried the product?

Well, D7 we can write as it's a probability they hadn't tried it yet

multiplied by the probability that they do not try it in the current week.

6:04

So, D6 tells us the probability they hadn't tried it previously,

multiplied by 1-F1, that's the probability they don't try it In the current period.

And we're just going to copy that formula all the way down.

Now one of the reasons I wanted to put in 0.5 as that starting probability is it

produces that sequence that we might expect of what's the probability of

someone not having tried it by the beginning of the second week is 0.5.

Probability of someone not having tried it by the start of the third week is 0.25.

By the fourth week, .125.

So making sure that everything looks right.

So if we have column D telling us the probability that they have not yet

tried the product by the start of that week,

what's the probability they'd try it in a particular week?

We can take the difference between the survival probabilities at

different points in time to calculate this particular probability.

So what's the probability that someone tries the product in the first week?

Well, it's going to be the difference between,

not having tried it before the start of the first week, and

not having tried it by the start of the second week.

7:23

And so for our log likelihood what

we want to put on Column F, we want to take into account, how may people does

this particular probability correspond to, and what is the probability itself?

So the log likelihood for 8 individuals,

that's given in C6, well, we're going to take the natural logarithm

of the probability of trying it in the first week.

8:10

So that's going to give us our log likelihood for

the observations where people try the product in week one.

And we're going to copy this down, for the first 26 weeks,

because that's what we're going to be using test to made our model.

All right, so we've got the log likely to associate with

the people who tried the product in the first 26 weeks.

What we then have to take into account,

what about the people who didn't try this product by this particular point and time?

So in cell F32, we're going to calculate and

manually type in this log likelihood.

So for the 1499 minus the 101 individuals who tried the product.

10:14

All right, so the next step for us is going to be the u solver.

So to find the appropriate value for this probability.

Now that's under the data ribbon, and we're going to click on solver.

And you'll see the set of constraints that I added in place here.

The constraints that we have is we want to maximize the value of the cell F2.

So the objective function is in F2,

we want to maximize it by changing the value of F1, our adoption probability.

Now this has to be a probability, so you'll see I've added constraints

10:50

saying that it must be greater than a very, very, very small number that is

just bigger than 0, and it's gotta be less than 1.

And we click on Solve button, that's it.

So the acquisition probability in a given for

an individual is 0.003 or 0.03%.

All right, now what does that mean to us?

How good of a job is this model actually doing in terms of

forecasting customers adoption of this particular product?

Well let's take a look at that.

So we can look at the predicted number of incremental adopters.

11:33

So in cell G6, we can calculate this as the total sample size,

the 1499, multiplied by the weekly adoption probability, right?

So what fraction of people are going to try it in this particular week?

That's given to us in column E, and

we can copy this formula all the way down this spreadsheet.

12:15

And then in subsequent periods, it's the number of previous

cumulative adopters plus the number of new incremental adopters.

We can copy that formula down our spreadsheet.

And now we want to take a look at, and

see how good a job are we doing in actually capturing the data.

Let's take a look at this, looking at the cumulative triers,

14:11

It seems like we're doing a reasonable job during the calibration period.

Early on, we're okay, later on, we're okay, in the middle, it's not great.

But once we get into our forecasting period, our geometric model is assuming

much higher adoption that we're actually observing.

And if we wanted to change this instead of looking at the cumulative adaptors what if

we look at the incremental adaptors to see what's happening on a week to week basis.

So I can click on the lines in the graph and just drag the cell references.

And again, we do an okay job on in incremental basis for the first 26 weeks,

but looks like adoption afterward is considerable lower.

So the geometric module on its own doesn't seem to be doing the trick, all right.

So the next thing that we're going to do is we're going to move on to

building a model within linear time trend.

15:11

Now if you want to go back and review on the geometric completed sheet,

you'll see the work that we just conducted.

So let's try to improve the model to allow for a linear time trend.

The more time that has passed the less adoption we observe over time,

the lower the adoption probability in a given week.

And so we're starting roughly with the same worksheet.

15:43

But notice up top, we now have two coefficients.

There's an alpha and a beta, so alpha's going to be our intercept,

beta1 is going to be the impact of time.

We're going to follow the same structure,

what's the probability that someone has not yet tried the product?

And so we're going to calculate out that probability.

For each week, that's essentially our survival function.

We construct the incremental probabilities to give us the probability

of trial in a particular week and we use that to get the log likelihood.

So in this case again, we're going to start out as one,

because no one is trying the product before week one.

What about prior to week two?

Well, it's going to be a probability that you survived up until now,

multiplied by the probability that you don't try the product in week one.

All right, so

we're going to use transformation again to calculate that probability.

And what we want is for the loadshare probabilities,

so E raised to the linear combination divided by 1 plus E to that linear

combination to reflect the probability that you do try the product.

So 1 minus that is going to be the probability that you do not

try the product in the previous week.

So, it's going to be 1 divided by 1 plus

E raised to the power of the intercept plus the coefficient for

time multiplied by the week number.

19:20

Then, in cell F32, we're going to calculate

out the probability associated with the people who did not yet try the product.

So, that's the difference between our sample size and the number of cumulative

triers through week 26 multiplied

by probability of not trying the product by this particular point in time.

So, natural log of cell D32.

So we have our initial value set for

alpha and beta, our sample log likelihood,

we're going to calculate the sum of F6 through F32.

And now we're going to get solver up and running.

20:16

All right, so

things are going to be a little bit different in this case because previously,

when we ran the geometric model, we only had one cell that we we're changing.

Now, our objective function is in cell F3, and

we want to change cells F1 and F2.

I'm going to get rid of these prior constraints so I'm going to delete those.

20:46

And just to make sure that the solver doesn't go to values that are too extreme,

you can take a look at the constraints that I've used to run this

model previously, they're provided in the hand out.

Let's try to run it without any constraints and

then we'll see what happen.

If we get an error message, we'll put in some constraints.

21:12

And so this was what the concern was.

You can see we're getting that error message that

solver's just going to values that are too large, and it's producing errors for us.

And that's probably because of the week variable and

the number they are multiplied by a large coefficient giving us an error message.

You know, running into values that the computer just cant process.

So let's go back into solver and

adding the constraint that our time coefficient has

to be no larger than or has to be larger than.

Yeah we ran into that error message, we saw it's a negative 1.6.

Let's try negative .9999 as a minimum value and

let's take a look and see if we have any better luck in this case.

All right, there we go.

And when you could test out different solutions to see

how sensitive the results are to the starting values but

as we would expect in kind of what we saw from the geometric model where

we were over forecasting the number of triers was there's a negative time.

22:23

All right, there's a negative effect of time.

As more time passes, people are less and less likely to try the product.

Now, we can build out the same charts that we did before,

how many incremental tryers are we going to have,

it's our overall sample multiplied by that incremental tryer probability

23:18

Those last rows cause us some problems.

Those are, because the end of our probability equation.

So if we want to take a look at how good a job are we doing at capturing incremental

triers, we'll hold down Ctrl+Shift from C5, click on down,

that'll highlight, Column C for

us and it looks like we haven't dragged this formula down so let's do that first.

That is I'm going to copy down our incremental triers formula so

that we can compare for the 52 weeks, so

from C5 I'm going to hold Control Shift and press down to C 57

23:59

And I'm going to then release all of the keys,

and going to hold control when I click on G57.

And then I'm going to hold shift and

the up arrow to highlight up to G5.

And let's take a look at the graph that we produced doing that.

And in this cause it looks pretty good.

Notice that we're not perfect, but we only use the data up through week 26.

And our negative time trend seems to be a much better fit for the data.

And then, the number of incremental triers later on continues to decline and

we're able to pick that up with the linear time trend.

All right, and just as we had when it came to

26:02

Now, with the hardcore never buyers,

they're saying there are really two distinct segments in your population.

One of those segments is going to follow a distribution, in our case a geometric

distribution, they're actually going to try the product, we just don't know when.

The other segment is never ever going to try the product no matter

how long we wait.

26:28

So let's start by going down to C33 and

just copying the incremental formula down the rest of this column.

Now, we're going to start in column D by constructing our survival probability,

just as we have before.

And then, in column E, when we're saying,

that survival probability is going to be conditional on tried at some point.

26:58

hardcore never buyer segment.

And that needs to follow through all the way to that likelihood.

So let's start with our survival probability.

This survival probability is going to be identical

to how we did it on the geometric worksheet.

So we're going to start out with one for not having tried it before the first week,

then what's the probability that you try it after that period,

it's going to be probability they've not yet

tried it, multiplied by that weekly adoption probability.

27:51

All right? The next step.

Now, here's where we differ, alright?

What we had done before,

we said how do we calculate the probability that you try it this week?

Well, we just take the difference between not having tried it befor week one and

not having tried it before week two.

But that only works if we know you're in the segment of people

who are eventually going to try the product.

28:44

All right, so our log likelihood specification for

the first 26 weeks, same as we've done before, at this point.

It's the number of individuals that exhibit this behavior,

multiplied by the logarithm, the natural log of what's the probability that you try

it in this particular week And

we're going to drag this formula down for

the first 26 weeks in our file.

29:36

And so, we've captured the log likelihood for

the 101 people who tried the product by the end of week 26.

What about the individuals who didn't try the product?

Well, one possibility for

the 1,499 individuals

minus the 101 individuals.

So B2 minus B33.

It's the natural log of the probability that they don't try the product,

that they're not observed to try the product by the end of week 26.

One possibility is that they just haven't gotten around to it yet.

That is, they are going to try the product,

so the chances of them trying the product at some point,

they have to be in the segment of people who will do that.

So one minus F2.

31:05

The other possibility, and I'm going to add just some parentheses to make

sure that I don't have any problems with order of operations.

The other possibility is that they're never ever going to try it, and

that's given by a probability that they're in the segment of hardcore never

buyers with a probability of F2.

All right, so, it's the number of individuals who didn't try the product,

B2 minus B33, multiplied by the log of the probability.

Probability comes about in two ways, either you're never going to

ever try this product no matter how long we wait, that probability is given by F2.

Or you are going to try the product at some point,

with the probability of one minus F2.

And you just haven't gotten around to it yet, with the probability given by D34.

All right, so that gives us our log likelihood for each time period.

Let's calculate that sum, so in F3,

we're going to enter the overall, the sum from F8 through F34.

And we're going to use solver to figure out

what are the appropriate values for what we're calling P and Q.

33:24

And same approach for the cumulative adopters.

So the first ones are given by the week one incremental adopters,

and then in subsequent weeks it's the previous cumulative adopters plus the new

incremental adopters.

And we can copy that one down, all right?

And this last row again,

just created by that formula going a little bit long for us.

So let's take a look at how good a job this model does.

36:43

Calculate the probability of not yet trying the product the same way that we

had previously.

Then we're going to in column E when we calculate the incremental

trier probability we are going to take into account

the probability of never ever trying the product.

Calculate our log of likelihood, and when it comes to that final observation,

the block of people who didn't try the product,

remember we've got to take into account the two possibilities.

You could try it at some point, you just haven't done it yet.

Or you may never ever try that product.

And then we can look at the comparison between actual data and our predictions.

And just to see what this looks like,

I'm going to jump over to the completed worksheet.