Count Company's number of patent applications

[jkoester]

Dear all,

Since I'm currently writing my master thesis on post-merger innovation performance, I'm using patent counts as measure for the company's innovation performance after the merger/acquisition. Hence, I have an excel sheet with around 400 acquirers that have been active between 2004 and 2009.

What I'm asking you is, how to design a query that gives me the number of patent applications after the merger/acquisition for each individual company? How can I make sure that the the result is as accurate as possible (in other words: that the company I'm looking for is really been found in PATSTAT)?

It is probably a very "dumb" question, but I'm a little lost here and I'd really appreciate your support.

many thanks!!!
Julia

[nico.rasters]

Could you post one example?
You have Firm A acquiring Firm B in 2005, and then you want all patents of Firm A and B in 2005 and beyond?

This basically is a "Count all applications by Firm X" problem, except that in your case your Firm X is a more complex temporal structure with several entities involved that may or may not have kept their original names.

I'm a few PATSTAT versions behind, but in general there's tls206, doc_std, OECD HAN, and EEE-PPAT for finding company names. Maybe you should use all four, combined with a lot of manual checking. What's your deadline?

[Tim Grünebaum]

Hi all,

@ Nico: how exactly would we handle a "Count all applications by Firm X in Year Y" problem?
I did not find an explicit guidline of such an undertaking, neither in the forum nor in manuals. I was wondering if anyone has done this before and if there is such thing as an "easy way" other than write the query myself.
I an new to PATSTAT and SQL anyway :-/

However, manual checking afterwards will be inevitable I suppose.
A reliable data selection based on harmonized bariables such as OECD HAN or EEE-PPAT would be great!

Thanks and regards

Tim

[nico.rasters]

Sorry for the late reply. I was abroad.

To answer "Count all applications by Firm X in Year Y", we first need to define what is Year Y. Let's use the YEAR(`tls201_appln`.`appln_filing_date`) for this. Then we need to define "by Firm X". I'm assuming you're looking for the applicant instead of the inventor. In the October 2014 version of PATSTAT which I am using you can find applicants and inventors in table `tls206_person`. Table `tls207_pers_appln` has two variables which indicate whether the "person" (the name is misleading) is an applicant and/or inventor. These variables are `applt_seq_nr` and `invt_seq_nr`. If `applt_seq_nr`>0 then the "person" is an applicant; if `invt_seq_nr`>0 then the "person" is an inventor. So we need `applt_seq_nr`>0. Finally we are looking for "Firm X". That could be `tls206_person`.`person_name` if you like raw data, or `tls208_doc_std_nms`.`doc_std_name` if you have faith in the EPO's standardization methods. Alternatively you can use the EEE-PPAT or OECD HAN database.

For example, to retrieve data on patents from Nokia Corporation (doc_std_name_id=1):

SELECT YEAR(`tls201_appln`.`appln_filing_date`), COUNT(*)
FROM `tls201_appln`
INNER JOIN `tls207_pers_appln` ON `tls201_appln`.`appln_id` = `tls207_pers_appln`.`appln_id`
INNER JOIN `tls206_person` ON `tls207_pers_appln`.`person_id` = `tls206_person`.`person_id`
WHERE `doc_std_name_id`=1 AND `applt_seq_nr`>0
GROUP BY YEAR(`tls201_appln`.`appln_filing_date`);

But this is a very very basic example. Nowadays we count patents through families. Also, there are many more doc_std_names that match "Nokia". And perhaps you only want granted patents.

[Tim Grünebaum]

Thank you very much, Nico!

That was quite helpful and I also tried to build up my own code in combination with your suggestions.

