Creating Ranking Leaderboard of Transaction

[Matzumoto]

There are many Campaigns (1 Campaign: 1 Leaderboard), each of them can have multiple Transaction from a CUSTOMERs.

Is there a way to show the top Total Spent from each CUSTOMERs in each CAMPAIGN?

Is this a list of Total Spent, filtered somehow? Or should this be a list of CUSTOMERs?

Say that I multiple CAMPAIGNs (named a, b and c)
Each CAMPAIGN can have multiple CUSTOMERs (named customer1, customer2, customer3)
Each customer can have multiple Transaction for each campaign that we need to sum (named transaction1, transaction2, transaction3)

How to structure a list such that you have for each CAMPAIGN, the top Total Spent per CUSTOMER?

I have already tried to do it but have problem with Sorting Total Spent, and Double Spending of Customer in a campaign, and running ranking #1, #2, … #help:api

Thanks,
Matzumoto

[Yongki]

Hi @Matzumoto ,

This is possible using parent-child (1-to-many) relationship.

An example is like this,

Campaign is higher level
Customer is middle level
Transaction is lower level, this is where price located

If you need more detail, you can have
lowest level for Detail of Transaction, which can have its own price
highest level for Department, which can accumulate total from Campaign

I have unfinished tutorial regarding this,

Collections cascading and copy

Hi All, This is a series of tutorial about relational collections that are cascaded, meaning it is maintained in 1-to-many relationship. There are 5 levels of collection, from top to bottom: highest such as projects higher such as activities inside project middle such as purchase orders inside activity lower such as order items inside purchase order lowest such as accessories inside order item But, we will begin with lower first and later, we will use lowest, then going to middle, higher …

each level need to be connected to the level above it, using 1-to-many.

I suggest you simulate this in spreadsheet first, and think about leveling.

[Matzumoto]

I did the 1-manny at

1. Campaign → Users
2. User → Transactions

Show there are show in the leaderboard but “cannot sort with top total spent” of each campaign because the collection of Campaign List cannot sort by total spent because total spent collect in Transaction Collection.

[Yongki]

It is better, if you give screenshots, both database setup, screens, especially the filter in list.

This need to be examined carefully.

[Matzumoto]

I got a problem with double show up of customer who spent twice time (Customer1 spent THB100 and THB300 so total are THB400)

[Yongki]

Hi @Matzumoto ,

Got it,

@Matzumoto

double show up of customer who spent twice time

Do you want to display double or just once ?

If you want to display double, you don’t need sum, just amount of spend.

If you want to display single, you need to choose users for the list and filter using current campaign > users and then choose sum

All the relationships are correct.

[Matzumoto]

If I use USERs for the list, I cannot sort the top of total amount of customer spend. That’s the problem.

[Yongki]

Then you need to have additional collection, campaign users, which store total amount of transaction per user.

[Matzumoto]

YES, I created the Leaderboard Collection that collect : Users, Campaign and Total amount of spend.

I can sort the top by using total amount of spend but I cannot SUM amount from Transaction collection to Leaderboard Collection real-time.

If I can sum total spend from transaction collect to leaderboard transaction, I can sorting and do the ranking in leaderboard perfectly.

Do you have any solution about it?

Thanks,
Matzumoto

[Yongki]

@Matzumoto

Do you have any solution about it?

You need additional collection that are combination of campaign and users.

All your users transaction will be totaled there.

Then you can have sorting options.

[Matzumoto]

Follow your solution that no sorting by total spend from users transaction show up in the list if the new collection don’t have NUMBER column.

[Matzumoto]

[Yongki]

Have a total property in leaderboards collection, and update them when they make transaction.

I think you don’t need transactions relationship property.

Because you already have campaign and users, so you know where to put those transactions.

[Matzumoto]

It’s go back to the first problem that double show up user name again

[Yongki]

When transactions is made, do update, don’t create.

[Matzumoto]

How to fix and fill this one?

[Yongki]

Add another action to update leaderboards collection.

[Matzumoto]

now Transaction and Leaderboard Collection are the same so I delete Leaderboard Collection and use Transaction Collect.

Then I add Update action into transaction collect but I cannot find how to update only not create.

Follow by this one

[Yongki]

@Matzumoto

now Transaction and Leaderboard Collection are the same

They are not the same.

Transactions is for details, that contain each amount to spend.

While Leaderboards is for totals, that contain sum of its transactions, which is accumulated of amount to spend, by making this its own property, rather than sum, you can do sorting.

[Matzumoto]

OK I got it. Create new transaction at transaction collection. then update to leaderboard collection at the Total Spent Amount property.

The question is how to update to Total Spent Amount at Leaderboard Collection when the new transaction created?