Time window to control forward-citations

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

EduardoJJM
Posts: 3
Joined: Mon Jun 15, 2020 8:25 am

Time window to control forward-citations

Post by EduardoJJM » Fri Jul 10, 2020 9:26 am

Dear EPO and other research fellows,

How can I integrate the time-span (3 and or 5) to retrieve forward citations?
So far I have been using the following query to retrieve information on forward citations:

Code: Select all

SELECT *
FROM tls201_appln
join tls207_pers_appln on tls201_appln.appln_id = tls207_pers_appln.appln_id
join tls206_person on tls207_pers_appln.person_id = tls206_person.person_id
where psn_id in (xxxxxxxxxx)
and tls201_appln.appln_filing_year in ('2007', '2008', '2009', '2010', '2011', '2012', '2013', '2014', '2015', '2016', '2017')
order by tls201_appln.appln_filing_year
However, when I have tried to integrate the 3/year time span explained in DeRassenfose et al (2014), the results exclude patents that appear using the first query. The query I used reads as follows:

Code: Select all

SELECT *
FROM tls201_appln
join tls207_pers_appln on tls201_appln.appln_id = tls207_pers_appln.appln_id
join tls206_person on tls207_pers_appln.person_id = tls206_person.person_id
join tls211_pat_publn on tls201_appln.appln_id = tls211_pat_publn.appln_id
join tls212_citation on tls211_pat_publn.pat_publn_id = tls212_citation.pat_publn_id
where tls206_person.psn_id in (xxxxxxxxx)
and tls201_appln.appln_filing_year in ('2007', '2008', '2009', '2010', '2011', '2012', '2013', '2014', '2015', '2016', '2017')
and [b](DATEDIFF (YEAR, tls201_appln.earliest_publn_date, tls211_pat_publn.publn_date) <=  3)[/b]
order by tls201_appln.appln_filing_year
Could you please explain to me where is the mistake? And more important, how can I properly integrate the 3-5 years time span for controlling the forward citations?

Thank you so much for your kind time and attention.

Best regards,
Eduardo


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

Re: Time window to control forward-citations

Post by EPO / PATSTAT Support » Mon Jul 13, 2020 3:21 pm

Hello Eduardo,
the methodology in "An introduction to the Patstat database with example queries - Query8" is based on calculating the difference between the earliest publication of an application and the earliest publication date of its forward citations.
In the publication we used a sample based on wind energy applications filed in 2005 - reflected by the conditions in Query 1. But you can of course use whatever criteria you want, such as the PSN_ID - to count all the forward citations from applications assigned to a "PATSTAT STANDARDISED NAME"_id.
In order to stay in line with the publication -I used the Wind energy patents- and here is the updated query -according to the publication- which will run on PATSTAT Online.

The "our sample t1"-data is defined by the following sub-query which generates 2191 patent applications in the current PATSTAT2020a release.

Code: Select all

SELECT
DISTINCT table1.appln_id, table1.appln_auth, table1.appln_nr, table1.appln_kind, table1.appln_filing_date
FROM
tls201_appln table1
INNER JOIN
tls209_appln_ipc table2 ON table1.appln_id = table2.appln_id
WHERE
year(table1.appln_filing_date) = 2005
AND (table1.appln_kind = 'A' OR table1.appln_kind = 'W')
AND table2.ipc_class_symbol LIKE 'F03D%'
Integrating the above query into Query 8 would then look like this (PATSTAT Global users would probably create a intermediate table to store the results):

Code: Select all

