Calculating product average cross sale on product level in GD the easy(-ish) way.

The general basic formula for calculating cross sale of products is fairly easy. It usually goes something like: amount of products sold / amount of orders the products were on


It's there to inform the analyst about the average basket size where the items occurred.

This quantifies the cross sale potential of a product category or brand, and how likely the customers are to do a bigger purchase when they buy these brands or type of goods.


Basically, if an item has been on two orders, one had 5 items and the other 4 then the average cross sale of it is (5+4)/2.


This is all pretty easy when you need to break this down by some kind of product category or brand. Let's say you have something like this as a model:



All you need it to do something along these lines.

Count your orders:


# Orders

SELECT COUNT(ID Order (Orders),ID Order Items Events)) WHERE Event Type=Created AND Status (Orders)<>canceled


Count your order items:


# Items Sold

SELECT SUM(Qty Ordered (Order Item Events)) BY ID Order (Orders),Date (Date)) WHERE Event Type (Order Items Events =Shipped AND Product Type (Catalogue)<>SHIPPING

Nest them in a third metric:


AVG Cross sale

SELECT # Items Sold / #Orders


This is totally straightforward, as the cross sale between products falling within a certain category will calculate correctly (of course in case there's nothing wrong with the model, referential integrity or the source data).


You will however run into problems the minute you try and slice such a report by product. But why?

The # Orders metric will very likely function properly as it will list the amount of orders a particular product has been on. The # Items Sold metric however will start counting the amount of the product sold, but you need the “overall amount of products sold on the orders where that product occurred”.


Now to try and solve this all within GD metrics would probably require some crazy complicated filtering and variables. What can be done to work with this is to add a second “items sold” fact to the order table in the model that runs up the total amount of items on it no matter what.




Once you have this sorted out, you need to create a new lifted metric to sum the amount of the sold order items on the orders dataset and make it react to the “date dimension” that is placed on the “order items events table”


It should look something like this:


# Total Quantity Ordered

SELECT MAX(SELECT SUM(Total Qty Ordered (Orders)) BY ID Order (Orders)) BY ID Event (Order Items Events)


All you have to do now is just divide it by the order number in a nested metric and put it in your report just as before so something like this:


SELECT # Total Quantity Ordered / # Orders



Comments