Query to find top 30% spenders per store. | Community
Skip to main content
VarnikaJa2
New Participant
Jun 14, 2023 9:59 AM UTC
Solved

Query to find top 30% spenders per store.

I have a dataset with customers is and their max spending store code. I have another table with their transaction details. I need pull top 30% spenders from each store. How can I do this is ACS?

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
aggabhiAccepted solution
Level 5
Jun 15, 2023 8:46 AM UTC

Hi @varnikaja2 ,

This is link for ACS https://experienceleague.adobe.com/docs/campaign-standard/using/managing-processes-and-data/targeting-activities/segmentation.html?lang=en

 

Here for joining the tables, you can use enrichment and once you have all the columns in outbound population, use Segmentation Activity and use Ordered sampling:by limiting the 30 percent population by :-

By data grouping (as a %): you can limit the segment population according to the values of a specific inbound population field by using a percentage. Select the field to apply the grouping, then specify the values to be used.

 

Let me know if you are still unclear, I can guide more on same. Its just about understanding your data model cardinality and then grouping and limiting correctly using segmentation and enrichment activity

View reply

4 replies

aggabhi
Level 5
Jun 14, 2023 12:17 PM UTC

Hi @varnikaja2 ,

Firstly you need to take main table as customer profile table and get join through additional data field to transaction table. In transaction table, you need to pick all the fields which are related to customer spend.

Now I assume that Store code is foreign key in transaction table. So you simply need to use split activity and use record count limit by chosing top 30 percent for each Store code.

 

 

Ref :- https://experienceleague.adobe.com/docs/campaign-classic/using/automating-with-workflows/targeting-activities/split.html?lang=en

Regards

Abhishek

 

Translate
VarnikaJa2Author
New Participant
Jun 15, 2023 8:16 AM UTC

Thanks for your response @aggabhi 

I actually need a solution for Campaign Standard, the link you shared if for Classic. I tried but I have no such functionality available with ACS.

Translate
aggabhiAccepted solution
Level 5
Jun 15, 2023 8:46 AM UTC

Hi @varnikaja2 ,

This is link for ACS https://experienceleague.adobe.com/docs/campaign-standard/using/managing-processes-and-data/targeting-activities/segmentation.html?lang=en

 

Here for joining the tables, you can use enrichment and once you have all the columns in outbound population, use Segmentation Activity and use Ordered sampling:by limiting the 30 percent population by :-

By data grouping (as a %): you can limit the segment population according to the values of a specific inbound population field by using a percentage. Select the field to apply the grouping, then specify the values to be used.

 

Let me know if you are still unclear, I can guide more on same. Its just about understanding your data model cardinality and then grouping and limiting correctly using segmentation and enrichment activity

Translate
Terms & ConditionsAccessibility statement

Loading footer...