SELECT
t1.appln_id, COUNT(distinct t3.pat_publn_id) AS cites_3y
FROM
--our_sample t1
--XXXX
(SELECT
DISTINCT table1.appln_id, table1.appln_auth, table1.appln_nr, table1.appln_kind, table1.appln_filing_date
FROM
tls201_appln table1
INNER JOIN
tls209_appln_ipc table2 ON table1.appln_id = table2.appln_id
WHERE
year(table1.appln_filing_date) = 2005
AND (table1.appln_kind = 'A' OR table1.appln_kind = 'W')
AND table2.ipc_class_symbol LIKE 'F03D%') as t1
--XXXX
INNER JOIN
(SELECT
appln_id, MIN(publn_date) AS earliest_date
FROM
tls211_pat_publn
GROUP BY appln_id) t2 ON t1.appln_id = t2.appln_id
INNER JOIN
tls211_pat_publn t2b ON t2b.appln_id = t2.appln_id
INNER JOIN
tls212_citation t3 ON t2b.pat_publn_id = t3.cited_pat_publn_id
INNER JOIN
tls211_pat_publn t4 ON t3.pat_publn_id = t4.pat_publn_id
WHERE
t2b.publn_auth = 'DE'
AND t4.publn_auth = 'EP'
AND YEAR(t2.earliest_date)!= 9999
AND YEAR(t4.publn_date)!= 9999
AND t4.publn_date <=  DATEADD( year , 3 ,t2.earliest_date )
GROUP BY t1.appln_id
ORDER BY COUNT(distinct t3.pat_publn_id) DESC, t1.appln_id ASC
The query generates a simple list giving the application ID from the cited application, and the number of forward citations over a time frame of 3 years after the application was published. The query might serve as a "fairly naive" patent value indicator, and researchers and economists will normally look much deeper at the underlying forces and ask questions that serve impact analysis: "who cites who ?" "are patents filed by universities cited more in comparison to patents filed by companies", "can we identify inbound/outbound technology flows by analysing the classification codes assigned to cited and citing applications ?" "can we identify technology flows by looking at the countries of applicants/inventors", "can we identify emerging technologies by unusual cited document combinations" etc... There is plenty of research done in this area. But it is all based on a good and correct understanding of the data in the tables tls212 (tls214, tls215).

Here is a variation that gives the application and publication instead of a number and a count:

Code: Select all

SELECT t1.appln_id,
t1.appln_auth+t1.appln_nr+t1.appln_kind cited, t2.earliest_date
, t4.publn_auth+t4.publn_nr+t4.publn_kind citing,t4.publn_date
FROM
--our_sample t1
--XXXX
(SELECT
DISTINCT table1.appln_id, table1.appln_auth, table1.appln_nr, table1.appln_kind, table1.appln_filing_date
FROM
tls201_appln table1
INNER JOIN
tls209_appln_ipc table2 ON table1.appln_id = table2.appln_id
WHERE
year(table1.appln_filing_date) = 2005
AND (table1.appln_kind = 'A' OR table1.appln_kind = 'W')
AND table2.ipc_class_symbol LIKE 'F03D%') as t1
--XXXX
INNER JOIN
(SELECT
appln_id, MIN(publn_date) AS earliest_date
FROM
tls211_pat_publn
GROUP BY appln_id) t2 ON t1.appln_id = t2.appln_id
INNER JOIN
tls211_pat_publn t2b ON t2b.appln_id = t2.appln_id
INNER JOIN
tls212_citation t3 ON t2b.pat_publn_id = t3.cited_pat_publn_id
INNER JOIN
tls211_pat_publn t4 ON t3.pat_publn_id = t4.pat_publn_id
WHERE
t2b.publn_auth = 'DE'
AND t4.publn_auth = 'EP'
AND YEAR(t2.earliest_date)!= 9999
AND YEAR(t4.publn_date)!= 9999
AND t4.publn_date <=  DATEADD( year , 3 ,t2.earliest_date )
order by t1.appln_filing_date, t1.appln_id
;
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


EduardoJJM
Posts: 3
Joined: Mon Jun 15, 2020 8:25 am

Re: Time window to control forward-citations

Post by EduardoJJM » Tue Jul 14, 2020 12:35 pm

Dear EPO,

Thank you so much for all the comments and guidance. After having read your queries and further information on the tables involved; I have adapted the following query to my research criteria. This query is the only one that seemed to work after including all the necessary clauses. It reads as follows:

Code: Select all

