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.
Best Answer
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.
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