Exporting Data Question

Joanne Felci 2 · March 2018

Hello!

I'm trying to export gift information - including appeal/fund/gift subtype that is kept in the cross reference portion of the donation form and contact information for the donor including name, address, etc. - and getting stuck on the cross reference appeal/fund/gift subtype

What I've done is set up a report writer transaction report and that gives me most of the information but I do not seem to be able to access the appeal/fund/gift subtype.

Does anyone have any suggestions?

What I'm trying to achieve is a data file to give to Omatic to start IOM implementation.

Joanne

Brian Mucha · March 2018

Joanne Felci:

Hello!

I'm trying to export gift information - including appeal/fund/gift subtype that is kept in the cross reference portion of the donation form and contact information for the donor including name, address, etc. - and getting stuck on the cross reference appeal/fund/gift subtype

What I've done is set up a report writer transaction report and that gives me most of the information but I do not seem to be able to access the appeal/fund/gift subtype.

Does anyone have any suggestions?

What I'm trying to achieve is a data file to give to Omatic to start IOM implementation.

Joanne

Are you talking about the Cross References for the donation forms? I don't think there is any report that shows those.

What you could do is built a spreadsheet of those, and then do a lookup in Excel to join them with your transactions report.

Before I go too far, am I right that these are Cross References from Data Management > Import//Export > CrossReference Types? How many do you have mapped?

Joanne Felci 2 · March 2018

Hi Brian...yes that is what I mean the Cross Reference items for each donation form that tie into RE appeal/fund/campaign/gift subtype

I can keep them in a spreadsheet as you say but the whole point of using ImportOmatic is not having to do all that - IF I can figure out how to export that information.

I have a case started with support and will see what they have to say...if they have a solution I'll share

Brian Mucha · March 2018

You need to use a Dictionary in IOM to replace the whole XREF system from the old datasync. This will be based on the donation form Id. You'll need the complete set of existing CrossRefs to build your dictionary. You won't need them with transactions, just on their own.

Form ID, RE appeal, RE fund, RE campaign, RE gift

You didn't say how many you have. If its just a few, then you can build this spread sheet easily just by copy/paste from Luminate.

If you are like us, with many hundreds, then its a bit harder. I did this very thing recently with our in-progress migration to IOM.

You might need support to help with this. I used the Web Services console to generate a complete list. Are you familiar with the API at all? You'll need to know your API Administrator account and password.

It's hard to navigate to the WebServices console, but you'll find it here:

https://webservices.cluster3.convio.net/1.0/abc/console

You'll need to adapt the URL to your org by replacing the yellow with the right values for you.

In the QUERY tab, try this:

SELECT SiteId, ForeignKey, ForeignKey2, ForeignKey3 , ForeignKey4 FROM CrossReferenceValue WHERE XrefTypeId=404

This should give you XML results like this (SiteId is the Form Id and the ForeignKeys are your mappings) :

<?xml version='1.0' encoding='UTF-8'?>

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">

  <soap:Body>

    <QueryResponse xmlns="urn:soap.convio.com" xmlns:ens="urn:object.soap.convio.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

      <Record xsi:type="ens:CrossReferenceValue">

        <ens:SiteId>1730</ens:SiteId>

        <ens:ForeignKey>MRICBORD12</ens:ForeignKey>

        <ens:ForeignKey2>RCMRCMRIC</ens:ForeignKey2>

        <ens:ForeignKey3>PERSPAPL1210</ens:ForeignKey3>

        <ens:ForeignKey3>PERSPAPL1210</ens:ForeignKey3>

        <ens:ForeignKey3>APPL66</ens:ForeignKey3>

      </Record>

    </QueryResponse>

  </soap:Body>

</soap:Envelope>

Once this is all retrieved, you can convert that to a CSV format using an online converter like this: http://www.convertcsv.com/xml-to-csv.htm

Joanne Felci 2 · March 2018

Thanks Brian!

You lost me at API

BUT we do not have that many donation pages and Omatic has already mentioned 'directories' to me so I think I'm following that

