Point estimate options with highly skewed data

Question

(Added regression tag since I think that my question overlaps with that area but not sure. Added transformation tag since I discuss log transformations. Tag recommendations welcome)

I would like to create a single coefficient to estimate a customers 2 year spend based on their total spend after having been a customer for 7 days. A ratio.

Very rough a crude formula which I would like to reasses:

2 year estimated customer value = sum(total revenue from customers after exactly 730 days) / sum(total revenue from customers after exactly 7 days)

I'm aware there are far more appropriate and advanced approaches to estimate lifetime value such as a regression model. But for here and now I am trying to figure out the best way to calculate a point estimate given the distributions of my data.

Here is the distribution of spend after 7 days for a cohort of customers:

pdata %>% 
  ggplot(aes(x = d7_total_rpi)) +
  geom_histogram(fill = "lightblue") +
  scale_x_continuous(labels = comma)

Most customers don't spend anything so the data really skew to 0. Here is the same chart but filtered for customers with any spend (> 0):

pdata %>% 
  filter(d7_total_rpi > 0) %>% 
  ggplot(aes(x = d7_total_rpi)) +
  geom_histogram(fill = "lightblue") +
  scale_x_continuous(labels = comma)

Pretty much looks the same except there is a tiny visible second bar.

Here is the same distribution but with a log transformation:

pdata %>% 
  filter(d7_total_rpi > 0) %>% 
  ggplot(aes(x = log(d7_total_rpi))) +
  geom_histogram(fill = "lightblue") +
  scale_x_continuous(labels = comma)

Presumably this 'looks' better because it's more normal?

The 7 day spend distributions shown above are my predictor. I'd like to use 7 day spend as a proxy to estimate 2 year spend.

Here are the same distributions above but for my target variable:

Just the raw as is distribution of 2 year spend for the cohort:

pdata %>% 
  ggplot(aes(x = d730_total_rpi)) +
  geom_histogram(fill = "lightblue") +
  scale_x_continuous(labels = comma)

Similar to the raw predictor variable distribution, pretty much all 0's. Now the same distribution but filtering out 0's, so only those with any spend:

pdata %>% 
  filter(d730_total_rpi > 0) %>% 
  ggplot(aes(x = d730_total_rpi)) +
  geom_histogram(fill = "lightblue") +
  scale_x_continuous(labels = comma)

Not much change.

Here is the same but with a log transformation:

pdata %>% 
  filter(d730_total_rpi > 0) %>% 
  ggplot(aes(x = log(d730_total_rpi))) +
  geom_histogram(fill = "lightblue") +
  scale_x_continuous(labels = comma)

Like with the predictor variable, the target variable looks more normal with a log transformation.

I'm not sure where to go from here given my goal of determining a coefficient, single predictor of 2 years spend. I'd prefer to include those with 0 spend after 7 days since they may yet spend after 2 years. But the charts above suggest I should remove non spenders in order to make a log transformation. Am I thinking about this the right way?

Here are some more distributions if they are useful:

Distribution of log target / log predictor after filtering for any spend after 7 days:

pdata %>% 
  filter(d7_total_rpi > 0) %>% 
  ggplot(aes(x = log(d730_total_rpi) / log(d7_total_rpi))) +
  geom_histogram(fill = "lightblue") +
  scale_x_continuous(labels = comma, limits = c(-10, 10))

Does the chart above tell me anything useful when it comes to trying to determine a best way to calculate a coefficient?

Here is the same chart but with exponentiation values after the log transformation:

pdata %>% 
  filter(d7_total_rpi > 0) %>% 
  ggplot(aes((x = log(d730_total_rpi) / log(d7_total_rpi) %>% exp()))) +
  geom_histogram(fill = "lightblue") +
  scale_x_continuous(labels = comma, limits = c(-10, 10))

I cannot think of anything else which might be relevant. Given my input and target variable, and their distributions, what is an approach to estimate a coefficient of 2 year revenue based on 1 year revenue?

For example, would taking the ratio of the log of each variable and then exponentiating it be a sound approach?

(log(2 year spend) / log(7 day spend)) %>% exp()

Would this be a reasonable way to try to predict 2 year spend based on 7 day spend?

I guess I could compare this to the raw formula at the start of my post:

2 year estimated customer value = sum(total revenue from customers after exactly 730 days) / sum(total revenue from customers after exactly 7 days)

How would I compare them? I'm guessing based on standard deviation from the actual mean?

Advice and pointers most welcome.

Answer 1

The overarching issue that I can see in your data/problem are the customers with 0 spend. Because they haven't spent anything it's kind of hard to quantitatively predict their spending behavior except in an average sense (the average 2 year spend of people with 0 seven day spend and the standard deviation). So yes I would consider them differently by setting up two separate models.

I definitely agree with the log transform approach. It is highly recommended for data that is restricted to being positive. See here for a good explanation: https://statmodeling.stat.columbia.edu/2019/08/21/you-should-usually-log-transform-your-positive-data/

Now as far as evaluating this as a prediction problem: In your approach you are considering each variable separately but never together. You are trying to infer the relationship between 7 day spend and 2 year spend so you should be looking at them on the same plot. Then you can see the relationship between the two variables and that will guide the kind of analysis you do. Does the relationship look linear? Or is it non-linear?

Again I would do this with the log of the values.

So my approach would be:

1) Make a scatter plot of log(7 day spend) vs log(2 year spend) 2) Perform a regression analysis (should be easy to do in R) such as a linear regression on the log transformed data:

$\log(2 year spend) = \alpha*\log(7 day spend) + \beta$

3) Evaluate the regression analysis in the context of the prediction error (how far off are the predictions the model makes vs the actual 2 year spend of your training data) and see if that is good enough for your purposes. If it isn't then you may need to consider using a longer interval (e.g., 1 year spend) or possibly a different model with more variables (do you know anything else besides spend?)