In this post I am trying to build some keyword research tool. Let's start by going to Keyword Suggestion like keword Sheeter write some keyword and let it sheet the keywords for you. Once you are satisfied stop the sheeter and export the file into CSV. Now we need to clean the data. Most of the time we are interested in long tail keywords.
Here is the link to GitHub Repository Consider leaving a star.
Read CSV in Python using Pandas
Read that csv file in Python Pandas library like this
import pandas as pd
input_csv_file = 'esp8266_shitter.csv'
# Read CSV file into a pandas DataFrame with custom delimiter and quoting character
df = pd.read_csv(input_csv_file , sep=',', quotechar='"')
df.head()
In my case it print out the result like this
id Keyword ... Domain Words
0 724 use esp8266 without wifi ... suggestqueries.google.com 4
1 723 esp8266 wifi without password ... suggestqueries.google.com 4
2 722 flash esp8266 without usb ... suggestqueries.google.com 4
3 721 esp8266 https without fingerprint ... suggestqueries.google.com 4
4 720 power esp8266 without usb ... suggestqueries.google.com 4
[5 rows x 8 columns]
You can get the idea that our keywords are in keyword column which we are interested in. So let's go back and filter this column and create a new list which only have the keywords which are longest then the desired length.
If you wanted to see the names of the columns you can do that with the help of print(df.columns). When I put this command in idle python shell it returns the following column names in my csv file.
Index(['id', 'Keyword', 'Length', 'Unnamed: 3', 'CPC', 'Search', 'Domain',
'Words'],
dtype='object')
If I try to print the Keyword Column it returns output like this
df['Keyword']
0 use esp8266 without wifi
1 esp8266 wifi without password
2 flash esp8266 without usb
3 esp8266 https without fingerprint
4 power esp8266 without usb
...
720 esp8266 board manager
721 esp8266 pinout
722 esp8266wifi.h
723 esp8266 wifi module
724 esp8266 price in pakistan
Name: Keyword, Length: 725, dtype: object
Now I can ask simply for the keywords list which are longer then specific word length by this
filtered_keywords = df[df['Keyword'].apply(lambda x: len(x.split()) >= 4)]['Keyword']
print(filtered_keywords)
Convert the Visuals to Flask Web Application
Now that we have our basic structure ready we can start building the basic flask application to give some good visual and user interface like. Let's create a backend code with flask framework and create a templates folder and inside that templates folder create index.html file.
Here is the basic app.py file for the backend code
from flask import Flask, request, render_template
import pandas as pd
app = Flask(__name__)
# Function to filter keywords with at least n words
def filter_keywords(csv_file, min_words):
df = pd.read_csv(csv_file)
total_keywords = len(df)
filtered_keywords = df[df['Keyword'].apply(lambda x: len(x.split()) >= min_words)]['Keyword']
long_tail_keywords = filtered_keywords.tolist()
total_long_tail_keywords = len(long_tail_keywords)
return long_tail_keywords, total_keywords, total_long_tail_keywords
@app.route('/', methods=['GET', 'POST'])
def index():
total_keywords = 0
total_long_tail_keywords = 0
long_tail_keywords = []
min_words = 1 # Default minimum number of words
if request.method == 'POST':
file = request.files['file']
min_words = int(request.form['minWords'])
if file:
long_tail_keywords, total_keywords, total_long_tail_keywords = filter_keywords(file, min_words)
return render_template('index.html', keywords=long_tail_keywords, total_keywords=total_keywords, total_long_tail_keywords=total_long_tail_keywords)
if __name__ == '__main__':
app.run(debug=True)
and This is the index.html file
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Long-tail Keyword Finder</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha1/dist/css/bootstrap.min.css" rel="stylesheet">
<style>
</style>
</head>
<body>
<div class="container mt-5">
<h1 class="mb-4">Upload a CSV File</h1>
<form method="POST" enctype="multipart/form-data">
<div class="mb-3">
<input type="file" class="form-control" name="file" accept=".csv" required>
</div>
<div class="mb-3">
<label for="minWords" class="form-label">Minimum Words in Long-tail Keywords:</label>
<input type="number" class="form-control" name="minWords" id="minWords" min="1" required>
</div>
<button type="submit" class="btn btn-primary">Upload and Find Long-tail Keywords</button>
</form>
{% if keywords %}
<h2 class="mt-5">Long-tail Keywords Found:</h2>
<p class="mt-3">
<span class="badge bg-danger">total Keywords: {{ total_keywords }}</span>
<span class="badge bg-success">Long-tail: {{ total_long_tail_keywords }}</span>
</p>
<table class="table">
<thead>
<tr>
<th scope="col">Keyword</th>
</tr>
</thead>
<tbody>
{% for keyword in keywords %}
<tr>
<td>{{ keyword }}</td>
</tr>
{% endfor %}
</tbody>
</table>
{% endif %}
</div>
</body>
</html>
Top comments (0)