How to predict orders when unit of measure is different across and within months I have to predict the order quantity for a 6 month time period. The products are available in different unit of measurement and i dont the relationship between the measures i.e i dont know the relations between unit A and Unit B. A could be 10 times B or smaller, i dont know.
In such a case, how to forecast the order quantity. A snapshot of the data is available below:
customer    area    year    month   ordererd in units   order_qty
1   NORTH AMERICA   2014    10  DR  2
1   NORTH AMERICA   2014    10  RBC 1
2   EUROPE  2014    11  DR  1
2   EUROPE  2015    6   KG  120
2   EUROPE  2015    6   DR  1
2   EUROPE  2015    7   DR  1
2   EUROPE  2015    9   DR  2
3   EUROPE  2014    8   KG  2800
3   EUROPE  2014    10  BAG 2
3   EUROPE  2015    5   KG  500
3   EUROPE  2015    6   KG  50
3   EUROPE  2015    7   KG  500
3   EUROPE  2014    11  KG  50
4   NORTH AMERICA   2014    9   BAG 1
4   NORTH AMERICA   2014    9   KG  3
4   NORTH AMERICA   2014    11  KG  8
4   NORTH AMERICA   2014    12  EA  4
4   NORTH AMERICA   2014    12  EA  7

 A: Have you tried figuring out and converting the units? I found a good PDF that lists things like "DR":  http://www.producetraceability.org/documents/dst_units_of_measure_codes_121611.pdf
Painful, but I think the only thing that will work.
KG (kilograms) is easy, DR (drums) and BAG (bags?) could be standardized in the industry or at least for that product. EA (each?) would be a total guess, since you know that this could include drums or bags, at a minimum.
If you have actual customers and they sometimes order in different units, you might be able to figure it out: does it make sense that EA is drums or bags? Or if you have comparable customers who order in different units, you could do some kind of matching of customers, then equate their units to figure things out.
I don't see how mixed units can be automatically handled, though. Do you have shipment records that go with order records? Are they measured in a standard format?
A: One approach to reconciling the differences in scaling would be to, 1) treat the products as cross-sections in a pooled time series model and, 2) take the natural log of the continuously distributed metrics. Leveraging a natural log relativizes the metrics into a more or less consistent scale for analysis.
A: Based on your description of the problem, the sample data, and your stated assumption that you don't have visibility into the specifics on each unit of measurement, I see no other solution than to build one forecast model for each product group. The product groups can be created based on their units. In the sample you provided, there are five product groups (DR, RBC, KG, BAG, EA) which implies five separate forecast models. This is the only way to avoid the confounding of different product units.
I would suggest building a time-series based regression model to make this forecast. You can first aggregate the input data by month and product group, such that you have one record for each $product \ group + month$ combination. For each such record, you would take the sum of the order quantities (for each product group), which becomes your target/independent variable. You can start with a simple Autoregressive model of the following form, and then add complexity as needed:
$$
y_{t}=\beta_{0}+\beta_{1}y_{t-1}+\beta_{2}y_{t-2}+\epsilon_{t}
$$
Where $y_t$ is the order quantity for month $t$, and so on.
Alternatively, if you have customer-level explanatory variables available, you could build a model at the $customer + month$ level as well.
