Need Help with Timeseries

Question

Hi,

I'm new to the SQL server data mining and analysis services. I've been trying to create a mining model to predict customer-wise purchases patterns using SQL server analysis services Time series. In my Purchases table I have columns

PurchaseID as primary Key

PurchaseDate

CustomerID

PurchasedItem

Amount

When I create a time series model using SQL server analysis services 2012 using purchaseID and CustomerID as keys and PurchasedItem as the Input and Prediction .

But Im not getting separate time series for each customer.

• Is there any way of doing this with my existing table structure?
• Do I need to have separate columns in my table for each of the customers purchases?

Answer 1

Hi Lasith,

In your data mining project, there is no date column as single time key that is used as the case series, which defines the time slices that the model will use. So you can't get time series. There is no need to take the purchaseID as key in the model. Please refer to http://technet.microsoft.com/en-us/library/ms174923.aspx to learn time series algorithm.

Thanks,
Jerry

Answer 2

What are you trying to predict? Time series algorithm is used to predict numerical values over time. Are you trying to forecast Amount for each customer? If I was doing it, I would aggregate all customer transactions within a month (week, year or any other time period), use Date as time key, CustomerID as key, and TotalAmount as input and predict. Note that if you have many customers, algorithm performance might be very bad so you would need to do something different. (Let me know if this is what you are trying to do and I can provide more details on how to do this).

Since you used PurchasedItem as input and Predict, I suspect that you are trying to perform different type of analysis, that should be done using algorithm different form Time Series. For example, association rules algorithm is often used for "shopping basket analysis". It is used to predict what items you are likely to puchase based on items you purchased in the past.

---

Tatyana Yakushev [PredixionSoftware.com]

Answer 3

Thx Jerry :)

Answer 4

Hi Tayana,

Sorry for the late reply. I was stucked with several other projects therefore couldnt look in to this..

Yes I have a table with the aggregated values as well. But the columns are the same as above except for the replacement of Amount with Aggregated Amount and PurchasedItems removed.of course ur point is true coz my data set contains millions of transactions therefore such an aggregation is necessary.

the thing is even in Jerry's link http://technet.microsoft.com/en-us/library/ms174923.aspx the Example 1 table is quite similar to my case but I can;t generate seperate time series for each of the products even though it says it can..

It would be greatly appreciate if you can help me with ur expertise knowledge.