(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.