I do not have my first meeting with the consultant until late April (mainly because we have a big event April 21 and I can't really focus on anything else until late April), but I think I'm following what you are saying

Basically no way to export that data, but I AM able to export the donation page name/id and I can use that to program the directory

like page id = 12309

THEN

Campaign = Y

Fund = general

appeal = event

gift type = individual giving

As long as they show me how to program this stuff moving forward I think we should be fine

I'm also looking into the Omatic Luminate Connector (as I heard that helps with some of this back end stuff) but we will see..

Joanne

Brian Mucha · March 2018

Gotcha. That makes it easier.

I assume Omatic will be helping you build your profiles, which should include the mapping dictionary.

A dictionary is basically a look-up table for IOM. This dictionary will have all of your Form Ids, along with the RE mapping for each one.

When the IOM profile processes a transaction, it will look at the dictionary and find the proper mapping for the transaction's form id.

You can find your current cross-references at Data Management > Import/Export > Cross References Types tab > Donation Form Cross-Reference

If you only have a few forms, then this page should be easy to copy and paste from when you are ready to build your IOM dictionary.

The Connector is just a way to automatically send the data back and forth between IOM and Luminate. Without the connector, you have to export a CSV file from Luminate, copy it over to IOM and feed it in. The connector just retrieves the data directly over the web, no exports needed.

Joanne Felci 2 · March 2018

Brian Mucha:

Gotcha. That makes it easier.

I assume Omatic will be helping you build your profiles, which should include the mapping dictionary.

A dictionary is basically a look-up table for IOM. This dictionary will have all of your Form Ids, along with the RE mapping for each one.

When the IOM profile processes a transaction, it will look at the dictionary and find the proper mapping for the transaction's form id.

You can find your current cross-references at Data Management > Import/Export > Cross References Types tab > Donation Form Cross-Reference

If you only have a few forms, then this page should be easy to copy and paste from when you are ready to build your IOM dictionary.

The Connector is just a way to automatically send the data back and forth between IOM and Luminate. Without the connector, you have to export a CSV file from Luminate, copy it over to IOM and feed it in. The connector just retrieves the data directly over the web, no exports needed.

Brian - does the connector also send information from RE to Luminate? For example if there is a new donor in Luminate, then you import it into RE and give it a Con ID, does the Con ID get back into Luminate?

Brian Mucha · March 2018

Yes, pretty sure it does.

Philip Nawrocki · March 2018

Hi Joanne,

It does, export profile is called Lumiante Constituent Export. You can map what you want to come out of RE into LO. We are basing this on a set of criteria as we don't want every RE record to flow into LO. We are basing it on if the RE record has been updated since the last export was run. It can be any update to the record.

This will also update the accepts e-mail flag in Lumiante.

Additionally you can export groups and you can even set up groups to be automatically updated (users added and removed) via the scheduler that comes with IOM.

We are going live this week, so if you have more questions, fire away. We will answer them the best we can.

Thanks,

Phil

Joanne Felci 2 · March 2018

Philip Nawrocki:

Hi Joanne,

It does, export profile is called Lumiante Constituent Export. You can map what you want to come out of RE into LO. We are basing this on a set of criteria as we don't want every RE record to flow into LO. We are basing it on if the RE record has been updated since the last export was run. It can be any update to the record.

This will also update the accepts e-mail flag in Lumiante.

Additionally you can export groups and you can even set up groups to be automatically updated (users added and removed) via the scheduler that comes with IOM.

We are going live this week, so if you have more questions, fire away. We will answer them the best we can.

Thanks,

Phil

Thanks Phil!

So you are going live with the Luminate Connector for ImportOmatic?

Would love to hear how it goes

Right now our timeline is IOM implementation by mid May, we will see how that goes and maybe add the connector later

Philip Nawrocki · March 2018

Correct!

We are going live with the Luminate Connector today actually.

We will be running our first import and export sometime this afternoon.

Can definitely keep ya posted.

Thanks,

Phil

Joanne Felci 2 · May 2018

Hi Phil,

Hope all went well with the Luminate Connector

I'm finishing up my training today and have profiles created and have been using them since last week and all is going well

It will be great to play around with it more and figure out ways ImportOmatic can be used for other data imports

How have things been going for you? Besides gifts what are you using it for?

We only have two profiles - sustainers and donations as we do not use Luminate for too much

Joanne

Philip Nawrocki · May 2018

Hey Joanne,

We have been live since my last post and it is actually going well!

We have over 15 profiles... :-)

We are using TeamRaiser and there are some things we have to handle that can't be done on the first pass of a profile. We have skip rows created and then run the next profile.

We use it for TeamRaiser, obviously, for registrations and additional donations. We use it for eCommerce and surveys. We are exploring using the constituent import to bring e-mail actions over into RE.

Additionally, we will be testing the events profile because with data dictionaries we can finally make that module work for us the way we need it to.

We are exporting back to Luminate as well. We are using the group sync to populate some groups in LO from RE queries. Also, we are sending unsubscribes marked in RE to LO. We just started working on an import profile to take LO unsubscribes and get them in RE. I do a lot of manual updates from our e-mail sends and we are currently only sending constituents to RE that have made a gift. This is based on an LO query.

Let me know if you want more info on any of these.

We will start bringing in data from other systems but are waiting on a few updates before we do that as well.

We really like the flexibility it gives us.

Thanks,

Phil

Joanne Felci 2 · May 2018

Philip Nawrocki:

Hey Joanne,

