CRM'de aynı müşteri üç kez kayıtlı: mükerrer kayıtlar nasıl bulunur ve birleştirilir?

Aynı müşterinin sistemde iki, bazen üç kaydının olması bir veri girişi dikkatsizliği değil. Kayıt oluşturma yolunda hiçbir engel olmamasının doğal sonucu. Temizlik işi de üç ayrı adımdan oluşuyor ve bunları birbirine karıştırmak projenin en pahalı hatası: önce karşılaştırılabilir bir anahtar üretirsiniz (normalleştirme), sonra hangi kayıt çiftlerinin aday olduğunu bulup bir benzerlik skoru verirsiniz, en sonunda hangi değerin hayatta kalacağına ve eski kimliğin nereye gideceğine karar verirsiniz. Asıl kazanç bu listenin hiçbir maddesinde değil. Bugün 4.000 mükerrer kaydı temizleyip giriş yolunu aynı bırakırsanız, altı ay sonra aynı tabloyu yeniden sayarsınız.

Faturanın kesildiği yer veritabanı değil

Mükerrer kaydın maliyeti disk alanında görünmez. Aynı firmanın iki cari kartı varsa biri 30 gün vadeli, diğeri peşin çalışıyordur ve satış hangisini açarsa o koşul geçerli olur. Risk limiti ikiye bölünür, yani limitin iki katı kadar açık hesap verebilirsiniz. Ekstre eksik çıkar. Destek tarafında temsilci müşterinin geçmişinin yarısını görür ve "daha önce bu konuyu açmış mıydınız" diye sorar. Raporlamada ise müşteri sayısı şişer, müşteri başına ortalama sipariş düşer ve kimse neden düştüğünü açıklayamaz. Her raporun başka sayı vermesi probleminin bir ayağı neredeyse her zaman buradadır.

Bu konuda en çok dolaşan rakam Gartner'ın 2020 tarihli veri kalitesi çalışmasındaki yıllık 12,9 milyon dolarlık maliyet. Kullanmadan önce kaynağını bilin: rakam, veri kalitesi yazılımı satın almış 154 referans müşterinin kendi tahminine dayanıyor. Yani problemi zaten olduğunu düşünen büyük şirketlerin beyanı. Sizin şirketinizin sayısı bu değil, ve bulmanın yolu da tahmin değil, bir sonraki bölümdeki sorguyu çalıştırmak.

Aslında iki ayrı problem var, çözümleri de ayrı

Birincisi teknik ikizleme. Tek bir gerçek olay, sistemde iki kez işlenmiştir: webhook yeniden denemesi, kullanıcının kaydet düğmesine iki kez basması, aynı içe aktarma dosyasının iki kez yüklenmesi. Burada iki kaydın alanları genelde harfi harfine aynıdır. Çözümü eşleştirme değil, idempotent işleme ve veritabanı seviyesinde eşsizlik kısıtıdır; entegrasyonlarda veri senkronizasyonu yazısında bu tarafı ayrıntılı anlattık.

İkincisi anlamsal mükerrerlik. İki ayrı insan, iki ayrı zamanda, aynı gerçek dünya varlığını farklı yazmıştır. "Yılmaz Gıda San. ve Tic. Ltd. Şti.", "Yılmaz Gıda Ltd" ve "YILMAZ GIDA SANAYI VE TICARET" aynı firmadır ama hiçbir karakter dizisi karşılaştırması bunu kendiliğinden söylemez. Asıl eşleştirme problemi budur.

Ayrımı baştan yapın. Bulanık eşleştirme altyapısı kurup birinci problemi çözmeye çalışmak, kapıyı açık bırakıp yerleri silmeye benzer.

Karşılaştırmadan önce iki kaydı aynı dile çevirin

Eşleştirmenin ucuz kısmı burası ve en çok atlanan kısmı da burası. Baştaki ve sondaki boşluklar, iki kelime arasındaki çift boşluk, noktalama, firma unvanındaki ekler (A.Ş., Ltd. Şti., San., Tic., "ve") karşılaştırma için gürültüdür. Bunları temizlenmiş bir anahtar kolonunda saklayın, orijinal değeri bozmayın.

Unicode tarafında bir tuzak var. Aynı görünen iki metin farklı kod noktalarından oluşabilir, bu yüzden karşılaştırmadan önce normalleştirme formu uygulanır. PostgreSQL'de normalize(text, NFC) bunu yapar, varsayılan form NFC'dir ve fonksiyon yalnızca sunucu kodlaması UTF8 iken çalışır. Belgeler ayrıca IS NORMALIZED kontrolünün çoğu zaman doğrudan normalleştirmekten hızlı olduğunu söylüyor, çünkü zaten normal olan metni yeniden işlemezsiniz.

