I have offer statistics for list of offer received from multiple partner with CPC value. Here, I am interested to shows the Summary table as follows.
Request Id - It is common offer id shared by each perks.
Offer Count - Total offer received per perk
Publisher - it’s name of publisher from which we received this offer.
Max CPC - It’s maximum CPC among all the received offers.
Min CPC - it’s minimum CPC among all the received offers.
Avg CPC - it’s average CPC among all the received offers.
Offer 1 Partner - Highest CPC partner (Ideally Max CPC Partner Name)
Offer 1 CPC - Highest CPC value
Offer 2 Partner - Second highest CPC Partner
Offer 2 CPC - Second highest CPC value
Offer 3 Partner - Third highest CPC Partner
Offer 3 CPC - Third highest CPC value
Here in database table data store as following columns
id, publisher_name, partner_name, cpc, request Id
I can get the information about Offer 1 Partner using firstValue function.
How can I get the second and third highest cpc and their partner name ?
Hello @margil, welcome to the QuickSight community!
Based on your explanation, I believe the best way to retrieve the top 3 CPC values would be to utilize the denseRank calculation.
This would allow you to rank the values, then to return the CPC value and partner name you could use an ifelse statement. It would look something like this:
Hello @margil, how are you building out the denseRank function? Maybe we can alter that calculation to figure this out. Also, what would be the expected output? Could a single request ID be linked to more than 1 of the top 3 values?
The best way for me to assist here would be if you could create a demo version of this analysis in QuickSight Arena, publish the dashboard, and share the link over with me. That would allow me to test some possible solutions and send them over to you in QuickSight to view.
I can explain the limitation. Basically, by including the Offer 1, 2, or 3 Partner names in the table as well as the Offer Rank, you are going to create multiple rows for each tracking_id field. If we are referencing a value like Offer 1 CPC, we can utilize minOver partitioned by tracking_id to ensure it will appear on the same row. Once multiple partitions are added for the tracking_id, it will populate multiple rows. The Partner name will need to be excluded if you do not want multiple rows because I can’t use minOver to bring it in on a single row.
Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!
I can understand that scenario very well, Offer Rank is just for understanding purpose. I don’t want that in table. Interested to have single row per tracking_id their count publisher name and Offer 1 to 3 Partner name along with their CPC value.
I can fetch the data using following SQL but that creates load on database that’s why I am interested to render the data using QuickSight.
SQL:
SELECT tracking_id, publisher_name,
first_value(cpc) OVER (PARTITION BY tracking_id, publisher_name ORDER BY cpc DESC) AS offer_1_cpc,
first_value(partner_name) OVER (PARTITION BY tracking_id, publisher_name ORDER BY cpc DESC) AS offer_1_partner
…
FROM table
So, Is there any solution for above issue in QuickSight ?
Hello @margil, what database are you using as your datasource connection in QuickSight? Setting up a seperate dataset that would have some of these aggregations within the SQL shouldn’t be a huge constraint on your database. How many rows of data are you ingesting?
Alternatively, there is the firstValue calculation that you can use in QuickSight:
From what I can tell, this should function the same way as the SQL statement you wrote above.