PropCount with Multiple Group By properties

SAyo

Very new to Amplitude … I am trying to do something along these lines: TOTALS(B)/PROPCOUNT(A) where A is Event A Group By product_id AND UserID and B is Event A; but PropCount only considers the first item in a Group By. In PostGres, I could write the same thing as <strong>select </strong>(<strong>count</strong>(*)*1.0/<strong>count</strong>(<strong>distinct</strong> <strong>concat</strong>(product_id, UserID)))::<strong>decimal</strong> as metric <strong>from</strong> event_a;, but I have been tasked with writing this in Amplitude. Is there any way to do this in Amplitude? I am looking for the average number of times users visit each product page.

Thanks in advance.

belinda.chiu

Hi SAyo ! Welcome to the Amplitude Community 👋

Based on your original statement looking for the average number of times users visit each product page, I would have also suggested the Average tab with a group by or something like this: https://analytics.amplitude.com/demo/chart/new/6e1z8s1 

But it sounds like you want to get total visits / unique product_id-user_id combination which is slightly different. The complex part is getting “unique product_id-user_id combination”. Unless your organization has already instrumented an event property that would represent product_id-user_id combination, the only other way to basically do a concat like you would do in PostGres would be to create a derived property: https://help.amplitude.com/hc/en-us/articles/360058731292. You can then do a PROPCOUNT on that newly derived property. Otherwise, out of the box, there isn’t a way to create “unique product_id-user_id combination” as part of the Event Segmentation formula. 

Please note though that Derived Properties are part of our Govern add-on which is a paid add-on available to Growth and Enterprise customers. If you do not see this feature available in your Amplitude org, I’m more than happy to connect you with our Sales team to further assist!