Türkçe'de ise harf durumu tek başına bir mayın tarlası. Unicode'un SpecialCasing.txt dosyasına göre "İ" (U+0130) harfinin dil bağımsız küçük harf karşılığı tek karakter değil, iki kod noktasıdır: 0069 0307, yani "i" artı birleşen üst nokta. Türkçe (tr) ve Azerice (az) yerelinde ise aynı harf düz "i" olur ve "I" harfi "ı"ya döner. Sonuç şu: aynı ismi iki farklı sunucu yerelinde küçük harfe çevirirseniz iki farklı anahtar elde edersiniz ve ekranda ikisi de aynı görünür. Microsoft'un metin karşılaştırma kılavuzu buna doğrudan "Turkish-I problemi" diyor ve çarpıcı örneği veriyor: Türkçe yerelde "Bill" metninin "BILL" ile başlayıp başlamadığı sorusunun cevabı False dönüyor.

Pratik kural: eşleştirme anahtarını üretirken yerel ayara bağlı lower() çağrısına güvenmeyin. Dönüşümü kendiniz, açıkça tanımlayın (İ ve I harflerini hangi harfe indirdiğinizi yazın), sonucu üretilmiş bir kolonda saklayın ve o kolona indeks koyun. Aynı kuralı aksan katlamada da uygulayın: ş→s, ı→i, ğ→g, ü→u, ö→o, ç→c. Bu dönüşüm "Şahin" ile "Sahin"i, "Çetin" ile "Cetin"i aynı kovaya koyar.

Güvenilir bir anahtarınız varsa iş zaten bitmiştir

Vergi numarası, e-posta, telefon ve alan adı dört ana adaydır. Her birinin kendi tuzağı var.

Vergi numarası Türkiye'de tek formatta değil. Tüzel kişilerde 10 haneli VKN, gerçek kişilerde ise 11 haneli T.C. kimlik numarası vergi kimlik numarası yerine geçiyor. Yani kolon tek, format iki. Hane sayısına göre ayırın, her ikisinin de kontrol hanesi algoritmasını uygulayın ve sıfırla başlayan numaralar için metin tipi kullanın. Sayı tipine çevrilen her vergi numarası er geç baştaki sıfırını kaybeder.

Telefonda hedef E.164 biçimi: artı işareti, ülke kodu ve en fazla toplam 15 hane. "0532 111 22 33", "+90 532 111 2233" ve "905321112233" tek bir değere inmeli. Kütüphane kullanın, kendi düzenli ifadenizi yazmayın. Bir uyarı: "son 7 hanesi aynıysa aynı numaradır" kısayolu ülke sayısı arttıkça çakışmaya başlar, ayrıca santral numaralarında dahili hatlar birbirine karışır.

E-posta tarafında RFC 5321 bölüm 2.4 net konuşuyor: posta kutusunun yerel kısmı büyük/küçük harfe duyarlı sayılmak zorundadır, alan adı ise DNS kurallarına tabi olduğu için duyarlı değildir. Aynı metin "bu duyarlılığı kullanmak birlikte çalışabilirliği zorlaştırır ve tavsiye edilmez" diye ekliyor. Pratikte alan adını her zaman küçük harfe indirin, yerel kısmı da indirin ama bunun teknik bir varsayım olduğunu bilin. Nokta kaldırma kuralını ise tüm alan adlarına uygulamayın: Google yalnızca @gmail.com adreslerinde noktaları yok sayıyor, kurum alan adlarında (Workspace dahil) nokta adresi değiştiriyor.

Bir de ortak kutu tuzağı var. Küçük şirketlerde üç farklı kişi info@ adresini paylaşır. HubSpot kişileri e-posta adresine göre otomatik tekilleştirir, firmaları da alan adına göre. Yani ortak kutuyu kişi kaydına yazan ekip, farkında olmadan üç kişiyi tek kayda indirir. Kişi tablosunda e-postayı eşsiz anahtar yapmadan önce bu senaryoyu sorun.

Anahtar yoksa: önce aday üretin, sonra karşılaştırın

Herkesin aklına gelen ilk çözüm her kaydı her kayıtla karşılaştırmak ve bu çözüm ilk denemede ölür. Karşılaştırma sayısı n(n-1)/2 ile büyür. Splink belgelerinin verdiği örnek şu: 1 milyon kayıtlık bir veri kümesi yaklaşık 500 milyar kayıt çifti üretir.

Çözüm bloklama. Önce ucuz bir kuralla aday havuzunu daraltırsınız, pahalı benzerlik hesabını yalnızca o havuzda yaparsınız. İyi blok kuralları: aynı il artı firma adının ilk üç harfi, vergi numarasının ilk dört hanesi, telefonun son yedi hanesi, e-posta alan adı. Tek bir blok her zaman bir şeyleri kaçırır, bu yüzden birkaç blok kuralını arka arkaya çalıştırıp sonuçları birleştirin. Aynı belgedeki kural da şu yönde: uzun ve sıkı bir blok kuralı listesi, kısa ve gevşek bir listeden iyidir.

