Hello Team, We are seeking help in creating segments/audiences to identify members who are having active subscription. We have subscription data stored as event where it has subscription start date (data type - datetime) and subscription end date (data type - datetime). One profile will have only one subscription event. Definition of active subscription: subscription start date should be before current timestamp and subscription end date is after current timestamp Sample Data:Profile 1 -Subscription Start Date: 2024-01-01 00:00:00Subscription End Date: 2024-12-31 23:59:59Profile 2 -Subscription Start Date: 2023-01-01 00:00:00Subscription End Date: 2025-12-31 23:59:59Profile 3 -Subscription Start Date: 2023-01-01 00:00:00Subscription End Date: 2050-12-31 23:59:59Profile 4 -Subscription Start Date: 2022-01-01 00:00:00Subscription End Date: 2999-12-31 23:59:59 With the current capability we have in audience builder, is there any way we can create an audience which fulfills the requirement: subscription start date should be before current timestamp and subscription end date is after current timestampWe are still exploring the various capabilities present in the Audience builder, but so far, we could not find any way to implement this. Any suggestions or pointers will be really helpful. Thanks a lot for your help.

Level 2

Solved

Create audience where profiles having an active subscription

Forum|Forum|1 year ago
January 15, 2025
4 replies
1658 views

Hello Team,

We are seeking help in creating segments/audiences to identify members who are having active subscription.

We have subscription data stored as event where it has subscription start date (data type - datetime) and subscription end date (data type - datetime). One profile will have only one subscription event.

Definition of active subscription: subscription start date should be before current timestamp and subscription end date is after current timestamp

Sample Data:

Profile 1 -

Subscription Start Date: 2024-01-01 00:00:00
Subscription End Date: 2024-12-31 23:59:59

Profile 2 -

Subscription Start Date: 2023-01-01 00:00:00
Subscription End Date: 2025-12-31 23:59:59

Profile 3 -

Subscription Start Date: 2023-01-01 00:00:00
Subscription End Date: 2050-12-31 23:59:59

Profile 4 -

Subscription Start Date: 2022-01-01 00:00:00
Subscription End Date: 2999-12-31 23:59:59

With the current capability we have in audience builder, is there any way we can create an audience which fulfills the requirement:

subscription start date should be before current timestamp and subscription end date is after current timestamp

We are still exploring the various capabilities present in the Audience builder, but so far, we could not find any way to implement this. Any suggestions or pointers will be really helpful.

Thanks a lot for your help.

Real-Time Customer Data Platform

Best answer by BitunSenAEP

As Audience Builder UI is not supporting the feature I was looking for to implement the above mentioned requirement, I have decided to create the segment using PQL.

I have used the below expression:

"expression": {

"type": "PQL",

"format": "pql/text",

"value": "select _subscription from _XXX.subscription where (_subscription.subscriptionStartDate occurs before now) and (_subscription.subscriptionEndDate occurs after now)"

}

This worked like a charm. I have validated the evaluation and verified the data of the qualified profiles. All good.

I will go ahead with this solution for now, until we see this capability in Audience Builder UI.

Thanks a lot Team for all your support and time. Happy learning!

DavidRoss91

Community Advisor

Hi @bitunsenaep
It may be easiest to use Data Distiller and SQL to build this type of audience in AEP. I have provided some documentation down below:
Build Audiences using SQL | Adobe Experience Platform

B

BitunSenAEPAuthor

Level 2

Hi @davidross91,

For last 3/4 years, I have been handling this type of requests/requirements

using data distiller Query Service Only

OR

have an additional field "status" and requesting data source to derive and set the value or adding computed field mapping in the dataflow during ingestion (as suggested by @pratheeparunraj )

All these options create an additional complexity. As users of AEP is mainly expected to be marketers, I think for this type of simple use cases, reaching out to IT team pushes the capability AEP in bad place - I have seen people raising eyebrows.

There are so many features getting added in AEP every month. Can't we expect an enhancement in AEP Audience Builder UI to be able to do this using Audience Builder UI only? This will make it more powerful to position.

What do you think?

DavidRoss91

Community Advisor

@bitunsenaep I do agree with you on this. It becomes very complex this way. I would recommend adding an enhancement request in the "idea's" section of the community stating this request. I have seen success in doing this.

PratheepArunRaj

Community Advisor and Adobe Champion

Dear @bitunsenaep,

If I understand correctly, you do have a field capturing the start date and the end date in the data type datetime.

If that is the case, can't we create a segment using Before and After using rolling dates? Or can we use compare operands to compare the start date and end date? Or am I completely missing your tricky question?

But the best option is to capture or add a variable to understand whether the subscription is active/inactive. Even if you are not capturing it currently, you can use the Query Service to update existing profiles while adjusting the implementation.

Thank You, Pratheep Arun Raj B (Arun) | NextRow Digital | Terryn Winter Analytics

Thank You, Pratheep Arun Raj B (Arun) | Xerago | Terryn Winter Analytics

DavidRoss91

Community Advisor

@pratheeparunraj I thought this as well, but I don't think that you would be able to use before/after conditions in this instance since we can't capture a rolling "current timestamp" in the before or after condition within the UI? Please correct me if i'm wrong here.

PratheepArunRaj

Community Advisor and Adobe Champion

Hello @davidross91 ,

I didn't try creating one, so what you say is that rolling timestamp is not applicable for before/after conditions? It is such a basic condition to include, right? And Segment Builder didn't have this? Wondering buddy.

Thank You, Pratheep Arun Raj B (Arun) | NextRow Digital | Terryn Winter Analytics

PS: Let me try this out today and keep you posted if I have any solution.

Thank You, Pratheep Arun Raj B (Arun) | Xerago | Terryn Winter Analytics

kautuk_sahni

Community Manager

@bitunsenaep Did you find the suggestions helpful? Please let us know if you require more information. Otherwise, please mark the answer as correct for posterity. If you've discovered a solution yourself, we would appreciate it if you could share it with the community. Thank you!

Kautuk Sahni

B

BitunSenAEPAuthorAccepted solution

Level 2

As Audience Builder UI is not supporting the feature I was looking for to implement the above mentioned requirement, I have decided to create the segment using PQL.

I have used the below expression:

"expression": {

"type": "PQL",

"format": "pql/text",

"value": "select _subscription from _XXX.subscription where (_subscription.subscriptionStartDate occurs before now) and (_subscription.subscriptionEndDate occurs after now)"

}

This worked like a charm. I have validated the evaluation and verified the data of the qualified profiles. All good.

I will go ahead with this solution for now, until we see this capability in Audience Builder UI.

Thanks a lot Team for all your support and time. Happy learning!

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded