Class: Report::AdsOverviewReport

Inherits:
Object
  • Object
show all
Defined in:
app/services/report/ads_overview_report.rb

Overview

Read model behind /ads-overview — every paid channel's spend, delivery and
return in one place, for a trailing window and the window before it.

Everything keys on the campaign's Source: spend arrives there via
AdSpendSyncWorker → SourceDataPoint, and revenue through the order's
write-once original_source_id. So ROAS is a join rather than a
reconciliation (doc/architecture/SOURCE_ATTRIBUTION.md).

Sourced and influenced are separate numbers and never added together.
Sourced is acquisition and is what ROAS divides. Influenced — from
source_influenced_invoices, the digital-touch arm — is a deal the campaign
reached but did not win; it is a LIST, so it does not sum across campaigns or
providers, and #totals says so via :influenced_revenue_is_summable.
Collapsing the two is how 20k Costco orders came to read "Trade Show > 2011
Toronto"; see Marketing::CampaignInfluence for the CRM-campaign half.

Coverage caveat worth repeating wherever these totals are quoted: spend is
synced for Google, Microsoft, OpenAI and Amazon only. Pinterest and Facebook
campaigns mirror into sources but have no spend adapter yet, so they show
zero spend rather than no row.

Examples:

report = Report::AdsOverviewReport.new(window_days: 30)
report.provider_summaries # => [{ provider: 'google', spend: 1234.5, roas: 4.2, ... }, ...]

Constant Summary collapse

DEFAULT_WINDOW_DAYS =

Trailing window when the caller doesn't pick one: a month against the month
before it, which is the comparison the dashboard is read for.

Trailing 30 days rather than calendar months on purpose. "August so far vs
all of July" compares a part-period against a whole one and reports a
collapse that is really just the month being young — the same fabricated
move this dashboard already suppresses elsewhere.

30
WINDOW_OPTIONS =

Windows the UI offers. WINDOW_LABELS names them.

[7, 30, 90].freeze
WINDOW_LABELS =

What each window is called in the UI, so the buttons read as periods rather
than as raw day counts.

{ 7 => 'Week', 30 => 'Month', 90 => 'Quarter' }.freeze
REVENUE_INVOICE_TYPE =

