Class: Query::OrderQuery

Inherits:
BaseQuery
  • Object
show all
Defined in:
app/services/query/order_query.rb

Overview

Service object: order query.

Constant Summary collapse

ABANDONED_CART_CADENCE =

Inactivity threshold for each touch of the 3-step abandoned-cart reminder
sequence, keyed by the order's CURRENT reminder state — the next reminder
only becomes eligible once the cart has sat untouched this long. Lands the
touches at roughly 24h / 3d / 7d after abandonment.

Reminders sent under ~1h after abandonment depress conversion versus no
reminder at all; the research-optimal window is 24–72h. The first touch
therefore waits a full day rather than the previous 1 hour.

{
  "reminder_not_sent" => 24.hours,
  "first_reminder" => 3.days,
  "second_reminder" => 7.days
}.freeze

Instance Method Summary collapse

Constructor Details

#initialize(relation = nil) ⇒ OrderQuery

Returns a new instance of OrderQuery.



5
6
7
 
# File 'app/services/query/order_query.rb', line 5

def initialize(relation = nil)
  super(relation || Order.all)
end

Instance Method Details

#abandoned_carts(reminder_state = "reminder_not_sent") ⇒ Object 



71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
 
# File 'app/services/query/order_query.rb', line 71

def abandoned_carts(reminder_state = "reminder_not_sent")
  reminder_state = reminder_state.to_s
  inactive_for = ABANDONED_CART_CADENCE.fetch(reminder_state)

  joins = <<-SQL.squish
    inner join parties customers on customers.id = orders.customer_id
  SQL

  min_last_visit_query = <<-SQL.squish
    (select time from visit_events inner join visits on visits.id = visit_events.visit_id where visits.user_id = customers.id order by time desc limit 1) < ?
  SQL

  max_last_visit_query = <<-SQL.squish
    (select time from visit_events inner join visits on visits.id = visit_events.visit_id where visits.user_id = customers.id order by time desc limit 1) > ?
  SQL

  has_email_query = <<-SQL.squish
    (
      exists(select 1 from contact_points where party_id = customers.id and category = 'email')
      or
      exists(select 1 from contact_points inner join parties on parties.id = contact_points.party_id where parties.customer_id = customers.id and category = 'email')
    )
  SQL

  has_items_query = <<-SQL.squish
    (exists(select 1 from line_items where resource_type = 'Order' and resource_id = orders.id and line_items.tax_class = 'g'))
  SQL

  no_recent_orders_query = <<-SQL.squish
    NOT EXISTS (SELECT 1 FROM orders past_orders WHERE past_orders.customer_id = customers.id AND past_orders.state NOT IN ('cart', 'cancelled')  AND past_orders.created_at >= ?)
  SQL

  inactive_since = inactive_for.ago
  # Require a real on-site visit within the week leading up to the cart going
  # quiet: recent enough to retarget, but not so recent the shopper is still
  # active (which would reset the inactivity clock). The ceiling widens per
  # stage so the later touches (3d / 7d) aren't excluded by a fixed window.
  earliest_visit = (inactive_for + 1.week).ago

  @relation.joins(joins)
           .where(state: 'cart')
           .where(abandoned_cart_reminder: reminder_state)
           .where(has_items_query)
           .where(orders: { updated_at: ...inactive_since })
           .where(min_last_visit_query, inactive_since)
           .where(max_last_visit_query, earliest_visit)
           .where(has_email_query)
           .where(no_recent_orders_query, 30.days.ago)
end

#average_invoiced_revenue ⇒ Object 



9
10
11
 
# File 'app/services/query/order_query.rb', line 9

def average_invoiced_revenue
  @relation.invoiced.average(:line_total).round(2)
end

#invoiced_count ⇒ Object 



24
25
26
 
# File 'app/services/query/order_query.rb', line 24

def invoiced_count
  @relation.invoiced.count
end

#invoiced_count_between(start_range, end_range) ⇒ Object 



34
35
36
 
# File 'app/services/query/order_query.rb', line 34

def invoiced_count_between(start_range, end_range)
  invoiced_records_between(start_range, end_range).count
end

#invoiced_count_since(start_range) ⇒ Object 



29
30
31
 
# File 'app/services/query/order_query.rb', line 29

def invoiced_count_since(start_range)
  invoiced_count_between(start_range, Date.current)
end

#invoiced_records_between(start_range, end_range = Date.current) ⇒ Object 



49
50
51
 
# File 'app/services/query/order_query.rb', line 49

def invoiced_records_between(start_range, end_range = Date.current)
  @relation.invoiced.where(orders: { shipped_date: start_range..end_range })
end

#invoiced_revenue ⇒ Object 



19
20
21
 
# File 'app/services/query/order_query.rb', line 19

def invoiced_revenue
  revenue @relation.invoiced
end

#invoiced_revenue_between(start_range, end_range) ⇒ Object 



44
45
46
 
# File 'app/services/query/order_query.rb', line 44

def invoiced_revenue_between(start_range, end_range)
  revenue invoiced_records_between(start_range, end_range)
end

#invoiced_revenue_since(start_range) ⇒ Object 



39
40
41
 
# File 'app/services/query/order_query.rb', line 39

def invoiced_revenue_since(start_range)
  revenue invoiced_records_between(start_range, Date.current)
end

#median_invoiced_order_revenue ⇒ Object 



14
15
16
 
# File 'app/services/query/order_query.rb', line 14

def median_invoiced_order_revenue
  median_record.try(:line_total) || 0.00
end

#revenue(rel) ⇒ Object 



53
54
55
 
# File 'app/services/query/order_query.rb', line 53

def revenue(rel)
  rel.sum(:line_total)
end