Now I am using this one:
SELECT
COUNT(*),
TLS206_PERSON.hrm_l2,
TLS201_APPLN.APPLN_FILING_YEAR,
TLS206_PERSON.SECTOR
FROM TLS201_APPLN
LEFT JOIN TLS207_PERS_APPLN ON TLS201_APPLN.APPLN_ID = TLS207_PERS_APPLN.APPLN_ID
LEFT JOIN TLS206_PERSON ON TLS207_PERS_APPLN.PERSON_ID = TLS206_PERSON.PERSON_ID
LEFT JOIN TLS202_APPLN_TITLE ON TLS201_APPLN.APPLN_ID = TLS202_APPLN_TITLE.APPLN_ID
WHERETLS201_APPLN.APPLN_AUTH LIKE 'DE'
AND TLS201_APPLN.APPLN_KIND LIKE 'A'
AND TLS206_PERSON.SECTOR NOT LIKE 'INDIVIDUAL'
AND APPLT_SEQ_NR>0
GROUP BY TLS206_PERSON.hrm_l2, TLS201_APPLN.APPLN_FILING_YEAR, TLS206_PERSON.SECTOR
ORDER BY TLS206_PERSON.hrm_l2, TLS201_APPLN.APPLN_FILING_YEAR, TLS206_PERSON.SECTOR;

It works quite well but I am not sure if this is really the end of the journey.
I compared data for the 20 most innovative (in terms of applications) firms in Germany from PATSTAT with official application numbers from the German Patent Office for the period 2000 to 2010 and they differed quite substantially (some were higher some were lower). You see I use e very harmonized company name (hrm_l2) to simplify merging with other company data.

Do you have any idea if there are still duplicates or missings in the data I dig out with the code above? This is pretty hard to find out...

Thanks again!

Tim

[nico.rasters]

Hi Tim,

When using harmonized names it is possible that you'll get a double count because a patent may have multiple assignees that when harmonized become the same entity.
For example when Abbott Laboratories and Abbott Laboratories Chile are the assignees and the hrm_l2 says that both are "Abbott Labs".

But this does not explain the differences with the statistics of the German Patent Office.
On http://presse.dpma.de/presseservice/eng ... index.html it is stated that in 2008 there were 62330 patent applications at the DPMA. When you query PATSTAT for "German patent applications" in 2008 you'll find:

appln_filing_year appln_kind COUNT(*)
2008 A 53556
2008 C 1
2008 D 1
2008 F 142
2008 T 13172
2008 U 14782
2008 W 2090

The sum of which is 83744, but that's including utility patents. Without utilities it's 68962. Still quite a difference. The problem here is reproducibility. The DPMA does not give any details as to how they produced their statistics. Maybe an application "at the DPMA" is a special kind of application, maybe they only counted granted patents. Based on http://presse.dpma.de/presseservice/eng ... index.html we know that they do count PCT patent applications after entering the national phase.
I recommend contacting the DPMA and asking what kind of query they used.
Same procedure when comparing data with Eurostat, etc. Though you'll be hard pressed to receive a useful answer... I never managed anyway. This is partially because they just reuse data, so the trick is to find the primary data provider.

For more recent years PATSTAT simply is not complete.

Btw, if you leave out the ORDER BY statement that should (in theory) speed things up a bit.

I don't have access to PATSTAT Online so I can't verify your query.

[Tim Grünebaum]

Hello Nico,

I have been thinking about your suggestions and the topic as a whole.
However, the problem is not solved entirely.

The DPMA said they are actually using DOCDB as their primary data source. This is interesting because there should not be any substantial differences between their data and PATSTAT. Maybe I made a mistake by counting patents, perhaps double counted things like artificial applications? But I probably ruled artificial duplicates out by only counting " AND TLS201_APPLN.APPLN_KIND LIKE 'A' ". I haven't understood this topic completely.
I am using PATSTAT2015b and the code in my previous reply works in MSSQL and MySQL. So maybe you (and/or other users) can check my code. You can also axchange "hrm_l2" by "doc_std_name". But doing so yields even more confusing results The same is true if we only go for "doc_std_name_id" which should not yield any duplicates, right?

The goal is to count all patents applied by companies at the german patent office between 2000 and 2010.

You can easily chek it for specific companies like Siemens or Volkswagen (which is faster than doing it for all companies):

Code: Select all

