Cart Persistence and Duration [SiteCatalyst]

I was recently working with a client who had some interesting questions. In general, he wanted to see different derivations of how long products had been in the shopping cart prior to being purchased. Some of his detailed questions included:

1. Of all visitors hitting my website today, how many already have items in their Shopping Cart (which is persistent on this website)?
2. For those visiting the site today, for how long have they had items in their Shopping Cart? (i.e. 1 Day, 10 Days, etc…)?
3. At the time visitors purchase items, for how many days had they had items in the Shopping Cart?
4. Is it possible to see cart duration by product?

While it is easy to see why this might be interesting to know, after some reflection, it turned out to not be a very straight-forward thing to understand/report upon using Adobe SiteCatalyst. I wrestled with a few different ways to answer these questions, but ran into a few roadblocks. In the end (and after bouncing some ideas off some friends), I settled on an approach that seemed to work (by no means the only one), so thought I would share it in case it is helpful to others out there with the same questions. If you have the Adobe Insight product, solving this question is much easier, but this post will deal with answering it for those of us who only have SiteCatalyst.

Establishing Cart Addition Date

The first challenge is to identify the date on which each visitor added items to the Shopping Cart. This is similar to an earlier post I had about Date Stamping, but with a twist. In the Date Stamping post, we just set the current date of each visit, but in this case, we want to set the date that a Product was added to the Shopping Cart (I suggest you use an eVar with original value, expiring at the Purchase event). Once you have done this and have data processing for a while, you can open the new Persistent Cart Date eVar report and add the Visits metric and see a report like this (in this example using the current date of 3/3/12):

Here we can see that we have answered our first question. By looking at the “None” row, we can see that approximately 92% of the time, Visits are from people that have not previously added items to their Shopping Cart (does not include those pesky cookie deleters!). If you broke this report down by the Products variable, you would be able to see the actual products that were associated with each date:

Identifying Duration in Cart

Our next challenge is to determine exactly how many days products have been in the shopping cart. As mentioned above, there are actually two flavors of this question. The first is to see how long ago products were added to the shopping cart at the time the current visit takes place, and the other is to see how long ago products were added to the shopping cart at the time a purchase takes place. We’ll start with the former.

With the preceding report and its breakdown by the Products variable, we have all of the key elements needed to figure out how long items have been in the shopping cart. However, to calculate this, it’s easier to use Microsoft Excel so I suggest you move the data to a spreadsheet using ReportBuilder or Data Extracts and then adding some formulas to break out the data as shown here (I have replaced the None row with the value “NO CART” in Excel):

Once this is done, you can create a pivot table to group like items together and build a report like this (for illustrative purposes, I only created a few rows but in reality there would be many more rows of dates in this report):

In this pivot table, we can still see our same 8% of Visits with no items in the shopping cart, but now we can see that our largest percentage is tied to cases where visits had items in the cart for 6 days. If you had more data, the next logical step would be to group the number of days into meaningful buckets using SAINT Classifications or directly in Excel. Also, note that instead of moving data to Excel, another way to create a report like the one shown here would be to create a SAINT Classification file that maps the current date to the number of days in the past (i.e. 3/2/12 = 1 Day), but we’d have to update the SAINT file each time to adjust for the current date which would be a pain!

Next, since we have the report data by product ID, we can also break down the above pivot table by product to see which products are associated with each # of Days in the cart:

Conversely, if your organization is more product-focused, you can flip the pivot table and look at Product ID’s by days in shopping cart like this (which will have more values per product ID when the data is real!):

You will note that these reports help us answer the first cart duration question which is how long products were in the shopping cart at the time a Visit took place, but the same process can be used to answer the second question which is how long products have been in the shopping cart at the time of purchase. To do this, all we need to do is modify our original SiteCatalyst report to show Orders instead of Visits like this:

Note that in this case, we should no longer see a “None” row since to complete an Order, something must have been added to the shopping cart prior to purchase. It’s likely that you will also see that the majority of the rows are for the current date (which in this case is 3/3/2012). Once you create this report, you export it to Excel and create the table and pivot table in the same manner described above. This might result in a report that looks something like this:

Product-Specific Cart Duration

The last question to be answered is related to the duration in cart of each product. In the examples above, we have set a date when products were added to cart, but this date was a general one or the date that the first product was added to the shopping cart. There will be cases when you want to get more granular and know the date for each product since visitors can add a product to the cart on 2/28/12 and then add different products to the cart on 3/1/12. If you desire this level of detail, in addition to setting the Persistent Cart eVar described above, you can set an additional Merchandising eVar (with “Original Value” allocation and expire at the “Purchase” event). This will “bind” the date to the specific product that is being added to the shopping cart. Since this is more complex, I won’t go into all of the intricacies here, but if you have questions, feel free to contact me.

Final Thoughts

As you can see, this is a somewhat complex solution, but should get you the answers you need. There may be other ways to answer these questions, so if you have tackled this, feel free to leave a comment here. Thanks!