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 =

Generic sources that should be replaced with the specific campaign source.
nil (no source), Unknown (851), Google Ads (1155)
Note: Google PPC (1151) was consolidated into Google Ads (1155).

[nil, 851, 1155].freeze

Instance Method Summary collapse

Instance Method Details

#collection ⇒ Object 



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

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 ⇒ Object 



74
75
76
77
78
 
# File 'lib/online_migrations/data_migrations/backfill_google_ads_visit_sources.rb', line 74

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

#process(visits) ⇒ Object 



43
44
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
 
# File 'lib/online_migrations/data_migrations/backfill_google_ads_visit_sources.rb', line 43

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