Class: OnlineMigrations::DataMigrations::BackfillGoogleAdsVisitSources

Inherits:
OnlineMigrations::DataMigration
  • Object
show all
Defined in:
lib/online_migrations/data_migrations/backfill_google_ads_visit_sources.rb

Overview

Online migration helper: backfill google ads visit sources.

Constant Summary collapse

BATCH_SIZE =

Batch size.

1_000
GAD_CAMPAIGN_TO_REF_CODE =

gad_campaignid → Source referral_code (used as utm_campaign)

{
  "23159247768" => "K59XWM",
  "23159247564" => "TQTM9H",
  "23129542030" => "GFN8HW",
  "21091557817" => "LFWLVN",
  "22740889094" => "2PYD8U",
  "22744589941" => "VH9WUM",
  "23120171169" => "CMSWF9",
  "21058743486" => "1916S2",
  "22586287676" => "8EXDN2",
  "22586169272" => "9UQUCZ",
  "19589570621" => "15Q3QN",
  "19582099368" => "27NJEE",
  "19589570618" => "FKWHI2",
  "19582099371" => "S6QEX4",
  "19589540828" => "D7HBI7"
}.freeze
LEGACY_YOUTUBE_GAD_IDS =

Legacy youtube gad ids.

%w[22240833061 21703527019].freeze
REPLACEABLE_SOURCE_IDS =
Note:

Google PPC (1151) was consolidated into Google Ads (1155).

Generic sources that should be replaced with the specific campaign source.
nil (no source), Unknown (851), Google Ads (1155)

[nil, 851, 1155].freeze

Instance Method Summary collapse

Instance Method Details

#collection ⇒ ActiveRecord::Batches::BatchEnumerator

Returns batches of unattributed Google Ads visits.

Returns:

  • (ActiveRecord::Batches::BatchEnumerator) —

    batches of unattributed Google Ads visits



38
39
40
41
42
 
# File 'lib/online_migrations/data_migrations/backfill_google_ads_visit_sources.rb', line 38

def collection
  Visit.where(utm_campaign: nil, referral_code: nil)
       .with_any_marketing_key(Visit::GOOGLE_ADS_MARKETING_KEYS)
       .in_batches(of: BATCH_SIZE)
end

#count ⇒ Integer

Returns number of unattributed Google Ads visits.

Returns:

  • (Integer) —

    number of unattributed Google Ads visits



77
78
79
80
81
 
# File 'lib/online_migrations/data_migrations/backfill_google_ads_visit_sources.rb', line 77

def count
  Visit.where(utm_campaign: nil, referral_code: nil)
       .with_any_marketing_key(Visit::GOOGLE_ADS_MARKETING_KEYS)
       .count
end

#process(visits) ⇒ void

This method returns an undefined value.

Returns assigns sources and propagates them downstream.



45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
 
# File 'lib/online_migrations/data_migrations/backfill_google_ads_visit_sources.rb', line 45

def process(visits)
  visit_ids = visits.ids

  # 1) Visits with a known gad_campaignid → set utm_campaign + source_id
  GAD_CAMPAIGN_TO_REF_CODE.each do |gad_id, ref_code|
    source = cached_source_by_ref(ref_code)
    next unless source

    Visit.where(id: visit_ids)
         .where("landing_page LIKE ?", "%gad_campaignid=#{gad_id}%")
         .update_all(utm_campaign: ref_code, source_id: source.id)
  end

  # 2) Legacy YouTube campaigns → source found by google_campaign_id
  LEGACY_YOUTUBE_GAD_IDS.each do |gad_id|
    source = cached_source_by_gad(gad_id)
    next unless source

    Visit.where(id: visit_ids)
         .where("landing_page LIKE ?", "%gad_campaignid=#{gad_id}%")
         .update_all(utm_campaign: source.referral_code, source_id: source.id)
  end

  # 3) Bare gclid (no gad_campaignid) → assign to Moss Digital parent source
  Visit.where(id: visit_ids, utm_campaign: nil, source_id: nil)
       .update_all(source_id: moss_digital_source_id)

  # 4) Propagate updated visit sources to downstream records
  propagate_to_downstream(visit_ids)
end