TRG and NCOA formats

Hi Sadie,

Are you using the built-in Tessitura NCOA Import Utility, or have you rolled your own?

We're now using the utility for export, but still use our own tool for import, as we have a requirement that certain customers only be updated after user review.  My process actually involves getting the file back from our processor (I honestly don't even know what their name is right now, it's changed about 10 times) and then running it through a python script to remove the unnecessary rows and columns, and then I have a BCP format file, but it's just for the limited column output that my python script creates.

--Gawain

Hi Sadie,

Are you using the built-in Tessitura NCOA Import Utility, or have you rolled your own?

We're now using the utility for export, but still use our own tool for import, as we have a requirement that certain customers only be updated after user review.  My process actually involves getting the file back from our processor (I honestly don't even know what their name is right now, it's changed about 10 times) and then running it through a python script to remove the unnecessary rows and columns, and then I have a BCP format file, but it's just for the limited column output that my python script creates.

--Gawain

Gawain Lavers said:

I honestly don't even know what their name is right now, it's changed about 10 times

Ha! That must be Zeta Global, formerly known as eBay, formerly known as MBS. :-)

Gawain Lavers said:

use our own tool for import

We use the standard utility for export & import, but roll our own for "processing". Much more flexibility, not to mention much, much faster.

How do you use it to import without actually processing?

Gawain Lavers said:

How do you use it to import without actually processing?

I only use the export and import modes. Then rather than using the process mode, I get what I want from TW_NCOA_INCOMING, put it into a local table along with some data from T_ADDRESS, and do my own processing, still considering the nncoa_foot values, among other data.

Huh, I think when I first built the utility our provider didn't send files back in the format Tessitura wanted, and so I didn't look into doing that at all.  Also, the overwhelming majority of returned rows are "Address checks out, ignore!", specified by one of about 100 different flags, so it was nice to strip those out before adding clutter.  Still, it would be nice to be able to skip the file processing step I have to do on my local computer with a python script.

Oh, but crap, they have a key on address_no in TW_NCOA_INCOMING: that means you can't do a trial run in Test because the import will crash on addresses added since the last Live->Test.  One of the things my script does is let me strip those out for the file I upload to Test.  Kind of defeats the point of a staging table, though, to have foreign keys on it.

Gawain Lavers said:

"Address checks out, ignore!",

Yeah, many of the addresses we get back from Zeta/MBS are just fine, but the standard utility slowly grinds through them anyway, in "process" mode. Addresses like these, with no changes at all, are skipped in my code. This, and the code 19 fiasco (discussed elsewhere in these forums, I think), pushed me to finally move to my own processing code.

The two things we didn't like were:

1. Overwriting old addresses (we file them away as inactive and use a Contact Point Preference to describe why).
2. Not being able to put on useful holds for certain customers.

To the second point, you can use a list to prevent updates for certain customer accounts, but then you wouldn't know which accounts had been skipped, why they had been skipped and what operation had been recommended.  I needed to provide all of that to the different departments who wanted holds so that they could review them and decide what to do with the USPS recommendation readily available.

Gawain Lavers said:

Overwriting old addresses

Forgot about those; yes, likewise, we never want to lose old addresses. We inactivate and save rather than overwriting, which always seemed abhorrent, un-Tessitura-like behavior. 

Gawain Lavers, Chris Jensen, and anyone else -- would you be willing to share the code you use to save inactive copies of the old address? Please feel free to email me directly ssur@publictheater.org.  Thank you in advance!

Sheela,

I just sent you our process.  I went through this a few years ago and I feel like I have it down pretty solidly at this point in time.  Feel free to respond if you have any questions about our process.

John

Hi Sheela,

I'm in the middle of rebuilding our system, but the broad outline is this:

1) I have a local table of constituencies that are to be skipped by the update.

2) Customers where the address has been altered since the addresses were pulled are skipped as well.

3) Customers that have been part of a merge since the addresses were pulled are also skipped.

4) I created a Contact Point Purpose Category called "Contact Point History".  It has three Contact Point Purposes: "Unknown or Bad Address", "NCOA Move" and "NCOA Delete" (the first for manual use).  The process then inactivates the address in question and applies one of these purposes to it.  If a new address is supplied, that is added (i.e. an NCOA Move).  If it's a Delete I think it might make any other active addresses primary, and if none exist, add our "default" address (i.e. Street 1 = "None", ZIP = 00000). 

I will say that Tessitura doesn't approve of this use of Contact Point Purposes, but I can see no reason to not use them for things like this.