The exponential adoption of Tesla in the Netherlands

Exploring the RDW license plate data set with BigQuery, Cloud Storage and Data Studio

Last year, driving the Dutch highway, I noticed that the amount of Tesla’s on the road has been increasing rapidly.

There are a few good reasons for this. The adoption of full-electric (lease) cars is actively supported by the Dutch government through tax incentives. Furthermore, the newest Model 3 is the first Tesla that is relatively friendly priced around 60k, instead of 100k+ EUR.

To validate my observation I will explore an open data set that is available through the RDW. The RDW (Dutch Vehicle Authority) is responsible for the licensing of vehicles and they provide a daily updated data set of all licensed cars in the Netherlands.

Photo by Bram Van Oost on Unsplash

Prepping the data…

First, let’s download the data set (updated: Jan 10, 2020).

All Dutch vehicles in one big CSV, yum!

The CSV (about 7.7GB) is too big to import in a spreadsheet so we’ll use Google’s petabyte-scale data tool BigQuery to crunch the numbers.

Because BigQuery limits manual CSV uploads to 10MB, we need to upload our file to a bucket in Cloud Storage first:

The Cloud Storage bucket

Next, we create a new table in BigQuery by selecting the file in Cloud Storage, set a table name kentekens_2020 , the schema settings auto-detect , the field delimiter comma and header rows to skip 1 .

Creating our table in BigQuery

In a few seconds the table is created and our data set is ready to explore. When we look at the details, there are 14.4 million rows waiting to be queried. According to the schema there are 64 columns. This means our table contains almost a billion fields (!). Each row represents a vehicle with a registered license plate.

Let’s query!

Since we only are interested in data about passenger cars, we get rid of all other categories by applying a filter on Voertuigsoort (Vehicle category). Let’s group all cars by brand and count them.

SELECT

merk AS brand,

COUNT(*) AS cars

FROM

`<project>.RDW_kentekens.kentekens_2020`

WHERE

Voertuigsoort = 'Personenauto'

GROUP BY

brand

ORDER BY

cars DESC

And the winner is… Volkswagen! To see how Tesla is ranking we can scroll down, but we can also visualise our results in Data Studio.

Horizontal bar charts FTW!

There we are. We find Tesla at rank 29. If you look closely you will also spot ‘Tesla Motors’. This means we can’t do an exact match in our brand filter. We want all brands that contain (lower or upper case) tesla .

Now we count the percentage of Tesla vehicles compared to all passenger cars.

SELECT

all_cars,

tesla_cars,

ROUND(tesla_cars/all_cars*100,2) AS percentage_tesla_cars

FROM (

SELECT

COUNT(*) AS all_cars,

(

SELECT

COUNT(*)

FROM

`<project>.RDW_kentekens.kentekens_2020`

WHERE

LOWER(merk) LIKE '%tesla%') AS tesla_cars,

FROM

`<project>.RDW_kentekens.kentekens_2020`

WHERE

Voertuigsoort = 'Personenauto')

In early 2020 almost 50k Tesla’s are roaming the Dutch streets. This is about 0.5% of all passenger cars in Holland. These numbers don’t impress me yet.

To answer our initial question (is the amount of Tesla’s on the Dutch roads growing exponentially?) we need to plot these numbers on a time frame. Therefore we use the column Datum_eerste_afgifte_Nederland , which gives us the date of the first registration of a specific car in the Netherlands.

To exclude a lot of oldtimers from our data (the first cars were licensed even before the Great War, data quality alert!) we only look at data from 2008 and onwards. According to Wikipedia the first Tesla was delivered to Elon Musk personally that year.

SELECT

Datum_eerste_afgifte_Nederland AS date,

CASE

WHEN merk LIKE '%TESLA%' THEN 'TESLA'

ELSE

merk

END

AS brand,

COUNT(*) AS cars

FROM

`<project>.RDW_kentekens.kentekens_2020`

WHERE

Voertuigsoort = 'Personenauto'

AND Datum_eerste_afgifte_Nederland >= 20080101

GROUP BY

date,

brand

ORDER BY

date

If we explore the results of this query in Data Studio we get a line chart that shows us the top (max 20) brands.

Anybody seen my Tesla?

As Tesla is nowhere to be found in the top 20, we have to set an include filter on the brand TESLA only to see the trend we expect to see.

