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)?
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It only takes a minute to sign up.Sign up to join this community
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
for the field schema and be sure to leave some fields as nullable as the docs suggest.
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.
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)
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.