Türkçe isimlerde fonetik eşleştirme çalışmaz

Benzerlik için PostgreSQL'in iki hazır eklentisi var ve ikisi arasındaki seçim Türkçe veride net.

pg_trgm metni üçlü harf gruplarına böler ve similarity() ile 0 ile 1 arasında bir skor verir. Belgelerdeki varsayılan eşik pg_trgm.similarity_threshold = 0.3, % operatörü de bu eşiğe göre çalışır. En önemlisi GIN ve GiST indeks desteği olması; indekssiz benzerlik sorgusu milyonluk tabloda kullanılamaz.

fuzzystrmatch ise levenshtein, soundex, metaphone ve daitch_mokotoff sunuyor. Burada belgelerin kendi uyarısı kararı veriyor: soundex, metaphone, dmetaphone ve dmetaphone_alt fonksiyonları UTF-8 gibi çok baytlı kodlamalarla iyi çalışmıyor, bu tür veride daitch_mokotoff veya levenshtein kullanılmalı. Üstüne soundex için ayrıca "İngilizce olmayan isimlerde pek işe yaramaz" notu var. Yani Türkçe müşteri isimlerinde fonetik kodlama yolunu baştan kapatın. Kalan yol belli: aksan katlanmış metin üzerinde trigram benzerliği, kısa alanlarda levenshtein (255 karakter sınırı var, uzun unvanlarda dikkat).

Hazır CRM kuralları da aynı sınırı taşıyor. Salesforce'un bulanık eşleştirme yöntemlerinden Fuzzy: Last Name Exact, Keyboard Distance ve Metaphone 3 algoritmalarını 90 eşiğiyle kullanıyor. Metaphone İngilizce telaffuz için tasarlandı. Türkçe soyadlarında bu kuralın yakalama oranı sizin beklentinizin altında kalır, o yüzden hazır gelen eşikleri kendi verinizde ölçmeden kabul etmeyin.

Skoru tek alana bakarak vermeyin

Eşleştirmenin matematiği 1969'da Ivan Fellegi ve Alan Sunter'ın makalesiyle oturdu ve bugünkü araçların çoğu hâlâ o modeli kullanıyor. Fikir basit. Her alan için iki olasılık tahmin edersiniz: iki kayıt gerçekten aynı kişiyse bu alanın tutma olasılığı (m) ve iki kayıt farklı kişilere aitken bu alanın tesadüfen tutma olasılığı (u).

Splink'in anlatımındaki sayılar sezgiyi oturtuyor: doğum tarihi için m yaklaşık 0,98 (aynı kişide bile yüzde iki veri hatası payı), soyadı için u yaklaşık 0,005 (iki yabancının soyadının tutması nadir). Her alanın katkısı log₂(m/u) ile bir ağırlığa çevrilir ve ağırlıklar toplanır. Aynı kaynaktaki ölçek: toplam ağırlık 2 ise eşleşme olasılığı 0,8, 7 ise 0,99.

Bunun günlük karşılığı şu: "aynı şehirde" olmak neredeyse hiçbir şey söylemez, "aynı vergi numarası" tek başına belirleyicidir, "benzer firma adı artı aynı telefon" ikisinin toplamından güçlüdür. Alanları eşit ağırlıkta toplayan bir skor fonksiyonu yazmayın.

Çıktıyı tek eşikle değil iki eşikle kullanın. Üst eşiğin üstü otomatik birleşir, ara bant bir insan kuyruğuna düşer, altı yok sayılır. Ara bandın genişliği ekibin günde kaç çift inceleyebildiğine göre ayarlanır.

Yanlış birleştirme, mükerrer kayıttan pahalıdır

Bu cümleyi projenin başında yazıya geçirin. İki ayrı firmanın cari hesabını birleştirdiğinizde borç bakiyesi, açık siparişler, sözleşmeler ve destek geçmişi iç içe geçer. Ayırmak, birleştirmekten kat kat zordur ve çoğu sistemde mümkün değildir.

Pratik sonucu şu: kesinlik, kapsamdan önce gelir. Otomatik birleştirme eşiğini yüksek tutun, ara bandı geniş bırakın, "kaçırdığımız mükerrer kalsın" demeyi göze alın. Ve bir soruyu önceden cevaplayın: bu birleştirmeyi geri alabiliyor muyuz? Cevap hayırsa otomatik birleştirme yapmayın, insan onayına bağlayın.

Hangi değer hayatta kalır

Birleştirme ekranının asıl kararı burada veriliyor ve bu bir yazılım kararı değil, iş kararı. Alan bazında kaynak önceliği belirleyin: resmi unvan ve vergi dairesi muhasebe sisteminden, teslimat adresi ERP'den, e-posta en son doğrulanmış olandan gelsin.

