In one of my previous posts I shared with you how to do basic web scraping with a combination of google sheets, the importxml() formula and xpath.

As soon as you’re project will become a bit bigger and you start to use more and more importxml() queries in your sheet you eventually will be capped by googles limit on the formula. The content just won’t load anymore.

Enter Nokogiri

Luckily there are plenty of other options for web scraping and you’ll be able to reuse your knowledge of xpath. Nokogiri is a so called ruby “gem” that you can run in terminal.

An example of what can be done with Nokogiri & Ruby

• Scrape multiple elements of an URL as columns in a CSV
• Scrape multiple (similar) URLs at once
• Save the whole dataset to one CSV

An image tells a thousand words

The scraper in action

Pretty satisfying to watch this script doing it’s job.

Not sharing the code

Unfortunately scraping a website is an extremely “grey” area of the law – see this great writeup on the topic. That’s why I won’t share the code here. Think carefully about the consequences of your actions and ask the the content owner for permission before you do anything.

Why writing the post without an exact guide?

Scraping is a great thing to have in your toolbelt as technical marketing especially as you probably will find yourself in a situation where you’ll want to scrape your own content for one or another reason. Without knowing (and searching for) other options you’ll never question and improve your own approach.

How do you get started?

Check out this awesome tutorial, it’s very detailed and allowed me to finish my project.

In this MySQL tutorial we’ll look into creating databases, creating tables and some query examples.

MySQL short for Structured Query Language is an open-source relational database management system (see Wikipedia) and is used in millions of companies as the main database.

Applications / Companies that use the MySQL database include: WordPress, Drupal, Facebook, Twitter, Flickr, and YouTube.

It is very likely that the company that you work for or started yourself uses MySQL for some use case.

What would I do with my knowledge?

Most marketers use their knowledge of MySQL to do some kind of data-analysis. If you don’t want to be dependent on an IT-guy for every bit of data-analysis of your application – you start teaching yourself MySQL. The alternative would be to grab the data (which is really just table data like you’d find in excel) and copy it to excel and do your analysis there.

Why isn’t copying to Excel an option?

One of the biggest reasons is, that you can work with live data. You don’t have to copy a snapshot of the data – you’ll work right there live on your database.

You’ll still export the result of your query (more on that later) to your data analysis software later but with mysql you can join data from multiple tables beforehand which saves you a ton of time and hundreds of VLOOKUPS or INDEX-MATCHES in your excel files.

Start with the most important part: The Test-Environment

In my opinion the most important step of learning how to work with MySQL is the test-environment and some test-date.

You’ll do thousands of iterations of selects and it’s fine that most of them fail as your software will tell you where you failed and you can improve on that.

Software vs. Web Services

Although there are some web services like sqlfiddle.com where you can try some selects but the performance is really slow and getting a big dataset in there is not very practical.

Recreating a real scenario

My approach is to recreate a real case as close as possible. Normally we’d connect to a web-server with some databases with a local software.

The mysql software

I chose Sequel Pro to do my selects but you could go with any other software out there.

The database / server

The only real database that I could connect to would be my running wordpress installation and its backend database – but I don’t want to mess with the live date.

So I use MAMP to set up a locally running webserver which comes with a MySQL server.

MySQL Tutorial – Install the Software to connect to a database

1) Install Sequel Pro
2) Install MAMP
3) Start the Server using MAMP
4) Connect to the server using Sequel Pro

1) and 2) and even 3) are pretty straightforward, you should be able to do that without any help. If something fails, just google the error – there is loads of help on those topics.

Talking about 4) This is the connection data that you have to enter into Sequel Pro to connect.

(You find the port number in MAMP -> Preferences -> Ports; but the standard port is 8889 so you can try my settings first. The PW is “root” as well)

Now you should be greeted with an empty window of Sequel Pro

Creating the database schema

Now that we have successfully connected to a sequel server we’re ready for databases & tables and some data. The process of creating the database and tables is called “creating a database schema”. I’ve created a google sheet with some sample data that I want to get into my database, if you want to follow along.