SELECT a.appln_id, a.appln_nr_epodoc, a.appln_auth, a.appln_filing_year, a.ipr_type, a.granted, a.docdb_family_id, a.nb_citing_docdb_fam, a.nb_applicants, a.nb_inventors, tls206_person.psn_id, tls206_person.psn_name
FROM tls201_appln a
join tls207_pers_appln on a.appln_id = tls207_pers_appln.appln_id
join tls206_person on tls207_pers_appln.person_id = tls206_person.person_id
join 
(SELECT
appln_id, MIN(publn_date) AS earliest_date
FROM
tls211_pat_publn
GROUP BY appln_id ) c on a.appln_id = c.appln_id
join 
tls211_pat_publn c1 on a.appln_id = c1.appln_id
join
tls212_citation d ON c1.pat_publn_id = d.cited_pat_publn_id
INNER JOIN
tls211_pat_publn e ON d.pat_publn_id = e.pat_publn_id
where psn_id in ('18560738')
and a.appln_filing_year in ('2007', '2008', '2009', '2010', '2011', '2012', '2013', '2014', '2015', '2016', '2017')
and YEAR(c.earliest_date)!= 9999
AND YEAR(e.publn_date)!= 9999
AND e.publn_date <=  DATEADD( year , 3 , c.earliest_date )
order by a.appln_filing_year, a.appln_id
This query retrieves (as far as I have been able to cross-check) the applications that have at least 1 citation within the 3 years. I don't know if you could help me to confirm that fact. However, when I tried to select and retrieve the column that shows the forward citation using (COUNT(distinct d.pat_publn_id) the server shows me an errors message... So I don't know how to integrate correctly in the query.

Secondly, I really appreciate all your comments on the possibilities that depth patent-level analysis can add to research. So far, the number of forward citation will work for my project. I hope that in the near future I will be able to master PATSTAT so I can contribute to the scholar conversation with more complex analysis.

Thank you so much for your time and attention.
Best regards,
Eduardo


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

Re: Time window to control forward-citations

Post by EPO / PATSTAT Support » Wed Jul 15, 2020 3:34 pm

Here is your query with a count of the forward citations.

Code: Select all

SELECT a.appln_id, a.appln_nr_epodoc, a.appln_auth, a.appln_filing_year, a.ipr_type, a.granted, a.docdb_family_id, 
a.nb_citing_docdb_fam, a.nb_applicants, a.nb_inventors, tls206_person.psn_id, tls206_person.psn_name,
count(distinct(e.pat_publn_id)) forward_citations
FROM tls201_appln a
join tls207_pers_appln on a.appln_id = tls207_pers_appln.appln_id
join tls206_person on tls207_pers_appln.person_id = tls206_person.person_id
join 
(SELECT
appln_id, MIN(publn_date) AS earliest_date
FROM
tls211_pat_publn
GROUP BY appln_id ) c on a.appln_id = c.appln_id
join 
tls211_pat_publn c1 on a.appln_id = c1.appln_id
join
tls212_citation d ON c1.pat_publn_id = d.cited_pat_publn_id
INNER JOIN
tls211_pat_publn e ON d.pat_publn_id = e.pat_publn_id
where psn_id in ('18560738')
and a.appln_filing_year in ('2007', '2008', '2009', '2010', '2011', '2012', '2013', '2014', '2015', '2016', '2017')
and YEAR(c.earliest_date)!= 9999
AND YEAR(e.publn_date)!= 9999
AND e.publn_date <=  DATEADD( year , 3 , c.earliest_date )
group by a.appln_id, a.appln_nr_epodoc, a.appln_auth, a.appln_filing_year, a.ipr_type, a.granted, a.docdb_family_id, 
a.nb_citing_docdb_fam, a.nb_applicants, a.nb_inventors, tls206_person.psn_id, tls206_person.psn_name
order by a.appln_filing_year, a.appln_id
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


EduardoJJM
Posts: 3
Joined: Mon Jun 15, 2020 8:25 am

Re: Time window to control forward-citations

Post by EduardoJJM » Tue Jul 28, 2020 8:12 am

