The guidance of pupils is built up throughout their schooling, with key stages at the end of year 10, year 11 and year 13. Thus, career guidance can begin as early as the end of the year 10, with a choice between an apprenticeship or school-based vocational training. As integration in employment is the primary objective of vocational training, knowing the integration rates of initial training courses makes it possible to inform the choices of young people and their families.

Since the early 1990s, the Directorate of Evaluation, Forecasting and Performance Monitoring (direction de l’Évaluation, de la prospective et de la performance – DEPP) has conducted two annual integration surveys making it possible to monitor the entry into active life of those leaving apprenticeships and school-based vocational training. These operations provide valuable information, but do not allow for the publication of statistics at establishment level, given the response rates observed.

However, the French law of 5 September 2018 on the freedom to choose one’s professional future provides for the publication of statistics by establishment on the educational pathway and integration into employment of young people in vocational training. In order to meet this need, DEPP and the Directorate of Research, Economic Studies and Statistics (direction de l’Animation de la recherche, des études et des statistiques – DARES) created this new system: InserJeunes is based on the matching of comprehensive administrative sources relating to the schooling of pupils and apprentices, exam success, apprenticeship contracts and salaried contracts of the Nominative Social Declaration (déclaration sociale nominative – DSN). The first results were disseminated in early February 2021.

On the national education side, the pupil databases can be matched using a specific identifier, the National Pupil Identifier (identifiant national élève - INE). However, there is no common identifier to match the “schooling” databases of pupils and apprentices with the salaried contracts of the DSN. These matching operations can therefore only be performed indirectly, based on the five variables of surname, forename(s), date and place of birth and sex. The introduction of an efficient matching tool based on indirect quality identifiers is therefore a key challenge for InserJeunes. This issue, which is well known to statisticians, is the subject of a vast amount of literature (see for example (Ouvrir dans un nouvel ongletKilss and Alvey, 1985)).

This article presents the overall approach adopted, the main sources used, the legal framework to be respected and the choices made between the different methods and digital matching tools based on indirect identifiers.

The main process is structured around several phases (Figure 1). Initially, for a given school year, the field of students in the final year of training is calculated by using three administrative “schooling” databases, each covering part of the InserJeunes field: apprenticeships, the school-based vocational route in an establishment of the French Ministry of National Education and the school-based vocational route in an establishment of the French Ministry of Agriculture. These databases contain the indirectly identifying variables, as well as the INE, and information on the establishment and the training followed.

In a second phase, the coverage of people leaving training is established, i.e. those who are no longer in training. To do this, we search, mainly on the basis of the INE, whether these pupils are still present the following school year in all the available pupil databases, i.e. the three databases already used in the previous phase as well as three additional databases, in order to be as exhaustive as possible. Any student found is recorded as still in education, the others are called people leaving training. This makes it possible to establish study continuation rates.

In the third phase, the pupil/apprentice databases are enriched with their exam passes (depending on the case, this matching is carried out using the INE or indirect identifiers), which makes it possible to calculate the rate of interruption during training.

Finally, in the fourth phase, the databases of those leaving school/apprenticeships are matched using indirect identifiers and the DSN, which makes it possible to measure a rate of salaried employment in France for those leaving training then the value added by the establishment to this employment rate. The DSN contains detailed information on the employee contracts (type of contract, wage, working time, socio-professional category, etc.) as well as on the employing establishment (sector, commune in which it is located, etc.): as a result, InserJeunes can also be used to carry out statistical studies, for example, on training/job suitability.

In InserJeunes, the matching rate does not provide any indication of the level of quality of the process. For example, when a person leaving training is not matched with the DSN, it is not possible to know whether this is because they are not in salaried employment or because of an error in the matching process. However, the system includes an annual matching based on indirect identifiers called “quality matching”, for which the theoretical rate is 100%: this involves reconciling the file listing apprentices as at 31 December who have an active apprenticeship contract with the DSN. Thus, the actual matching rate obtained is an indicator of the level of quality of the matching process.

The InserJeunes statistical process involves a total of ten matching operations based on indirect identifiers for each school year. The issue of matching based on indirect identifiers is therefore key. This requires developing a general matching process and then implementing it digitally in a generic and fast manner.

Figure 1. The sources of the InserJeunes system

In InserJeunes, each matching operation using indirect identifiers is carried out between two individual tables, without double counting. The matching process chosen for InserJeunes (Figure 2) consists of five successive steps, as is also the case in Peter Christen’s presentation (Figure 3) (Christen, 2012).