Not interested in how to create databases & tables? Just want to get to the sample data? Scroll down to “Shortcut – Just give me the sample data”.

Add a database

To get started we create an empty database by selecting that option form the dropdown in the top left corner.

Create a table

You now have an empty database without any tables in it. Head over into Sequel Pro and click the small (+) in the left lower corner to create a table.

Then add all the columns of the data that you want to import and also the data types.

Data Types?

There are quite a lot of data types – these can be thought of kind of like cell types in excel. You find a https://www.w3schools.com/sql/sql_datatypes.asp.

Common ones are INT, VARCHAR, TEXT or DECIMAL.

The whole process could have also be done with a mysql command like so:

CREATE TABLE orders (
order_id int default 0,
total_price decimal(10,2),
cust_id int,
PRIMARY KEY (order_id)
);

If that confuses you, just use the software for now as shown in the gif above.

Importing the data

Now that the database scheme is ready – we’re ready to import some data into it.

Import from a CSV File

A CSV-File (Comma Separated Values) is a very common file-format to transfer database-data from one place to another. (In this case from google sheets to my database)

I’ve exported the orders sheet from my google spreadsheet as csv (File -> Download as -> .csv) to import it into my Database.

To do so we need a run mysql command. Copy the command into the “Query” tab of Sequel Pro and hit CMD + R to execute it.

LOAD DATA LOCAL INFILE '/Users/Daniel/Downloads/orders.csv' INTO TABLE orders
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

If everything worked the data is now in your table. Check the “Content” tab of Sequel Pro and it should now look like this:

Repeat the steps for the “customers” sheet from the sample google spreadsheet. Just adapt the commands / data types.

Shortcut – Just give me the sample data

If you’re not interested in the whole process of creating databases and tables and importing data just use my sample database. Download it, double click it and it should open in Sequel Pro and ask you if you want to import the data.

→ Download the Sample Database

Finally some examples with the example data

I’m going to show you a few examples – that you should be able to recreate. After you lost your fear of getting started head to one of the resources provided at the end of the guide to get in-depth knowledge of MySQL.

Select All

select * from orders;

This is as simple as it gets:

select

As long as we’re doing data analysis and not altering table date your query almost always starts with “select”.

*

The added asterisk basically just says “everything” – so we’ve just expanded our “select …” to “select all”

from orders

Answers the question what table we’re selecting from.

Execute by hitting CMD + R and you should end up with this result:

Select the sum of money spent per customer

SELECT cust_id,
   sum(total_price)
FROM orders
GROUP BY cust_id
ORDER BY sum(total_price) DESC;

After the select keyword we add the two columns that we want to see. First cust_id and then the of the values in total_price.

We also add a GROUP BY cust_id to let the server know that we’re interested in one row per customer.

The ORDER BY sum(total_price) DESC just reorders the values.

Revenue per Customer-Country

We have two tables so we actually can and want to join those tables. We have a common identifier (cust_id) which we’re going to use here. You can only join two tables if you have some (preferably unique) identifier in both tables.

SELECT Country,
   sum(total_price)
FROM orders,
 customers
WHERE orders.cust_id = customers.cust_id
GROUP BY Country
ORDER BY sum(total_price) DESC;

In this MySQL tutorial for beginners I cant’t go into detail describing this one as it get’s a bit complex. But head over to the resources provided below and start experimenting – you’ll quickly get the hang of it.

Where to become an expert

I can’t teach you the how to become a mysql expert – there are way better resources out there. But you now have a test environment and even some test data, which in my opinion is the most important part as you now can test your knowledge anytime and expand your datasets to fit your personal use-cases.

Where to learn MySQL – MySQL Tutorials to become an Expert

1) Codecademy – Check out this course
2) Udemy – There are many courses

Google Sheets is the cloud-based alternative to Microsoft Excel. Although Excel might still be the choice for some business needs, I’d strongly advise everybody to know your way around Google Spreadsheets. It’s fast, free and pretty easy to use.