Only real sales count as ad revenue (quotes, credits and the rest don't).

'SO'
CAMPAIGN_ROW_LIMIT =

Campaign rows rendered before the table would stop being readable.

200
INFLUENCE_WINDOW_DAYS =

How far before an invoice a visit still counts as influence. Matches
Marketing::CampaignInfluence::DEFAULT_WINDOW_DAYS on purpose — the two
halves of influence reporting have to answer over the same reach — and is
independent of the spend window above, which is a delivery window.

90
METRICS =

source_data_points.metric_type → the key this report exposes it under.

{ ad_spend: :spend, ad_clicks: :clicks, ad_impressions: :impressions,
ad_conversions: :conversions, ad_sales: :platform_sales }.freeze
PLATFORM_ATTRIBUTED_PROVIDERS =

Providers whose orders cannot reach an individual campaign through
invoices, so return has to be read from the platform's own attributed
sales (ad_sales) instead of from acquisition.

Amazon marketplace orders arrive via SP-API carrying the Amazon retailer
Source, never the ad campaign's — measured 2026-08-02, 0 of 842 Amazon
campaign sources had a single acquired order while all 834 marketplace
orders sat on the platform node. Reading invoices for Amazon therefore
reports every campaign at zero return forever, which is what made the
account's best campaign (8.79 ROAS, $43,627 sales) show up as wasted spend.

This is a different KIND of number from sourced revenue — Amazon's own
attribution, on Amazon's own window — so the dashboard labels it as such
rather than quietly mixing the two.

%w[amazon_ads].freeze

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(window_days: DEFAULT_WINDOW_DAYS, today: Date.current) ⇒ AdsOverviewReport

Returns a new instance of AdsOverviewReport.

Parameters:

  • window_days (Integer) (defaults to: DEFAULT_WINDOW_DAYS) —

    trailing window; clamped to WINDOW_OPTIONS

  • today (Date) (defaults to: Date.current) —

    injection point for tests



76
77
78
79
80
81
82
 
# File 'app/services/report/ads_overview_report.rb', line 76

def initialize(window_days: DEFAULT_WINDOW_DAYS, today: Date.current)
  @window_days = WINDOW_OPTIONS.include?(window_days.to_i) ? window_days.to_i : DEFAULT_WINDOW_DAYS
  # Providers finalize a day's numbers overnight, so the window ends
  # yesterday — including today would show every campaign "down".
  @current_range = (today - @window_days)..(today - 1)
  @prior_range = (today - (@window_days * 2))..(today - @window_days - 1)
end

Instance Attribute Details

#current_range ⇒ Range<Date> (readonly)

Returns the window being reported.

Returns:

  • (Range<Date>) —

    the window being reported



85
86
87
 
# File 'app/services/report/ads_overview_report.rb', line 85

def current_range
  @current_range
end

#prior_range ⇒ Range<Date> (readonly)

Returns the equal-length window immediately before it.

Returns:

  • (Range<Date>) —

    the equal-length window immediately before it



87
88
89
 
# File 'app/services/report/ads_overview_report.rb', line 87

def prior_range
  @prior_range
end

#window_days ⇒ Integer (readonly)

Returns length of the trailing window in days.

Returns:

  • (Integer) —

    length of the trailing window in days



72
73
74
 
# File 'app/services/report/ads_overview_report.rb', line 72

def window_days
  @window_days
end

Instance Method Details

#action_item_stats ⇒ Hash

Open-item counts for the dashboard's action-items panel.

Returns:

  • (Hash) —

    :open, :by_category, :by_provider, :by_status



210
211
212
213
214
215
216
217
 
# File 'app/services/report/ads_overview_report.rb', line 210

def action_item_stats
  @action_item_stats ||= {
    open: AdActionItem.actionable.count,
    by_category: AdActionItem.actionable.group(:category).order(Arel.sql('COUNT(*) DESC')).count,
    by_provider: AdActionItem.actionable.group(:provider).count,
    by_status: AdActionItem.group(:status).count
  }
end

#campaign_rows(provider: nil) ⇒ Array<Hash>

Per-campaign detail, biggest spender first. Only campaigns with a data
point in the window are included — dormant campaigns would be noise.

provider filters in SQL, BEFORE CAMPAIGN_ROW_LIMIT is applied. Filtering
the limited set afterwards in Ruby silently renders an empty table for any
provider whose campaigns all rank below the top-200 spend cutoff — which is
every smaller provider, exactly when someone filters to look at one.

Parameters:

Returns:

  • (Array<Hash>) —

    :source_id, :name, :provider, :label, :external_id, :campaign_url, :spend, :clicks, :impressions, :conversions, :sourced_revenue, :influenced_revenue, :cpc, :roas, :open_items



174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
 
# File 'app/services/report/ads_overview_report.rb', line 174

def campaign_rows(provider: nil)
  @campaign_rows ||= {}
  @campaign_rows[provider] ||= begin
    rows = select_all(campaign_rows_sql(provider: provider), from: current_range.first, to: current_range.last,
                                                             provider: provider,
                                                             platform_providers: PLATFORM_ATTRIBUTED_PROVIDERS)
    # Source#campaign_url reads config, so it can't be computed in SQL —
    # one lookup for the whole table rather than one per row.
    urls = Source.where(id: rows.pluck('id')).index_by(&:id).transform_values(&:campaign_url)
    influenced = influenced_revenue_by_source

    rows.map do |row|
      spend = row['spend'].to_f
      sourced_revenue = row['revenue'].to_f
      clicks = row['clicks'].to_i
      {
        source_id: row['id'], name: row['name'], provider: row['campaign_provider'],
        label: AdActionItem.human_provider_label(row['campaign_provider']),
        external_id: row['campaign_external_id'],
        campaign_url: urls[row['id']],
        spend:, clicks:, impressions: row['impressions'].to_i, conversions: row['conversions'].to_f,
        sourced_revenue:,
        influenced_revenue: influenced[row['id']].to_f,
        # ROAS is deliberately sourced-only: influence is a list, not a share
        # of credit, so folding it in would inflate return by whatever the
        # campaign merely touched.
        cpc: safe_divide(spend, clicks), roas: safe_divide(sourced_revenue, spend),
        open_items: row['open_items'].to_i
      }
    end
  end
end

#daily_trend ⇒ Array<Hash>

Spend / clicks / conversions per calendar day across all providers,
oldest first — the dashboard's trend strip.

Returns:

  • (Array<Hash>) —

    :date, :spend, :clicks, :conversions



143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
 
# File 'app/services/report/ads_overview_report.rb', line 143

def daily_trend
  @daily_trend ||= begin
    sql = <<~SQL.squish
      SELECT lower(dp.period)::date AS day, #{metric_sum_columns}
      FROM source_data_points dp
      JOIN sources s ON s.id = dp.source_id
      WHERE s.campaign_provider IS NOT NULL
        AND dp.period && daterange(:from, :to, '[]')
      GROUP BY 1
      ORDER BY 1
    SQL

    select_all(sql, from: current_range.first, to: current_range.last).map do |row|
      { date: row['day'], spend: row['spend'].to_f, clicks: row['clicks'].to_i, conversions: row['conversions'].to_f }
    end
  end
end

#prior_window_has_spend_history?(provider = nil) ⇒ Boolean

Whether spend history reaches back far enough to compare the two windows.

source_data_points only goes back to whenever AdSpendSyncWorker first
ran — 2026-06-27 in production. Comparing a full current window against a
prior window that is half empty reports a spend "increase" that is really
just the arrival of the data: on 2026-08-02 the dashboard showed +541.6%
off 28 days of data against 8.

Deliberately keyed on the earliest row rather than on counting days with
data, because a day of genuinely zero spend writes NO rows at all (see
AdSpendSyncWorker) — so a gap in the middle is ambiguous, while "our
history starts after this window does" is not. Running the backfill far
enough back makes this true again on its own.

Asked PER PROVIDER. A single global answer reads the earliest row we hold
for anyone, which lets a provider with deep history vouch for one that only
started collecting last week: on 2026-08-05 Google's history (from
2026-05-01) cleared ChatGPT's row to report +694.1% off 9 days of
prior-window data against a full 28.