First of all, the data are standardised. Then comes the indexing stage, which consists of establishing a reasonable sized list of “potentially interesting” pairs. A pair corresponds to the cross-tabulation of a row from the first table with a row from the second table. Each pair therefore consists of a/some surname(s), a/some forename(s), a date of birth, a place of birth and a sex variable from each of the two tables being matched. Thirdly, a similarity is calculated for each of the five pairs of indirect identifiers in each pair (e.g. pair of names, pair of dates of birth). Fourthly, each pair is classified: pairs assumed to be from the same individual (i.e. where the five similarities calculated in the previous step are sufficiently high) are accepted and the others are rejected. Finally, the quality of the matching process is assessed.

Figure 2. The process for matching two tables

The indirect identifiers used in the matching are presented in heterogeneous formats in the various sources used in InserJeunes. Data standardisation, the first step in the matching process, consists in recoding the data according to a common structure in order to facilitate further processing.

For surnames and forenames, the following main processing operations are carried out:

Furthermore, date, month and year of dates of birth are stored in different variables in order to be able to perform calculations on birth dates even when they are only partially filled in.

The sources used in InserJeunes are of good quality. Indeed, there are no duplicates in the main sources, there are very few missing values for the indirect identifiers, and the COG of the commune of birth, which is much more precise than the name of the commune, is generally provided. This is due to the fact that, for all pupils, registration in the National Register of Pupil, Student and Apprentice Identifiers has already required all identifying variables to be provided. Similarly, each employee in the DSN source is subject to a certification procedure for the associated NIR, which also ensures a high level of quality of the identifying variables.

For the second step, that of indexing the data, an initial naive approach consists in analysing all the possible cross-tabulations between the two tables. However, the processing time increases quadratically with the number of observations in the tables to be matched and therefore, in practice, this method is no longer applicable beyond a certain threshold.

In the case of quality matching, about 315,000 apprentices are reconciled to the 7.5 million employees with an active contract in December of the year under consideration. The comprehensive analysis of each of the 2.3 trillion possible pairs would take several days or even several dozen days.

Furthermore, there is no point in analysing all the pairs. Indeed, where the surnames or forenames or dates of birth are very different, it is extremely unlikely that the pair will be accepted.

The objective of the indexing stage is to establish a reasonable sized list of “potentially interesting” pairs. The indexation method chosen must combine two apparently contradictory objectives: drawing up a list of pairs that is as small as possible, while ensuring that as many pairs as possible relating to the same individual are included in the list.

The most common method used to index data is to keep only those pairs that share the same modality of one or more indirectly identifying variables, which are called blocking keys (Christen, 2012; Jabot and Treyens, 2018). For example, if the blocking key is the code of the commune of birth, this means that only pairs sharing this code will be retained.

This method was not chosen for InserJeunes, as it has several disadvantages. First of all, it produces a list of “potentially interesting” pairs that is still too large. Furthermore, it leads to some pairs being discarded incorrectly. For example, if the blocking key is the code of the commune of birth, and if this variable has been entered incorrectly by an individual, then that individual will never be matched. Finally, it is not possible to apply a blocking key to surnames or forenames, as the slightest typing or spelling error will result in a different blocking key. To solve this problem, the surnames and forenames can indeed be replaced with their phonetic version. There are numerous phonetic algorithms that can be used for this purpose (Box 1). Let’s use the example of a pair with the names “christina” and “kristina”. These two forenames will have the same phoneticised version when using Phonex (i.e. c623). In contrast, “peter” and “pedro” have the same phoneticised version when using Soundex (i.e. p360), which means that, depending on the algorithm, we will still have too large a list of “potentially interesting” pairs. Moreover, these phonetic algorithms were initially developed for the English language and not all of them have been adapted for the French language.

Figure 3. The Five Steps Defined by Christen

Taking into account the drawbacks of the traditional blocking key approach, a specific indexing method has been developed for InserJeunes.

First, an exact match between the two tables is made for all the following fields: first surname, first forename, date, month and year of birth, code of the commune of birth and sex. As part of the quality matching, this step matches approximately 84% of apprentices with the DSN source. Once this is done, only about 50,000 apprentices remain to be matched with 7.2 million employees with an active contract in December. The volume of work is thus already divided by a factor of six.

Second, the union (without duplicates) of the following three lists of pairs is established:

