How can I get conversion stats via SQL in Marketing Cloud?

Question

I can get this data using Extract Data in Automation: https://help.salesforce.com/articleView?id=mc_as_create_a_data_extract_file.htm&type=0

but I was wondering, If I can get clicks via SQL with dataviews, how can I get conversions via SQL? Is there any dataview for conversions? If there is none, is there anyway to get this information into a data extension and extract it via SQL?

Thanks

Answer 1

If you're tracking conversions using the Collect Implementation that shows up in Web & Mobile Analytics and have implemented a Catalog in Email/Web Recs, you can use the IGO_PURCHASES joined to the IGO_PROFILES Data Extensions. There's the JobID, BatchID etc you need to join to your _Job Data View in IGO_PURCHASES, providing you're using Conversion Tracking Links in your emails or are tagging all links with the required parameters using Parameter Manager in Setup. It's as easy(?) as that.

If you're using the classic conversions tracking that shows up on the tracking tab in Email Studio <rant>I don't know why Collect's not incorporated this as it's easy to fork Collect, but should be implemented for you</rant>, the Tracking Extract is the way to go. Using Automation Studio, this is an outline of the process:

1. Create a Data Extension with the same fields as the Conversions Tracking Extract file
2. Create a new Automation Studio program with the following steps
3. Step 1: Data Extract Activity of type "Tracking Extract". Elect that you need Conversions to be included in the output. The output file's a .zip file when you're choosing the file name.
4. Step 2: File Transfer Activity to move the extracted .zip to the Enhanced FTP (Import Directory).
5. Step 3: File Transfer Activity to "Manage File". Unzip the file into its current directory.
6. Step 4: Import Activity to import conversions.csv into your Data Extension
7. Run/Schedule your Automation Studio program

Answer 2

Conversions is not available as a standard data view. You can follow the steps that Macca mentioned above. If you want this to be readily available as other data views you can ask your account manager to see if they can create one for you(maybe paid).