BABY BOY NAMES AS A SERVICE, ALL DONE USING LOW CODE
WHAT WE WILL BUILD
A baby name Facebook Messenger chatbot that will provide us with baby boy names. Randomly chosen. Why? Baby names are hard to choose! Every little bit of inspiration is welcome, right?
THE INGREDIENTS
- Google Sheets, therefore a Google Account is needed
- A public dataset
- Integromat, the free tier will do
- ChatFuel, the free tier will do
THE DATA
Baby name source:
You will be able to download a ZIP file there.
FILE WRANGLING
Extract the ZIP file. It contains plenty of txt files. Delete the contained pdf and then run:
cat * > mega.txt
Now your file is too big for Google Sheets which supports:
5 million cells or 100MB — as of Jan 2020 that is.
There is no header in that file as you can tell with this command:
head -1 mega.txt
The number of lines in that concatenated file:
wc -l mega.txt
…returns it’s 6028151 (>6 MM)
Since all the rows look like this, just one example:
AK,F,1910,Mary,14
…you can split by the gender column (#2):
awk -F ‘,’ ‘{ print > (“split-” $2 “.csv”) }’ mega.txt
Which results in these files:
-rw-r — r — 1 moes staff 66M 25 Jan 19:51 split-F.csv
-rw-r — r — 1 moes staff 52M 25 Jan 19:51 split-M.csv
Since I am only interested in boy names and only in the name itself and nothing else, here is the extract:
awk -F”,” ‘{print $4}’ split-M.csv > boynames.csv
Leaving us with a slim file:
-rw-r — r — 1 moes staff 17M 25 Jan 20:00 boynames.csv
And 2.7 MM rows. (2668532, to be exact.)
This will be the file we will feed into Google Sheets.
To get your CSV file into a Google spreadsheet:
- Open Google Sheets
- Choose “File” → “Import” → “Upload” → “Select a file from your computer.”
- Choose your CSV file from your Documents or Desktop folder.
- The following window will pop up. Choose “Import data.”
(More tips on importing CSVs here.)
Did you know Google Sheets has a strong query language?
https://developers.google.com/chart/interactive/docs/querylanguage
EXPOSE THE DATA
For this, we’ll build an integration flow with Integromat and expose the data via a webhook, as Integromat calls the open endpoint.
So: Create a webhook.
URL looks like this:
https://hook.integromat.com/s8nfq4tw58ryqzszkzkget9
Configure it like this:
Make sure to test it and then turn it ON.
NB: You can retrieve the total row count in a Google Sheet using the Total Number Of Bundles variable.
READY FOR PRIME TIME: USER INTERFACE TIME
Now we are ready to go for Chatfuel.
Chatfuel is the chatbot engine that will communicate for us via Facebook Messenger with our users.
In fact, you can control the chat flow with your response:
https://docs.chatfuel.com/en/articles/735122-json-api
You have to send a response like this to display text in Chatfuel:
{
“messages”: [
{“text”: “Welcome to the Chatfuel Rockets!”}
]
}
So that is why the Integromat flow response looks like that.
My baby name bot for reference:
The Facebook page it belongs to is:
It might be up or not, depending or whether the Constraints hit. And the constraints are important to be aware of.
CONSTRAINTS TO BEAR IN MIND
Google Sheets API:
The Google Sheets API has a limit of 500 requests per 100 seconds per project, and 100 requests per 100 seconds per user. Limits for reads and writes are tracked separately. There is no daily usage limit.
That said, the Google Sheets API is most of the time pretty fast but can see some weird latency. The ChatFuel JSON API connector times out after 10 seconds, so be ready to see error cases.
Possible workaround:
You can set up a custom HTTP call using the correspondent module HTTP OAuth 2.0 Call module where you’ll find an option to set up a max timeout.
Other than that, pumping the data out into SQL/NoSQL data stores would be an actual solution as the Google Sheet API seems to struggle at a certain volume.
ChatFuel has certainly limits, too. (Have not checked.) — In particular the Non-Pro version. Aside from technical constraints (applying to the free version at least): Expect to see ads for the “Pro” version of ChatFuel in your Chatbot communication.
Integromat has call and data volume limits — other than that, in terms of functionality, it’s not limited.
