1475672 - Exploration: LTV calulations in Spark Rather than Vertica

Status: RESOLVED FIXED

(Data Platform and Tools :: General, defect, P1)

Description

[pgerman]

Currently Mozilla's LTV model is being run inside of the Vertica environment. It takes 12 hours to run on a single sample_id.

We would like to increase the volume of clients that get processed through the model. :mreid suggested that it might be possible to do this by adjusting the lifetimes in mozilla github [1] to run in a distributed manner in pyspark rather than vertica.

For this task, please:
1. Confirm if this is possible
2. Outline the system resources (and cost) required to process this in a reasonable amount of time (<12 hrs) sampled at 2%,3%,5%,10%,50%,100% (entire population)

[1]https://github.com/orgs/mozilla/teams/ltv-methodology/repositories

:nancywong can support any questions with the existing github repo

Comment 1

[pgerman]

Thanks for assigning this. 

:amiyaguchi - When should we expect to take a look at this?

Comment 2

[Anthony Miyaguchi [:amiyaguchi]]

I took a look at this at the end of last week. I have a few questions and a notebook to consider while computing the LTV calculations. For context, there are two main models that are computed in the lifetimes library.[1]

* BG/NBD - A model of future transactions and likelihood of survival based on frequency and recency of transactions
* Gamma-Gamma - A sub-model of spending based on value and frequency of transactions

The first model is fed into the second one to calculate the total customer lifetime value by utilizing the time value of money.

I transformed 180 days of clients-daily data in 10 minutes resulting in a 7GB parquet dataset. This is probably one of the heavier computations because it requires 3 groupby-aggregates and 2 full table joins. The data transformation portion plays into Spark's strengths. This can be done with modest resource (1-5 nodes at < $1 an hour) in less than an hour. The performance I've seen here corroborates with a comment from the library author in [2].

I was able to fit a 1% sample into the BG/NBD model in 1.5 hours. I had trouble fitting data beyond this size in reasonable time. I haven't spend any time with the other two computations, but the Gamma-Gamma model is also fit via maximum likelihood estimation and the final calculation looks linear with the size of the transaction dataset.

From what I observe, there are several computationally expensive portions in the forecast pipeline. 

* transactional to frecency data transformation
* fitting the BG/NBD model
* fitting the Gamma-Gamma model
* forecasting customer lifetime value

Increasing the volume of clients might not be the right direction to take this. There are diminishing returns to model accuracy since the number of parameters are fixed. It'll be more fruitful to determine the sample size necessary to reach convergence (which is most likely *much* smaller than a 1% sample of release). 

Additionally, it might be informative to have many smaller models across sub-populations that can be used to forecast at more granular levels. For example, it might be valuable to forecast the LTV of beta clients across different locales. Spark can be used to fit and forecast across many sub-populations in parallel, which is also how shopify runs this in practice.[3] I would choose these dimensions with care, because the number of resulting models is exponential with respect to the number of columns.

As a result, I have a few questions:

* How many days of activity are you interested in? Is a sliding window of fixed size sufficient?
* How long does each step in the LTV calculation take? What is the observed bottleneck?
* Have you checked if sub-sampling is sufficient for model convergence? O(10^5) clients might be enough to paint a telling story.
* Are you interested in forecasting across segments of the population? What segments of the population are the most valuable? Is there enough statistical power to tell the difference between different segments?
* How are you storing the models and resulting forecasts?


I've included a link to the notebook with the data transformation and basic usage of the lifetimes library. It can also be found in Databricks.[4]


[1] http://lifetimes.readthedocs.io/en/latest/Quickstart.html
[2] https://github.com/CamDavidsonPilon/lifetimes/issues/132#issuecomment-325132163
[3] https://github.com/CamDavidsonPilon/lifetimes/issues/127#issuecomment-323503090
[4] https://dbc-caf9527b-e073.cloud.databricks.com/#notebook/22068/command/22076

Comment 3

[Anthony Miyaguchi [:amiyaguchi]]

Attachment: LTV cleaning clients_daily.ipynb

Comment 4

[Mark Reid [:mreid]]

Peter, Nancy, can you comment on Anthony's questions from Comment 2?

Comment 5

[pgerman]

Hey Anthony,

