Created
October 4, 2025 08:12
-
-
Save smtm/a2ec4de45d2cc3abc0cc459fcdb07a90 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| require_relative '../../config/environment' | |
| ActiveRecord::Base.establish_connection(Rails.configuration.database_configuration[Rails.env]) | |
| # Searches never seen before: tend to be junk, we can later | |
| # revoke the negative entry and allow it in. | |
| # shoot first - ask later | |
| @lastnegative = Negative.where(shared_set_id: 11680763103).last | |
| sql = " | |
| select * from searchterms st | |
| join (select | |
| search_term, | |
| sum(impressions) all_impressions, | |
| sum(clicks) all_clicks, | |
| sum(conversions) all_conversions, | |
| sum(costs) all_cost | |
| from searchterms | |
| group by search_term) | |
| allsearches on allsearches.search_term = st.search_term | |
| where st.created_at >='#{(@lastnegative.created_at - 3.minutes).utc}' | |
| and st.status = 'NONE' | |
| and allsearches.all_impressions <= 3 | |
| and all_clicks = 0 | |
| order by clicks desc,status desc,created_at desc,costs desc, st.search_term | |
| limit 500" | |
| #puts sql | |
| @searchterms = Searchterm.find_by_sql(sql) | |
| #where st.created_at >='2025-07-24' | |
| #where st.created_at >='#{(@lastnegative.created_at - 3.minutes).utc}' | |
| if @searchterms.size > 0 | |
| #puts "recent searchterms for auto negatives" | |
| keywords =[] | |
| @searchterms.each do |st| | |
| words = st.search_term.split(" ").size | |
| keywords << st.search_term if words <=8 #if keywords.include? st.search_term == false | |
| end | |
| puts keywords.join(";") | |
| # Negative.add_negative(keywords,10437505308,:EXACT) # Auto Negatives | |
| Negative.add_negative(keywords,11680763103,:EXACT) # Auto Negatives 2 | |
| end | |
| # Nonperfformin keywords go in a own negative List | |
| sql2="select search_term, neg.created_at, | |
| sum(impressions) imp, sum(clicks) clks, sum(conversions) conv,sum(all_conversions) aconv, | |
| sum(clicks)/sum(impressions) ctr,min(date), max(date) | |
| from searchterms st | |
| left outer join negatives neg on neg.text = st.search_term | |
| where neg.created_at is null | |
| group by 1,2 | |
| having conv =0 | |
| and aconv<2 | |
| and imp>42 | |
| and clks<=1 | |
| and ctr < 0.025" | |
| @searchterms = Searchterm.find_by_sql(sql2) | |
| #puts "searchterms: #{@searchterms.size}" | |
| if @searchterms.size > 0 | |
| puts "recent searchterms for 'Nonperf Negatives'" | |
| keywords =[] | |
| @searchterms.each do |st| | |
| #puts st.search_term | |
| keywords << st.search_term #if keywords.include? st.search_term == false | |
| end | |
| puts keywords.join(";") | |
| Negative.add_negative(keywords,10456948625,:EXACT) #Nonperf Negatives | |
| end | |
| # Near Phrase and Near exact, we give them a chance but at 23 impressions and low ctr | |
| # the fun ends | |
| sql3="select search_term, neg.created_at, | |
| sum(impressions) imp, sum(clicks) clks, sum(conversions) conv,sum(all_conversions) aconv, | |
| sum(clicks)/sum(impressions) ctr,min(date), max(date) | |
| from searchterms st | |
| left outer join negatives neg on neg.text = st.search_term | |
| where neg.created_at is null | |
| and st.match_type in ('NEAR_PHRASE','NEAR_EXACT') | |
| group by 1,2 | |
| having conv =0 | |
| and aconv < 1 | |
| and imp >22 | |
| and ctr < 0.05;" | |
| @searchterms = Searchterm.find_by_sql(sql3) | |
| #puts "searchterms: #{@searchterms.size}" | |
| if @searchterms.size > 0 | |
| puts "recent searchterms for non performing 'NEAR_PHRASE','NEAR_EXACT'" | |
| keywords =[] | |
| @searchterms.each do |st| | |
| #puts st.search_term | |
| keywords << st.search_term #if keywords.include? st.search_term == false | |
| end | |
| puts keywords.join(";") | |
| Negative.add_negative(keywords,10521405971,:EXACT) #Nonperf NEAR_PHRASE NEAR_EXACT | |
| end | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment