Class: Customer::UpdateProfileStats

Inherits:
BaseService show all
Defined in:
app/services/customer/update_profile_stats.rb

Defined Under Namespace

Classes: Result

Constant Summary collapse

FIRST_ORDER_DATE_VALUE_MINIMUM = 
200
UPDATE_STATS_OPTIONS = 
{
  profiling_data_last_calculated_at: "now() AT TIME ZONE 'UTC'",
  first_order_date: "(select min(i.gl_date) from invoices i where i.customer_id = customer_records.party_id and i.invoice_type = 'SO')",
  customer_since: "(select min(o.shipped_date) from orders o where o.customer_id = customer_records.party_id and o.order_type = 'SO' and o.state = 'invoiced' and o.line_total >= #{FIRST_ORDER_DATE_VALUE_MINIMUM})",
  last_order_date: "(select max(i.gl_date) from invoices i where i.customer_id = customer_records.party_id and i.invoice_type = 'SO')",
  last_opportunity_date: "(select max(o.created_at) from opportunities o where o.customer_id = customer_records.party_id and o.opportunity_type = 'S')",
  trailing_twelve_months_revenue: "(select sum(i.revenue_consolidated) from invoices i where i.customer_id = customer_records.party_id and i.invoice_type = 'SO' and i.gl_date > (now() - INTERVAL '1 year' ))",
  lifetime_revenue: "(select sum(i.revenue_consolidated) from invoices i where i.customer_id = customer_records.party_id and i.invoice_type = 'SO')",
  lifetime_number_of_orders: "(select count(i.id) from invoices i where i.customer_id = customer_records.party_id and i.invoice_type = 'SO')",
  trailing_twelve_months_number_of_orders: "(select count(i.id) from invoices i where i.customer_id = customer_records.party_id and i.invoice_type = 'SO' and i.gl_date > (now() - INTERVAL '1 year' ))",
  lifetime_number_of_opportunities: "(select count(opp.id) from opportunities opp where opp.customer_id = customer_records.party_id and opp.opportunity_type = 'S')",
  lifetime_number_of_won_opportunities: "(select count(opp.id) from opportunities opp where opp.customer_id = customer_records.party_id and opp.opportunity_type = 'S' and opp.state = 'won')",
  trailing_twelve_months_number_of_opportunities: "(select count(opp.id) from opportunities opp where opp.customer_id = customer_records.party_id and opp.opportunity_type = 'S' and opp.created_at > (now() - INTERVAL '1 year' ) )",
  trailing_twelve_months_number_of_won_opportunities: "(select count(opp.id) from opportunities opp where opp.customer_id = customer_records.party_id and opp.opportunity_type = 'S' and opp.state = 'won' and opp.created_at > (now() - INTERVAL '1 year' ) )",
  last_completed_sales_activity_date: %{ (
    select max(a.completion_datetime)
    from activities a
    inner join activity_types at on a.activity_type_id = at.id
    inner join taggings tg on tg.taggable_id = at.id and tg.taggable_type = 'ActivityType'
    inner join tags t on t.id = tg.tag_id
    where LOWER(t.name) = 'sale'
      and a.activity_result_type_id IS NOT NULL
      and a.activity_result_type_id <> 1
      and a.completion_datetime IS NOT NULL
      and a.customer_id = customer_records.party_id
  ) },
  a12net_rev: %{ (
    select sum(i.line_total) + sum(coalesce(cm.line_total,0))
    from invoices i
    left join rmas r on i.id = r.original_invoice_id
    left join credit_memos cm on r.id = cm.rma_id
    where i.customer_id = customer_records.party_id
    and i.gl_date between '#{Date.current.months_ago(15)}' and '#{Date.current.months_ago(3)}'
    and i.invoice_type = 'SO'
    and i.state = 'paid'
  ) }
}
UPDATE_CONVERSION_RATE_SQL = 
<<-EOS
  trailing_twelve_months_conversion_rate = CASE WHEN trailing_twelve_months_number_of_opportunities > 0 THEN trailing_twelve_months_number_of_won_opportunities::decimal * 100 / trailing_twelve_months_number_of_opportunities ELSE 0 END,
  lifetime_conversion_rate = CASE WHEN lifetime_number_of_won_opportunities > 0 THEN lifetime_number_of_won_opportunities::decimal * 100 / lifetime_number_of_opportunities ELSE 0 END
EOS
INSERT_CUSTOMER_RECORDS_SQL = 
<<-EOS
  insert into customer_records (party_id, created_at, updated_at)
    select id, now() AT TIME ZONE 'UTC', now() AT TIME ZONE 'UTC'
    from parties
    where type = 'Customer'
      and state <> 'guest'
EOS

Instance Method Summary collapse

Methods inherited from BaseService

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

Constructor Details

This class inherits a constructor from BaseService

Instance Method Details

#process(customer_ids: [], batch_size: 100, limit: nil, min_age: 24.hours.ago, stat_keys: nil) ⇒ Object 



59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
 
# File 'app/services/customer/update_profile_stats.rb', line 59

def process(customer_ids: [], batch_size: 100, limit: nil, min_age: 24.hours.ago, stat_keys: nil)
  populate_missing_customer_records(customer_ids: customer_ids)
  customer_records = CustomerRecord.all.select(:id, :party_id)
  if customer_ids.present?
    customer_records = customer_records.where(party_id: [customer_ids].flatten.compact)
  else
    customer_records = customer_records.where(CustomerRecord[:profiling_data_last_calculated_at].eq(nil).or(CustomerRecord[:profiling_data_last_calculated_at].lt(min_age)))
  end
  customers_updated = 0
  customer_records.find_in_batches(batch_size: batch_size).with_index do |group, batch|
    puts "Batch #{batch}"
    group_cids = group.map(&:party_id)
    customers_updated += update_stats(customer_ids: group_cids, stat_keys: stat_keys)
  end
  Result.new(success: true, customers_updated: customers_updated )

end