• Replies 11 replies
  • Subscribers 414 subscribers
  • Views 301 views
  • Users 0 members are here
Related

Duplicates and how to manage

Stephanie Kemp (Past Member)
$organization

Hi, new to Tessitura as of December last year, though not new to databases.

We have a huge amount of duplicates, which seem to mostly be the result of donors logging in to buy tickets and setting up new records for themselves. Today I came across a record of a man who apparently has four daughters named Ruby! Each time new tickets (for classes in this case) have been booked, a new record is set up.

I know how to merge duplicates. My question is more around best practice in catching these and fixing as they come up, and also if anyone can shed any light as to why this happens and if we can do anything from the ticketing side to prevent it??

Any thoughts gratefully received.

  • $organization

    Good morning Stephanie,

    Welcome (somewhat belatedly) to the Tessitura Network!

    I don't know that I necessarily have any magic bullet solution to assist with regard to avoiding duplicates, though I can speak to how we manage them in our specific consortium environment.

    We use Freshdesk for our support ticket/help desk solution and have specific forms set up so that when our users find duplicate accounts, they can report them to us and let us know which account needs to remain and which needs to merge into that one (plus space for any specific directions). Our consortium staff (which is small-yet-mighty and numbers 3 employees including me) then acts on these merge requests from our 90+ users at 7 organizations.

    Do you all use TNEW for online purchasing? If so, having logins assigned to your users should help to avoid the issue you have with people creating duplicates with every new online transaction, so long as people are using the same email address to login each time.

    Thank you,

    Brian

  • $organization

    Hi Stephanie,

    Could you let us know what your online cart is operating on - is it Tnew? (Tessitura's web cart)

    Megan

  • $organization

    Hello, Stephanie,

    Tessitura does have some procedures to identify potential duplicates for you to confirm and schedule the merges.  However, this identification process was halted by request from someone in your organization in 2013 in order to work on a large backlog of duplicates and never re-enabled.  If you'd like to revisit how that process works and potentially re-enable it, please open a support help ticket and we'll be happy to review it with you.  

    It'd also be helpful to review the help system topic on Duplicate Management, which includes some best practices suggestions. 

    TNEW generally shouldn't permit a duplicate login with the same address and login type.  Instead, it should direct the patron to the account management page to retrieve their login info.  
    The duplicates you're seeing may be the result of guest checkout or an educational customization that's building the customer records.

  • $organization in reply to Brian Parker (Past Member)

    Thanks Brian, appreciate your thoughts on this! Definitely think it's worth us setting up a system re merging so that we don't have too many hands in it and have a clear structure. We do use TNew and assigned logins...and have some users using different email addresses, which gets a bit tricky :-)

  • $organization in reply to Brian Pedaci

    Thanks Brian, that's really helpful to know. I will open a support help ticket to review this option for sure!

  • $organization

    Stephanie,

    We are also on TNEW and see a lot of duplicates for the same reason! One of the ways we try to catch them early is through the New Record Summary report under Data Management. I set up a schedule for the report to run everyday and then our data entry coordinator checks it and merges any dupe records. It doesn't take long to do, a couple minutes each day and it keeps from adding to the back log.

    I don't really have any advice on how to stop them or how to prevent them, Brian Pedaci pointed you to good practices, but its just a part of database life. One of the things you can do is train anyone entering records to do a through search before adding a new record. We tell our front line people to search for names, addresses, and emails before adding a new record. Online is a little harder to manage, which is where the new record summary report comes in handy.

    Melissa

  • $organization
    If your original record has an email address, but not a web login, creating a temporary web login will help reduce the number of duplicates created.
     
    Here’s a handy-dandy script that will help.  Have it modified for your organization and run it in Test first.
     
    USE [impresario]
    GO
    /****** Object:  StoredProcedure [dbo].[lp_create_logins]    Script Date: 03/09/2011 14:06:05 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    Alter procedure [dbo].[lp_create_logins]
    AS
    /*           Creates logins for all email addresses that don't have one. 
     
                    The code below needs to be modified for specific site needs. At a minimum, the login and email types
                    need to be filled in with the approrpiate values.  Other modifications, such as only looking at primary
                    emails, can also be easily made in the Cursor query.  Also, the default password can be determined
                    by changeing the query in the Insert statement.
                   
                    Created by David Woodall
     
    */
     
    begin tran
    --
    -- Declare some needed variables
    Declare @customer_no int,
                                    @email varchar(80),
                                    @eaddress_no int,
                                    @new_login_no int,
                                    @login_type int,
                                    @eaddress_type int
                                   
    --
    -- Set the login type that should be created and the email type from which is should be created.
    select    @login_type = 1,
                                    @eaddress_type = 1
     
    -- This cursor gets all emails without logins, as of now.....
    DECLARE mycur INSENSITIVE CURSOR FOR
    SELECT e.customer_no, e.address, e.eaddress_no
                    from t_eaddress e
                    where eaddress_type = @eaddress_type
                    and inactive <> 'Y'            -- and primary_ind = 'Y'
                    and e.address not in (select login from t_cust_login where login_type = @login_type)
     
    OPEN mycur
     
    FETCH mycur INTO @customer_no, @email, @eaddress_no
                   
    While @@fetch_status >= 0
    BEGIN
     
    --             Only add a login if one does not already exist. We do not want dups, so we double check
    --             we have not added one since the cursor select was executed.
                    if not exists (select * from t_cust_login where login = @email and login_type = @login_type)
                      begin
     
    --                             New Login ID Value
                                    Exec @new_login_no = [dbo].ap_get_nextid_function @type = 'LO'
     
    --                             New Login is inserted
                                    insert into t_cust_login(login_no, customer_no, login_type, login, password, eaddress_no, primary_ind, temporary_ind )
                                                    select @new_login_no, @customer_no, @login_type,
                                                                                    @email as login,
                                                                                    convert(varchar,@customer_no)+'pass123' as password,
                                                                                    @eaddress_no,
                                                                                    'Y' as primary_ind,
                                                                                    'Y' as temporary_ind
                      end
     
                    FETCH NEXT FROM mycur INTO @customer_no, @email, @eaddress_no
    END
     
    CLOSE mycur
    DEALLOCATE mycur
     
    select * from t_cust_login
    order by login
     
    commit tran
     
     
  • $organization in reply to Melissa Scott

    Thanks Melissa, that's great advice! I will look into setting up that New Record Summary report!

  • $organization in reply to Dot Krebs (Past Member)

    Wow! Thanks Dot, that is very kind of you to pass on.

  • $organization in reply to Stephanie Kemp (Past Member)

    Hi Stephanie,

    If you go this route, which will certainly prevent a bunch of duplicate accounts, be sure that you communicate people's log-ins in future emails that ask that they take action. Typically I see orgs do it under the call to action button. "Your log-in is megan.hall@seattlesymphony.org."

    Without this step people will think that they don't have a login and they won't understand why Tnew is preventing them from creating an account.

    Good luck!
    Megan