How to create a computed attribute that counts the total of email opens in the last month?

[FilipeFreitas]

Dear Community,

I am a RTCDP and AJO user who is trying to create a computed attribute that counts the amount of emails a user has opened in the previous month. I am struggling to create it and I am writing to see whether anybody can help me solving the issue. The purpose is to a have this field at profile level, even knowing that we can count the amount of emails opened when creating audiences.

How was it created?

It was created by applying the following settings: 

-select interactionType event

-equals "Message opened"

-Count

-1 Month

-Fast refresh is checked

The definition looks like this:

It generates the following expression:

xEvent[_experience.customerJourneyManagement.messageInteraction.interactionType in ["open"]].count()

After creating it, I noticed the info "Summary data is not available for this field due to lack of clustered values" in the InteractionType field. Is this somehow related to the Summaries job? I admit I didn't understand it.

Value distribution is empty, last evaluated on 03/23/2025, 10:00 PM:

This is the link I've followed to create the attribute:

https://experienceleague.adobe.com/en/docs/experience-platform/profile/computed-attributes/ui#view-d...

Other links I navigated to:

https://experienceleague.adobe.com/en/docs/journey-optimizer/using/audiences-profiles-identities/pro...

https://experienceleague.adobe.com/en/docs/experience-cloud-kcs/kbarticles/ka-25851

My apologies for the long post. I am new to computed attributes and I did my best to share here what I've done so far.

Any help here would be highly apprecitaed.

Best Regards,

Filipe C. Freitas

[supratim320]

Summary data should not be a problem. I can see the same message, however, when I try with query service, I can see data in "interactionType" field in OOB cjm_email_tracking_experience_event_dataset.

Can you double check the AJO schema field in your env:

SELECT count(_experience.customerJourneyManagement.messageInteraction.interactionType) as "Event_Count", _experience.customerJourneyManagement.messageInteraction.interactionType as "Interaction"
FROM cjm_email_tracking_experience_event_dataset
group by _experience.customerJourneyManagement.messageInteraction.interactionType

[FilipeFreitas]

Hi supratim320,

thank you for reply. The query you provided runs perfecly if I change the FROM clause to ajo_email_tracking_experience_event_dataset. After ruunning it, the statuses unsubscribe, spam_complaint, click, and open and their respectives count are displayed accordingly. Please note that we currently do not have any cjm datasets, I assume this might be due to the product stack we use. Even so, I must thank you for your kind help.

Let me share something here: I ended up contacting customer support with the same question I posted here, and this was their answer:

" I've reviewed the details shared and can confirm that you are not doing anything wrong. After further investigation, we would like to inform you that Computed Attributes currently do not support System generated datasets and is why you are seeing a count of zero on all profiles.

Our Engineering teams are aware of this and they are actively reviewing how to best support this. There is currently no ETA on when Computed Attributes will support system datasets."

At this point, I understand there is nothing much we can do. 

Thank you very much indeed for your help with this case. Highly appricated!

Best,

Filipe

[supratim320 · Accepted solution]

Thank you for that information, I was unaware of it. However, your issue still persists. I have another approach that might work (obviously not a vanilla computed attribute, but a computed attribute anyway):

You can set up a scheduled query to run the same sql every day (in a batch manner) and take the incremental data, aggregate it to calculate required counts, populate/insert into a new schema/dataset (profile enabled).

Hope this helps!

[FilipeFreitas]

This is definetely the best possible approach. Thank you very much indeed, supratim320!