Parameters:

  • provider (String, nil) (defaults to: nil) —

    nil asks about the totals card

Returns:

  • (Boolean) 


241
242
243
244
 
# File 'app/services/report/ads_overview_report.rb', line 241

def prior_window_has_spend_history?(provider = nil)
  earliest = spend_history_starts_on(provider)
  earliest.present? && earliest <= prior_range.first
end

#provider_summaries ⇒ Array<Hash>

One row per provider that has any campaign mirrored into sources, in
descending spend order. Providers with no spend adapter yet still appear
(zeroed) so their absence is visible rather than silent.

Returns:

  • (Array<Hash>) —

    :provider, :label, :spend, :clicks, :impressions, :conversions, :sourced_revenue, :influenced_revenue, :cpc, :roas, :campaigns, :spend_change_pct, :roas_change_pct



96
97
98
99
100
101
102
103
104
105
106
107
108
109
 
# File 'app/services/report/ads_overview_report.rb', line 96

def provider_summaries
  @provider_summaries ||= begin
    metrics = metrics_by_provider
    revenue = revenue_by_provider
    influenced = influenced_revenue_by_provider
    counts = Source.where(campaign_provider: Source::CAMPAIGN_PROVIDERS).group(:campaign_provider).count

    Source::CAMPAIGN_PROVIDERS.map do |provider|
      m = metrics[provider] || {}
      r = revenue[provider] || {}
      summary_row(provider, m, r, counts[provider].to_i, influenced[provider].to_f)
    end.sort_by { |row| -row[:spend] }
  end
end

#spend_comparison_blocked_by ⇒ String?

Which provider's short history is what stops the TOTALS being comparable.

Without this the dashboard could only say "our ad-spend history starts
2026-06-29" — which reads as though we hold nothing before that date, when
Google in fact goes back to February. One late-arriving provider withholds
the total while every other row is comparable, so the banner has to name it
or the blank total looks like a fault.

