0

I created a custom report to include multiple fields in my report such as campaign name, emails included in those campaigns, measures such as open rate, clicks, etc and most importantly information about the links that are there in my emails such as how many clicks are there on each link, etc.

I was able to include most of the fields but I did not get an option to include information on individual links(email-wise). For achieving this, I used 'Blank Template for Email Sending Performance' in Discover Report type.

All I need to do is create a consolidated report where I have data about my active campaigns(the columns that I mentioned above). Is there any other way to achieve this like using Data Extension to capture the information and then aggregating it in some way?

Actual Scenario- I have 5 campaigns in a BU. Each campaign consists of 2-3 different emails. I need to create a report which contains the information of all the campaigns such as Campaign name, Email name tagged to it, Opens, Clicks, bounces, individual link information, etc.

Things I have already tried so far:

1) Created a custom report in which I was able to get all the fields but wasn't able to capture the links in each email and their click rates.

2) Tried using Data Views but issue is that I could not find any data view for campaigns and others won't help my case.

3) Created a DE with all the fields that are in the report and imported the report in DE but my main aim was to import two separate reports each containing different info, one about campaigns and the other about email links but issue is I could not map those links against the emails in my DE.

Improve this question
5
  • You can use SQL to get this information and aggregate into a Data Extension. – garek007 Dec 26 '18 at 20:41
  • Thank you for your answer but can you be a bit precise? Where do I use SQL to get the information? I have a lot of fields that I need info on such as all the campaigns present in a Business Unit, the emails tagged to these campaigns and then the links present in the emails. How do I segregate all this info and put in a DE where each link is tagged to its email and each email is tagged to its campaign? – Krati Garg Dec 27 '18 at 5:59
  • 2
    You would use the SQL inside of a Query Activity inside Automation Studio. Also the segmentation and aggregation you are asking for is a custom solution with a fairly high level of effort involved - you will need to provide a good faith attempt, at the least, in order to get detailed responses. Check out Query(help.salesforce.com/…) and DataViews(help.salesforce.com/…) to begin your good faith attempt. – Gortonington Dec 27 '18 at 12:23
  • Post all the fields and information you're trying to get, and I'll write something up for you to help you get started. – garek007 Dec 27 '18 at 15:04
  • I have updated my question with more details. Let me know how can I go about it. Thanks in advance @garek007 – Krati Garg Dec 28 '18 at 11:46
2

Ok not sure if this is going to help, but may be your only route. I have briefly used Discover and did not like it. It seemed way more complicated to use than it should be, and slow. If you have Automation Studio go there and write some query activities to do this. Set up a data extension for you to write to. You may need several data extensions as this can get very complex, fast. It's easier to break the queries up into smaller pieces rather than write one very complex query.

enter image description here enter image description here enter image description here enter image description here

See the screenshots above. A Query can either be an Overwrite, an Update, or an Append. You usually start with an Overwrite as your first query. You might start with a very simple query that does one thing, then subsequent queries will use an Update and fill in the data. To make it really easy on yourself, each query can grab one piece of data. In order to do this, the Data Extension will need to have a Primary Key. Usually that's going to be a JobID.

Here is a set of queries I did for Content Reporting. It's not exactly what you're doing, but if it should help. The instructions are here

https://github.com/garek007/Marketing-Cloud/blob/master/README.md

The queries are here: https://github.com/garek007/Marketing-Cloud/tree/master/content-reporting/sql

Improve this answer

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.