Class: Opportunity::OutletPurchaseMatcher

Inherits:
BaseService
  • Object
show all
Defined in:
app/services/opportunity/outlet_purchase_matcher.rb

Overview

Proposes CustomerOutletPurchase links: retailer invoices whose ship-to
matches an opportunity a rep worked shortly before.

Every row lands unverified — this service never decides anything, it only
puts a candidate in front of a sales manager. It writes nothing to the
Opportunity itself.

Two match keys, both needed (see
doc/tasks/202608081330_OUTLET_PURCHASE_ATTRIBUTION.md § 2):

  • name — normalised: lowercased, legal suffixes dropped, tokens sorted, so
    Turner Electric Inc. == Turner Electric and FEDOROV ALEXANDR ==
    Alexandr Fedorov.
  • address — street + zip5, alphanumerics only. Irreplaceable for the trade
    accounts where the invoice ships to a person and the CRM holds the company
    (Danielle Lacolla ↔ Pine Cone Construction): 22% of matches, and no name
    normaliser can reach them.

Amazon FBA is deliberately unreachable here — Amazon anonymises the FBA
ship-to, so those links can only ever be added by hand.

Constant Summary collapse

DEFAULT_LOOKBACK =

How far back to look for invoices on a normal run. The nightly schedule
overlaps itself so a late-arriving invoice is not missed.

3.days
WINDOW =

Opportunities older than this before the invoice can't be the cause of it.

CustomerOutletPurchase::MATCH_WINDOW_DAYS
NAME_STOPWORDS =

Legal-entity noise that stops an otherwise exact company match.

%w[inc llc ltd co corp company the and].freeze
SALES_TYPE =

Only sales opportunities can be credited with a purchase.

A Tech ticket is the customer coming back about something they ALREADY own —
a warranty claim, a replacement part — so the causality runs backwards: the
purchase creates the ticket, not the other way round. Matching a later
retailer order to one credits a support call with a sale nobody worked.
Reference case ON855681: a replacement-part ticket opened 2025-10-24 for an
Amazon order placed 2025-10-07, matched instead to a SEPARATE Amazon order
on 2025-11-24 — the original was invisible to the sweep because it precedes
the ticket, and the one it found had nothing to do with it.

Marketing behaves the same way. The state machine already knows the
difference — win and reopen both route a non-sales opportunity to
untracked rather than won (Opportunity#sales_opportunity?), so
verifying one of these could never have produced the won the ruling
promises.

'S'

Instance Attribute Summary

Attributes inherited from BaseService

#options

Class Method Summary collapse

Instance Method Summary collapse

Methods inherited from BaseService

#log_debug, #log_error, #log_info, #log_warning, #logger, #tagged_logger

Constructor Details

#initialize(since: DEFAULT_LOOKBACK.ago, dry_run: false, **options) ⇒ OutletPurchaseMatcher

Returns a new instance of OutletPurchaseMatcher.

Parameters:

  • since (ActiveSupport::TimeWithZone, Time) (defaults to: DEFAULT_LOOKBACK.ago) —

    earliest invoice to consider

  • dry_run (Boolean) (defaults to: false) —

    when true, return candidates without writing

  • options (Hash) —

    passed to BaseService

Options Hash (**options):

  • :logger (Logger) —

    overrides Rails.logger (see BaseService)



101
102
103
104
105
 
# File 'app/services/opportunity/outlet_purchase_matcher.rb', line 101

def initialize(since: DEFAULT_LOOKBACK.ago, dry_run: false, **options)
  @since = since
  @dry_run = dry_run
  super(options)
end

Class Method Details

.local_date_sql(column) ⇒ String

Local calendar date of a timestamp column, matching what Ruby's .to_date
produces on the model.

The two must agree or the sweep proposes rows the model then rejects. Most
of these columns are legacy naive timestamp holding UTC (see AGENTS.md,
Migration safety): a bare ::date yields the UTC day, while Rails reads the
same value as America/Chicago and can land a day earlier — a 04:31 UTC
opportunity is the 15th to Ruby and the 16th to SQL, which is exactly how
INV012513359/ON5730188 came out 60 days here and 61 there. Casting to
timestamptz first (session TZ is UTC) then converting mirrors
view_opportunity_conversions.

Parameters:

  • column (String) —

    SQL expression yielding the timestamp

Returns:

  • (String) —

    SQL expression yielding a date



82
83
84
85
 
# File 'app/services/opportunity/outlet_purchase_matcher.rb', line 82

def self.local_date_sql(column)
  zone = ActiveRecord::Base.with_connection { |conn| conn.quote(Time.zone.tzinfo.name) }
  "timezone(#{zone}, (#{column})::timestamptz)::date"
end

.normalized_address_sql(street, zip) ⇒ String

Returns SQL expression.

Parameters:

  • street (String) —

    SQL expression yielding street1

  • zip (String) —

    SQL expression yielding the postal code

Returns:

  • (String) —

    SQL expression



90
91
92
93
94
95
 
# File 'app/services/opportunity/outlet_purchase_matcher.rb', line 90

def self.normalized_address_sql(street, zip)
  <<~SQL.squish
    upper(regexp_replace(COALESCE(#{street}, ''), '[^a-zA-Z0-9]', '', 'g'))
      || '|' || left(upper(regexp_replace(COALESCE(#{zip}, ''), '[^a-zA-Z0-9]', '', 'g')), 5)
  SQL
end

.normalized_name_sql(column) ⇒ String

Sorted, punctuation-free, stopword-free name. Sorting the tokens makes the
comparison order-insensitive, which is what catches FEDOROV ALEXANDR.

Parameters:

  • column (String) —

    SQL expression yielding the raw name

Returns:

  • (String) —

    SQL expression



57
58
59
60
61
62
63
64
65
66
 
# File 'app/services/opportunity/outlet_purchase_matcher.rb', line 57

def self.normalized_name_sql(column)
  <<~SQL.squish
    (SELECT string_agg(t, ' ' ORDER BY t)
       FROM unnest(string_to_array(
         btrim(regexp_replace(
           regexp_replace(lower(COALESCE(#{column}, '')), '[^a-z ]', ' ', 'g'),
           '\\y(#{NAME_STOPWORDS.join('|')})\\y', ' ', 'g')), ' ')) t
      WHERE t <> '')
  SQL
end

Instance Method Details

#process ⇒ Array<CustomerOutletPurchase>

Returns rows created (or, when
dry_run, unsaved rows that would have been).

Returns:



109
110
111
112
113
114
115
116
 
# File 'app/services/opportunity/outlet_purchase_matcher.rb', line 109

def process
  candidates.filter_map do |row|
    purchase = build_candidate(row)
    next purchase if @dry_run

    persist(purchase)
  end
end