What is the Trends Catcher?
The trends catcher provides the trends for around 20 countries from Google Trends and Twitter. It is a very useful tool if you are searching for a fresh topic to write about and engage with your followers.
In addition, it gives you the opportunity to explore other ways of doing SEO such as Google News or Google Discover, as the content which works the best in these platforms is usually related to present.
If you wanna have a look at the Trends Catcher tool, click over here.
How did I build it?
In this post I would like to explain you how I built the Trends Catcher as I have received a number of questions related to the coding language that I have used, libraries, how I store the data, etcetera.
In short, this tools comprises four main elements: the python script which collects the data, Amazon AWS Lambda where I run the scripts, the database where the data is stored and the visual interface which displays the data through WordPress and WP Data Access.
1.- The Python Scripts
To start with, the data needs to be gathered from Google Trends and Twitter. For this purpose, you do not need to create an ad-hoc scraper as there are already existing Python libraries which make this process much easier and faster.
The libraries which I have used are Pytrends, to get the data from Google Trends, and Tweepy, to get the data from Twitter. The requests to get this data are quite simple, the unique thing that you need to worry about is finding the correct key for each country.
Below you can find as an example a call from Tweepy to get the trends data for Spain. This library uses the WOE ID as a key for each location if you wanna do a request for that specific location. The best page that I have found with a list of this WOE ID is this one, where the different locations are provided with a JSON file.
spain_trends = api.trends_place(764814) trends = json.loads(json.dumps(spain_trends, indent=1))
When it comes to Pytrends, the request is a bit different as it uses the country name. Keep it mind that the trends from Google Trends are not available for all the countries. You can check in the language selector from Google Trends if the country of your interest is accessible or not. Here we see one example of the Google Trends requests:
listtrends = pytrends.trending_searches(pn="argentina")
Finally, once this data is gotten, it is stored in a SQL database by using the pymysql library and the “INSERT INTO” command.
2.- Amazon AWS Lambda
Amazon AWS Lambda is a serverless platform which enables us to execute scripts in a scheduled mode which is very convenient in the case that you only need to run a few of scripts without having to acquire an entire server or in case that you have a shared server and you cannot modify language versions or you do not have too much flexibility to add many libraries. In this post you can find with more details the pros and cons of using a server or Amazon AWS Lambda.
For running the Pytrends and Tweepy scripts, I needed to adapt the scripts to the Lambda function format, upload them by using the zipped function and set up a Cloudwatch which executes this script every four hours.
3.- The Database
In order to store this data, I created a specific database and a SQL table in the shared server where the website is hosted. If you would like to create a database into your website server to store information, most likely you will need to access to the Database section in your CPanel. In this section, you will also have the chance to get the credentials from your database with the purpose of connecting to the database from a better interface such as MySQL Workbench or Chrome MySQL Admin.
An important point to be able to store the data in this database is allowing the access to the IPs where the scripts are ran by using the Amazon AWS Lambda. For this you will need to allow the access to something like this: %.eu-west-3.compute.amazonaws.com, depending on which characters is used as a wild card in your hosting provider.
4.- WordPress and WP Data Access
Finally, now that the data extraction and storing is set up, we only need to find a visual way to display this data. Searching for plugins which can be used to show SQL tables on your WordPress website, I came across “WP Data Access”.
This plugin honestly has some limitations but its freemium version is quite powerful and works quite well in case you do not need to do something too sophisticated.
To create a table snippet which can be inserted into a WP page, first you will need to access to the section “Data Published” and create a table from the database and table where the information is stored, selecting the columns you are interesting in displaying, the conditional clauses and the sorting out options.
Once the table is created, you will be able to copy a shortcode which can be inserted in a page and will show the table on the page.
And that is it! Now you will be able to display dynamic tables on your website with valuable information that can engage with your buyer-person much better than an outdated block of text.