Developer forum

Forum » Ecommerce - Standard features » Last viewed, Last Searched, Most bought, Most Frequent Bought

Last viewed, Last Searched, Most bought, Most Frequent Bought

Adrian Ursu
Adrian Ursu
Reply

Hi Guys,

I am trying to build a few Product lists in one solution.

I am aiming to build the following:

1. Bestsellers (Shopwide)

2. Most Viewed (Shopwide)

3. FavoritesProducts (User related)

4. Most Ordered Products (User related)

5. Recently ordered products (User related)

6. Last viewed (User related)

I am trying to build these lists as feeds in order to take advantage of the Handlebars approach.

I could find a few Macro parameters already defined in DW: MostFrequentBoughtProducts, MostBoughtProducts, FavoritesByUserId. These could potentially solve some items from my list, but I am not sure how I can avoid defining separate feeds for each of these purposes.

I would very much like to add the remaining ones and I believe some of them should maybe be based on the stats (Most Viewed, Last Viewed). Does anybody have any direction on where should I start? Or if there is already something in DW that I can use to avoid reinventing the wheel?

Thank you,

Adrian

 

 


Replies

 
Nicolai Pedersen Dynamicweb Employee
Nicolai Pedersen
Reply

Hi Adrian

The existing macros simply return a list of productids that are used as a parameter on the query. So make custom ones that does the same.

To make them work on the same query, you would need the macros to understand which one you want - otherwise all of them will return a list of product ids and all get activated on the search.

A quirky workaround is to first create the macros you need - and then create macro that wraps the other macros - "CustomMacroWrapperMacro" or something. Then when you call the feed, you can pass a long a querystring /gimmmeproducts?usemacro=mostordered and then in your macro wrapper, look at that parameter and then call the appropiate macro to return the productids.

Then you can create a query that will use your macro wrapper macro as value on the productid field, and only if the usemacro querystring parameter is set, it will return a list of product ids and activate that criteria on the query.

BR Nicolai

 
Adrian Ursu Dynamicweb Employee
Adrian Ursu
Reply

Hi Nicolai,

It sounds good. I was actually trying to figure out how to use the current macros without customizing anything but I guess I will have to wrap them as you suggested.

It might be a good standard functionality though. I have also opened another thread about adding Sale and New in Generated Fields in the ProductIndex Schema in order to make this whole listing a bit easier.

Any suggestion about how to use Stats to create the Most Viewed and Last Viewed filters?

I am not sure I can identify the CurrentUser in the stats. I see there are some VisitorIds in the stats but I am not sure if I can connect them to the AccessUser table.

Thank you,

Adrian

 
Nicolai Pedersen Dynamicweb Employee
Nicolai Pedersen
Reply

Hi Adrian

You have either a visitor id or a accessuserid on the stat tables. So you can use that - that information is available from cookie.

The last viewed products can be found in "Dynamicweb:YouHaveSeenTheseRelatedListProducts" session variable.

This is how we find most popular products (most viewed) - but it is slow, so you have to deal with that:

(Also remember that showing this list makes no sense (for the user) and will be biased with its own products since they are clicked more....)

