Retrieve inventors patenting activity from list of target firms

[filoppo123]

Hi all,
I am carrying out a master thesis on pre and post-acquisition performances of the acquired inventors. Basically, I have to identify the inventors that belong to a sample of 450+ firms in a given time window and then retrieve their patenting activity of their whole career.

I managed to identify the inventors' IDs and names through this query:

SELECT DISTINCT *
FROM tls201_appln a
INNER JOIN tls204_appln_prior b on a.appln_id = b.appln_id
INNER JOIN tls201_appln b2 on b.prior_appln_id = b2.appln_id
INNER JOIN tls207_pers_appln c on a.appln_id = c.appln_id
INNER JOIN tls206_person d ON c.person_id = d.person_id
WHERE a.granted = 'Y'
AND a.appln_auth IN ('EP', 'US')
AND a.appln_filing_year BETWEEN 2000 AND 2010
AND a.appln_id IN
(SELECT c.appln_id
FROM tls207_pers_appln c
WHERE c.person_id IN
(SELECT d.person_id
FROM tls206_person d
WHERE d.psn_name LIKE '%acunia%'))

The limitation of this query however is that it retrieves only those patents generated inside the target companies. Since I must identify possible leavers (inventors that left the firm and started patenting elsewhere) and I'm interested in the date of their first patent (as proxy of their age), I now need to gather all the patents they've generated in their career. Do you know how could I modify the query to achieve this result?

Thank you very much in advance,
I would really appreciate any advice,

Very Best,
Filippo

[EPO / PATSTAT Support]

Hello Filippo,
I assume you are using PATSTAT Online, which rather complicates doing the kind of data aggregation you need. PATSTAT Online does not allow users to create intermediate tables or views. Your question is a typical example where one would need the intermediate results from an initial first query to be used in a second query. With "sub queries" we can obtain a more or less similar result, but it's not really elegant.

In an nutshell: you would like to get a list of patents filed by inventors from your 450+ list but "across their career" outside the 450+ list.
Using your example: psn_name LIKE '%acunia%'; I would do the following -but there might be better or shorter routes- .First check if the wild card search using "psn_name LIKE '%acunia%' " effectively retrieves the variations of the applicant name that you want to be included in your set.

Code: Select all

SELECT DISTINCT applicant.person_id, applicant.psn_name, applicant.person_name, applicant.person_address , applicant.psn_id
FROM tls207_pers_appln a JOIN tls206_person applicant ON a.person_id = applicant.person_id
WHERE applicant.psn_name LIKE '%acunia%' and a.applt_seq_nr > 0 and a.invt_seq_nr = 0

Assume we are happy with the result, then we adapt the query with a new JOIN via tls207 to find all the inventors linked to the "set of" applicant(s) from subquery 1.

Code: Select all

SELECT DISTINCT applicant.person_id, applicant.psn_name, applicant.person_name, 
applicant.person_address , applicant.psn_id,inventors.*
FROM tls207_pers_appln a JOIN tls206_person applicant ON a.person_id = applicant.person_id
join tls207_pers_appln b on a.appln_id =b.appln_id
join tls206_person inventors on b.person_id = inventors.person_id
WHERE applicant.psn_name LIKE '%acunia%' and a.applt_seq_nr > 0 and a.invt_seq_nr = 0
and b.invt_seq_nr>0

Looking at the results, we can see that there are quite some variations in the way the applicant names have been registered for what we may assume are the same physical persons.
Assume we are happy with this result, now we use this query and extend it so that we retrieve all the applications from those inventors across ALL possible applicants. With other words, we widen the scope to include companies that are not retrieved via the "applicant.psn_name LIKE '%acunia%'" criteria.
(In a local installed PATSTAT database, I would have stored the results in from subquery 2 in a seperte table.)

Code: Select all

SELECT ap.appln_id, appln_auth,appln_nr, appln_kind, appln_filing_date,appln_nr_epodoc,
pers.person_id, ap.applt_seq_nr, ap.invt_seq_nr,pers.person_name, pers.person_address
,pers.person_ctry_code,pers.psn_name,pers.psn_id
FROM tls201_appln app
left join tls207_pers_appln ap on app.appln_id = ap.appln_id
left join tls206_person pers on ap.person_id = pers.person_id
where ap.appln_id in 
(SELECT DISTINCT b_extended.appln_id
FROM tls207_pers_appln a JOIN tls206_person applicant ON a.person_id = applicant.person_id
join tls207_pers_appln b on a.appln_id =b.appln_id
join tls206_person inventors on b.person_id = inventors.person_id
join tls206_person invt_extended on inventors.psn_id = invt_extended.psn_id
join tls207_pers_appln b_extended on invt_extended.person_id = b_extended.person_id
WHERE applicant.psn_name LIKE '%acunia%' and a.applt_seq_nr > 0 and a.invt_seq_nr = 0
and b.invt_seq_nr>0)
order by appln_filing_date asc, ap.appln_id, applt_seq_nr, invt_seq_nr

