Solved

Average over all groups

  • 24 April 2023
  • 7 replies
  • 191 views

We group our users into “customers” in the Accounts Addon in Amplitude, given we are a B2B service.

Now I would like to understand the average number of new users (there is an event “user created” for that) per customer.

Meaning, NOT the rolling average of each customer’s new users. But the average across the customers. So the average of CustomerA:5, CustomerB: 10 → 7.5 new users.

icon

Best answer by kornelius 16 May 2023, 08:56

View original

7 replies

Userlevel 5
Badge +6

Hi @kornelius -- since you want to look at the user base regardless of which account each user is apart of, I’m thinking querying on “any users” rather than “any customers” & calculating PROPAVG would do the trick. Here’s an example chart of what this would look like. What do you think?

Would that not just calculate the avg of the userids? Like there is user 10, user 20 so the avg would be 15? So irrelevant to actually get the average of how many events were triggered?

Userlevel 5
Badge +6

Good point! In that case I think PROPCOUNTAVG (returns the average number of distinct values) would suit. Thoughts?

This unfortunately also does not work.

The problem is, that average is either per group (so I have many averages) or per user (which only gives the average how often a user performed this). I would like to see one average on the average of the groups.

Userlevel 5
Badge +6

Hi @kornelius - I see, I see. What do you think about this custom formula in this example chart?

PROPCOUNTAVG(A)*UNIQUES(B)/PROPCOUNT(A)

The PROPCOUNTAVG formula is applied to event A, and event A is grouped by 'Group name (Playlists)’. So the PROPCOUNTAVG(A) in this chart returns the average number of playlists that a user joins.
 
The numerator shows total users (non-deduplicated) across provided playlists, which is equivalent to 'average number of playlists that a user joins' (PROPCOUNTAVG(A)) multiplied by 'total unique users' (UNIQUES(B)). 

As the denominator is the number of workspaces, this chart returns the average number of users across playlists.

HI @jmagg, Thanks a lot.

Not sure if that works. The problem is, that PROPCOUNTAVG(A) is always 1, given that every user is just within 1 company. So Average is also always 1.

 

Basically what I ended up dogin was rather simple.

A: User Created events (uniques, while totals would not differ) 

B: Any Active Event grouped by customer_name

Then I divide the two by 

UNIQUES(A) / PROPCOUNT(B)

So I get the number of Users Created per number of Customers.

Userlevel 5
Badge +6

Kudos @kornelius !! Nice job figuring this one out. Thank you for closing the loop and sharing 

Reply