Thanks for the detailed response, and Apologies for the delayed response. I have been out on PTO. 

I think you are absolutely right about there being diminishing returns leveraging a larger sample for the purposes of fitting the model. 

The reason why we are looking to increase the sample is because there are campaigns that we run which may acquire 1000 downloads.

Example:
1000 downloads x ~60% install rate X 1% sample = 6 clients

As such, if it were possible to modify the script to stop fitting the model once convergence has been reached, but keep computing LTVs across the client's life, that would be acceptable as well.

At present, some key dimensions are attribution.source, medium, campaign, content, sync_configured, is_default_browser, etc. At present, these are never passed into the lifetimes package, but are joined back onto the data set based on client_id after the LTV has been computed. 

WRT your questions from above:
* How many days of activity are you interested in? Is a sliding window of fixed size sufficient? 
We need the client's life as far as it will go back

* How long does each step in the LTV calculation take? What is the observed bottleneck? 
The bottle neck is running the data through the lifetimes, it takes 12 hours to process at present. 100% of the population would take 50 days.

* Have you checked if sub-sampling is sufficient for model convergence? O(10^5) clients might be enough to paint a telling story.
We have not. We would need a big enough sample to get all of the cuts we are looking for.

* Are you interested in forecasting across segments of the population? What segments of the population are the most valuable? Is there enough statistical power to tell the difference between different segments? 
This is the limitation at present, and there are a number of use cases that we are looking to support.

* How are you storing the models and resulting forecasts?
In big query

Comment 6

[Anthony Miyaguchi [:amiyaguchi]]

Attachment: LTV flow.png

Thanks Peter, I appreciate your response.

I'd like clarify that creating the LTV calculations can be split into two smaller parts. The first part is building the model, which can be done on a small sample of data. The LTV over a client's life can then be computed independently of the first part.

The entire process can be framed as a pipeline split into stages. There are several intermediate data-sets that are created during the pipeline of varying sizes. If the goal is to have this run quickly and often, then it's important to know where the bottlenecks are going to be. 

Here is a catalog of the system parts:

Data
  - clients daily              (large, source)
  - transactional dataset      (large, from clients daily)
  - summary dataset            (large, from transactional, input into lifetimes model)
  - training set               (small, from summary, input into lifetimes model)
  - validation set             (small, from summary, used to check for model fit)

Pipeline
  - ETL: clients_daily to transactional  (fast w/ Spark)
  - ETL: transactional to summary        (fast w/ Spark, *very slow* with pandas/lifetimes)
  - ETL: training and validation sets    (fast, but sample size chosen here affects model training)
  - Training model                       (*very slow* if training set does not fit into memory, diminishing returns on sample size)
  - Validating model                     (should only include enough data for determining fit)
  - LTV prediction                       (scales linearly with data, can be run on full transactional dataset with some effort)


The attached notebook includes ETL that generates the summary dataset for 100% of the last 180 days of data. It can also be broken into pieces to also create the transactional dataset. This can also be used to create a holdout dataset for validation, with more examples in [1].

I've included a flowchart for a visual representation of the process. It should be possible to increase the number of clients fed through the model, if the model is trained independently in the pipeline.


[1] http://lifetimes.readthedocs.io/en/master/More%20examples%20and%20recipes.html

Comment 7

[Anthony Miyaguchi [:amiyaguchi]]

I'm closing this with respect to the issues brought up in comment #1.

It is possible to calculate LTV using the full telemetry data-set. Pre-processing the data should be done in Spark rather than lifetimes. Fitting/training the model can be thought of a separate process from generating LTV, which should bring the original 12 hour process down to something closer to few hours at most.

One thing that I'd like to stress is that validating the pipeline is important for interpretation. The lifetimes quick-start guide provides a quick and useful tutorial for understanding what's happening at each step. It also provides graphs that can be used for visually checking for fit. In Fader 2005 [1], the fit of the BG/NBD model is evaluated with the chi-squared test for goodness of fit. This metric could be a useful addition to the lifetimes library. 

[1] http://mktg.uni-svishtov.bg/ivm/resources/Counting_Your_Customers.pdf

Comment 8

[pgerman]

Thanks team. IT is in the midst of getting scheduling the build of this work.