Segment in CJA for % Calculation

[user79428]

Hi

I'm trying to create a segment in CJA to calculate the percentage of money spent in a category / total money spent

We need to export this as an audience to AEP, so it needs to be in a segment and not a calculated metric. A calculated metric looks like this: 

(Department == Snacks (segment) (Net Total Spend)) / (Net Total Spend)

The logic behind what we are trying to solve is "Retrieve the list of users who's top department spend is Snacks."

- Every product has a department

- We collect net sales amount with purchase, department, and person.

I have tried to use segments, functions in the calculated metrics, segments that are person based, derived fields and almost all the functions there, adding data view fields.

I'm SURE someone has done this before. 

Any help is really appreciated! Please help, and thank you. 

[Asheesh_Pandey · Accepted solution]

@user79428 I'd solve this by either using Query Service or Data Distiller

Using Query Service - 

1. Aggregate net spend per department per user
2. Identify the top department per user and filter to users where top department = ‘Snacks’

3. Output to a profile-enabled dataset

4. Build a segment in AEP from this enriched dataset

Using Data Distiller Audiences  - Use SQL in Data Distiller to create the audiences by writing queries. Use created audience in AEP. It's much faster than doing it in CJA with limitation.

[gautham_madala]

To identify users whose top department spend is "Snacks" in CJA and export this audience to Adobe Experience Platform (AEP), you can create a person-level segment that captures users with the highest net spend in the "Snacks" department. While calculated metrics like the percentage of spend in "Snacks" over total spend are useful for analysis, they cannot be directly used in audience definitions for export. Instead, define a segment where the "Department" equals "Snacks" and aggregate the "Net Total Spend" at the person level. Then, identify users whose spend in "Snacks" surpasses their spend in other departments. Once this segment is defined, you can publish it as an audience to AEP for activation across various channels

[kautuk_sahni]

@user79428 Just checking in — were you able to resolve your issue?
We’d love to hear how things worked out. If the suggestions above helped, marking a response as correct can guide others with similar questions. And if you found another solution, feel free to share it — your insights could really benefit the community. Thanks again for being part of the conversation!