SELECT
COUNT(DISTINCT TLS207_PERS_APPLN.APPLN_ID),
TLS206_PERSON.hrm_l2,
TLS201_APPLN.APPLN_FILING_YEAR
FROM TLS201_APPLN
LEFT JOIN TLS207_PERS_APPLN ON TLS201_APPLN.APPLN_ID = TLS207_PERS_APPLN.APPLN_ID
LEFT JOIN TLS206_PERSON ON TLS207_PERS_APPLN.PERSON_ID = TLS206_PERSON.PERSON_ID
LEFT JOIN TLS202_APPLN_TITLE ON TLS201_APPLN.APPLN_ID = TLS202_APPLN_TITLE.APPLN_ID
WHERE
UPPER(TLS206_PERSON.hrm_l2) IN('SIEMENS','VOLKSWAGEN')
AND TLS201_APPLN.APPLN_AUTH LIKE 'DE'
AND TLS201_APPLN.APPLN_KIND LIKE 'A'
AND TLS206_PERSON.SECTOR NOT LIKE 'INDIVIDUAL'
AND TLS201_APPLN.APPLN_FILING_YEAR >= 2000
AND TLS201_APPLN.APPLN_FILING_YEAR <= 2010
AND TLS207_PERS_APPLN.APPLT_SEQ_NR>0
GROUP BY TLS206_PERSON.hrm_l2, TLS201_APPLN.APPLN_FILING_YEAR
ORDER BY TLS206_PERSON.hrm_l2, TLS201_APPLN.APPLN_FILING_YEAR;

If we can solve this problem we end up with a workable code for counting applications by company (and year) which I think would be a good thing also for other users

Regards

Tim

[nico.rasters]

Hi Tim,

You mentioned you had (DPMA) data for the twenty most innovative firms? Some numbers were higher, others were lower. Can you post one example of each? Preferably with a year range starting in 2005 or so. Earlier is fine too.

The fact that the DPMA uses DOCDB as well does not guarantee that results will be the same. Same source yes, but same query/procedure? Probably not. For example, you are using hrm_l2 (which I think comes from the EEE-PPAT database). If they use person_name or even hrm_l1 then the results will already differ.

So let's take two firms and we can try all possible query variants. I think it will be a good exercise.

[Tim Grünebaum]

Hi Nico,

wow, what a quick response

Here we go:

Code: Select all

