Skip to main content

Metrics – Process

1. Introduction

2. Goal & Objectives

The primary goal of this process is to 

The process covers the following objectives:

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

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
Print PDF

The final PDF generated by the Typesetting Software

5. Activities

Date Table

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

  • Go to DateTable
  • SQL Tab

To add:

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 is a pain.

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

Screen_Shot_2022-06-27_at_07.00.16.png
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 data for the previous month. They can be downloaded in the backend.

  • Click "Reports an Statistics – Usage Statistics" in the top menu.
  • Click "Run Statistics," then "Data Export."
  • Enter the pertinent year, month, select "country" "institution," and "CSV," then click "Submit."

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)
  • Append 1 column to the left, add date in YYYY-MM-01 format
  • 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.

Screenshot 2024-03-18 at 10.22.57.png

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 Center.

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

Screenshot 2023-04-17 at 12.36.27.png

  • 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 usage data API. We have written a script that extracts these data automatically and generates a CSV file, organized by Thoth workId and country per timeframe (usually a month).

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"

Screenshot 2024-07-29 at 11.58.03.png

  • 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.0
Last Date of Change October 3, 2025
Next Review Due Date
Version & Change Tracking
Back to top