Boom!

There! My observation that the amount of Tesla’s on Dutch roads increased rapidly, seems validated by the data.

Digging deeper

The next step is to investigate the Dutch share in the global Tesla production supply. I’ve found some numbers on Wikipedia and did some data cleaning, resulting in this small CSV, which I uploaded directly as a table to BigQuery:

Now we can join these numbers with our own data.

WITH

p AS (

SELECT

CAST(year AS string) AS year,

production

FROM

`<project>.RDW_kentekens.production` ),

k AS (

SELECT

CASE

WHEN CAST(Datum_eerste_afgifte_Nederland AS string) LIKE '2014%' THEN '2014'

WHEN CAST(Datum_eerste_afgifte_Nederland AS string) LIKE '2015%' THEN '2015'

WHEN CAST(Datum_eerste_afgifte_Nederland AS string) LIKE '2016%' THEN '2016'

WHEN CAST(Datum_eerste_afgifte_Nederland AS string) LIKE '2017%' THEN '2017'

WHEN CAST(Datum_eerste_afgifte_Nederland AS string) LIKE '2018%' THEN '2018'

WHEN CAST(Datum_eerste_afgifte_Nederland AS string) LIKE '2019%' THEN '2019'

ELSE

NULL

END

AS date,

CASE

WHEN merk LIKE '%TESLA%' THEN 'TESLA'

ELSE

merk

END

AS brand,

COUNT(*) AS cars

FROM

`<project>.RDW_kentekens.kentekens_2020`

WHERE

Voertuigsoort = 'Personenauto'

AND Datum_eerste_afgifte_Nederland BETWEEN 20140101

AND 20191231

GROUP BY

date,

brand

HAVING

brand = 'TESLA')

SELECT

date,

brand,

cars,

production,

ROUND(cars/production*100,1) AS percentage_dutch_teslas

FROM

k

LEFT JOIN

p

ON

k.date = p.year

ORDER BY

date DESC

Watch the pink line. That is the percentage of Dutch Tesla’s compared to the global production per year. 8.5 percent of all Tesla’s produced in 2019 were licensed in the same year in the Netherlands. That’s quite a share for such a small country!

OK. Let’s focus on the best selling models next and visualise the top 10 in Data Studio.

SELECT

Datum_eerste_afgifte_Nederland AS date,

CASE

WHEN merk LIKE '%TESLA%' THEN 'TESLA'

ELSE

merk

END

AS brand,

Handelsbenaming AS model,

COUNT(*) AS cars

FROM

`<project>.RDW_kentekens.kentekens_2020`

WHERE

Voertuigsoort = 'Personenauto'

AND Datum_eerste_afgifte_Nederland >= 20080101

GROUP BY

date,

brand,

model

ORDER BY

date

Hello Model 3!

Seems like the Model 3 is a best seller in the Netherlands. What about the other Tesla models? Any 2008 Tesla Roadsters out there?

With 24 rows we have some cleaning to do, since Tesla doesn’t have 24 different models out there. Let’s also query models by date so we can search for our 2008 Roadster.

SELECT

Datum_eerste_afgifte_Nederland AS date,

CASE

WHEN merk LIKE '%TESLA%' THEN 'TESLA'

ELSE

merk

END

AS brand,

CASE

WHEN Handelsbenaming LIKE '%MODEL 3%' THEN 'MODEL 3'

WHEN Handelsbenaming LIKE '%MODEL S%' THEN 'MODEL S'

WHEN Handelsbenaming LIKE '%MODEL X%' THEN 'MODEL X'

WHEN Handelsbenaming LIKE '%R__DSTER%' THEN 'ROADSTER'

ELSE

NULL

END

AS model,

COUNT(*) AS cars

FROM

`<project>.RDW_kentekens.kentekens_2020`

WHERE

Voertuigsoort = 'Personenauto'

AND merk LIKE 'TESLA%'

AND Datum_eerste_afgifte_Nederland >= 20080101

GROUP BY

date,

brand,

model

HAVING

model IS NOT NULL

ORDER BY

cars DESC

Ten years of Tesla toys

The first Roadsters arrived in the Netherlands in Q4 2009. Those drivers were some true early adopters. Let’s find out if the first four owners from that quarter still drive the same car!

SELECT

Datum_eerste_afgifte_Nederland AS date,