We have been live since my last post and it is actually going well!

We have over 15 profiles... :-)

We are using TeamRaiser and there are some things we have to handle that can't be done on the first pass of a profile. We have skip rows created and then run the next profile.

We use it for TeamRaiser, obviously, for registrations and additional donations. We use it for eCommerce and surveys. We are exploring using the constituent import to bring e-mail actions over into RE.

Additionally, we will be testing the events profile because with data dictionaries we can finally make that module work for us the way we need it to.

We are exporting back to Luminate as well. We are using the group sync to populate some groups in LO from RE queries. Also, we are sending unsubscribes marked in RE to LO. We just started working on an import profile to take LO unsubscribes and get them in RE. I do a lot of manual updates from our e-mail sends and we are currently only sending constituents to RE that have made a gift. This is based on an LO query.

Let me know if you want more info on any of these.

We will start bringing in data from other systems but are waiting on a few updates before we do that as well.

We really like the flexibility it gives us.

Thanks,

Phil

Hi Phil,

that is great that it is working so well for you

I'm really hopeful

I put together one export today to get RE Con ID info for new donors back into Luminate - so I guess we have 2 imports and 1 export.

I'll take you up on connecting if questions happen

Please feel free to reach out to me as well

Best,

Joanne

Philip Nawrocki · May 2018

Sounds great!

I bet there will be more people posting about it in the community as more orgs migrate to it.

robert badley · June 2018

Philip Nawrocki:

Sounds great!

I bet there will be more people posting about it in the community as more orgs migrate to it.

Phil and Joanne

You (we) are part of a growing community of IOM LO Connector users. We came into it as fed-up RELO users. We have been live since February and like you Phil we use TR as well as one-time-gifts and monthly gifts through LO donation forms.

We had to set up two import routines for most of our TR events to capture the registration action and another to capture sponsorship gifts. Our monthly donor signup form also require a two pass process to set up the recurring-gift in RE and then another to pick up the recurring-gift-pay-cash transactions.

At this point we are not doing any regular RE to LO synchs but that is the next task at hand. One thing that I did like about RELO was that it handled these synchs (RE-LO) automatically. I am finding that now as I do cleanup in RE (merging duplicates etc) I am left with orphan records in LO that are not tied to anything in RE. A few headaches to deal with here but all in all a good experience. The flexibility for coding transactions coming in to RE is great.

Robert

Philip Nawrocki · June 2018

You are exactly right. If IOM LO Connector could figure out the duplicates error... No one would use RELO.

That is the missing piece.

We are going to start using scheduler soon to start taking care of some of the back and forth. It was broken when we launched, but they just patched it.That will hopefully allow us to set our Group Sync to run automatically every day.

I will let everyone know how it goes.

Phil

Joanne Felci 2 · June 2018

Phil...would love to hear more about scheduler

Right now I've been doing two imports every morning (sustainer gifts then donations)

And doing an export about once a week to sync what I care about into LO (which is RE Con ID for new donors)

Robert - what information are you wanting to sync back into LO? Are you doing exports from RE into LO?

My main exception issue - though it will get cleared up with time - is previous pledges and getting the correct Gift ID on the pledge record that is already set up in RE but I'm really used to what those exceptions look like and it is a pretty easy fix

It is just the strange error messages that appear time to time, but still nothing that has concerned me

Joanne

Philip Nawrocki · June 2018

We should be setting up scheduler in the next 2 weeks, so I will update ya then.

Yes, we run our profiles each morning. The error messages have been getting better on our end.

I will ask our RE guy about the pledges thing. We talked about it during our setup phase, but I can't remember what we did.

Phil

Joanne Felci 2 · June 2018

Phil,

The 'pledge thing' is unique (kind of) to our organization.

We have ongoing pledges that we create new pledge every year.

The gift ID for the first time the credit card for the pledge was processed is the one that needs to be on the pledge. So the error messages are for all these 'old' pledges and will be cleared up within a month.

The other thing I turned off was the spouse and organization link as those things were driving me crazy and I don't trust the information in Luminate for those things

Overall really liking Omatic!

Philip Nawrocki · June 2018

Oh I see.

Agreed. Luminate Orgs don't do anything worthwhile at this point.

We tested scheduler and it is working! We do not bring every LO user in RE, only if they make a gift, so a gap we had was with unsubscribes. A person could unsubscribe and then never be marked Do Not Contact in RE. We set up a recurring query and then have scheduler looking at the group created by the query and then it imports to RE and updates the status. We went the slow route and tested it on a non recurring query first. We have to do a little cleanup first, but it should be running in the next 2 weeks. Good to know scheduler will work. We are trying to create a notification after it runs that alerts us to any issues.

We like it too. Very flexible.

Phil

Exporting Data Question

Comments

Categories