How to model the relationship between geocoded and ungeocoded sales data?

Question

I am trying to model sales data for stores at the Census block group level in order to predict sales at potential new restaurants. For example, I know that store 2, which has a giant flashing neon sign, has $2K in sales from block group 101, which is 2.5 miles away from the store 2 and where there are 600 households and 50 people living in college dorm. So far this is pretty standard.

The fly in the ointment is that the average store has ~30% of the sales data that cannot be geocoded for some reason (new construction, college dormitories, military bases, lazy employees who take down the address in shorthand, and so on), so that I only know the store that handled the sales and not where those customers reside.

My approach to modeling the ungeocoded data starts with aggregating demographic data from around the store's trade area and all the ungeocoded sales, so that even if I don't know where those customers are, I can at least take a stab at understanding the sales behavior based on what's around the store. For example, if my store is near a college campus or has lots of construction, I would it expect it to have higher ungeocoded sales, all else being equal.

This works reasonably well, but the geocoded and ungeocoded sales models are not linked in any way, which is problematic. Essentially, my geocoded sales are measured with non-spherical error that is correlated with my explanatory variables. It's also the case that ungeocoded sales generally increase with geocoded sales. I tried to remedy the first by including the fraction of total sales that are ungeocoded in the geocoded sales model, and total geocoded sales in the ungeocoded model, but I don't know how to define those variables for potential sites whose sales I am interested in forecasting. I guess I can just set ungeocoded sales at 30%, then predict geocoded sales, and use that to forecast ungeocoded sales, but is there a better way to link the two models for the better estimation and forecasting?

Answer 1

I don't have a succinct answer, but I do have advice and comments too long for a single comment...

Having 70% of your sales come from transactions with a name and zip code (making it possible to match to an address in a given trade area) seems to be really really good. I would recommend not getting too bogged down with the untrackable transactions and simply scale the forecast up as needed. But, to this end, you should clarify if your current approach is able to model the sales from existing stores. Specifically, what is the distribution of forecast errors for existing stores?

Regarding the 30% of sales that you cannot geocode, I suspect that this is roughly the percentage of cash transactions at each store, and these are of course untrackable. But, I also suspect that cash transactions are generally lower ticket value, and that the ratio of cash vs credit card transactions correlates with the median income of the trade area. So, a useful forecast may be to predict the ratio of cash (and therefore lower ticket) transactions to credit card transactions based on the new store's real estate and trade area. That would give you the "effect size" of the ungeocoded transactions that you need to scale total sales by.

Answer 2

As I understand it, the situation is as follows. You have data about store, its whereabouts and the sales-geocoded or ungeocoded. For each block, you have some portion of sales as geocoded and the rest ungeocoded.

Now, if you have data available for many stores, you can use a model to predict FRACTION OF UNGEOCODED SALES from the data available for the new block. For example, you already have noticed that

"For example, if my store is near a college campus or has lots of construction, I would it expect it to have higher ungeocoded sales, all else being equal."

From that, you can forecast the fraction of geocoded sales=1- (fraction of ungeocoded sales). Now, check whether the fractions forecasted according to two models correspond to the above formula i.e. the fractions sum to 1. Checking correlation between them for significance using Fisher's test would give an idea of whether the idea of finding one fraction from other is correct or not.