[RelatedProductList("eCom:Related.MostPopularProducts")]
    public class MostPopularProducts : RelatedProductListProvider
    {
        public override ProductCollection GetCollection(RelatedProductListProviderEventArgs eventArgs)
        {
            var cachingTimeoutInMinutes = 60;
            var cacheKey = "Dynamicweb.MostPopularProductsQueryCache";
            if (!string.IsNullOrEmpty(Common.Context.LanguageID))
                cacheKey = cacheKey + "." + Common.Context.LanguageID;

            ProductCollection col;
            if (Cache.Current[cacheKey] != null)
            {
                col = (Cache.Current[cacheKey] as ProductCollection);
            }
            else
            {
                System.Text.StringBuilder sql = new System.Text.StringBuilder();

                sql.Append(@"
                    SELECT TOP 10
                     EcomProducts.*,
                     UnitsSold
                    FROM
                     EcomProducts INNER JOIN
                     (
                      SELECT
                       OrderLineProductID,
                       OrderLineProductVariantID,
                       SUM(OrderLineQuantity) AS UnitsSold
                      FROM
                       EcomOrders,
                       EcomOrderLines
                      WHERE
                       OrderLineOrderId = OrderID AND
                       OrderComplete = 1
                      GROUP BY
                       OrderLineProductID,
                       OrderLineProductVariantID
                     ) AS SumTable
                     ON
                      OrderLineProductID = EcomProducts.ProductID AND
                      OrderLineProductVariantID = ProductVariantID
                ");

                if (!string.IsNullOrEmpty(Ecommerce.Common.Context.LanguageID))
                    sql.AppendFormat("WHERE EcomProducts.ProductLanguageID = '{0}' ", Ecommerce.Common.Context.LanguageID);

                sql.Append("ORDER BY UnitsSold DESC");

                col = Product.GetProductBySql(sql.ToString());
                Cache.Current.AddOrUpdate(cacheKey, col, new CacheItemPolicy { AbsoluteExpiration = DateTime.Now.AddMinutes(cachingTimeoutInMinutes) });
            }

            return col;
        }

 
Adrian Ursu Dynamicweb Employee
Adrian Ursu
Reply

Hi Nicolai,

Thank you very much. I agree some of these lists are useless. But the customers are asking for them before we even have a chance to debate the reasons behind the requests.
So I figured I need to have them ready and if they want to add them to their homepage, is on their own expense. As long as they are aware the lists will be slow, for obvious reasons, I cannot argue too much with their reasoning.

Thank you,
Adrian

 
Nicolai Pedersen Dynamicweb Employee
Nicolai Pedersen
Reply

You can make them fast - just cache the result of the sql for i.e. 15 minutes or so.

 
Adrian Ursu Dynamicweb Employee
Adrian Ursu
Reply

Hi Nicolai,

I have looked into the Session variables but I could not find the one you mentioned.

I only got these ones:

Dynamicweb.Analytics.Experiments.TrackingHistory

Dynamicweb.OMC.VisitCreated

Dynamicweb.OMC.SessionVisit

Dynamicweb.OMC.DynamicsApplyCache

Visitor.VisitorEngagementCache

Dynamicweb.Analytics.Experiments.LastVisitedPageID

ParagraphSegment.GetDisabledParagraphIds.3419.0

ParagraphSegment.GetDisabledParagraphIds.3489.0

ParagraphSegment.GetDisabledParagraphIds.3420.0

FetchProductInfoResult

Dynamicweb.eCommerce.LiveIntegration.Prices.ProductInfosCached

ParagraphSegment.GetDisabledParagraphIds.3499.0

DW_Statv2Session_Statv2SessionID

ParagraphSegment.GetDisabledParagraphIds.3656.0

 

Should I try and use the "eCom:Related.YouHaveSeenTheseProducts" Template Tag?

https://doc.dynamicweb.com/template-tags/ecommerce/product-catalog/ecomrelated/ecomrelated-youhaveseentheseproducts-count

 

Thank you,
Adrian

 
Nicolai Pedersen Dynamicweb Employee
Nicolai Pedersen
Reply

The Dynamicweb:YouHaveSeenTheseRelatedListProducts session only have values if you use the eCom:Related.YouHaveSeenTheseProducts tag

 
Adrian Ursu Dynamicweb Employee
Adrian Ursu
Reply

Hi Nicolai,

I am not sure what you mean.

How should I use the tag? Should I just place it in the ProductDetailPage somewhere?

Adrian

 
Nicolai Pedersen Dynamicweb Employee
Nicolai Pedersen
 
Adrian Ursu Dynamicweb Employee
Adrian Ursu
Reply

Hi Nicolai,

Please bear with me for a sec.

You were saying there should be a Session variable holding the last visited products.

If I want to use this variable in a paragraph on the homepage, can I use it or not?

What is the connection between the tag and the Session variable?

Do I need to include the tag in all product lists and product detail pages in order to populate the values in the session variable?

Thank you,

Adrian

 
Nicolai Pedersen Dynamicweb Employee
Nicolai Pedersen
Reply
This post has been marked as an answer

If you use the tag, the session will be there, otherwise not - adding data to the session only happens if the tag is present. If the session is there, you can use it, otherwise you cannot.

Votes for this answer: 1
 
Adrian Ursu Dynamicweb Employee
Adrian Ursu
Reply

Hi Nicolai,

I confirm it now adds information to the variable.

The variable seems to be a ProductCollection object which is reported as Deprecated.

Should I ignore this deprecation method?

Thank you,
Adrian

 
Nicolai Pedersen Dynamicweb Employee
Nicolai Pedersen
Reply

Yes. I have removed the deprecated attribute as that object is far from deprecated.

 
Adrian Ursu Dynamicweb Employee
Adrian Ursu
Reply

Thank you Nicolai,

I have managed to make it work.

Thank you very much.


Adrian

 

You must be logged in to post in the forum