• Replies 10 replies
  • Subscribers 339 subscribers
  • Views 84 views
  • Users 0 members are here
Related

Database Management - Donor Merges

Robert Taulty
$organization

We are doing some clean-up for on our database for constituents duplicates.

 Has anyone written an automated procedure to do a mass merge of constutents? 

 I am thinking of building a local table based on an agreed upon set of rules and call the internal Tessitura procedure with the from and to constituent id's

Bob

  • $organization

    Bob,

    The folks at the Sydney Opera House consortium (AKA the UCSS) have a great routine for this.  I believe there are two elements - the SP to identify duplicate records based on rules agreed by the consortium members (I used to work for one of them) and then the merge routine which they run weekly.  It didn't completely eliminate the need for some human work to be done on merging but sure made life a LOT easier!

    Martin

  • Former Member
    Former Member $organization
    Hi Robert
    We do an auto-merge process.
    Basically, the logic is that we run a heavily customised identify potential dupes process; then feed the output into another process that takes sets of identified dupes (not just pairs); makes a decision about which one of each set to Keep based on factors that affect the assumed "well-keptness" of the record, like whether the record is a subscriber or donor, and recency of activity; creates records for each merge-pair from that set; and puts those pairs back into the Tess merge schedule table; where they are picked up by the normal scheduled (nightly, for us) merge.
    We currently run that process monthly.
    It relies on some local tables that assign merge priority rankings to constituencies and attributes, so there are a few components.
    It seemed like a fairly Brave thing to do, but it's worked well so far.
    I think all of the components of that are on my TessWeb profile files page - if not I can add them in if you'd like to have a look.

    Ken McSwain :: sent from the Blackberry
     
    From: Robert Taulty [mailto:bounce-roberttaulty1608@tessituranetwork.com]
    Sent: Friday, September 09, 2011 07:01 AM
    To: Ken McSwain
    Subject: [Tessitura Technical Forum] Database Management - Donor Merges
     

    We are doing some clean-up for on our database for constituents duplicates.

     Has anyone written an automated procedure to do a mass merge of constutents? 

     I am thinking of building a local table based on an agreed upon set of rules and call the internal Tessitura procedure with the from and to constituent id's

    Bob




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
    Read about the ACO’s Stradivarius violin at aco.com.au/Stradivarius.
    Explore the ACO’s 2011 season at aco.com.au/season2011

    This email is confidential. If you are not the intended recipient you must not disclose or use the information contained in it. If you have received this email in error please notify us immediately by return email and delete the document. The ACO is not responsible for any changes made to a document other than those made by the ACO or for the effect of the changes on the document's meaning. The ACO accepts no liability for any damage caused by this email or its attachments due to viruses interference, interception, corruption or unauthorised access.
  • $organization
    Thank you - I am taking a look.
     
    Bob

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Ken McSwain
    Sent: Thursday, September 08, 2011 6:51 PM
    To: Robert Taulty
    Subject: Re: [Tessitura Technical Forum] Database Management - Donor Merges

    Hi Robert
    We do an auto-merge process.
    Basically, the logic is that we run a heavily customised identify potential dupes process; then feed the output into another process that takes sets of identified dupes (not just pairs); makes a decision about which one of each set to Keep based on factors that affect the assumed "well-keptness" of the record, like whether the record is a subscriber or donor, and recency of activity; creates records for each merge-pair from that set; and puts those pairs back into the Tess merge schedule table; where they are picked up by the normal scheduled (nightly, for us) merge.
    We currently run that process monthly.
    It relies on some local tables that assign merge priority rankings to constituencies and attributes, so there are a few components.
    It seemed like a fairly Brave thing to do, but it's worked well so far.
    I think all of the components of that are on my TessWeb profile files page - if not I can add them in if you'd like to have a look.

    Ken McSwain :: sent from the Blackberry
     
    From: Robert Taulty [mailto:bounce-roberttaulty1608@tessituranetwork.com]
    Sent: Friday, September 09, 2011 07:01 AM
    To: Ken McSwain
    Subject: [Tessitura Technical Forum] Database Management - Donor Merges
     

    We are doing some clean-up for on our database for constituents duplicates.

     Has anyone written an automated procedure to do a mass merge of constutents? 

     I am thinking of building a local table based on an agreed upon set of rules and call the internal Tessitura procedure with the from and to constituent id's

    Bob




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
    Read about the ACO’s Stradivarius violin at aco.com.au/Stradivarius.
    Explore the ACO’s 2011 season at aco.com.au/season2011

    This email is confidential. If you are not the intended recipient you must not disclose or use the information contained in it. If you have received this email in error please notify us immediately by return email and delete the document. The ACO is not responsible for any changes made to a document other than those made by the ACO or for the effect of the changes on the document's meaning. The ACO accepts no liability for any damage caused by this email or its attachments due to viruses interference, interception, corruption or unauthorised access.



    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
  • $organization

    Semi-related to this, in v11, there are now a few options for identifying duplicates:

     

    -          Name and Primary Address (as in previous versions)

    -          Name and Primary Email Address

    -          Primary Email Address Only (no name consideration)

     

    These can be used separately or in combination with priority for identification method (first identify by email, then by address, e.g.).

     

    +Ryan Creps

    +Tessitura Network

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Robert Taulty
    Sent: Friday, September 09, 2011 10:12 AM
    To: Ryan Creps
    Subject: RE: [Tessitura Technical Forum] Database Management - Donor Merges

     

    Thank you - I am taking a look.

     

    Bob

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Ken McSwain
    Sent: Thursday, September 08, 2011 6:51 PM
    To: Robert Taulty
    Subject: Re: [Tessitura Technical Forum] Database Management - Donor Merges

    Hi Robert
    We do an auto-merge process.
    Basically, the logic is that we run a heavily customised identify potential dupes process; then feed the output into another process that takes sets of identified dupes (not just pairs); makes a decision about which one of each set to Keep based on factors that affect the assumed "well-keptness" of the record, like whether the record is a subscriber or donor, and recency of activity; creates records for each merge-pair from that set; and puts those pairs back into the Tess merge schedule table; where they are picked up by the normal scheduled (nightly, for us) merge.
    We currently run that process monthly.
    It relies on some local tables that assign merge priority rankings to constituencies and attributes, so there are a few components.
    It seemed like a fairly Brave thing to do, but it's worked well so far.
    I think all of the components of that are on my TessWeb profile files page - if not I can add them in if you'd like to have a look.

    Ken McSwain :: sent from the Blackberry
     

    From: Robert Taulty [mailto:bounce-roberttaulty1608@tessituranetwork.com]
    Sent: Friday, September 09, 2011 07:01 AM
    To: Ken McSwain
    Subject: [Tessitura Technical Forum] Database Management - Donor Merges
     

    We are doing some clean-up for on our database for constituents duplicates.

     Has anyone written an automated procedure to do a mass merge of constutents? 

     I am thinking of building a local table based on an agreed upon set of rules and call the internal Tessitura procedure with the from and to constituent id's

    Bob




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
    Read about the ACO’s Stradivarius violin at aco.com.au/Stradivarius.
    Explore the ACO’s 2011 season at aco.com.au/season2011

    This email is confidential. If you are not the intended recipient you must not disclose or use the information contained in it. If you have received this email in error please notify us immediately by return email and delete the document. The ACO is not responsible for any changes made to a document other than those made by the ACO or for the effect of the changes on the document's meaning. The ACO accepts no liability for any damage caused by this email or its attachments due to viruses interference, interception, corruption or unauthorised access.



    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

  • $organization

    Hi Bob,

    We have such process in place. The DB procedures are (see attached):

    1. procedure lp_chc_const_dups_PREP
    gets customer duplications into custom table C_CHC_CONST_DUPS;

    2. procedure lp_chc_const_dups_PREP
    populates Tess tbl T_POTENTIAL_DUPS;

    3. procedure lp_chc_const_dups_TO_MERGE
    Prepares potential dups to merge:
    the smallest customer_no will be selected to Keep, the rest - to Delete;

    4. two service functions:
    lfn_chc_get_norm_name
    lfn_chc_get_norm_street.

    A few notes:

    A. for the custom dups identification, we used the business rules which are quite different than Tessitura standard ones. When we run the custom de-dup first time back in 2008, about 20,000 accounts were de-duped;

    B. for the custom dups merge, we still rely on the Tessitura's AP_MERGE_CUSTOMER2;

    C. for day-to-day dedup, we still use the Tessitura's AP_IDENTIFY_DUPLICATES .

    D. we have a customized way to treat the merged accounts interest and weight (tx_cust_tkw.Selected and tx_cust_tkw.Weight) and also marketing restrictions (t_customer.mail_ind, phone_ind and emarket_ind) through the customizable proc. LP_CONST_MERGE which is called from AP_MERGE_CUSTOMER.

    Thanks,
    Simon Basyuk,
    DBA of Carnegie Hall
    sbasyuk@carnegiehall.org

    CH_custom_dedup.zip
  • $organization
    Hi - Thank you so much - I don't see the attachment though.
    I really look forward to looking at them
     
    My direct email is rtaulty@92y.org
     
    Bob

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Simon Basyuk
    Sent: Monday, September 12, 2011 3:16 PM
    To: Robert Taulty
    Subject: Re: [Tessitura Technical Forum] Database Management - Donor Merges

    Hi Bob,

    We have such process in place. The DB procedures are (see attached):

    1. procedure lp_chc_const_dups_PREP
    gets customer duplications into custom table C_CHC_CONST_DUPS;

    2. procedure lp_chc_const_dups_PREP
    populates Tess tbl T_POTENTIAL_DUPS;

    3. procedure lp_chc_const_dups_TO_MERGE
    Prepares potential dups to merge:
    the smallest customer_no will be selected to Keep, the rest - to Delete;

    4. two service functions:
    lfn_chc_get_norm_name
    lfn_chc_get_norm_street.

    A few notes:

    A. for the custom dups identification, we used the business rules which are quite different than Tessitura standard ones. When we run the custom de-dup first time back in 2008, about 20,000 accounts were de-duped;

    B. for the custom dups merge, we still rely on the Tessitura's AP_MERGE_CUSTOMER2;

    C. for day-to-day dedup, we still use the Tessitura's AP_IDENTIFY_DUPLICATES .

    D. we have a customized way to treat the merged accounts interest and weight (tx_cust_tkw.Selected and tx_cust_tkw.Weight) and also marketing restrictions (t_customer.mail_ind, phone_ind and emarket_ind) through the customizable proc. LP_CONST_MERGE which is called from AP_MERGE_CUSTOMER.

    Thanks,
    Simon Basyuk,
    DBA of Carnegie Hall
    sbasyuk@carnegiehall.org

    From: Robert Taulty <bounce-roberttaulty1608@tessituranetwork.com>
    Sent: 9/8/2011 3:58:44 PM

    We are doing some clean-up for on our database for constituents duplicates.

     Has anyone written an automated procedure to do a mass merge of constutents? 

     I am thinking of building a local table based on an agreed upon set of rules and call the internal Tessitura procedure with the from and to constituent id's

    Bob




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
  • Former Member
    Former Member $organization in reply to Robert Taulty

    Hi Robert

    I just noticed the main automerge script wasn't there. It is now, here.

    Ken

  • Former Member
    Former Member $organization in reply to Former Member

    Hello Ken and Simon

    We are a 4 member UK consortium TNEM who went live on Tessitura in January this year. Having migrated 4 databases into one, we are also looking to have an auto-merge process to help us reduce a large number of duplicate records.  Having looked at the files and information you have posted we wondered if it would be ok to contact you directly about this to answer a few more questions that we have regarding this process?

    Thanks

    Annie Scally

     

     

  • Former Member
    Former Member $organization in reply to Former Member

    Hi Annie

    Always happy to email (or talk)

    Direct email is    ken.mcswain@aco.com.au

    Ken

     

  • $organization

    Hi Annie, please do.

     

    Simon

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Annie Scally
    Sent: Tuesday, October 04, 2011 6:57 AM
    To: Basyuk, Simon
    Subject: Re: [Tessitura Technical Forum] Database Management - Donor Merges

     

    Hello Ken and Simon

    We are a 4 member UK consortium TNEM who went live on Tessitura in January this year. Having migrated 4 databases into one, we are also looking to have an auto-merge process to help us reduce a large number of duplicate records.  Having looked at the files and information you have posted we wondered if it would be ok to contact you directly about this to answer a few more questions that we have regarding this process?

    Thanks

    Annie Scally

     

     

    From: Ken McSwain <bounce-kenmcswain5454@tessituranetwork.com>
    Sent: 9/8/2011 5:50:49 PM

    Hi Robert
    We do an auto-merge process.
    Basically, the logic is that we run a heavily customised identify potential dupes process; then feed the output into another process that takes sets of identified dupes (not just pairs); makes a decision about which one of each set to Keep based on factors that affect the assumed "well-keptness" of the record, like whether the record is a subscriber or donor, and recency of activity; creates records for each merge-pair from that set; and puts those pairs back into the Tess merge schedule table; where they are picked up by the normal scheduled (nightly, for us) merge.
    We currently run that process monthly.
    It relies on some local tables that assign merge priority rankings to constituencies and attributes, so there are a few components.
    It seemed like a fairly Brave thing to do, but it's worked well so far.
    I think all of the components of that are on my TessWeb profile files page - if not I can add them in if you'd like to have a look.

    Ken McSwain :: sent from the Blackberry
     

    From: Robert Taulty [mailto:bounce-roberttaulty1608@tessituranetwork.com]
    Sent: Friday, September 09, 2011 07:01 AM
    To: Ken McSwain
    Subject: [Tessitura Technical Forum] Database Management - Donor Merges
     

    We are doing some clean-up for on our database for constituents duplicates.

     Has anyone written an automated procedure to do a mass merge of constutents? 

     I am thinking of building a local table based on an agreed upon set of rules and call the internal Tessitura procedure with the from and to constituent id's

    Bob




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
    Read about the ACO&#x02019;s Stradivarius violin at aco.com.au/Stradivarius.
    Explore the ACO&#x02019;s 2011 season at aco.com.au/season2011

    This email is confidential. If you are not the intended recipient you must not disclose or use the information contained in it. If you have received this email in error please notify us immediately by return email and delete the document. The ACO is not responsible for any changes made to a document other than those made by the ACO or for the effect of the changes on the document's meaning. The ACO accepts no liability for any damage caused by this email or its attachments due to viruses interference, interception, corruption or unauthorised access.




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!



    Simon Basyuk
    Manager, Database Administration

    Carnegie Hall
    881 Seventh Avenue, New York, NY 10019
    Tel: 212-903-9691
    Fax: 212-581-6539
    sbasyuk@carnegiehall.org

    Carnegie Hall

    carnegiehall.org | facebook | twitter | youtube | blog

    Please consider the environment before printing this e-mail
     

    This E-mail and any attachments thereto is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential or otherwise protected from disclosure. Dissemination, distribution or copying of this E-mail or the information herein by anyone other than the intended recipient, or an employee or agent responsible for delivering the message to the intended recipient, is prohibited. If you receive this E-mail in error please immediately notify me at 212-903-9600 and permanently destroy the original E-mail message, all copies, and any printout thereof.

    [V.2007.0809.CARNEGIE.HALL.CORPORATION]