Class: Customer::SalesPriorityCalculation

Inherits:
Object
  • Object
show all
Defined in:
app/services/customer/sales_priority_calculation.rb

Constant Summary collapse

FORMULAS = 
{
  channel_ranking: {
    description: 'Pending their channel/affiliation they earn more points due to success of similar customers in the same channel',
    sql: "case when c.report_grouping in ('Trade') THEN 12 when c.report_grouping in ('Direct Buy','Dealers') THEN 10 when c.report_grouping in ('CCA Prosource USA','CCA Carpet One USA') THEN 8 when c.report_grouping in ('Shaw Flooring Alliance','BKBG','CCA Flooring America','SEN') THEN 6 ELSE 2 END",
    state: %i[customer lead prospect]
  },
  customer_status: {
    description: 'For earning the customer status',
    sql: "CASE WHEN c.state = 'customer' THEN 100 ELSE 0 END",
    state: [:customer]
  },
  trailing_twelve_month_100_dollar_increments: {
    description: 'For every 100 dollars spent at WY they earn a point.  This helps tweak accuracy of similar accounts',
    sql: "CASE WHEN c.state = 'customer' THEN round((cast(cr.trailing_twelve_months_revenue as decimal)/100))::integer ELSE 0 END",
    state: [:customer]
  },
  number_of_opportunities_0_12mo: {
    description: '3 pts for every opportunity created in the last year with at least one completed quote.',
    sql: "3 * (select COALESCE(count(*),0) from opportunities o where o.customer_id = c.id and exists(select 1 from quotes q where q.opportunity_id = o.id and q.state = 'complete') and o.created_at > now() - interval '1 year' and o.value > 300)",
    state: %i[customer lead prospect]
  },
  number_of_opportunities_12_24mo: {
    description: '1 pt for every opportunity created in the last 12-24 months with at least one completed quote.',
    sql: "(select COALESCE(count(*),0) from opportunities o where o.customer_id = c.id and exists(select 1 from quotes q where q.opportunity_id = o.id and q.state = 'complete') and o.created_at between (now() - interval '2 years') and (now() - interval '1 year') and o.value > 300)",
    state: %i[customer lead prospect]
  }
}.freeze
COMMON_FILTERS = 
[
  'c.profile_id is not null',
  "c.type = 'Customer'"
].freeze

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(options = {}) ⇒ SalesPriorityCalculation

Returns a new instance of SalesPriorityCalculation.



37
38
39
40
41
42
43
 
# File 'app/services/customer/sales_priority_calculation.rb', line 37

def initialize(options = {})
  @employee_id = options[:employee_id]
  @customer_id = options[:customer_id]
  @customer_ids = options[:customer_ids]
  @logger = options[:logger] || Rails.logger
  @logger.info "Sales Priority Calculation Initialized, employee_id: #{@employee_id}, customer_id: #{@customer_id}, customer_ids: #{@customer_ids}"
end

Class Method Details

.get_measurement_definition(key) ⇒ Object 



112
113
114
115
116
117
118
119
120
121
122
123
 
# File 'app/services/customer/sales_priority_calculation.rb', line 112

def self.get_measurement_definition(key)
  definition = begin
    FORMULAS[key.to_sym][:description]
  rescue StandardError
    nil
  end
  case key
  when 'buying_group_watch'
    definition << "(#{BuyingGroup.where(sales_watch: true).select('buying_groups.name').order('name').map(&:name).join(', ')})"
  end
  definition
end

.rank_single_customer(customer_id, logger = nil) ⇒ Object 



125
126
127
128
 
# File 'app/services/customer/sales_priority_calculation.rb', line 125

def self.rank_single_customer(customer_id, logger = nil)
  spc = Customer::SalesPriorityCalculation.new(customer_id: customer_id, logger: logger)
  spc.execute_breakdown_query_to_a
end

.score_multiple_customers(customer_ids, logger = nil) ⇒ Object 



135
136
137
138
 
# File 'app/services/customer/sales_priority_calculation.rb', line 135

def self.score_multiple_customers(customer_ids, logger = nil)
  spc = Customer::SalesPriorityCalculation.new(customer_ids: customer_ids, logger: logger)
  spc.execute_update_query
