I'm trying to connect marketo email data (just email data) to my database housed on redshift. I am seeing marketo has the following API export fields: GetLead, GetMultipleLeads, GetLeadActivities, GetLeadChanges.
How would I go about getting email data? I know I can export it but I want the data by date. To give you a broader view of what I'm doing is making it so the links in my marketo emails which lead people to fill out a form on my site have a UTM parameter captured by a cookie on our site. That UTM parameter is then sent to salesforce on the campaign member object which will enable me to see form fills, then tasks, then opportunities coming from my email campaigns.
I want to be able to connect all this data in my instance of redshift like I currently do with adwords data and bing search data to optimize my campaign performance and house everything in one set of dashboards for company use.
Right now, after reading all the documentation, I do not see how I can get email data into my DWH and when I do an export on the email data it does not have a date range. To get around this I could in theory export email data every day and upload the csv into my DWH after adding a date column but that seems extremely unproductive. I was hoping there would be a way to just get the raw data.
You should be able to do this using the Get Lead Activities call and related calls. First, you get the IDs for the activity types:
Then you get the paging token (this is when you choose the time/date after which you want to pull activities):
Then you start pulling the activities, using the activity type ID(s) and paging token from the previous calls:
As shown in the example at the last link, it should include date-time values of the activities in the response.
Thanks Grant Booth ! Where would I put the connection call to the ETL tool I'm using (RJMetrics)?
Also where/how would I run these scripts? Kind of a noob here I know... It seems like I can filter pretty easily for JUST email sends?
I'm afraid I can't help much with that, since I'm not familiar with RJMetrics and I'm not a developer (I'm in Support and familiar with the APIs, but haven't done much live implementation myself). For that I recommend seeking the help of an experienced developer or consultant.
Brian, if your data warehouse does not integrate an HTTP REST client and/or scripting layer -- if it expects to act only as a database endpoint or perhaps pick up imports via SQL or text file -- you will need to use an integration server to connect to Marketo and either transfer to your DWH directly or save data in a format the DWH can import.
From what I can see, RJMetrics doesn't offer outbound connections for "pull" import other than their database connectors. That is, they will not start up client REST connections for data import direct from Marketo.
Rather, they expect you to either  push data into RJMetrics using their own REST Import API or  get your data into one of their supported databases, such as MySQL, so you can use use a pull connector.
If you use  you'll be pulling data from Marketo's API and then pushing data (likely after saving it to a temporary database or file and mapping it to the RJMetrics schema) into RJMetrics using their API. For this, you'll need some kind of integration server. Some people try to build a "poor man's" integration server by using cron jobs + scripts on a (hopefully dedicated) box. If you don't have complete command of the technology (or, frankly, want to have non-embarrassing infrastructure) I don't advise trying to roll your own. Rather, deploy a product that's built for this purpose, such as Microsoft SSIS, MuleSoft, Talend, etc.
Alternately, if you use  you'll be pulling data from Marketo's API as in  but only storing it in your db for RJMetrics to pull. You won't necessarily need to do any mapping before storing the data. But I still believe you should use an integration platform for this if it's a mission-critical, always-on job.