Returns:

  • (String, nil) —

    human provider label, nil when the totals ARE
    comparable or when we hold no campaign ad-spend at all



256
257
258
259
260
261
262
 
# File 'app/services/report/ads_overview_report.rb', line 256

def spend_comparison_blocked_by
  return nil if prior_window_has_spend_history?

  provider = spending_providers.select { |name| spend_history_starts_by_provider[name] }
                               .max_by { |name| spend_history_starts_by_provider[name] }
  AdActionItem.human_provider_label(provider) if provider
end

#spend_history_starts_on(provider = nil) ⇒ Date?

First day we hold campaign AD-SPEND data for, which is the only history the
spend comparison depends on.

Scoped to ad_spend on campaign sources deliberately: source_data_points
is a general time series, so an older row for a different metric (ad_sales
arrived later than spend, for one) or on a non-campaign Source would push
this date back and let the guard pass while campaign spend history still
started inside the prior window — resurrecting the fabricated percentage
this exists to suppress.

Parameters:

  • provider (String, nil) (defaults to: nil) —

    nil takes the LATEST start among the providers
    feeding the totals card, since a sum is only as comparable as its
    shortest-lived contributor. Both windows count: a provider that spent in
    the prior window and has since gone quiet still sits in the denominator,
    so truncated history there inflates the comparison just as surely.
    Providers at zero across BOTH windows are ignored — they move neither
    side of the ratio, so their history can't distort it.

Returns:

  • (Date, nil) —

    nil when we hold no campaign ad-spend at all



282
283
284
285
286
 
# File 'app/services/report/ads_overview_report.rb', line 282

def spend_history_starts_on(provider = nil)
  return spend_history_starts_by_provider[provider] if provider

  spending_providers.filter_map { |name| spend_history_starts_by_provider[name] }.max
end

#spending_providers ⇒ Array<String>

Providers that move either side of the totals ratio, and whose history
therefore constrains it. See #spend_history_starts_on.

Returns:

  • (Array<String>) —

    provider keys with spend in either window



292
293
294
295
 
# File 'app/services/report/ads_overview_report.rb', line 292

def spending_providers
  provider_summaries.select { |row| row[:spend].positive? || row[:prior_spend].positive? }
                    .pluck(:provider)
end

#top_action_items(limit: 10) ⇒ ActiveRecord::Relation<AdActionItem>

The highest-value open items, for the dashboard's inline panel.

Parameters:

  • limit (Integer) (defaults to: 10)

Returns:



301
302
303
 
# File 'app/services/report/ads_overview_report.rb', line 301

def top_action_items(limit: 10)
  AdActionItem.actionable.includes(:source).by_priority.limit(limit)
end

#totals ⇒ Hash

Totals across every provider, for the dashboard's headline cards.

Sourced revenue sums honestly — an invoice has exactly one acquisition
source, so provider figures are disjoint. Influenced revenue does NOT: it
comes from #total_influenced_revenue's distinct invoice set rather than
from adding the per-provider numbers up, because a deal touched through two
providers appears under both at full value.

Returns:

  • (Hash) —

    metric keys from #provider_summaries plus :roas,
    :influenced_revenue and :influenced_revenue_is_summable



121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
 
# File 'app/services/report/ads_overview_report.rb', line 121

def totals
  @totals ||= begin
    rows = provider_summaries
    summed = %i[spend clicks impressions conversions sourced_revenue prior_spend prior_revenue]
             .index_with { |key| rows.sum { |row| row[key] } }
    summed.merge(
      cpc: safe_divide(summed[:spend], summed[:clicks]),
      roas: safe_divide(summed[:sourced_revenue], summed[:spend]),
      spend_change_pct: spend_pct_change(summed[:spend], summed[:prior_spend]),
      revenue_change_pct: pct_change(summed[:sourced_revenue], summed[:prior_revenue]),
      influenced_revenue: total_influenced_revenue,
      # Mirrors Marketing::CampaignInfluence::Result#revenue_is_summable? —
      # says so in the data so no surface can quietly total the column.
      influenced_revenue_is_summable: false
    )
  end
end