HowTo: Use of the Elements Reporting Database to understand ORCID adoption by your researchers

Symplectic have long been a supporter of ORCID and in 2015 we introduced a means for researchers to authorise/link their Elements user account with ORCID.

In 2017, we introduced ‘automatic claiming’, which allowed Elements to search for person identifiers (including ORCIDs) from amongst the bibliographic/publication data harvested from external data sources and to then present identifiers back to the researcher, so as to help reduce the ongoing effort associated with claiming publications in Elements.

However, if you have yet to roll out Elements to all your researchers and/or would like to be reassured that your researchers are in fact using their ORCID, then the following example queries might be useful to you.

This simple query returns a list containing each ORCID that is registered for auto-claiming in Elements and the user it is registered to.

-- get a list of Elements users and their ORCIDs registered for auto-claiming
SELECT  a.[Identifier Value]  "ORCID"
        ,u.[Proprietary ID]  "User Proprietary ID"
        ,u.[Computed Name Alphabetical]  "Full Name"
FROM    [User Identifier Association] a  -- the table of all person identifiers registered by users for auto-claiming
        JOIN [Identifier Scheme] s       -- the table of all identifiers schemes recognised by Elements
            ON s.ID = a.[Identifier Scheme ID]
        JOIN [User] u
            ON u.ID = a.[User ID]
WHERE   s.[Name] = 'orcid'               -- we are only interested in identifiers belonging to the ORCID scheme
;

This example returns a list of users who do NOT have an ORCID registered for auto-claiming.

-- get a list of users with no ORCID registered for auto-claiming
WITH RegisteredOrcid AS (
    SELECT  a.[User ID]
            ,a.[Identifier Value] "ORCID"
    FROM    [User Identifier Association] a
            JOIN [Identifier Scheme] s
                ON s.ID = a.[Identifier Scheme ID]
    WHERE   s.[Name] = 'orcid'
)
SELECT  u.[Proprietary ID]              "User Proprietary ID"
        ,u.[Computed Name Alphabetical] "Full Name"
        ,u.[Email]
FROM    [User] u
        LEFT JOIN RegisteredOrcid r
            ON r.[User ID] = u.ID
WHERE   r.[ORCID] IS NULL -- return only users who do not have a registered ORCID
        AND u.ID > 1      -- we are not interested in the system user
;

This builds on example 2 to look for ORCIDs in publication metadata that are not already registered for auto-claiming.

-- find ORCIDs in publication metadata that are not yet registered for auto-claiming
WITH RegisteredOrcid AS (
    SELECT  a.[User ID]
            ,a.[Identifier Value] "ORCID"
    FROM    [User Identifier Association] a
            JOIN [Identifier Scheme] s
                ON s.ID = a.[Identifier Scheme ID]
    WHERE   s.[Name] = 'orcid'
)
SELECT	DISTINCT
		u.[Proprietary ID]  "User Proprietary ID"
        ,u.[Computed Name Alphabetical]  "Full Name"
		,u.[Email]  "Email"
		,i.[Value]  "Unregistered ORCID in publication metadata"
		,r.[ORCID]  "Registered ORCID"
FROM	[Publication Record Person Identifier] i  -- the table of all person identifiers found in publication metadata
		JOIN [Publication Record Person] p        -- the table of all persons found in publication metadata
			ON p.ID = i.[Publication Record Person ID]
		JOIN [User] u
			ON u.ID = p.[Resolved User ID]
		LEFT JOIN RegisteredOrcid r
			ON r.[User ID] = p.[Resolved User ID]
WHERE	i.[Scheme] = 'orcid'
		AND (
			r.[User ID] IS NULL       -- case 1: the user has no registered ORCID
			OR r.[ORCID] <> i.[Value] -- case 2: the registered ORCID does not match the ORCID found in metadata
		)
;

In this example, we allow users of the reporting database to produce a list of Elements user accounts that have an ORCID associated with their last name and first names within the author strings harvested from external data sources.

Please note: we are NOT attempting to replicate the algorithm used by Elements as part of our automatic claiming feature, but instead, attempting to identify which of your researchers may have started to use an ORCID (i.e. as part of their publication workflows), but have yet to claim their ORCID either via the automatic claiming feature or via authenticating with ORCID not yet failed to have authenticated with ORCID.

Below is a high-level breakdown of the SQL query.

