Marketing ToolsSich Marketing

Python: Skript A Google Autosuggest Extrait vun Trends fir Är Nisch Sich Schlësselwierder

Jiddereen huet Google Trends gär, awer et ass e bësse komplizéiert wann et ëm Long Tail Keywords geet. Mir all wéi den offiziellen Google Trends Service fir Abléck iwwer d'Sichverhalen ze kréien. Wéi och ëmmer, zwou Saache verhënneren datt vill et fir eng zolitt Aarbecht benotzen;

  1. Wann Dir musst fannen nei Nisch Schlësselwierderan, do sinn net genuch Daten iwwer Google Trends 
  2. Mangel u offizieller API fir Ufroe fir Google Trends ze maachen: Wa mir Moduler benotze wéi pytrends, da musse mir Proxy Servere benotzen, soss gi mir blockéiert. 

An dësem Artikel deelen ech e Python Skript dat mir geschriwwen hunn fir Trend Schlësselwierder iwwer Google Autosuggest ze exportéieren.

Fannt a späichert Autosuggest Resultater iwwer Zäit 

Stellt Iech vir datt mir 1,000 Seed Schlësselwieder hunn fir op Google Autosuggest ze schécken. Als Géigeleeschtung kréie mer wuel ronn 200,000 Laangschwanz Schlësselwierder. Da musse mir datselwecht eng Woch méi spéit maachen an dës Datensätze vergläichen fir zwou Froen ze beäntweren:

  • Wéi eng Ufroe sinn nei Schlësselwierder am Verglach mat der leschter Kéier? Dëst ass wahrscheinlech de Fall dee mir brauchen. Google denkt datt dës Ufroen méi bedeitend ginn - doduerch kënne mir eis eege Google Autosuggest Léisung kreéieren! 
  • Wéi eng Ufroe sinn Schlësselwierder net méi Trend?

De Skript ass relativ einfach, an de gréissten Deel vum Code deen ech gedeelt hunn hei. De aktualiséierte Code späichert d'Donnéeë vu fréiere Runen a vergläicht d'Suggestioune mat der Zäit. Mir hunn Dateibaséiert Datenbanken wéi SQLite vermeit fir et einfach ze maachen - sou datt all Datenspeicher CSV Dateien hei ënnen benotzt. Dëst erlaabt Iech d'Datei an Excel z'importéieren an Nischen Schlësselwuert Trends fir Äert Geschäft ze entdecken.

Fir dëse Python Skript ze benotzen

  1. Gitt Äre Set-Schlësselwuert-Set an, deen op d'Autokomplett geschéckt soll ginn: keywords.csv
  2. Ajustéiert d'Skriptinstellunge fir Äre Besoin:
    • SPROOCH: Standard "en"
    • LAND: Standard "eis"
  3. Plan de Skript fir eemol d'Woch ze lafen. Dir kënnt et och manuell ausféieren wéi Dir wëllt.
  4. Benotzt keyword_suggestions.csv fir weider Analyse:
    • éischt_gesinn: dëst ass den Datum wou d'Ufro fir d'éischte Kéier am Autosuggest ass
    • lescht_gesinn: den Datum wou d'Ufro fir d'lescht gesinn ass
    • ass_nei: wann first_seen == last_seen setze mir dëst op richteg - Filter just op dëse Wäert fir déi nei trendend Recherchen am Google Autosuggest ze kréien.

Hei ass de Python Code

# Pemavor.com Autocomplete Trends
# Author: Stefan Neefischer (stefan.neefischer@gmail.com)
import concurrent.futures
from datetime import date
from datetime import datetime
import pandas as pd
import itertools
import requests
import string
import json
import time

charList = " " + string.ascii_lowercase + string.digits

def makeGoogleRequest(query):
    # If you make requests too quickly, you may be blocked by google 
    time.sleep(WAIT_TIME)
    URL="http://suggestqueries.google.com/complete/search"
    PARAMS = {"client":"opera",
            "hl":LANGUAGE,
            "q":query,
            "gl":COUNTRY}
    response = requests.get(URL, params=PARAMS)
    if response.status_code == 200:
        try:
            suggestedSearches = json.loads(response.content.decode('utf-8'))[1]
        except:
            suggestedSearches = json.loads(response.content.decode('latin-1'))[1]
        return suggestedSearches
    else:
        return "ERR"

def getGoogleSuggests(keyword):
    # err_count1 = 0
    queryList = [keyword + " " + char for char in charList]
    suggestions = []
    for query in queryList:
        suggestion = makeGoogleRequest(query)
        if suggestion != 'ERR':
            suggestions.append(suggestion)

    # Remove empty suggestions
    suggestions = set(itertools.chain(*suggestions))
    if "" in suggestions:
        suggestions.remove("")
    return suggestions

