Title: Solving Data Sync
1Solving Data Sync
- Online and Offline to Work Together
2Presenters
Al Anderson Director Membership
Marketing Minnesota Public Radio www.mpr.org
Jim Shea Vice President, Technology ACCESS
International www.accessint.com jshea_at_accessint.co
m
3Data Synchronization Strategy
- Offline fundraising database is the master
database - Collects data from multiple sources
- Contains data not available in online databases
- Insures online data reflects data from all
sources - Fundraising application users can rely on a
single database for information - Provides central database for all data analysis
- Import as much data from online database as
possible - Insures accurate exports
- Prevents loss of data in online database
- Maintain a one-to-one relationship between
online and offline accounts
4Data Synchronization Strategy
- Create source and other related codes within
master - Maximize the use of application functionality to
transfer data - Implement processes to insure that data from
online systems meet internal requirements - Manually review data as it is imported (if
possible) - Implement data scrubbing routines to
automatically correct common errors - Timing is everything
- Master should reflect up-to-date information
before extracting data for online databases - Imports exports should be scheduled to insure
data is accurate prior to sending communications
5MPR Renewal Notices
Preliminary Email
Expiration Email
Expiration Call
1st Notice
2nd Notice
3rd Notice
4th Notice
5th Notice
6th Notice
MPR utilizes email messages to provide
preliminary renewal and membership expiration
notifications. These messages notify the
recipient that they can easily renew immediately
and encourages them to do this by indicating that
doing so will allow MPR to forgo additional
renewal notices.
6MPR Email Renewal Process Flow
Direct Mail Vendor
MONTHLY
Extract Email Renewal Data from Central
Fundraising Database
Import Email Renewal Data into Email Processing
Database
Generate Email Renewal Messages
DAILY
Export Renewal Pledges from Online Donation
Database
Import Online Renewals into MS Access Database
Generate Printed Pledge Cards for Online Renewals
Manually Key Online Renewals and Account Updates
7MPR Email Renewal Data Flow
Data is extracted from the offline fundraising
database prior to each email renewal to insure
the appropriate accounts receive an email
message.
Fundraising Master Database
Fundraising Online Email Database
Fundraising Online Donation Database
Data Entry
Online Pledge Report
Creating a pledge report allows MPR to format the
data to match their data entry windows.
Email Renewals
Donation Site
Data is extracted daily from the online donation
database to insure the offline fundraising
database accurately reflects all donations and
account information.
Donors are given their offline fundraising
database account ID to key into the donation site
to insure their account is updated accurately and
to minimize the creation of duplicate accounts
8MPR Pledge Drive Data Entry
Volunteers
Staff Overflow
Donors
Pledge Cards
Staff overflow and some volunteers use the online
donation site to enter pledges. The online
donation site includes an internal entry form
that allows MPR to apply the appropriate source
code to the phone in pledges entered by
volunteers and overflow staff.
Fundraising Online Donation Database
Data Entry
Fundraising Master Database
9Other Email Renewal Strategies
Follow-up Email
Expiration Email
1st Notice
2nd Notice
3rd Notice
4th Notice
5th Notice
6th Notice
Follow-up email messages act as a reminder for
the member and provide an easy mechanism to
renew now without taking the time to write a
check and mail back their renewal.
10Email Renewal Process Flow
MONTHLY
Extract Email Renewal Data from Central
Fundraising Database
Import Email Renewal Data into Email Processing
Database
Generate Email Renewal Messages
DAILY
Export Renewal Pledges from Online Donation
Database
Import Online Renewals and Account Updates into
Central Fundraising Database
11Email Renewal Data Flow
Offline Fundraising Database
Utilizing the same online system for email and
donations allows you to easily link the account
information with the donation. This makes entry
of the donation easier for the donor, reduces the
processing associated with importing donations to
your offline fundraising database, and eliminates
the potential for creating duplicate accounts.
Online Database
Email Renewals
Donation Site
12Email Renewal Data Flow
Having a separate online donation system may
require users to re-key name and address
information. If users must re-key account
information, then you will need to rely on the
donor to enter an account ID and/or rely on your
offline fundraising system to detect duplicates.
Offline Fundraising Database
Online Email Database
Online Donation Database
Email Renewals
Donation Site
13 Pledge Drive Data Entry
Volunteers
Staff Overflow
Donors
Pledge Cards
Online Donation Database
Offline Pledge Drive Database
Data Entry
fundraising Master Database
14Pledge Drive Process Flow
DAILY
Export Pledges from Online Donation Database
Import Pledges from Online Donation and Offline
Pledge Drive Databases
Export Pledges from Offline Pledge Drive
Application Database
Key Pledge Cards into Offline Fundraising
Application
15Synchronization Challenges
Including calculated variables in an email message
Online systems do not typically provide the
flexibility to allow you to include account based
calculated values, like ask amount, in an email
message.
To accomplish this
- utilize the functionality in your offline
fundraising application to calculate and/or
include these values in the email export data - utilize the custom fields in the online database
to import the calculated values - include the custom fields in your email to
customize the message for each account
16Synchronization Challenges
Managing processing lags data from multiple
sources
Data from online systems is not reflected
real-time in your offline fundraising database,
and donors may supply information through other
mechanisms in addition to your online
application.
To minimize data accuracy problems
- implement procedures to insure data is imported
from your online database daily - utilize features in your offline fundraising
application to review and approve data as it is
imported - utilize features within your offline fundraising
application to prevent overwriting recently
changed data
17Synchronization Challenges
Preventing duplicate accounts pledge drives
Pledges entered in the online donation database
during an on-air pledge drive typically do not
include any indication that the donor has an
existing account in the fundraising database.
To prevent duplicate accounts
- provide a mechanism on the donation site to allow
the user to indicate that they are an existing
member - make the offline account ID known to your members
and provide a field for them to enter it on your
donation site - utilize the matching procedures from your offline
fundraising application to identify duplicates - utilize alternate checking procedures to search
for other potential duplicates - diligently merge accounts in the offline
fundraising database when duplicates are detected
18Synchronization Challenges
Preventing duplicate accounts pledge drives
MPRs donation site provides a place for the
donor to indicate that they are an existing
member. This allows MPR to easily identify which
transactions apply to existing accounts.
MPR makes the ID number known to their members
and provides a place for them to enter this ID.
Having the ID insures that the transaction will
be assigned to the correct account.
19Synchronization Challenges
Preventing duplicate accounts email responses
If your online donation application is different
from your email application, pledges entered as a
result of clicking the link in an email message
may not include an indication that the donor has
an existing account in the fundraising database.
To prevent duplicate accounts
- check to see if you can pass an account ID in the
email link to the donation site - utilize the matching procedures from your offline
fundraising application to identify duplicates - utilize alternate checking procedures to search
for other potential duplicates - diligently merge accounts in the offline
fundraising database when duplicates are detected
20Synchronization Challenges
Maintaining a one-to-one relationship between
offline and online accounts
An account with multiple email addresses in your
offline fundraising database typically must
become multiple accounts in your online database
or you must select one email address to use for
the account in the online database
Online Database
Offline Database
Online Database
jdoe_at_mywork.com
Becomes
or
Online Database
jdoe_at_mywork.com jdoe_at_aol.com
jdoe_at_mywork.com
jdoe_at_aol.com
21Synchronization Challenges
If you
create two accounts
- you must maintain the offline ID in a user
defined field in the online database - be prepared to process account updates from both
online accounts - condense to a single record prior to importing
- offline fundraising system may accommodate
multiple updates to a single account
create a single account
- be prepared for when the user changes the email
address on their online account to the address
you did not use on their online account - Manually review changes and approve them before
updating the offline fundraising database
22Synchronization Challenges
Addressing household accounts
Online databases are designed to treat an account
as an individual and address the account by the
first name, while most offline fundraising
applications allow you to maintain multiple
individuals or households on a single account. To
accommodate this you should insure that the first
name on an account is appropriate for the email
address on the account.
23Synchronization Challenges
Managing new online accounts
Online systems typically include a tell a
friend feature, which allows email recipients to
forward the email to others, who can in turn
sign-up to receive future mailings. This can
result in accounts within the online database
that contain insufficient data for creation of an
account in your offline fundraising database
(i.e. no mailing address).
For these accounts
- implement a mechanism to collect more information
from the account to determine if they already
exist in your offline fundraising database or
should be added - you can continue to maintain them in the online
database until they make a donation and provide
the data required to add them to the offline
fundraising database - make sure you include these accounts in
appropriate future mailings - implement a process to insure the source code on
these accounts is updated to reflect the latest
mailing
24Synchronization Challenges
Managing bounce backs
Online systems track hard (e.g. email address not
valid) and soft (mailbox is full) email bounce
backs. This information must be processed in
order to maintain accurate online and offline
accounts.
For hard bounce backs
- insure the account is contacted in another manner
if the email was the only mechanism used to
contact them - implement a process to update the offline account
to insure that you do not continue to send email - implement a process to contact the account to get
their correct email address
For soft bounce backs
- insure the account is contacted another way, or
with a second email, if email was the only
mechanism used to contact the account - implement a process to review these account and
identify those that experience repeated soft
bounce backs as it may indicate that the
recipient no longer uses that email address
25Synchronization Challenges
Subscription management
Online applications typically allow a user to
both unsubscribe from and subscribe to email
groups within the application.
To maintain this information offline
- implement a process to export this information
and import it into your offline fundraising
database - make sure you accommodate both unsubscribe and
subscribe options
If you cannot maintain this offline
- you should insure that this information is not
overwritten by future imports from your offline
fundraising database
26Synchronization Challenges
Managing charge installment transactions
Online systems typically include the use of a
charge processing application (e.g. ICVerify) to
facilitate the processing of installment payments
at a scheduled interval.
Implementing this feature
- can result in transactional data from the online
system that does not distinguish between new
pledges and installment payments - creates an additional mechanism that generates
cash into your merchant account that requires
reconciliation - requires unique coding to insure these
transactions are not settled a second time by
your offline fundraising system
Not implementing this feature allows you to
process all charge data through your offline
fundraising application and simplifies the back
end processes associated with charge transactions
27Synchronization Challenges
Source code management mailing history
Online applications typically do not include
automated processes to create source codes for
each mailing and assign them to accounts.
Additionally, online applications do not maintain
historical source code information for each
account. You should utilize the source code
creation and assignment features of your offline
fundraising application when you extract accounts
for email mailings and import them into your
online database
- send email messages in your online system based
on source code assignment - insure that all source codes exist in your
offline fundraising database and avoid errors
when importing donations - maintain last contact source code on accounts and
have it automatically assigned when importing
donations - insure a complete history of contacts and source
codes is maintained for all accounts - insure consistent analysis of all donor contacts
This allows you to
28Synchronization Challenges
Assigning transaction source codes email
responses
If your online donation application is different
from your email application, pledges entered as a
result of clicking the link in an email message
will not include the email source code on the
transaction data.
To assign the source code
- check to see if you can pass a source code in the
email link to the donation site to save on the
pledge - utilize the solicitation history/tracking
features of your offline fundraising application
to assign the source code based on the last
solicitation sent to the donor
29Synchronization Challenges
Assigning transaction source codes pledge drives
Pledges entered in your online donation site as a
result of an on-air pledge drive will not include
data to indicate the break that motivated the
pledge.
To assign the break
- utilize the source creation capabilities of your
offline fundraising application to create source
codes for online pledges that mirror the source
codes for call-in pledges - utilize the date/time stamp on the online pledge
to assign the appropriate source code during the
import process - if your offline fundraising system cannot assign
source codes based on the time stamp then
implement a preliminary process to assign the
codes based on date/time stamp or segment the
input records by date/time stamp and import each
segment with the appropriate default code value
30Synchronization Challenges
Source code management online only accounts
Accounts created within the online database
through tell-a-friend options are typically
assigned either a default or no source code.
Since this code can only be changed via a manual
process you should create a default source code
that will be useful for future analysis or
implement a process to update the source code on
these accounts each time you send them a new
email message.
Extract online only accounts from online database
Update online only account records to reflect new
source code
Import updated online only accounts to online
database
31Synchronization Challenges
Managing pledge transaction codes
Online applications may hard-code some code
values (e.g. transaction types, payment methods,
payment frequencies, etc.) utilized on the
transactions.
When this occurs
- utilize the scrubbing or correction mechanisms
in the offline fundraising application import
feature to change the values - create mechanisms to translate the codes prior to
importing the data into the offline fundraising
database
32Synchronization Challenges
Mapping online codes to offline codes
Online applications provide flexibility to create
questionnaires that must be mapped to fields and
code values within your offline fundraising
database.
- utilize the same code values in your online
application that are used in your offline
fundraising application - assign distinct code values rather than yes or
no values for check box/radio button questions
that will equate to a code value within your
offline fundraising application
To minimize import issues
33Synchronization Challenges
Pledge drive tally reporting
The process for importing data from an online
database to an offline fundraising database is
not efficient for reporting tallies during a
pledge drive. A process should be implemented to
add tallies from both online and offline sources.
Online Donation Database
Offline Pledge Drive Database
Pledge Cards
Pledge Card Tally
Pledge Drive Application Tally
Online Pledge Tally
Tally
34(No Transcript)