Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Identifying Top % of users and Top % of Spenting

Greetings

Need your help with the following. Please refer to the attached data.

USER_ID   MODEL      YYYYMM     AVG_TOTAL_SPENT    AVG_ONLINE_SPENT   AVG_OFFLINE_SPENT  AVG_REWARDS

AVG_TOTAL_SPENT is monthly average which is derived using some internal algorithm which averages based on past month.

So AVG_TOTAL_SPENT may not be the sum of AVG_OFFLINE and AVG_ONLINE.

The data is on Monthly Basis. ie YYYYMM. It is 5 months of revenue for a small firm. Revenue grows over period of 5 months. so all users not necessarily spending every month but majority does revisit regularly.

I want to find the following

Phase 1(Across 5 months)

1. Identify VIP Customers: Top 20% of Users and their total avg_total_spenting in %

    Example: Top 20% of users (1000 users) contribute to 70% of revenue ( 3.2 Million)

2. Identify revenue: % users and their contribution in revenue

    Example: Top 70% of users(14000 users) contribute to 90% of revenue (4.0 million)

Phase II(Monthly basis)

3. How do I distribute the above on monthly basis?

I am using some complicated method to find as below. Then I select this users manually and find total revenue and % of users.WHich is not a right way.

UsersDistribution.PNG.png

SpentingDistribution.PNG.png

Thank you for your expert advise.

3 Replies
Colin-Albert

Have a look at the dimension limits tab. This can show you the Top N % based on the first expression.

Not applicable
Author

I have updated my original postings with charts. Is their any simplified method like RANK function, FIRSTSORTEDVALUE function?