Datum_tenaamstelling AS ownership_date,

CASE

WHEN merk LIKE '%TESLA%' THEN 'TESLA'

ELSE

merk

END

AS brand,

CASE

WHEN Handelsbenaming LIKE '%MODEL 3%' THEN 'MODEL 3'

WHEN Handelsbenaming LIKE '%MODEL S%' THEN 'MODEL S'

WHEN Handelsbenaming LIKE '%MODEL X%' THEN 'MODEL X'

WHEN Handelsbenaming LIKE '%R__DSTER%' THEN 'ROADSTER'

ELSE

NULL

END

AS model,

kenteken AS license_plate,

CASE

WHEN Datum_eerste_afgifte_Nederland = Datum_tenaamstelling THEN 'TRUE'

ELSE

'FALSE'

END

AS first_owner

FROM

`<project>.RDW_kentekens.kentekens_2020`

WHERE

Voertuigsoort = 'Personenauto'

AND merk LIKE 'TESLA%'

AND Datum_eerste_afgifte_Nederland > 20080101

GROUP BY

date,

ownership_date,

brand,

model,

license_plate

HAVING

model IS NOT NULL

AND model = 'ROADSTER'

AND date < 20100101

ORDER BY

date

We have a winner! This bad boy (or girl) is still driving like it’s 2009.

What about the other Tesla’s? Are they still licensed by the first owner? We’ll find out.

SELECT

brand,

model,

ROUND(COUNT(CASE

WHEN first_owner = 'TRUE' THEN 1

ELSE

NULL

END

)/ COUNT(first_owner)*100,1) AS percentage_first_owners

FROM (

SELECT

Datum_eerste_afgifte_Nederland AS date,

Datum_tenaamstelling AS ownership_date,

CASE

WHEN merk LIKE '%TESLA%' THEN 'TESLA'

ELSE

merk

END

AS brand,

CASE

WHEN Handelsbenaming LIKE '%MODEL 3%' THEN 'MODEL 3'

WHEN Handelsbenaming LIKE '%MODEL S%' THEN 'MODEL S'

WHEN Handelsbenaming LIKE '%MODEL X%' THEN 'MODEL X'

WHEN Handelsbenaming LIKE '%R__DSTER%' THEN 'ROADSTER'

ELSE

NULL

END

AS model,

kenteken AS license_plate,

CASE

WHEN Datum_eerste_afgifte_Nederland = Datum_tenaamstelling THEN 'TRUE'

ELSE

'FALSE'

END

AS first_owner

FROM

`<project>.RDW_kentekens.kentekens_2020`

WHERE

Voertuigsoort = 'Personenauto'

AND merk LIKE 'TESLA%'

AND Datum_eerste_afgifte_Nederland > 20080101

GROUP BY

date,

ownership_date,

brand,

model,

license_plate

HAVING

model IS NOT NULL

ORDER BY

date)

GROUP BY

brand,

model

ORDER BY

percentage_first_owners DESC

Turns out that Tesla owners are quite fond of their cars.

Allright, wrapping up now. There is still a lot of data to play with, but I’ll leave you with the development of Tesla catalog prices in the Netherlands (according to the RDW).

SELECT

Datum_eerste_afgifte_Nederland AS date,

CASE

WHEN merk LIKE '%TESLA%' THEN 'TESLA'

ELSE

merk

END

AS brand,

CASE

WHEN Handelsbenaming LIKE '%MODEL 3%' THEN 'MODEL 3'

WHEN Handelsbenaming LIKE '%MODEL S%' THEN 'MODEL S'

WHEN Handelsbenaming LIKE '%MODEL X%' THEN 'MODEL X'

WHEN Handelsbenaming LIKE '%R__DSTER%' THEN 'ROADSTER'

ELSE

NULL

END

AS model,

ROUND(AVG(catalogusprijs)) AS price

FROM

`<project>.RDW_kentekens.kentekens_2020`

WHERE

Voertuigsoort = 'Personenauto'

AND merk LIKE 'TESLA%'

AND Datum_eerste_afgifte_Nederland > 20080101

GROUP BY

date,

brand,

model

HAVING

model IS NOT NULL

ORDER BY

date DESC

Average price (EUR) per Tesla model per year in the Netherlands

Note: I am learning everyday, please feel free to add your remarks and suggestions in the comment section or contact me via LinkedIn.