PATSTAT DPMA hrm_l2   APPL_FILING_YEAR
2612	2202	ROBERT BOSCH	2006
2827	2509	ROBERT BOSCH	2007
3282	2645	ROBERT BOSCH	2008
3438	3213	ROBERT BOSCH	2009
3444	3477	ROBERT BOSCH	2010
3474	3602	ROBERT BOSCH	2011
4048	3972	ROBERT BOSCH	2012
4051	4144	ROBERT BOSCH	2013
521	1626	DAIMLER	2006
1107	1216	DAIMLER	2007
1659	1279	DAIMLER	2008
1869	1756	DAIMLER	2009
1902	1917	DAIMLER	2010
1997	2014	DAIMLER	2011
1992	1991	DAIMLER	2012
1843	1854	DAIMLER	2013
2390	2501	SIEMENS	2006
1802	2474	SIEMENS	2007
1647	1741	SIEMENS	2008
1471	1750	SIEMENS	2009
1541	1654	SIEMENS	2010
1744	1910	SIEMENS	2011
1659	1921	SIEMENS	2012
1567	1784	SIEMENS	2013
565	201	GM GLOBAL TECHNOLOGY OPERATIONS	2006
853	682	GM GLOBAL TECHNOLOGY OPERATIONS	2007
1361	994	GM GLOBAL TECHNOLOGY OPERATIONS	2008
1255	1347	GM GLOBAL TECHNOLOGY OPERATIONS	2009
1585	1540	GM GLOBAL TECHNOLOGY OPERATIONS	2010
1832	1566	GM GLOBAL TECHNOLOGY OPERATIONS	2011
1578	1565	GM GLOBAL TECHNOLOGY OPERATIONS	2012
1292	1289	GM GLOBAL TECHNOLOGY OPERATIONS	2013
511	371	SCHAEFFLER TECHNOLOGIES & COMPANY	2006
653	477	SCHAEFFLER TECHNOLOGIES & COMPANY	2007
807	605	SCHAEFFLER TECHNOLOGIES & COMPANY	2008
638	747	SCHAEFFLER TECHNOLOGIES & COMPANY	2009
1059	1249	SCHAEFFLER TECHNOLOGIES & COMPANY	2010
1340	1832	SCHAEFFLER TECHNOLOGIES & COMPANY	2011
1357	1854	SCHAEFFLER TECHNOLOGIES & COMPANY	2012
1443	2100	SCHAEFFLER TECHNOLOGIES & COMPANY	2013
488	382	BSH (BOSCH UND SIEMENS HAUSGERAETE)	2006
589	496	BSH (BOSCH UND SIEMENS HAUSGERAETE)	2007
724	468	BSH (BOSCH UND SIEMENS HAUSGERAETE)	2008
702	701	BSH (BOSCH UND SIEMENS HAUSGERAETE)	2009
918	931	BSH (BOSCH UND SIEMENS HAUSGERAETE)	2010
844	884	BSH (BOSCH UND SIEMENS HAUSGERAETE)	2011
699	719	BSH (BOSCH UND SIEMENS HAUSGERAETE)	2012
382	509	BSH (BOSCH UND SIEMENS HAUSGERAETE)	2013
584	731	VOLKSWAGEN	2006
695	567	VOLKSWAGEN	2007
729	592	VOLKSWAGEN	2008
656	891	VOLKSWAGEN	2009
642	664	VOLKSWAGEN	2010
682	730	VOLKSWAGEN	2011
787	805	VOLKSWAGEN	2012
820	836	VOLKSWAGEN	2013
626	545	ZAHNRADFABRIK FRIEDRICHSHAFEN	2006
623	551	ZAHNRADFABRIK FRIEDRICHSHAFEN	2007
693	594	ZAHNRADFABRIK FRIEDRICHSHAFEN	2008
608	689	ZAHNRADFABRIK FRIEDRICHSHAFEN	2009
564	629	ZAHNRADFABRIK FRIEDRICHSHAFEN	2010
656	669	ZAHNRADFABRIK FRIEDRICHSHAFEN	2011
729	740	ZAHNRADFABRIK FRIEDRICHSHAFEN	2012
700	708	ZAHNRADFABRIK FRIEDRICHSHAFEN	2013
621	621	BMW (BAYERISCHE MOTOREN WERKE)	2006
656	605	BMW (BAYERISCHE MOTOREN WERKE)	2007
632	632	BMW (BAYERISCHE MOTOREN WERKE)	2008
662	650	BMW (BAYERISCHE MOTOREN WERKE)	2009
601	602	BMW (BAYERISCHE MOTOREN WERKE)	2010
658	658	BMW (BAYERISCHE MOTOREN WERKE)	2011
823	829	BMW (BAYERISCHE MOTOREN WERKE)	2012
1162	1182	BMW (BAYERISCHE MOTOREN WERKE)	2013
470	411	AUDI	2006
454	419	AUDI	2007
349	488	AUDI	2008
521	365	AUDI	2009
549	597	AUDI	2010
660	661	AUDI	2011
754	787	AUDI	2012
994	1027	AUDI	2013
719	708	DENSO CORPORATION	2006
771	757	DENSO CORPORATION	2007
651	716	DENSO CORPORATION	2008
453	636	DENSO CORPORATION	2009
486	489	DENSO CORPORATION	2010
567	512	DENSO CORPORATION	2011
436	428	DENSO CORPORATION	2012
439	423	DENSO CORPORATION	2013
248	167	PORSCHE	2006
261	163	PORSCHE	2007
443	244	PORSCHE	2008
344	394	PORSCHE	2009
447	443	PORSCHE	2010
399	405	PORSCHE	2011
397	413	PORSCHE	2012
413	431	PORSCHE	2013
160	180	LUK LAMELLEN UND KUPPLUNGSBAU BETEILIGUNGS	2006
176	190	LUK LAMELLEN UND KUPPLUNGSBAU BETEILIGUNGS	2007
346	221	LUK LAMELLEN UND KUPPLUNGSBAU BETEILIGUNGS	2008
416	399	LUK LAMELLEN UND KUPPLUNGSBAU BETEILIGUNGS	2009
137	392	LUK LAMELLEN UND KUPPLUNGSBAU BETEILIGUNGS	2010
		LUK LAMELLEN UND KUPPLUNGSBAU BETEILIGUNGS	2011
		LUK LAMELLEN UND KUPPLUNGSBAU BETEILIGUNGS	2012
		LUK LAMELLEN UND KUPPLUNGSBAU BETEILIGUNGS	2013
