How can I show all contact activity within a segment?
I'm trying to create a report to show the email send activity for any contact within a specific segment. The Email Analysis by Segment shows any activity that happened against that particular segement. However, the contacts within the specificed segment may be in several segments in the system, and I want to see everything that they did. It seems like I should be able to write some logic statement, but I'm just not that familiar with the syntax. I'm thinking it would read something like "If a contact ID/email is in Prompted Segment, then return all contact activity." If anyone has any ideas, would love to get your input!
Thanks so much!
The tables within ELQ activities are actually split across multiple tables - it's not just one activity table. Each separate table (with corresponding activity type) contain quite a number of different columns, identifiers, and etc that would require the data to be transformed and united into one workable table that you can then use to join with contact data.
It's definitely something that would take significant steps in OBIEE, and might not even get you the output / join with other data that you need. It was a lot easier to jump into Tableau and get that data out. (Otherwise, you could also use BULK API to export all contact activity - but that also takes a while depending on your DB size).
Otherwise, the next best option would be to go into OBIEE, create a "new" analysis report with the first column as "email address", then go into each subject area available and get in the appropriate metric, then edit the formulas of each column with appropriate filters (to ensure each column is using the appropriate activity table's date range). However, this would only get you started with the aggregate activities - more steps would be needed to make it drillable.
In any case, would you be able to attach a screenshot of a "sample" report that you'd be trying to build? Even just headers would work, but at this point, still seems best to use an external data platform since you'll have transparency on the ERD and would not get erroneous data values.
Hong Tai Lee - [email protected]