Auburn Specific Instructions
We embarked on a crash project to transform key API endpoints from serving up solely JSON responses to also support CSV with some notable limitations. JSON responses could contain hierarchical/nested information (structs/maps within arrays and so on). CSV can't do that as a strictly two dimensional format. Simple values map directly. Structs within a returned object are flattened out into a KEY_innerkey column format. Anything more complex is dropped. If you ever want to know what you're missing, simply remove ".iqcsv" from the endpoint URI and execute in a browser to see the original response data.
A note on the CSV format
Other than some data elements being dropped, there's one other key consideration to keep in mind. The columns are always in alpha order. This isn't entirely human friendly, but since the objective is to get you feeds you can import to temp tables then process/ingest, there's no harm in them being defined that way. Any requests to add fields to the response data will have to consider this - we can't simply tack more on to the end, and you will need to update your import scripts accordingly.
Any endpoint that has no data to return will generate an empty file - not even including headers (JSON is far more forgiving).
A note on the domain
We'll reference the QA domain throughout; expect to keep your QA and prod domains (using separate and distinct API keys) set as variables in your scripts for easy maintenance.
Powershell? Curl? What are you using?
From Powershell it couldn't be any easier to get a CSV.
Invoke-WebRequest -Uri "https://qa-alumniq.auburn.edu/api/v1/index.cfm/giving/giftpayments.iqcsv?date=2020-06-18&apikey=API_KEY_GOES_HERE" -Outfile "c:\users\chad\desktop\giftpayments_20200618.csv"
Note: it is possible to stash your API key in your Powershell user profile; on the plus side it's great to have one variable to update but it's also a pain if you have multiple users potentially invoking the scripts.
Gifts
Objective: Nightly, receive a "file" of successful gift payments from the prior business day.
Notes: AlumnIQ object model is Pledge -< Gift -< GiftPayment - Transaction
So what you're really fetching are satisfied GiftPayment records with associated transaction data and gift information (which contains the donor, fund, frequency, term, and related information)
CSV: https://qa-alumniq.auburn.edu/api/v1/index.cfm/giving/giftpayments.iqcsv/?date=2020-06-18&apikey=API_KEY_GOES_HERE JSON: https://qa-alumniq.auburn.edu/api/v1/index.cfm/giving/giftpayments/?date=2020-06-18&apikey=API_KEY_GOES_HERE
You'll need to increment that date parameter daily and provide it in that format exactly. This allows you to do catch-up or review runs anytime you need to.
The CSV you receive from this call will contain 87 columns of data.
A few notes for you:
unmatched donors will not have a value in XID; you'll have a far easier time of ingesting them if and when matches are established
billing address will always be present, though home address may not be populated depending on which gift form is used (legacy vs. GiveNow)
per the object model, pledgeId = the wrapper object for one or more gifts (key:giftId). A gift in this feed will have one giftpaymentid
giftDesignationXCode is the Advance allocation ID
any currency values are always represented as cents so don't forget to divide by 100 before saving in Advance or you'll make someone's day in a bad way
cardtype is a string literal of the payment method. As Apple Pay, Google Pay, Venmo, and Paypal are new to us expect some interesting values in here.
confirmation is the conf code returned from Braintree which should square with the statements they deliver
Valid Enumerations
frequency : one-time, recurring, installment, perpetual
interval : monthly, quarterly, annual
iterations : -1 means perpetual, any positive value is number of interval units
method : onlineCC, offlineCC, cash, check
cardType : Visa, MasterCard, AmericanExpress, Discover, Venmo, ApplePay:XXX, GooglePay:XXX (where XXX = the actual card type)
Memberships
How you'll go about consuming these will depend on what exactly it is you need to do that day. I'm going to presume you'd prefer to create the memberships FIRST then apply payments to both new and existing memberships as needed.
So let's go get all memberships created today
That doesn't return terribly much except the most critical piece of information: a membershipId for each of them.
To get the details on each membership (again, this makes more sense using JSON) you'll need to invoke this for each one:
What you'll get from that is a monster 88 columns of data.
Notes on what's here:
the membership is the root object
a membership has a purchaser
the purchaser may or may not be the primary member (member1 in this response)
if it's a joint membership, the member2 fields will be populated. if not, they'll still be in here but empty
the purchaser should get hard credit for any gift made during the checkout process
That should be sufficient for you to create a new membership in Advance. We strongly recommend you stash our membershipId value in there somewhere as an alt id. You'll need it. If you can, we strongly recommend pushing member ids (whether those are memb numbers or the XID) into the xMemberId field in AlumnIQ. There is a separate PUT method on /memb/membership/MEMBERSHIP_ID_GOES_HERE that allows you to push that in to us.
Now, as for transactional information to record against those memberships...
For annual memberships this is a one transaction and done deal. For the life amortization memberships things get a little more complicated. Either way, you'll need a membership record in Advance to write these to.
These...what? Transactions.
Set your range begin/end thoughtfully so you don't re-credit a membership accidentally. The membershipId is as provided above; the transactionId identifies each specific payment. And don't forget - we always talk in cents so divide by 100 when you insert.
Valid Enumerations
method : onlineCC, offlineCC, cash, check
cardType : Visa, MasterCard, AmericanExpress, Discover, Venmo, ApplePay:XXX, GooglePay:XXX (where XXX = the actual card type)
Events
Notes
A policy decision should precede any movement of registrant data. Namely: do you want it as they come in (potentially maintaining future cancels in Advance forever) or do you want it once the event is "closed" (meaning it's an immutable permanent record that you can rely on)?
In-progress pulls get data into Advance faster, but post-closure pulls are more certain (and will rightfully indicate actual attendance).
Either way, the event will need to exist in Advance before you can map registrants/attendees to it. This isn't something we can do for you -- but once it's done, be sure to add the Advance activity code to AlumnIQ in the External Event ID (and/or External Activity ID) field as appropriate. Which one(s) you'll use depends entirely on the level of tracking granularity you're implementing. In most cases the code will reside at the activity level.
Object Model
An event has at least one activity. The activities are what people are signing up to attend.
Reservation = the party. A reservation has one or more Person records under it.
The first Person is the primary registrant, and the one responsible for payment (and hard credited for any gift as a result)
A party may make at most one gift to one fund during checkout.
An individual reservation may have multiple transactions executed against it (this is not how iModules works); keep that in mind as you dig into the financials.
For You
ensure the advance activity codes are added into AlumnIQ
if you want registrants regardless of what they signed up for: GET https://qa-alumniq.auburn.edu/api/v1/index.cfm/events/EVENT_ID_GOES_HERE/attendees.iqcsv?apikey=API_KEY_GOES_HERE
if you only one one activity's worth of registrants: GET https://qa-alumniq.auburn.edu/api/v1/index.cfm/events/activities/ACTIVITY_ID_GOES_HERE/attendees.iqcsv?apikey=API_KEY_GOES_HERE
DAILY retrieve gifts across all events GET https://qa-alumniq.auburn.edu/api/v1/index.cfm/events/gifts.iqcsv?giftDate=2020-06-01&apikey=API_KEY_GOES_HERE
Event Metadata
Events have a lifecycle. Creation, operation, and then closure. Closure is a deliberate action taken by the event manager to say "we're done and the data will not change further."
Consider that when choosing how to add event attendee and financial data into Advance.
three options for filters:
onOrAfter=yyyy-mm-dd
createdOnOrAfter=yyyy-mm-dd
closedOnOrAfter=yyyy-mm-dd
neat feature: eventid = the AlumnIQ event id. But if you put the Advance event ID on the event, you can also fetch by the Advance event ID using a slightly different syntax.
Advance event ID = b1234
then GET /events/x-b1234 will work too
activity metadata
Attendees
all people registered regardless of what they signed up for
all people signed up for this activity specifically
an individual human's registration meta
the mechanism to set an XID for a registrant
Finances and Accounting
all transactions regardless of method that happened on that date
primarily helpful for credit card reconciliation purposes
just gifts received and paid on the given date (reminder: only gifts given incidentally as part of event registration)
only the transactions related to a specific event
useful after the event is closed (maybe)
tells you where to put actual dollars post-event closure
awesome detail to complement the balance sheet report
tells you how we arrived at the bottom line numbers for this event
Profiles
Profile information updates are, by design, limited - and at the same time very atomic. Each field that is updated is captured independently of all others.
These fields are the only ones we pass through to you for backend updates:
addr_home_Street1
addr_home_Street2
addr_home_City
addr_home_State
addr_home_PostalCode
addr_home_Nation
addr_bus_Street1
addr_bus_Street2
addr_bus_City
addr_bus_State
addr_bus_PostalCode
addr_bus_Nation
employer
jobTitle
jobSummary
linkedIn_Url
gender
gender_other
Soon to be deprecated:
phoneDialCode
phoneCountryCode
phone
phoneType
New additions with profile refresh in July:
pronouns (free text)
homephonedialcode
homephonenumber
mobilephonedialcode
mobilephonenumber
workphonedialcode
workphonenumber
As part of the profile refresh, we're adjusting our rules so that you'll only receive change records for people who have matched records. Current customers are receiving them for unmatched constituents which is not helpful - without an xid you have nowhere to park the data.
XID is your Advance entity ID -- and the only value that matters for getting this info back into Advance. Changes made in a given section of the profile at the same time will have the same exact timestamp.
Two ways to get this data as shown during the deep dive:
For a single day:
For all days since (inclusive):
And once you've processed those updates, call us back to tell us they're in tomorrow's warehouse:
PUT https://qa-alumniq.auburn.edu/api/v1/index.cfm//directory/updates/accept-list?apikey=API_KEY_GOES_HERE with a request body of: { "changeIds": [changeid1,changeid2,changeid3...] } where changeid1...3 are the changeIDs given to you in either of the feeds above. If you do not do this, we will not know when you've actually ingested the changes.
Last updated