def autocomplete(csv_fileName):
    dateTimeObj = datetime.now().date()
    #read your csv file that contain keywords that you want to send to google autocomplete
    df = pd.read_csv(csv_fileName)
    keywords = df.iloc[:,0].tolist()
    resultList = []

    with concurrent.futures.ThreadPoolExecutor(max_workers=MAX_WORKERS) as executor:
        futuresGoogle = {executor.submit(getGoogleSuggests, keyword): keyword for keyword in keywords}

        for future in concurrent.futures.as_completed(futuresGoogle):
            key = futuresGoogle[future]
            for suggestion in future.result():
                resultList.append([key, suggestion])

    # Convert the results to a dataframe
    suggestion_new = pd.DataFrame(resultList, columns=['Keyword','Suggestion'])
    del resultList

    #if we have old results read them
    try:
        suggestion_df=pd.read_csv("keyword_suggestions.csv")
        
    except:
        suggestion_df=pd.DataFrame(columns=['first_seen','last_seen','Keyword','Suggestion'])
    
    suggestionCommon_list=[]
    suggestionNew_list=[]
    for keyword in suggestion_new["Keyword"].unique():
        new_df=suggestion_new[suggestion_new["Keyword"]==keyword]
        old_df=suggestion_df[suggestion_df["Keyword"]==keyword]
        newSuggestion=set(new_df["Suggestion"].to_list())
        oldSuggestion=set(old_df["Suggestion"].to_list())
        commonSuggestion=list(newSuggestion & oldSuggestion)
        new_Suggestion=list(newSuggestion - oldSuggestion)
         
        for suggest in commonSuggestion:
            suggestionCommon_list.append([dateTimeObj,keyword,suggest])
        for suggest in new_Suggestion:
            suggestionNew_list.append([dateTimeObj,dateTimeObj,keyword,suggest])
    
    #new keywords
    newSuggestion_df = pd.DataFrame(suggestionNew_list, columns=['first_seen','last_seen','Keyword','Suggestion'])
    #shared keywords with date update
    commonSuggestion_df = pd.DataFrame(suggestionCommon_list, columns=['last_seen','Keyword','Suggestion'])
    merge=pd.merge(suggestion_df, commonSuggestion_df, left_on=["Suggestion"], right_on=["Suggestion"], how='left')
    merge = merge.rename(columns={'last_seen_y': 'last_seen',"Keyword_x":"Keyword"})
    merge["last_seen"].fillna(merge["last_seen_x"], inplace=True)
    del merge["last_seen_x"]
    del merge["Keyword_y"]
    
    #merge old results with new results
    frames = [merge, newSuggestion_df]
    keywords_df =  pd.concat(frames, ignore_index=True, sort=False)
    # Save dataframe as a CSV file
    keywords_df['first_seen'] = pd.to_datetime(keywords_df['first_seen'])
    keywords_df = keywords_df.sort_values(by=['first_seen','Keyword'], ascending=[False,False])   
    keywords_df['first_seen']= pd.to_datetime(keywords_df['first_seen'])
    keywords_df['last_seen']= pd.to_datetime(keywords_df['last_seen'])
    keywords_df['is_new'] = (keywords_df['first_seen']== keywords_df['last_seen'])
    keywords_df=keywords_df[['first_seen','last_seen','Keyword','Suggestion','is_new']]
    keywords_df.to_csv('keyword_suggestions.csv', index=False)

# If you use more than 50 seed keywords you should slow down your requests - otherwise google is blocking the script
# If you have thousands of seed keywords use e.g. WAIT_TIME = 1 and MAX_WORKERS = 5
WAIT_TIME = 0.2
MAX_WORKERS = 20
# set the autocomplete language
LANGUAGE = "en"
# set the autocomplete country code - DE, US, TR, GR, etc..
COUNTRY="US"
# Keyword_seed csv file name. One column csv file.
#csv_fileName="keyword_seeds.csv"
CSV_FILE_NAME="keywords.csv"
autocomplete(CSV_FILE_NAME)
#The result will save in keyword_suggestions.csv csv file

Luet de Python Skript erof

Osman Sinn

Pemavor bitt verschidde Digital Marketing Tools fir Performance Marketing Experten a komplette Packagen fir verschidden Ufuerderungen. Mir hëllefen Iech mat data-driven Prozessautomatioun am Kampagnemanagement fir Annoncen, Shopping, Bidden, a verschidden aner Marketing Servicer.

Verbonnen Artikelen

Zréck erop zum Knäppchen
Zoumaachen

Adblock entdeckt

Martech Zone ass fäeg Iech dësen Inhalt ouni Käschten ze liwweren, well mir eise Site monetiséieren duerch Ad-Recetten, Partnerlinks a Sponsoren. Mir géifen appreciéieren wann Dir Är Annonce Blocker ewechzehuelen géif wéi Dir eise Site Vue.