SUbscription name, date, action with profile id in 1 single row

[Parth1]

Hello Everyone,

I have a use case

Profile ID from subscription table

Subscription ID from Subscription table

Action from Subscription table

Latest Date from Subscription table

Profile ID  Service #    Action             Date

1              Service 1     Unsubscribe  10/10/2022

1              Service 2     Subscribe       10/10/2022

i would like to fetch both the records in 1 single row similar to below

Profile ID  Service #    Action             Date

1              Service 1     Unsubscribe  10/10/2022  Service 2 Subscribe  10/10/2022

Method 1 : I am trying to fetch subscription history all service ID and their action with latest date, but in query activity, i am not able add Max(Date) which acts as group by clause.

Method 2: i tried to fetch all records with service ID and action, after i tried using Deduplication activity based on Date but it gives me only 1 record.

 Method 3: tried 2 query activity, one fetches profile ID and modified date, 2nd activity fetches only subscription name, action and then tried to enrich profile attributes, which works but i am not able to select only 1 records which is expected.

Any feedback is appreciated?

[Anasso1337]

Hi @Parth1 ,

Your request is not totally clear to me.

Are you trying to get the following : For each profile, and each service, the latest subscription history record? (even if the profile is not subscribed to the service anymore?)

Then you want the result aggregated into one line?

How many services you have to query in total?
Finally what's your final use for this? Do you want the data exported as a file?

[Parth1]

 For each profile, and each service, the latest subscription history record? (even if the profile is not subscribed to the service anymore?) : yes correct

I have somewhere around 10 service.

There are multiple profiles for same Profile ID(Primary profiles or Secondary Profiles), i need to copy paste the latest subscription to all same profiles ID?

[Anasso1337]

Hi,

This is my understanding :

For your 10 services you need the latest subscription history record for each profile.
Then, since some profiles share the same ID, you want to keep only one sub histo record per service and profile ID, the one with the latest date.

This is how to do it :

• Query Subscription History table : Add conditions on your 10 services
• Deduplication : Dedup on Service ID and Profile ID, and in dedup method, keep the latest sub histo record Date (can be of action sub or unsub btw).

 I'm not sure what you mean by copy paste here, but you can add an update activity if you have a dedicated table to hold this information. I also don't understand why you need to aggregate your records.

Please let me know if this works for you.

[Parth1 · Accepted solution]

Partially i had same understanding but with that the problem was we never used to get latest value for both subscription and unsubscription as user might have multiple entry in subscription history.

I have tried similar approach but using profile and service as ACS data model is based on 1 to N with profile and subscription and services. so i am able to fetch the same things with latest value in it.

Thanks for your suggestions.