Public Patent Data Now Available on Google BigQuery

At IFI CLAIMS, we are very excited about the release of the Google Patents Public Datasets on Google’s BigQuery.  We are proud to be the source of the core, public data table which provides global bibliographic data and US full text data free of charge.  It is our hope that researchers around the world will use this data table to make new patent related discoveries.  We are also announcing the availability of IFI patent data enrichments on BigQuery as a paid table.  
Modest processing fees from Google BigQuery will apply when querying the data, but the first 1TB of data processed per month is free.

The Free Public Patent Data Table

The Google Patents Public Data table on BigQuery is different from traditional patent search systems, including Google Patents.  The two main differences are:

• The ability to access the very large patent database using SQL commands instead of Boolean search.
• The ability to join to other databases – either public (free), paid or private.   

• Quarterly Updates
• Basic Bibliographic Data for Global Patents
• Full Text for US Patents and Applications
• No IFI Value Added Data
• No IFI full text translations

IFI CLAIMS^® Data Enrichments available as a Paid Table on Big Query

IFI CLAIMS is also announcing that it is offering IFI Data Enrichments as a paid table.  This table contains IFI's standardized assignee/applicant names, current assignee/applicant names and legal status information.  BigQuery users can link this data to other public and private datasets.  

SQL Query Examples on the Patents Public Data

To get started with Google BigQuery, follow these instructions: https://cloud.google.com/bigquery/quickstart-web-ui.  Note, the examples below are based on using Standard SQL. 

1.  Publication Numbers and Application Data

SELECT publication_number, application_number, country_code, kind_code, application_kind,
     application_number_formatted, publication_date, filing_date, priority_date
     FROM `patents-public-data.patents.publications` WHERE family_id = '46149598'

The result is:

2.  Dealing with Array Data - Inventor Example

SELECT publication_number, inventor_harmonized.name
     FROM `patents-public-data.patents.publications`
     WHERE publication_number IN
     ('US-8543680-B2',
     'US-8429119-B2') ORDER BY publication_number

You will receive the error: Cannot access field name on a value with type ARRAY<STRUCT<name STRING, country_code STRING>> at [1:48]

SELECT p.publication_number, i.name
     FROM `patents-public-data.patents.publications` AS p, UNNEST(inventor_harmonized) AS i
     WHERE publication_number IN
     ('US-8543680-B2',
     'US-8429119-B2') ORDER BY publication_number

3.  Dates and Counts

     SELECT FLOOR(patent.filing_date / 10000) AS year, COUNT(patent.publication_number) AS count
     FROM `patents-public-data.patents.publications` AS patent
     WHERE patent.country_code = "US" GROUP BY year ORDER BY year DESC;

4.  One Patent Per Family

    SELECT
    ARRAY_AGG((p.publication_number, p.filing_date) 
    ORDER BY CASE WHEN p.publication_date > 0 THEN p.filing_date ELSE 99999999 END ASC)[OFFSET(0)], 
    p.family_id
    FROM `patents-public-data.patents.publications` AS p
    WHERE (SELECT MAX(TRUE) FROM UNNEST(assignee_harmonized) AS a WHERE a.name = "CROSSBAR INC")
    GROUP BY p.family_id;

The result is 129 rows.  The first 10 rows are shown below.  A search for all "CROSSBAR INC." documents returns 362 documents.
 

5.  Citations

5.1  Simple Patent Citations

Result (first 10 rows out of 116)
 

5.2  Count of Patent Citations

Result (first 10 rows):

Result:

6.  JOIN patents with USPTO PTAB Data 

In the example below, we  JOIN the "patents.publication" patent data table and the "uspto_ptab.trails" table.  Patent number formats can be a challenge.  In the ptab.trials table, the "PatentNumber" field is in number format, for example "7499872".  This will not join to the "patents.publication" table where the publication_number field needs to include country and kind codes - for example "US-7499872-B1".  Each dataset has a "match" table to translate the number formats.  In the example below, the "ptab.match" table is used for the join.  

      SELECT patents.publication_number,
      patents.family_id AS Family,
      ptab.PatentOwnerName AS PatentOwner,
      ptab.PetitionerPartyName AS Petitioner,
      ptab.TrialNumber AS Trial,
      ptab.ProsecutionStatus AS Status,
      ptab_match.application_number
      FROM `patents-public-data.uspto_ptab.trials` AS ptab
      JOIN `patents-public-data.uspto_ptab.match` AS ptab_match
          ON ptab.ApplicationNumber = ptab_match.ApplicationNumber
      JOIN `patents-public-data.patents.publications` AS patents
          ON ptab_match.application_number = patents.application_number
      WHERE ptab.PetitionerPartyName = 'Mylan Pharmaceuticals Inc.'

The result shows patent information (patent number and family) and PTAB Trial and Case Status information for PTAB cases petitioned by Mylan Pharmaceuticals. Note that the "publication_number" field may be used as a key to link patent related databases. 

The result (first 10  records out of 366: