Counting international patent applications

Here you can post your opinions, ask questions and share experiences on the PATSTAT product line. Please always indicate the PATSTAT edition (e.g. 2015 Autumn Edition) and the database (e.g. PATSTAT Online, MySQL, MS SQL Server, ...) you are using.
Post Reply

carmen.calatrava
Posts: 8
Joined: Mon May 22, 2017 5:36 pm

Counting international patent applications

Post by carmen.calatrava » Mon Sep 24, 2018 1:06 pm

Dear PATSTAT community,

I am interested to know the number of patents applications from Austrian applicants in China and US. It is not clear to me from which attributes I can get this information.

I have created the following query, but the results are too low to be correct (16 in CN and 309 in US). They should be ~700 in CN and 2000 in US. Do you have any idea on how to do this correctly?

Code: Select all

SELECT COUNT(internat_appln_id), a.receiving_office
FROM tls201_appln a join tls209_appln_ipc aipc on a.appln_id = aipc.appln_id  
join tls207_pers_appln pa on a.appln_id = pa.appln_id  
join tls206_person p on p.person_id = pa.person_id  
WHERE  a.appln_id < 900000000 -- exclude artificial applications  
AND pa.applt_seq_nr > 0 -- consider only applicants
AND a.ipr_type = 'PI'  
AND a.appln_filing_year = 2012
AND p.person_ctry_code = 'AT'  
AND a.appln_auth = 'WO'
AND (a.receiving_office = 'US'
OR a.receiving_office = 'CN')
GROUP BY a.receiving_office  
ORDER BY a.receiving_office
Thank you so much in advance!
Best regards,
Carmen C.


EPO / PATSTAT Support
Posts: 95
Joined: Thu Feb 22, 2007 5:33 pm

Re: Counting international patent applications

Post by EPO / PATSTAT Support » Mon Sep 24, 2018 3:52 pm

Dear Carmen,
as far as I can see, I think you are counting something different then what you want to count.
In your query you count the internat_appn_id; that attribute links the PCT application to a later application referring to that PCT. But as you already are looking for PCT applications (receiving office in the WHERE clause), you are counting PCT applications based on earlier PCT applications. That is of course possible but rather exceptional an probably not what you want.

So question remains whether you want to count PCT applications or the national phase of earlier filed PCT applications.

Counting PCT applications: most Austrian patent applicants will probably file the PCT applications at the EPO, the AT patent office and WIPO (International Bureau). Below is a query to count those applications (I extended the list to make it clear):

Code: Select all

SELECT 
-- a.appln_auth+appln_nr, a.appln_kind, a.receiving_office ,person_name, person_ctry_code, a.internat_appln_id
COUNT(distinct(a.appln_id)), a.receiving_office
FROM tls201_appln a
join tls207_pers_appln pa on a.appln_id = pa.appln_id  
join tls206_person p on p.person_id = pa.person_id  
WHERE  a.appln_id < 900000000 -- exclude artificial applications  
AND pa.applt_seq_nr > 0 -- consider only applicants
AND a.ipr_type = 'PI'  
AND a.appln_filing_year = 2012
AND p.person_ctry_code = 'AT'  
AND a.appln_auth = 'WO'
AND (a.receiving_office = 'US'
OR a.receiving_office = 'CN'
OR a.receiving_office = 'IB'
OR a.receiving_office = 'EP'
or a.receiving_office = 'AT')
GROUP BY a.receiving_office
--a.appln_auth+appln_nr, a.appln_kind, person_name, person_ctry_code, a.internat_appln_id, a.receiving_office
ORDER BY a.receiving_office
In case you want to count the number of PCT applications that entered CN or US national phase, based on an earlier PCT (wherever it was filed), then you can use this query:

Code: Select all

SELECT  COUNT(distinct(a.appln_id)), a.appln_auth
FROM tls201_appln a
join tls207_pers_appln pa on a.appln_id = pa.appln_id  
join tls206_person p on p.person_id = pa.person_id  
WHERE  a.appln_id < 900000000 -- exclude artificial applications  
and a.internat_appln_id <> 0 -- counts national phase applications based on an ealier PCT
AND pa.applt_seq_nr > 0 -- consider only applicants
AND a.ipr_type = 'PI'  
AND a.appln_filing_year = 2012
AND p.person_ctry_code = 'AT'  
AND a.appln_auth in ( 'CN', 'US','EP')
GROUP BY a.appln_auth
ORDER BY a.appln_auth
If you now look a the result of the above query (where I have added EP to also list EP applications that originate from a PCT filing), you will see that China is missing. That is an artefact due to missing CN data. Knowing that we don't have that data, we can work around this problem by looking at the originating PCT application. If the PCT application has an AT applicant, we assume that the CN national phase also has a CN applicant. This is definitely not always the case; the CN applications can be attributed to a local CN applicant affiliated to the AT company - with a different name. If your research is about the "originating country of the innovation", the data of the PCT application can be used to enrich the missing CN data. If we agree to that presumption, then let us simply assume that the AT applicants filing a PCT are also the CN applicants of the respective national phase applications. Here is a query that retrieves those CN applications.

Code: Select all

Select count(distinct(tls201_appln.appln_id)), appln_auth 
from tls201_appln
where tls201_appln.internat_appln_id in (
    SELECT distinct a.appln_id
    FROM tls201_appln a
    join tls207_pers_appln pa on a.appln_id = pa.appln_id  
    join tls206_person p on p.person_id = pa.person_id  
    WHERE  a.appln_id < 900000000 -- exclude artificial applications  
    AND pa.applt_seq_nr > 0 -- consider only applicants
    AND a.ipr_type = 'PI'  
    AND a.appln_filing_year = 2012
    AND p.person_ctry_code = 'AT'  
    AND a.appln_auth = 'WO') 
    and appln_auth in ( 'CN')
	group by appln_auth
	order by appln_auth;
If you would like to fine-tune this even further, you could compare the names of the applicants and see if they are the same. (or similar). In all honesty, you will also find differences with the EP and US data due to for example inventors being registered as applicants. So the optimal solution might be to use both approaches and de-duplicate the results. (and as the list is limited, you could even check it manually if the above methodology is good)
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


Post Reply