125	113	CONTINENTAL AUTOMOTIVE	2006
603	135	CONTINENTAL AUTOMOTIVE	2007
511	632	CONTINENTAL AUTOMOTIVE	2008
326	671	CONTINENTAL AUTOMOTIVE	2009
341	375	CONTINENTAL AUTOMOTIVE	2010
396	424	CONTINENTAL AUTOMOTIVE	2011
401	435	CONTINENTAL AUTOMOTIVE	2012
429	465	CONTINENTAL AUTOMOTIVE	2013
356	396	FRAUNHOFER	2006
386	359	FRAUNHOFER	2007
392	384	FRAUNHOFER	2008
398	403	FRAUNHOFER	2009
336	368	FRAUNHOFER	2010
344	364	FRAUNHOFER	2011
402	424	FRAUNHOFER	2012
430	459	FRAUNHOFER	2013
183	123	FORD GLOBAL TECHNOLOGIES	2006
209	153	FORD GLOBAL TECHNOLOGIES	2007
258	174	FORD GLOBAL TECHNOLOGIES	2008
266	217	FORD GLOBAL TECHNOLOGIES	2009
355	359	FORD GLOBAL TECHNOLOGIES	2010
384	394	FORD GLOBAL TECHNOLOGIES	2011
413	504	FORD GLOBAL TECHNOLOGIES	2012
925	1060	FORD GLOBAL TECHNOLOGIES	2013
139	138	GE (GENERAL ELECTRIC COMPANY)	2006
252	181	GE (GENERAL ELECTRIC COMPANY)	2007
330	298	GE (GENERAL ELECTRIC COMPANY)	2008
355	387	GE (GENERAL ELECTRIC COMPANY)	2009
326	321	GE (GENERAL ELECTRIC COMPANY)	2010
451	418	GE (GENERAL ELECTRIC COMPANY)	2011
302	304	GE (GENERAL ELECTRIC COMPANY)	2012
197	196	GE (GENERAL ELECTRIC COMPANY)	2013
292	335	VOITH PATENT	2006
419	354	VOITH PATENT	2007
400	353	VOITH PATENT	2008
298	403	VOITH PATENT	2009
281	294	VOITH PATENT	2010
311	331	VOITH PATENT	2011
183	230	VOITH PATENT	2012
157	251	VOITH PATENT	2013
567	1236	INFINEON TECHNOLOGIES	2006
233	702	INFINEON TECHNOLOGIES	2007
425	472	INFINEON TECHNOLOGIES	2008
400	480	INFINEON TECHNOLOGIES	2009
247	266	INFINEON TECHNOLOGIES	2010
234	256	INFINEON TECHNOLOGIES	2011
293	311	INFINEON TECHNOLOGIES	2012
427	439	INFINEON TECHNOLOGIES	2013
210	187	CONTINENTAL TEVES	2006
196	212	CONTINENTAL TEVES	2007
216	197	CONTINENTAL TEVES	2008
150	215	CONTINENTAL TEVES	2009
188	264	CONTINENTAL TEVES	2010
203	327	CONTINENTAL TEVES	2011
250	306	CONTINENTAL TEVES	2012
257	313	CONTINENTAL TEVES	2013
191	160	DEUTSCHES ZENTRUM FUER LUFT- UND RAUMFAHRT	2006
162	179	DEUTSCHES ZENTRUM FUER LUFT- UND RAUMFAHRT	2007
137	176	DEUTSCHES ZENTRUM FUER LUFT- UND RAUMFAHRT	2008
127	172	DEUTSCHES ZENTRUM FUER LUFT- UND RAUMFAHRT	2009
227	241	DEUTSCHES ZENTRUM FUER LUFT- UND RAUMFAHRT	2010
207	233	DEUTSCHES ZENTRUM FUER LUFT- UND RAUMFAHRT	2011
207	226	DEUTSCHES ZENTRUM FUER LUFT- UND RAUMFAHRT	2012
229	253	DEUTSCHES ZENTRUM FUER LUFT- UND RAUMFAHRT	2013
346	265	HENKEL	2006
335	328	HENKEL	2007
293	325	HENKEL	2008
347	306	HENKEL	2009
233	239	HENKEL	2010
300	303	HENKEL	2011
263	276	HENKEL	2012
281	287	HENKEL	2013

