Export aggregated data from AEP to S3 bucket

[yumera]

Hello,

We are looking at an option to export data from AEP to S3 bucket but in a bit unusual way.

The high level process is:

1. Using a query service data from a large Event dataset has been aggregated into a small table.

2. Using again query service data from point 1 table has been extracted and send for detail reporting.

3. Due to the query service expiring credentials this cannot be automated. And we are looking at the option of optimizing this.

One of our options we look at is to create a lookup schema+dataset with a identity non customer profile. But lookup dataset is not allowed to be used inside AEP and destinations

We tried with creating a event schema with only 1 profile and add many events on a single profile but then destination do not allow to export the data as array and would like to export the data as single line since there is only 1 profile on the dataset.

We cannot use "Individual Profile" as we cannot create each profile per the aggregated data, since it can be 1000 line of aggregation. 

Has anyone got any idea how can we achieve that?

[arpan-garg]

Hi @yumera  - What do you mean by query service expiring credentials? Are you using a third party to connect to it?

[yumera]

Hi arpan-garg,

By query service i mean following:

psql "sslmode=require
  host=XXX
  port=80
  dbname=XXX
...

 The aggregated data is not PII if it's what your concern for third party connection.

[arpan-garg]

Hi @yumera - You can use no-expiring credentials, in case you are not able to automate it because of expiring credentials. More information can be found here https://experienceleague.adobe.com/docs/experience-platform/query/ui/credentials.html?lang=en

[yumera]

Hi arpan-garg,

I forgot to mention that is also unfortunately not an option. We are not allowed to developer console on the sandbox we are working. 

The only way we see this is using export using segments from destinations. But not possiblity of  export on non profile identities is stopping us. 

I was hopping that the compute exporting formulas to be able to at least extract some events as well. But that do not support data transpose into many lines.

Example transpose array into lines.

[arpan-garg]

Hi @yumera  - There are ways of exporting the entire aggregated profile. You can use profile export jobs or api to fetch the whole aggregated profile but please keep in mind there is a very limited number of calls you can make in a year. For example for B2C 5 calls per year per profile and for B2B 8 calls per year per profile.

[yumera]

Hi arpan-garg,

Thank you for the suggestion. But we are looking for automation of the this export on weekly base. 

At the end we start looking at an the option of exporting the complete dataset to the S3 which is not ideal as well since it's no exactly JSON formatted file and exported into a brand new folder every time. 

[NimashaJain]

@yumera Did you find the suggested solutions helpful? It would be great if you can  mark the answer as correct for posterity. If you have found out solution yourself, share it with wider audience in the community.

[yumera · Accepted solution]

Thank's all for suggestions. Unfortunately there were not suitable for our our solution. 

At the end we end up creating a query script which will populate a lookup dataset which could be exported on a daily based using dataset export activity.