1. Get ORCIDs parsed from publication data, from the [Publication Record Person Identifier] table.

2. Join those identifiers with corresponding authors in the [Publication Record Person] table.

3. Join the authors with their publication records in (you guessed it) [Publication Record] table.

4. Join publication records with publications in the [Publication] table, de-duplicating any multiple records per publication.

5. Do a very naive join of publication authors against system users, based on last name and first name. NB: this is where Elements automatic claiming algorithm get very clever, as it will look at not only first name, but initials, and some other attributes before making identifier suggestions to a user.This will give us a list of publications and suspected users, and their ORCIDs.

6. Join the above list with pending links from the [Pending Publication] table.

7. Join the above list with claimed links from the [Publication User Relationship] table.

8. For each user/ORCID combination, count the number of pending and claimed links.

-----------------------------------------------------------------------------------------
-- This example shows an extremely simplified "auto-claiming" report for all users 
-- with very rough estimates of the number of unclaimed publications per Orcid.
-----------------------------------------------------------------------------------------
;WITH PublicationAuthors AS (
SELECT
  p.ID [Publication ID], u.ID [User ID], prpi.Value [Orcid]
-- Get publication records with an Orcid.
FROM  [Publication Record Person Identifier] prpi
JOIN  [Publication Record Person] prp ON prp.ID = prpi.[Publication Record Person ID]
JOIN  [Publication Record] pr ON pr.ID = prp.[Publication Record ID]
-- Fetch publications with records containing Orcids.
JOIN  [Publication] p ON p.ID = pr.[Publication ID]
-- Do a naive name-based user resolution based on author's last and first names.
-- *This* is where Elements auto-claiming algorithm gets clever, trying to
-- figure out if a publication author is a user in the system.
JOIN  [User] u ON u.[Last Name] = prp.[Last Name] AND u.[First Name] = prp.[First Name]
WHERE  prpi.Scheme = 'orcid'
-- Fetch distinct publications (i.e. a publication has both Scopus and WoS records with Orcids), users and associated Orcids
GROUP BY p.ID, u.ID, prpi.Value
),
Links AS (
-- For each publication found in the previous step, see if it has a pending or a claimed link.
SELECT
  pa.[Publication ID], pa.[User ID], pa.Orcid, CASE WHEN pl.ID IS NULL THEN 0 ELSE 1 END [Has Pending Link], CASE WHEN cl.ID IS NULL THEN 0 ELSE 1 END [Has Claimed Link]
FROM  PublicationAuthors pa
LEFT JOIN [Publication User Relationship] cl ON cl.[Publication ID] = pa.[Publication ID] AND cl.[User ID] = pa.[User ID]
LEFT JOIN [Pending Publication] pl ON pl.[Publication ID] = pa.[Publication ID] AND pl.[User ID] = pa.[User ID]
),
LinkCounts AS (
-- For each user ID - Orcid combination, count the number of pending and claimed links.
SELECT
  l.Orcid, l.[User ID], SUM(l.[Has Pending Link]) [Pending Links], SUM(l.[Has Claimed Link]) [Claimed Links]
FROM  Links l
GROUP BY l.Orcid, l.[User ID]
)
-- Finally, show the results with some extra user information.
SELECT
  lc.Orcid, lc.[User ID], u.[Last Name], u.[First Name], lc.[Pending Links], lc.[Claimed Links]
FROM  LinkCounts lc
JOIN  [User] u ON u.ID = lc.[User ID]
ORDER BY lc.[Pending Links] DESC

The following table is an excerpt taken from the results of the above script when run against Symplectic’s demonstration Elements Reporting Database (i.e. demonstration data).

It returns separate rows for each unique ORCID found amongst the bibliographic/publication data harvested by our  demonstration Elements system.

Reading from left to right, the ORCID ’0000-0001-6305-2201 appears 139 times against the pending publications of Peter Sly. The same ORCID appears in 161  of Peter’s Claimed publications.

Using this information, it would seem sensible to ask Peter (or his delegate) to add his ORCID to his Elements profile so that all future publications that contain his ORCID are automatically claimed on his behalf in Elements.

In the real world, Peter has 774 works in his ORCID record, so were he to authenticate his ORCID with Elements then his claimed publications would go up considerably … conveniently demonstrating the value of using ORCIDs.