Solved

How to PROPSUM with unique users

  • 23 February 2022
  • 3 replies
  • 1008 views
Y
Badge

according to the doc
The PROPSUM does:
Returns the sum of the property values you are grouping the specified event by. 

However I am interested in the sum of the property values by unique users. The use case is: 

I want to know the total value of the out of stock product each day. However, a user could visit one product page multiple times per day but if we assume the user would only convert once per day, then PROPSUM(price) would be elevated. I want to know the PROPSUM(price) for distinct users. 

 

icon

Best answer by SheenaGreen 25 February 2022, 17:05

View original

3 replies

SheenaGreen
Rank
Userlevel 4
Badge +8

Hi @yaqi, 

For those users who do visit multiple out-of-stock product pages, do you want to take the average value for product_price?

Y
Badge

 

Hi @SheenaGreen,

 

I think the best case would be to take the sum of product_price for the unique combination of a user and an out-of-stock product page. I just don’t want to duplicate the calculation if user visits one product page multiple times. 

But if there is a way to calculate what you’ve described, it can also serve as a nice proxy.

 

Thanks.

SheenaGreen
Rank
Userlevel 4
Badge +8

@yaqi got it! I don’t think there’s a way to accomplish exactly what you’re looking for, but this is what I would do for a proxy.


Have 2 events in your segmentation chart:

  1. product page viewed where is_out_of_stock = true, grouped by product_price
  2. product page viewed where is_out_of_stock = true

Have 2 cohorts:

  1. Users who performed product page viewed = 1 time, where is_out_of_stock = true, any time in each day
  2. Users who performed product page viewed > 1 time, where is_out_of_stock = true, any time in each day

Use the following formula:

PROPSUM(A1)+(PROPAVG(A2)*UNIQUES(B2))

 

-PROPSUM(A1) - the sum of product_price for users who only viewed one out of stock product page
-PROPAVG(A2) - the average product_price for users who viewed more than one out of stock product page

-UNIQUES(B2) - the number of users who viewed more than one out of stock product page

 

OR a more simple but less accurate proxy would be to just use PROPAVG(A)*UNIQUES(A) in your existing set up

 

Reply


Terms & ConditionsCookie settings