Page 1 of 1

Time window to control forward-citations

Posted: Fri Jul 10, 2020 9:26 am
by EduardoJJM
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

Re: Time window to control forward-citations

Posted: Mon Jul 13, 2020 3:21 pm
by EPO / PATSTAT Support
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
;

Re: Time window to control forward-citations

Posted: Tue Jul 14, 2020 12:35 pm
by EduardoJJM
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

Re: Time window to control forward-citations

Posted: Wed Jul 15, 2020 3:34 pm
by EPO / PATSTAT Support
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

Re: Time window to control forward-citations

Posted: Tue Jul 28, 2020 8:12 am
by EduardoJJM
Dear EPO,

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

Best regards,
Eduardo