You should be able to copy and paste these results in excel and sort them, plot the differences etc.
Those differences are pretty "white noise".
We can also just pick two firms for simplicity.

I got the DPMA counts from here:
http://www.dpma.de/english/service/publ ... index.html
E.g. for the 2013 annual report the patents are on page 8.

I will try to ask the DPMA for a code but I don't think they will hand it out.

[nico.rasters]

Every company search should start outside PATSTAT.

Robert Bosch

Let's start with "Robert Bosch". First question: Which "Robert Bosch" are we looking at here? The most obvious one, e.g. "Robert Bosch GmbH"? In that case, we can find more information on https://en.wikipedia.org/wiki/Robert_Bosch_GmbH and http://www.bloomberg.com/research/stock ... pId=630409.

The company was formerly known as Workshop for Precision Mechanics and Electrical Engineering and changed its name to Robert Bosch GmbH in 1937. Robert Bosch GmbH was founded in 1886 and is headquartered in Stuttgart, Germany. Robert Bosch GmbH operates as a subsidiary of Robert Bosch Stiftung Gmbh.

In the 2013 Annual Report http://www.bosch.com/content2/publicati ... t_2013.pdf we read that "The Bosch Group comprises Robert Bosch GmbH and its more than 360 subsidiaries and regional companies in some 50 countries. If its sales and service partners are included, then Bosch is represented in roughly 150 countries."

How to search for "Robert Bosch GmbH" in PATSTAT?

SELECT * FROM `tls206_person` WHERE `person_name` LIKE "Robert Bosch%";
SELECT * FROM `tls206_person` WHERE `person_name` REGEXP "Robert Bosch";
SELECT * FROM `tls206_person` WHERE `person_name` REGEXP "[[:<:]]Robert Bosch[[:>:]]";
SELECT * FROM `tls206_person` WHERE `person_name` = "Robert Bosch GmbH";
Maybe add the clause AND `person_ctry_code` IN ("","DE")
Limit to applicants only by joining with tls207_pers_appln.
Or we could search in tls208_doc_std_nms.

Btw, REGEXP does not work in PATSTAT Online.

If you add "GmbH" to your search then you will miss "Robert Bosch Gesellschaft mit beschränkter Haftung".
If you use tls208 you will get false positives such as "ROBERT BOSCH TOOL CORPORATION" (at least in PATSTAT October 2014 with doc_std_name_id 401). It will also give you Robert Bosch GmbH in India, Japan, Italy, etc. I don't know if these are local offices, subsidiaries or just mistakes made by the applicant. I do know that these countries do not have the legal business entity GmbH.

Now we may think that we can just search for Robert Bosch and that will allow us to capture the subsidiaries as well. But that's not really a good approach. So should we leave out the subsidiaries?

If you restrict your search to Robert Bosch GmbH only, keep in mind that there might be an "Intellectual Property Management" subsidiary that holds most of the patents. That and the fact that financial data is usually consolidated (i.e. subsidiaries are included), are good reasons for trying to find patents for subsidiaries as well. But as always it depends on your research question.

More later...