We’re not going to talk about the basic stuff here (you should know the basic functions in spreadsheets) but two advanced functions that are very useful and will probably move you well above the crowd of people that say they know excel.

1) Index Match

Most people will use vlookup much too often. Really try to master INDEX(MATCH()) instead. It’s way more flexible. Just a bit harder to remember.

I always just remember one thing. You start with =INDEX( and the first thing to enter is the cell reference to the column that holds the desired results – once you’ve put that in say like this =INDEX(C:C just open the match formula and start of with the “search phrase” like so =INDEX(C:C;MATCH(A1, then refer to the column that you want to search in e.g. INDEX(C:C;MATCH(A1;F:F. Super important – always end the Match Formula with a 0 so that your finished function looks something like INDEX(C:C;MATCH(A1;F:F;0))

I’ve prepared a google sheet to show you a working example also visualising one of the advantages of Index-Match: the order of columns is not important – with VLOOKUP you need to worry about that stuff.

Just try to always use INDEX(MATCH()) instead of VLOOKUP() – you’ll understand the advantages soon enough.

2) Query Function

This one is a bit more complex but well worth your time. The Query-Function uses the Google Query Language to make data transformations quick and easy. The Google Query Language resembles MySQL a lot and just as MySQL it allows you to quickly drill down into large Datasets / Tables.

I’ve prepared another google sheet to show you a few examples. In the document you’ll see a simple dataset and a few options for a drilldown with the query function.

The Query Function looks like this:

=QUERY(A:Z;"select ...")

In this example A:Z would represent your dataset – just reference the whole dataset you want to grab data from.

The next parameter always starts with select and is enclosed by apostrophes. I’ll try to explain two different queries as an example here:

=QUERY(A:Z;"select B where A = '2016')

So, let’s say you have revenue in column B and the year in column A. With the query you’ll just get the values from column B where the column A is exacly 2016. Pretty easy right?

Off to something a bit more complex:

QUERY(A:Z;"select A,sum(B) group by A")

We’re looking at the same dataset, this time we want the sum of revenue grouped by the year. With select A,sum(B) we’re basically telling the function that we want A (the year) in the first column and sum(B) (the sum of revenue) in the second column. As there could be two rows with data for the same year we need to tell the function over which variable we want to calculate a sum thats what group by A is for.

We don’t want the result to look like this:

2011 | 2000
2011 | 1000
2012 | 5000

But instead what we want to get is this:

2011 | 3000
2012 | 5000

That’s why we need to group the result – in this case by A (the year)

Check out the documentation of the Google Query Language for a lot more examples and many more options. These examples really only show you a starting point of all the advantages.

This article serves two purposes. 1) To show you that there are basically no limits to what can be done with Google Sheets. 2) To give you a glimpse into web scraping.

The key takeaway for you should be that there is almost always a way of automating a task, just make sure not to fall into this trap.

What we’re doing

Basically we’re building a way to extract structured data from seemingly unstructured data (a website).

Example

• I extract all links from a given website.
• I filter the links to only look at specific links (in the example case: product pages)
• From each given filtered URL I grab a specific item on the page and extract it’s value to the sheet. (in the example the product price of the item)

Check out the example sheet

I’m utilising a few advanced functions that we will partly come back to later:

• importxml() in conjunction with XPath Query Language – this is the secret sauce to the example. This function enables us to import contents of a remote website (or other files)
• regexmatch() is one of three regex-related functions google sheets offers which I’ll explain in a future lesson.
• filter() is a basic function and should definitely be in your personal tool-stack.

With these functions in place we could in theory build a product feed from a website that we don’t even own. And with a bit of google apps script we could utilise these functions to build a price tracker for a website, pretty neat, right?

Update: I built another example – a reddit rank tracker. You’ll have copy it and set up a time trigger in Tools -> Script Editor. Also set the spreadsheet recalculation settings to “On change and every hour” (File -> Spreadsheet settings -> Calculation)

Update 2: Looking for more advanced web scraping techniques. Check out my article on web scraping with Ruby and Nokogiri.