We can now see that a number of new companies appear in the list: AVIOVISION, SMARTMOVE & TAKE FIVE. What we don't know is whether these are companies where inventors have worked before (or after) Acunia, or whether this is simply due to a name change of the same company or maybe mergers or acquisitions. As the address information is missing, it is even harder to tell. One could maybe also look at "inventor networks" to see if the same constellations appear, or the time frame of the filings for the 4 companies. (sequential or in parallel --> can easily be checked via a cross reference statistic in PATSTAT Online, see excel sheet)
The result are here:

sub.sql.xlsx

(136.67 KiB) Downloaded 171 times

If you want to do this for 450+ companies, you will need a local PATSTAT version to store the results from the 450 companies in separate tables, unless your research is looking at the interaction/changes between those 450 companies. Even then, I would probably extract the data and maybe remove the inventors that do not belong of the first 450+ companies.
I hope this helps you forward.

[filoppo123]

Dear Patstat Support,
Thank you very much for your prompt reply, you have been of very much help. In the past 2 days I have been working on a query that (I thought) fixed the problem. However, your query returns more than twice the rows in respect to mine. I uploaded my query below, just in case you have the patience to tell me where I am wrong.

More importantly, I forgot to mention that I have to retrieve only those inventors that were active in a specific time window (in the case of Acunia, between 1999 and 2011). Do you know how could I insert this additional constraint in your query? Just to be clear, this constraint aims just at retrieving those inventors that filed a patent in a given time window. For each of them, I still need the patenting activity of the whole career (therefore including also patents filed before 1999 or after 2011).

For what concerns instead the criteria --> "applicant.psn_name LIKE '%acunia%'"
do you think it is a proper way to search for the target firms, or you would suggest alternative procedures?

Once again, thank you for your help,

Very Best,
Filippo

Code: Select all

 SELECT DISTINCT  *
FROM tls201_appln a
LEFT OUTER JOIN tls204_appln_prior b on a.appln_id = b.appln_id
LEFT OUTER JOIN tls201_appln b2 on b.prior_appln_id = b2.appln_id
LEFT OUTER JOIN tls207_pers_appln c on a.appln_id = c.appln_id
LEFT OUTER JOIN tls206_person d ON c.person_id = d.person_id
WHERE a.granted = 'Y'
AND d.doc_std_name_id IN
(SELECT d3.doc_std_name_id
from tls206_person d3 
LEFT OUTER JOIN tls207_pers_appln c3 on c3.person_id = d3.person_id
where c3.appln_id IN
	(SELECT c4.appln_id
	FROM tls207_pers_appln c4
    LEFT OUTER JOIN tls201_appln ap on ap.appln_id = c4.appln_id
    WHERE ap.appln_filing_year BETWEEN 
	1999 AND 2011 AND c4.applt_seq_nr > 0 AND c4.person_id IN
		(SELECT d.person_id 
        FROM tls206_person d
		WHERE d.psn_name LIKE '%Acunia%'))) 

[EPO / PATSTAT Support]

Hello Filippo,
here is adapted version that takes into account the year of filing by joining tls201 and adding the timeframe in the WHERE clause.

Code: Select all

SELECT ap.appln_id, appln_auth,appln_nr, appln_kind, appln_filing_date,appln_nr_epodoc,
pers.person_id, ap.applt_seq_nr, ap.invt_seq_nr,pers.person_name, pers.person_address
,pers.person_ctry_code,pers.psn_name,pers.psn_id
FROM tls201_appln app
left join tls207_pers_appln ap on app.appln_id = ap.appln_id
left join tls206_person pers on ap.person_id = pers.person_id
where ap.appln_id in 
(SELECT DISTINCT b_extended.appln_id
FROM tls207_pers_appln a JOIN tls206_person applicant ON a.person_id = applicant.person_id
join tls207_pers_appln b on a.appln_id =b.appln_id
join tls206_person inventors on b.person_id = inventors.person_id
join tls206_person invt_extended on inventors.psn_id = invt_extended.psn_id
join tls207_pers_appln b_extended on invt_extended.person_id = b_extended.person_id
join tls201_appln on a.appln_id = tls201_appln.appln_id and appln_filing_year between 1999 and 2011 
WHERE applicant.psn_name LIKE '%acunia%' and a.applt_seq_nr > 0 and a.invt_seq_nr = 0
and b.invt_seq_nr>0)
order by appln_filing_date asc, ap.appln_id, applt_seq_nr, invt_seq_nr

I did not look into detail in your query, but at first sight you joined the tls204_appln_prior table without any obvious need for it - at least if I only look at your specifications. Joining that table will automatically reduce the sample because it forces the query to only take into account applications for which a priority is filed. (so less results...)