Hi Champions,
I'm stumped with a request and hoped some of you smart folks might have run into something like this before.
I work for a University that partners with 3rd party agents that sell leads for our online programs. We are supposed to reconcile the leads we accept from those vendor partners, versus those we do not, on a weekly basis. It's challenging to output a single list or spreadsheet and reconcile with vendors, because they sometimes send us multiple copies of the same lead for the same or different programs. Since Marketo's "single person" data architecture simply overwrites the previous data with the new, whenever we export a list (of campaign members, for instance) the person only ever shows once with their most recent inquiry information, even if they were submitted by the vendor multiple times.
The marketing manager in charge of this effort wants to be able to generate a daily list/spreadsheet of all inquiries (not person records, but individual inquiries) that looks something like this:
ABC Leads Reconciliation Report
Inquiry Date | Email Address | Marketo Person ID | Accepted Flag | Rejected Reason | Education Program
4/24/2019 | matthew.gomez@laureate.net | 3456789 | FALSE | Highest Level of Education does not match program requirements | MS Education
4/25/2019 | matthew.gomez@laureate.net | 3456789 | TRUE | Null | BS Education
In this simple example, the vendor inquired twice for me. On the 24th, "ABC Leads" submitted a request for a Masters in Education, but I don't have a bachelors so my lead was rejected. The same vendor submitted me again the next day for a bachelors program, and I was accepted. Even though its the same person, it's a different inquiry.
Some ideas we were tossing around:
- Static list - will not work because no matter how many times you add a person, they will only ever appear on the list once with most recent data
- Program member statuses - This has more possibilities, but you can still only have 1 person aligned with any single Marketo program. So I would need to create additional Marketo programs to capture subsequent inquiries; at reconciliation, we would need to export and merge those reports, which would get messy.
- Custom fields/object - There is the idea to create fields called Inquiry 1, Inquiry 2, Inquiry 3, etc, and Outcome 1, Outcome 2, Outcome 3, etc, and store the date/times and outcomes to these fields as the leads come in. Still not sure how to ensure all of these inquiries could be made to appear on separate lines on a spreadsheet though. Then we could clear the fields at the end of each month and allow the process to start over. It's still a messy build.
My hope is that some of you know of an app or something that would allow us to write to an external table to store these point in time inquiries as they happen, and then pull results from that table as needed. Would like to avoid bringing BI in for this, because it slows things down and often over-complicates things.
Any thoughts from my fellow Champs and alumni?
Matt Gomez
Walden/Laureate
matthew.gomez@laureate.net
.png)