Metrics – Process

1. Introduction

The dissemination of print and digital books to an assortment of platforms generates a large amount of usage data. These data are collated into a single SQL database so they can be visualized using our data visualization software. These data can subsequently also be exported for Library Metrics Reports and the Activity and Financial Report.

2. Goal & Objectives

The primary goal of this process is to collate and store monthly usage metrics.

The process covers the following objectives:

• The gathering of monthly usage metadata from all platforms we disseminate our books to, both print and digital
• The cleaning of these data
• The importing of these data into a central database

3. Role Model

The following roles are relevant in the context of this process and need to be assigned to persons or teams / groups involved in the process.

Role	Task	Assigned staff
Process Manager (PM)	To maintain the process definition / description (this document) To perform process reviews as a basis for the continual improvement of the effectiveness and efficiency of the process To ensure adequate communication to increase the awareness for the process To organize trainings on the process for people involved in the process	Vincent
Metrics Officer	To collect all the monthly metrics data To clean these data To input these metrics data into a centralized database so they can be displayed	Vincent

4. Relevant Business Objects

Business objects are “things” that are required, produced, or processed by the process or one of its activities. The following objects are relevant:

Business Object	Description
Usage metrics report	Report provided by a platform containing usage/sales/distribution number for a specific timeframe
Database	SQL Database
Visualization	Data Visualization

5. Activities

Date5.1 Table

Updating

Thethe Date Table in phpMyAdmin needs to be updated every month with the last month.

Go to DateTable SQL Tab (Monthly)

ToThis add:

activity

INSERT INTO `DateTable`(`Date`) VALUES ("2021-01-01")

To delete:

DELETE FROM `DateTable` WHERE `Date` = "2021-01-01"

KDP

Each month, download the sales data from KDP through its backend. After 90 days, data are no longer downloadable and data entry should be done manually, which iscreates a pain.

new

Click "Reports"entry in the main menu on top Select "Orders" from the leftside menu Select the appropriate month Click "Download Report"

KDP delivers XLSX files.

Open in LibreOffice Tab "Combined Sales" Delete table header Save as text CSV, comma-delimited Upload into phpMyAdmin table "KDP"

JSTOR

JSTOR sends out a monthly email reminders to vincent@punctumbooks.com with a link to the download report, but the links in it are currently broken. So Log In in to JSTOR, and selected "Publisher Reports" from the upper right admin drop down menu.

You need both the Book Usage by Country and by Institution reports.

JSTOR delivers XLSX files.

Country

Open in LibreOffice
Change Reporting Period Total into YYYY-MM-01 format
Delete table header rows. Save as text CSV, comma-delimited Upload into phpMyAdmin table "JSTOR_Country"

Institution

Open in LibreOffice
Change Reporting Period Total into YYYY-MM-01 format
Delete table header rows. Save as text CSV, comma-delimited Upload into phpMyAdmin table "JSTOR_Institution"

Project Muse

Somewhere halfway each month, Muse generates the usage datadatabase for the previousnew month. They can be downloaded in the backend.

Input Month over which usage metrics are gathered Roles Involved

Metrics Officer

Outputs

Updated usage metrics database

Actions

1. ClickDate "ReportsTable anUpdate

Statistics – Usage Statistics"inAlternative theActions, topExceptions, menu.Additional Information

5.2 Importing Print Publication Data (Monthly)

Input Print publication metrics data Roles Involved

Metrics Officer

Outputs

Updated usage metrics database

Actions

KDP Update ClickInvoiceNinja "Run Statistics," then "Data Export."Update EnterAsterism theBooks pertinent year, month, select "country" "institution," and "CSV," then click "Submit."Update Lightning Source Update (defunct) Alternative Actions, Exceptions, Additional Information

5.3 Importing Digital Publication Data (Monthly)

Input Digital publication metrics data Roles Involved

Metrics Officer

Outputs

Updated usage metrics database

Actions

JSTOR Update Project MUSE delivers CSV files, but they don't contain proper date column. Also, commas inside editor columns create errors in displaying the CSV.

Open in LibreOffice (make sure to set encoding to UTF-8)
Update AppendOAPEN 1 column to the left, add date in YYYY-MM-01 format
Update Delete column P "punctum books" Delete table header Check all rows have proper column count (because of comma issue) Upload into phpMyAdmin table "PROJECT_MUSE2"

InvoiceNinja

Sales data per product can be conveniently generated under "Reports," organized by Invoice Item. Product numbers in InvoiceNinja correspond to PBNs.

InvoiceNinja delivers CSV files. They suck.

Open in LibreOffice Sort by invoice number
delete rows with non-book data convert Product column to text, append 0 to PBNs fix InvoiceDate to 20XX-XX-01 fix price by removing $ sign delete table header save as text CSV, comma-delimited Upload into phpMyAdmin table "InvoiceNinja"

Lightning Source

The books from the Uitgeverij imprint are hosted through Lightning Source. Reports can be downloaded from the Reports>Print Sales menu item.

