2

Any reason why we cannot access data more than 6 months in data views (in marketing cloud)?

why we do not have access to our own data (all time data)?

4

What I did was to copy exactly the system data views and make my own data extensions using the exact schema. I then run a query every month on the first that grabs the entire last month's worth of data for each view and stores it. If you start doing this today, in 6 months you'll have a year's worth of engagement data. (Grab 6 months back today, then add a month every month for six months = one year)

As for which fields should be primary key, I don't have any, because I literally just want to scoop all the data up and store it. A primary key can cause some rows to be merged if they share common values. Follow the documentation

https://help.salesforce.com/articleView?id=mc_as_data_view_open.htm&type=5

for the field schema and be sure to leave some fields as nullable as the docs suggest.

Here is a snapshot of my open data view extensionenter image description here

One thing to think about is how much data can be stored. As far as my conversations with support, there is no limit, but large data extensions will take longer to query.

Creating the tables is the most time consuming part, the queries are easy.

SELECT * FROM _Open
WHERE EventDate >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0)
AND EventDate < DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)

Then just drop all of those in an automation. Use APPEND for the query because you want to keep adding rows every month.

3
  • Thank you so much @garek007 for sharing your approach. So, in each scoop you collect 2 months of data (present month + previous month) so have 1 years of worth data in 6 months? In my case, I need to access data older than 6 months so i need to pull 6 months of data in one scoop and then start appending data daily to accumulate 6months+ data. What should be the best approach? My concern is that pulling 6months data in one scoop can cause process / time out issues. Your thoughts?
    – kuml
    Apr 25 '18 at 19:28
  • On a side note, would you be able to help me with this too? salesforce.stackexchange.com/questions/216015/…
    – kuml
    Apr 25 '18 at 19:28
  • No I pull one month. So on Feb 1 I pull January, on March 1, I pull February, etc. This is helping future me, not present day me. If you want to get data older than six months you'll have to use @Adam Spriggs approach of exporting Data Extracts and importing them back into Marketing Cloud.
    – garek007
    Apr 25 '18 at 23:27
2

I have seen the SQL code for the data views, and it is hard coded to a 6 month data view. With the amount of data in these table that the views reference (and the number of joins, and it's a lot) it is a necessary evil for any kind of satisfactory performance.

1
  • The only reason I need data more than 6 months is because I need to find out audience who did NOT open our emails in last 6 months or more so I am assuming in order to get that audience I have to have data more than 6 months stored locally.
    – kuml
    Apr 25 '18 at 11:51
1

That is not an error but rather a restriction of marketing cloud. Your own data is accessible all the time as this is the data base holding potential customer data.

I can't find this in the new documentation right now but I remember that it had something to do with performance of operations that use data views.

Retrieve up to six months' data from any table.

(Source: Marketing Cloud Documentation - SQL Query Activity)

1

You do have access to all of your data via Tracking Extract Activity. It's just not readily available in its entirety with the System Data Views.

You can extract this data 30 days at a time via Activity or you can specify any range you like if you kick off an Data Extract via SOAP API.

8
  • But if I need to use queries to relate audience in DEs and data views, I am limited to 6 months of data view only.
    – kuml
    Apr 24 '18 at 15:38
  • Correct. However you can extract and import Tracking Data -- effectively creating your own "views" of tracking data. That's how we generally work around the 6 month limitation. Apr 24 '18 at 15:45
  • Thank Adam. Just confirming if I understood this correctly. Instead of using querying to extract data from data views and appending in DEs, you have configured an automation that automatically pulls tracking info and file transfer on rolling date basis and then append data in DEs to create a local copy of all tracking data?
    – kuml
    Apr 24 '18 at 15:57
  • And what format do you use to export file? I am trying .csv and it looks like every time I try to open it, it says corrupted file. If I download zip then import needs manual extraction.
    – kuml
    Apr 24 '18 at 16:24
  • Tracking extracts are Zip files, so you'll have to use a Transfer activity to unzip and then a series of imports to import each file. Apr 24 '18 at 17:46
0

It's possible to (as users have suggested above) code your own queries that leverage data views to pull out larger volumes of historical data. When you do this, however, you start to see why data views contain only 6 months of data - there's a huge performance hit if your account is reasonably active. I had to optimise queries twice for a client who had a requirement to access 2 years of data in their own tracking DE, it was really hard to get queries to complete in less than 15 minutes on this volume of data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.