InserJeunes uses the Levenshtein distance for surnames and forenames. This corresponds to the minimum number of characters that must be deleted, inserted or replaced to move from one surname/forename to another. The union of the different queries allows us to cover all frequently encountered cases and thus to keep almost all “potentially interesting” pairs. Moreover, as each query is relatively precise, the number of “potentially interesting” pairs retained is not too high. For quality matching, this indexing method leads to the retention of 1 million pairs, which is a reasonable number that can be processed sufficiently quickly in the later steps of the process.

This indexing method works very well on the volume of InserJeunes data but might not be suitable for matching tables of several tens of millions of rows.

The third step consists of enriching each of the pairs determined during indexing, with five “similarities” variables relating to surname, forename, date and commune of birth and sex. Each similarity is a measure of the degree of similarity of the indirect identifiers considered. Three different forms of similarity are used in InserJeunes, depending on the nature of the variables used as indirect identifiers.

First of all, the Jaro-Winkler similarity is implemented for surnames and forenames. The latter is an adaptation of the Jaro similarity developed by the statistician Winkler, which adds a “bonus” when the two strings being compared begin with a common prefix. The algorithm for calculating the Jaro similarity between two strings of characters is as follows:

There are numerous other ways of measuring the similarity between surnames and forenames. For example, some are based on the comparison of bigrams or trigrams between strings of characters, such as the Jaccard similarity coefficient. However, there does not appear to be a way that gives significantly better results than the Jaro-Winkler similarity for surnames and forenames (Ouvrir dans un nouvel ongletChristen, 2006).

Next, an InserJeunes-specific similarity was developed for birth dates. For example, when two dates of birth differ only in respect of the day of birth, the similarity is 0.9 if the difference is only in one of the two digits and 0.8 otherwise. If both dates of birth have the same year and the day of one date corresponds to the month of the other and vice versa, example:

then the similarity is 0.65.

Finally, for the sex variable, a binary similarity is used. For the commune of birth, the similarity is 1 when the COGs are identical. If they are different, the similarity is 0.5 if the department code is identical and 0 otherwise.

The fourth step is to make a decision regarding each pair, using the similarities calculated in the previous step. When the similarities are high, i.e. close to 1, the pair is accepted. Otherwise, the pair is rejected.

A simple initial approach entails calculating an overall similarity for each pair, a strictly increasing function of the similarities of the different fields. The pairs with an overall similarity above a certain threshold are accepted, with the others being rejected. The function and threshold are selected empirically, by analysing a sample of pairs for which the status (accepted or rejected) has been annotated manually. This method has the benefit of simplicity; however, the selection of the function and the threshold remain arbitrary and, therefore, there is no guarantee that these choices are optimal.

In view of the limitations of the first approach, classifications using supervised machine learning algorithms have been tested (random forests and support vector machines (SVMs) or wide margin separators (Box 3)). The approach consists of training the algorithm on a sample of pairs the status of which has been entered manually, and then applying it to the other pairs. The optimal parameters of each algorithm are determined by cross-validation by maximising the f-measure metric.

In the case of quality matching, the simple approach, random forests and SVMs all gave similar and excellent results so, ultimately, rough classification was selected for InserJeunes.

How can this result, which is surprising in principle, be explained? One way of presenting our problem is to view each pair as a point in a 5-dimensional space, with the dimensions being those of the 5 similarities (surname, forename, date of birth, commune of birth and sex). As the sex variable is not very selective, it could be eliminated from the analysis, which would reduce the space to 4 dimensions. In each dimension, the similarities are assigned values between 0 and 1.

The problem is therefore to find a separation boundary between accepted and rejected points/pairs in a space [0; 1]⁴, i.e. a space of very small size. Furthermore, the area “near” to the point (1,1,1,1) is the area in which almost all the pairs to be accepted are located. Thus, the points corresponding to the pairs to be accepted are not too “mixed in” with the points corresponding to the pairs to be rejected. It is therefore relatively easy to solve this type of problem, which explains why all the methods tested give equally very good results.

Probabilistic classification, originally developed by (Fellegi and Sunter, 1969), is another method developed specifically for matching using indirect identifiers and frequently cited in the literature. This method has not been investigated by the InserJeunes team, as it is not possible, to the best of our knowledge, to implement it quickly once the volume of data is quite large.

Given the key nature of matches using indirect identifiers in InserJeunes, it was appropriate to assess their quality. This is the fifth and final step in the process.