Lightning Source delivers XLS files.

Open in LibreOffice, switch off "space" as a possible delimiter Append 1 column to the left, add date in YYYY-MM-01 format Delete table header Upload into phpMyAdmin table "Lightning_source"

Lightning Source reports have bee discontinued. Their new reports can no longer be exported.

OAPEN

OAPEN-Dashboard provides a download for all countries in CSV format.

When importing switch off space/semicolon as delimiter Delete all columns from H up to present month Delete the Funders column Reformat column G to YYYY-MM-01 format
Delete header Upload into phpMyAdmin table "OAPEN2"

Google Books

Google Books usage data are available via the Google Play Books Partner CenterUpdate.

Go to menu item Reports > Custom Reports. Select Report Type "Google Books Traffic Report," Organize by "Book," and select date range.

Delete header
Append 1 column to the left, add date in YYYY-MM-01 format
save as XLS re-export as CSV Upload into phpMyAdmin table "GoogleBooks"

Internet Archive

Usage data are directly extracted using the Internet Archive usageUpdate dataCrossRef API.Event WeUpdate have written

Alternative Actions, Exceptions, Additional Information It is expected that over time all this work will taken over by the Thoth Metrics Dashboard. Until a full coverage of our sources has been established, we will continue to gather these data.

5.4 Importing Digital Publication Data (Quarterly)

Input Digital publication metrics data Roles Involved

Metrics Officer

Outputs

Updated usage metrics database

Actions

scriptThoth CDN Update Alternative Actions, Exceptions, Additional Information It is expected that extractsover time all this work will taken over by the Thoth Metrics Dashboard. Until a full coverage of our sources has been established, we will continue to gather these data.

5.5 Importing Digital Publication Data (Annually)

Input Digital publication metrics data automatically andgeneratesRoles Involved

Metrics Officer

Outputs

Updated usage metrics database

Actions

EBSCO Ebooks Update Alternative Actions, Exceptions, Additional Information It is expected that over time all this work will taken over by the Thoth Metrics Dashboard. Until a CSVfull file,coverage organizedof byour Thothsources workIdhas andbeen countryestablished, perwe timeframewill (usuallycontinue ato month).gather these data.

In order to generate the data, make sure all workIds of recently published books are added, and select the correct timeframe.

Import as encoded as UTF-8 Delete header Upload into phpMyAdmin table "InternetArchive"

Asterism

Asterism provides monthly order data via their backend as CSV.

In the left menu, go to Home Select the pertinent date range Click "Export Order Data"

Append 1 column to the left, add date in YYYY-MM-01 format Delete all rows up to the list of ISBNs Upload to phpMyAdmin table "Asterism"

Thoth CDN

The CDN operated by Thoth provides access to our PDF files (books.punctumbooks.com). The CDN also generates usage metrics. They are provided on a monthly basis by Javi from Thoth Open Metadata until an automated system is developed.

delete the column event_uri in work_uri, remove the DOI prefixes and .1.00 at the end such that only the PBN remains (make sure the format is set to "Text" to preserve the initial zero) double check that all PBNs are correct (with initial zero) delete the rows with the other publishers Upload to the phpMyAdmin table "Thoth_CDN"

CrossRef Event Data

CrossRef event data are plucked directly from their Query API. There are two DOI prefixes punctum uses, so we need records for both. Just fill in the correct start and end dates:

https://api.eventdata.crossref.org/v1/events?mailto=vincent@punctumbooks.com&obj-id.prefix=10.53288&from-occurred-date=2023-07-01&until-occurred-date=2023-07-31

https://api.eventdata.crossref.org/v1/events?mailto=vincent@punctumbooks.com&obj-id.prefix=10.21983&from-occurred-date=2023-07-01&until-occurred-date=2023-07-31

You can use Postman as a useful API GUI.

You get a JSON file that needs to be converted to CSV. Clean up the occurred_at column by replacing "T" and "Z" with a space, and set data format to timestamp. Make sure all columns line up, and all columns have at least one cell filled (even with a dummy)
Delete header row Upload into phpMyAdmin table "Crossref_Event"

EBSCO Ebooks

The data from EBSCO are prepared once a year and need to be requested by email. Be explicit you want them separated out per month. Email Amy Powers: apowers@EBSCO.COM.

The data comes in basically useless form and needs to be fully reformatted in order to be ingested into our MySQL database. See the example from the 2023–2024 data in the folder.

==

Data import happens through phpMyadmin. The tables are under "datadb."

In file to be imported:

• open file to be imported;
• make sure columns align properly;
• delete table headers;

In phpMyadmin:

• click on the pertinent table;
• select Import tab;
• select file
• click "Go."

Once all data are imported, in Metabase:

• Settings > Admin > Databases (in top menu) > punctum books data
• Click Sync database schema now
• Settings > Exit admin

Document Control

Document ID	SPM-MET-001
Document Owner	Vincent
Version	1.01
Last Date of Change	OctoberMarch 3,26, 2025
Next Review Due Date
Version & Change Tracking