Dear EPO,

Awesome! :D
Thank you so much for your help!

Best regards,
Eduardo


uleonidio
Posts: 1
Joined: Wed Nov 24, 2021 3:46 pm

Re: Time window to control forward-citations

Post by uleonidio » Wed Nov 24, 2021 4:16 pm

Dear EPO and other research fellows,
Could you help em about the question below, please.
I'm research about patent quality for my PHD based on paper Measuring Patent Quality: Indicators of Technological and Economic Value by Mariagrazia Squicciarini, Hélène Dernis, Chiara Criscuolo
and I want to use the Algorithm description in the patstat online: Forward citations index, p. 41, as below, but it hasn't been possible. Any tip about, please?

Code: Select all

UPDATE [Indicator_Table]
 SET Fwd_Cits5 = t.Cits_Total
 FROM [Indicator_Table] i
 INNER JOIN (SELECT Appln_id, COUNT(distinct Citing_appln_id) as 'Cits_total'
 FROM
 ((SELECT Cited_appln_id as 'Appln_id', citing_appln_id , 'EP_EP' as 'Source'
 FROM [OECD_CIT_EP_Citations]
 WHERE Cited_App_auth = 'EP' AND Citn_lag_month <=60)
 UNION
 (SELECT e.EP_Eqv_appln_id, c.Citing_appln_id, 'Equiv_EP' as 'Source'
 FROM [OECD_CIT_EP_Equiv] e
 INNER JOIN [OECD_CIT_EP_Citations] c
 ON e.Cited_Appln_id = c.Cited_appln_id
 WHERE e.Eqv_App_auth = 'EP'
 AND DATEDIFF(month, c.Citing_pub_date, e.Eqv_Pub_date) <=60)
 UNION
 (SELECTc.Cited_appln_id as 'Appln_id', e.EP_appln_id , 'EP_WO_EP' as 'Source'
 FROM [OECD_CIT_WO_Citations] c
 INNER JOIN [OECD_CIT_EP_WO] e
 ON c.Citing_appln_id = e.WO_Appln_id
 INNER JOIN [OECD_CIT_EP_Cit_Counts cc
 ON cc.EP_Appln_id = e.EP_Appln_id
 WHERE c.Cited_App_auth = 'EP'
 AND DATEDIFF(MONTH, c.Citing_pub_date, cc.EP_pub_date) <=60)
 UNION
 (SELECT eq.EP_Eqv_appln_id as 'Appln_id', e.EP_appln_id ,
 'Equiv_EP_WO_EP' as 'Source'
 FROM [OECD_CIT_EP_Equiv] eq
 INNER JOIN [OECD_CIT_WO_Citations] c
 ON eq.Cited_Appln_id = c.Cited_appln_id
 INNER JOIN [OECD_CIT_EP_WO] e
 ON c.Citing_appln_id = e.WO_Appln_id
 INNER JOIN [OECD_CIT_EP_Cit_Counts cc
 ON cc.EP_Appln_id = e.EP_Appln_id
 WHERE c.Cited_App_auth = 'EP'
 AND DATEDIFF(MONTH, c.Citing_pub_date, eq.Eqv_Pub_date) <=60)) n
 GROUP BY Appln_id) t
 ON t.Appln_id = e.Appln_id 
In this doc, at p.7 I see other folder different from patstat.
https://www.wipo.int/edocs/mdocs/mdocs/ ... _13_19.pdf
Best regards
Ueliton Leonidio
Last edited by uleonidio on Wed Nov 24, 2021 4:17 pm, edited 1 time in total.


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

Re: Time window to control forward-citations

Post by EPO / PATSTAT Support » Tue Dec 07, 2021 4:44 pm

The query is based on data provided by OECD - which in its' turn is based on PATSTAT data. The EPO can not assist you with support on those tables. To create user defined tables (as is the case in this query) also requires you to have access to the data on a local DB platform - or at a minimum a sample of extracted PATSTAT data, such as which you can define and download via PATSTAT Online.
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


Post Reply