Solved

PropCount with Multiple Group By properties

  • 19 October 2022
  • 4 replies
  • 238 views
S

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 select (count(*)*1.0/count(distinct concat(product_id, UserID)))::decimal as metric from 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.

icon

Best answer by belinda.chiu 20 October 2022, 20:11

View original

4 replies

timothy-permutable
Rank
Badge +10

Hi! I believe you’re overcomplicating this by using a formula - I think if you select the “average” tab and group by product_id, you have what you want. Please try that, otherwise let me know and we can find another way.

Timothy | Product Analytics Consultant - permutable.co
S

Thank you for the reply, but that does not work. I am trying to get to a singular daily number; total visits / unique product_id-user_id combination. If I use the Average tab, I get the average # of users per product_id. I am 99.7% sure I am overcomplicating this, I just don’t know how.

belinda.chiu
Rank
Userlevel 6
Badge +8

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! 

S

Thank you @belinda.chiu, that is what I was afraid of. We are Growth-level, but as we are still learning our way around it, I don’t think we are in the market to be including any paid add-ons. Appreciate the answer.

Reply


Terms & ConditionsCookie settings