end

.score_single_customer(customer_id, logger = nil) ⇒ Object 



130
131
132
133
 
# File 'app/services/customer/sales_priority_calculation.rb', line 130

def self.score_single_customer(customer_id, logger = nil)
  spc = Customer::SalesPriorityCalculation.new(customer_id: customer_id, logger: logger)
  spc.execute_update_query
end

Instance Method Details

#build_breakdown_query ⇒ Object 



63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
 
# File 'app/services/customer/sales_priority_calculation.rb', line 63

def build_breakdown_query
  sql = %{
    select
      c.id as customer_id,
      c.full_name as customer_name,
      #{build_select_clause},
      #{build_scoring} as sales_priority_index
    from
      parties c
      inner join addresses a on a.id = coalesce(c.shipping_address_id,c.mailing_address_id,c.billing_address_id)
      inner join addresses a2 on a2.id = coalesce(c.shipping_address_id,c.mailing_address_id,c.billing_address_id)
      left join statistics_by_zips sz on sz.zip = left(a.zip,5)
      left join customer_records cr on c.id = cr.party_id
      left join (select postal_code,avg(average_annual_temperature) as average_annual_temperature from average_monthly_temperatures group by postal_code) amt on amt.postal_code = left(a.zip,5)
      inner join (select c.id,(select store_id from catalogs where catalogs.id = c.catalog_id) as store_id,coalesce(c.report_grouping, pc.report_grouping, bg.report_grouping, bg.name, p.report_grouping, p.name) as report_grouping,c.primary_sales_rep_id
                from parties c
                inner join catalogs cat on cat.id = c.catalog_id
                left join buying_groups bg on bg.id = c.buying_group_id
                left join profiles p on p.id = c.profile_id
                left join parties pc on pc.id = c.parent_id
                where c.state <> 'guest') c1 on c1.id = c.id
    where
      #{build_filter}
    order by sales_priority_index desc
  }.freeze
  @logger.debug " Select query built: \n #{sql}"
  sql
end

#build_filter ⇒ Object 



45
46
47
48
49
50
51
52
53
 
# File 'app/services/customer/sales_priority_calculation.rb', line 45

def build_filter
  filters = COMMON_FILTERS.dup
  filters << "c.primary_sales_rep_id = #{@employee_id}" if @employee_id
  filters << "c.id = #{@customer_id}" if @customer_id.present?
  filters << "c.id IN (#{@customer_ids.join(',')})" if @customer_ids.present?
  sql = filters.join(' AND ')
  @logger.debug " SQL Filter Built: #{sql}"
  sql
end

#build_scoring ⇒ Object 



55
56
57
 
# File 'app/services/customer/sales_priority_calculation.rb', line 55

def build_scoring
  "round(#{FORMULAS.map { |_k, v| v[:sql] }.join("\n + ")})::integer"
end

#build_select_clause ⇒ Object 



59
60
61
 
# File 'app/services/customer/sales_priority_calculation.rb', line 59

def build_select_clause
  FORMULAS.map { |k, v| "#{v[:sql]} as #{k}" }.join(',')
end

#execute_breakdown_query ⇒ Object 



92
93
94
 
# File 'app/services/customer/sales_priority_calculation.rb', line 92

def execute_breakdown_query
  execute_query build_breakdown_query
end

#execute_breakdown_query_to_a ⇒ Object 



96
97
98
99
 
# File 'app/services/customer/sales_priority_calculation.rb', line 96

def execute_breakdown_query_to_a
  res = execute_breakdown_query
  res.to_a
end

#execute_update_query ⇒ Object 



101
102
103
104
105
106
107
108
109
110
 
# File 'app/services/customer/sales_priority_calculation.rb', line 101

def execute_update_query
  spi_record = execute_breakdown_query_to_a
  # group by similar sales priority index for speed
  spi_record.group_by { |r| r['sales_priority_index'].to_i }.each do |spi, spi_records|
    customer_ids = spi_records.map { |r| r['customer_id'] }.compact
    @logger.info "Updating #{customer_ids.size} customers with SPI #{spi}"
    Customer.where(id: customer_ids).update_all(sales_priority_index: spi)
  end
  spi_record
end