We covered how to handle the problems of overbooked dog sitters in Part 1 and elaborated on it a bit in Part 2. However, in those previous posts we examined fairly simple, static scenarios, where we only had to decide between two sitters. Here we’ll cover how to handle perhaps any number of sitters, with any number of available spaces, existing inquiries, booking rates, and even different revenue/sitting fees.
To do all this, however, requires some coding knowledge. I use Python as friends recommended I pick that one up first, and I highly encourage anyone else who works on complex analytical problems–yes, even fellow marketing people–to learn it as well.
Now imagine a scenario where we have to sort three sitters, Rob, Jane and Rose. When we were looking at only two sitters in Parts 1 and 2, we could simply compare the expected values of sending the next lead to one sitter vs. the other. When we’re dealing with more than two sitters, however, that method is simply too inefficient and too slow, so instead we’ll look at Marginal Expected Value. How much additional expected value can we expect from sending the next lead to a sitter? We’ll calculate that by taking the Expected Value from sending the next lead to the sitter and then subtracting the Expected Value the sitter would have had if he only had his initial set of leads (i.e. if we hadn’t sent over the next lead):
EVMarginal = EVAdditional – EVInitial
Once we’ve run that calculation for each sitter, we’ll sort the sitters from greatest Marginal Expected Value to least.
Now on to the script, which you can find here on GitHub.
We begin by importing Python’s ‘math’ module.
Then we set up a list of dictionaries, ‘sitter_info’.
Each dictionary within sitter_info contains the following data for one sitter:
- Spots – the number of open spots/dogs the sitter is still able to take in
- Leads – the number of requests/inquiries for sitting the sitter still has active
- Rev – the revenue associated with the stay
- Bookrate – the sitter’s booking rate (the odds that the sitter can convert a lead into a stay)
In this case, we’re working with three sitters. Rob has 2 spots, 10 leads, $40 revenue per stay, and a booking rate of 40%. Jane has 1 spot, 4 leads, $40 revenue per stay, and a booking rate of 33%. Rose has 3 spots, 6 leads, $0 revenue per stay, and a booking rate of 25%.
We’ve also set up an empty dictionary called ‘sitter_marginal_ev’ that we’ll use for entering each sitter’s name and Marginal Expected Value.
Then we’ll loop through the list sitter_info to calculate each sitter’s marginal EV. We set up the ‘for’ loop here and define the variables we’ll use in our calculations:
Note that we use the term/variable ‘wins’ for booked stays.
Within the ‘for’ loop, we’ll run a couple of while loops to get both EVInitial and EVAdditional. Starting with EVInitial, we set up this ‘while’ loop:
The loop begins by defining ‘effective_stays’ in the ‘if’ statement. We use this variable to reflect the limit on revenue placed by the maximum number of vacancies a sitter has. For example, even if a sitter gets more than two people wanting to book a stay, if she only has two vacancies, then she can only book two stays.
In each iteration of the ‘while’ loop, we calculate the expected value from the sitter being able to book 0, 1, 2, 3, and so on stays, up until we’ve calculated the expected value of the sitter booking as many stays as she has leads (‘initial_leads’). This expected value then gets added to the variable ‘ev’.
After iterating through all the possible numbers of stays and adding up the expected values for the initial set of leads, we then run a similar ‘while’ loop to get the expected value if the sitter gets the next lead. In other words, we find EVAdditional which we define as ‘ev2’:
Then we calculate Marginal Expected Value:
And then we populate the dictionary ‘sitter_marginal_ev’ that we defined back near the beginning of this script:
Finally we sort the sitters by their Marginal Expected Value and print the results:
In this case, we find that we’ll get the highest Marginal Expected Value from guiding the lead toward Rose, who, despite having the lowest booking rate, has the lowest ratio of leads to vacancies. Jane comes in second place, and Rob in last.
If you were to arrange them in search results, this is probably how you’d want to do it.