The assessment requires having a sample of pairs the status of which (accepted or rejected) has been annotated manually and which has not been used in the classification step. For this sample, the prediction resulting from the supervised classification is compared with the true status of the pair, i.e. the one established manually, which makes it possible to obtain four quantities initially:

For example, a false negative pair is a pair that has been rejected by the classification algorithm but accepted by the human who performed the annotation. Based on these four quantities, it is possible to establish several measures of overall quality.

The best known measure is accuracy, which is (TPs+TNs)/total number of pairs. However, like any measure that uses true negatives, it is not suitable. Why? Because the data is unbalanced: there are many pairs the true status of which is rejected and few pairs with a true status of accepted. In the case of quality matching, about 40,000 pairs are accepted in 1 million pairs, meaning that at least 950,000 pairs have the true status “rejected”. A naive classifier that rejects 100% of pairs therefore has an accuracy of at least (0+950,000)/(1,000,000) or 95%.

Three other measures have therefore been selected in the InserJeunes system:

In the case of the quality matching, the precision is 95% and the recall is 99%. In total, 97% of apprentices are matched in quality matching (84% through direct matching and 13% through best matching), a matching rate close to the theoretical rate of 100%.

Several matching software packages were tested as part of the InserJeunes project: FEBRL by Peter Christen, matchID developed at the French Ministry of the Interior and two R libraries. In R, the best implementation seems to be the R library fastlink, but according to the documentation it takes about 8 hours to process the matching of tables with 300,000 rows and it relies on the probabilistic classification of Fellegi and Sunter. Only the matchID tool met the needs, but its implementation proved to be relatively complex.

The InserJeunes team also studied the literature on other software. The Italian national statistics institute Istat has developed a tool called RELAIS which uses, in particular, probabilistic classification; but for 100,000 observations or more, the processing time is about 1.25 hours (Eurostat, 2009). In the United States, the Census Bureau has developed the bigMatch tool specifically to process large volumes; however, it seems to perform only the data indexing phase and it is written in C, which makes it difficult to integrate with machine learning tools.

As a result of this comparative work, it was decided to develop a specific tool that would meet four major needs of InserJeunes:

The InserJeunes matching tool will be made available in open source in summer 2021. However, for matching projects concerning much larger tables, the matchID tool seems to be more suitable, particularly because it performs the indexing through elastic search queries and not through SQL queries.

In view of the experience gained during the InserJeunes project, what lessons can be learned?

Firstly, it is clear that the overall quality of the matching process is very highly dependent on the quality of the indirect identifier variables. The context of InserJeunes was favourable, as the variables are provided correctly in the databases used. Respecting each of the steps is indeed essential to the success of the operation: correctly standardising the data in order to facilitate subsequent processing, spending time determining the best way to calculate similarities (this work is called “feature engineering” in machine learning) in order to significantly increase the quality of the classification, none of these activities is superfluous. The assessment, when based on a sample of manually annotated pairs that were not used in the classification stage, makes it possible to guarantee the overall quality of the process and, in particular, to check that there has been no overfitting. For InserJeunes, the ability to carry out annual “quality matching” is an opportunity, but not all information systems will be suitable for this.

From an IT point of view, the fact that it is based on several open source libraries has enabled developments to be carried out within a short time-frame. The decision to describe each matching specification in XML, respecting a specific matching language, made it possible to specify and then quickly integrate into the production chain all the matching operations needed by InserJeunes. Overall, these decisions made it possible to complete the implementation of the core of InserJeunes at the end of 2020, and to then disseminate the first results relating to young people leaving school-based vocational training and apprenticeships in the summer of 2018 and 2019 at the beginning of February 2021.

Ouvrir dans un nouvel ongletLoi n°2018-771 du 5 septembre 2018 pour la liberté de choisir son avenir professionnel. In: site de Légifrance. [online]. [Accessed 27 May 2021].

Ouvrir dans un nouvel ongletLoi n°51-711 du 7 juin 1951 sur l’Obligation, la coordination et le secret en matière de statistiques. In: site de Légifrance. [online]. Mise à jour du 25 mars 2019. [Accessed 27 May 2021].

Ouvrir dans un nouvel ongletRèglement (UE) 2016/679 du Parlement européen et du Conseil du 27 avril 2016 relatif à la protection des personnes physiques à l’égard du traitement des données à caractère personnel et à la libre circulation de ces données. In: site EUR-Lex. [online]. [Accessed 27 May 2021].