İki kural neredeyse her projede geçerli. Birincisi, boş değer dolu değeri ezmez. İkincisi, "en son güncellenen kazanır" kuralı kulağa adil gelir ama yanlıştır: gece üçte çalışan bir entegrasyonun yazdığı boş alan teknik olarak en son güncellenendir. Zaman damgasına değil, kaynağa bakın.

Geçmiş kayıtlara dokunmayın. Kesilmiş faturanın üstündeki unvan, o tarihteki unvandır; birleştirme sırasında güncellenmemelidir. Veri modelinde bu ayrım yapılmadıysa birleştirme işi tahmin ettiğinizden uzun sürer. Kararın kendisi de kayıt altına alınmalı: hangi kayıtlar, hangi kural, kim, ne zaman ve önceki değerler ne idi. Denetim izi olmadan yapılan toplu birleştirme, altı ay sonra kimsenin açıklayamadığı veri haline gelir.

Silmeyin, yönlendirin

Birleştirilen kaydı tablodan silmek en kolay ve en pahalı seçenek. O kimlik dışarıda yaşıyor olabilir: pazaryeri entegrasyonunda, muhasebe yazılımının eşleme tablosunda, kargo firmasının sisteminde, geçen hafta gönderilmiş bir bağlantıda.

Doğrusu kaydı bir mezar taşına çevirmek: satır kalır, merged_into_id alanı yeni kaydı gösterir, durumu birleştirildi olur. Eski kimlikle gelen API isteği hata dönmek yerine yeni kayda yönlendirilir ya da cevapta güncel kimliği taşır. Eski sistemden veri taşırken eski kimlikleri saklama tavsiyesinin aynısı burada da geçerli.

Araçların davranışı da bu yönde. Salesforce'ta merge işlemi aynı anda en fazla üç kaydı birleştirir, ilişkili kayıtları ana kayda bağlar ve diğerlerini siler. Üç sayısı tesadüf değil; geri alınamayan bir işlemde toplu çalıştırmayı zorlaştırmak bilinçli bir tasarım.

Asıl iş: yeni mükerrerin doğmasını engellemek

Temizlik bir proje, önleme bir ürün özelliği. Dört yerde iş yapılır.

Veritabanında: normalleştirilmiş anahtar için üretilmiş bir kolon ve üstünde eşsiz indeks. Kontrolü yalnızca uygulama katmanında yapan kod, iki isteğin aynı anda geldiği durumda kaybeder; kısıtı veritabanı uygulamalı.

Arayüzde: "Yeni müşteri" düğmesini ilk adım olmaktan çıkarın. Önce arama, bulunamazsa oluşturma. Arama kutusu da yazım hatasına toleranslı olmalı, yoksa kullanıcı aradığını bulamayıp yeni kayıt açar; site içi aramanın bu kadar belirleyici olmasının sebebi bu.

İçe aktarmada: dosyada dış kimlik kolonu zorunlu olsun ve yükleme ekle değil, varsa güncelle (upsert) mantığıyla çalışsın.

Entegrasyonda: her sistem kendi kimliğini taşısın, eşleme tablosu tek yerde dursun. Karşı tarafın kayıt kimliğini saklamayan bir entegrasyon, her senkronizasyonda yeni kayıt üretmeye adaydır.

Bu haftaya sığan ilk adım

Üç sorgu yazın, hepsi birer satır. Normalleştirilmiş firma adına göre, telefonun son dokuz hanesine göre ve e-posta adresine göre gruplayıp birden fazla kaydı olanları sayın. Bu üç sayı, ekibin "bizde biraz mükerrer var" tahmininin yerine geçecek ilk gerçek veridir.

Sonra çıkan listeden rastgele 20 çift seçin ve elle bakın: kaçı gerçekten aynı müşteri? Bu oran, kuracağınız eşleştirmenin kesinlik tahmini olur ve eşikleri konuşurken elinizdeki tek sağlam sayıdır. Aynı 20 çift, birleştirme kurallarını da yazdırır; hangi alanda hangi kaynağa güveneceğinizi gerçek örnek üstünde tartışmak soyut kural yazmaktan hızlıdır.

Üçüncü adım en net anahtarınıza eşsiz indeks koymak. Çoğu şirkette bu vergi numarası oluyor. İndeks ilk denemede hata verir, çünkü mevcut veride çakışan satırlar vardır. O hata listesi zaten temizlik kuyruğunuzun ilk sayfasıdır. Elinizde bu üç sayı ve 20 çiftlik inceleme varken bir görüşmeye oturmak, "mükerrer kayıt sorunumuz var" cümlesiyle başlamaktan çok daha hızlı sonuç verir.