Academic Journal Cost-Per-Use SQL Metric Specification

Role: Academic Library Systems Analyst specializing in scholarly communications, collection assessment, and bibliometrics.

Context

• Academic library system: {{university_library_system}}
• Subscription analysis window: {{subscription_year}}
• Usage data metric format: {{counter_standard_version}}
• Target relational database: {{target_rdbms}}
• Cost distribution strategy: {{cost_allocation_method}}
• Consortium contract classification: {{journal_consortium_tier}}

Task

Develop an end-to-end SQL transformation specification to ingest COUNTER harvest logs, merge institutional subscription invoice data, and generate departmental cost-per-use intelligence queries for {{university_library_system}}.

Method

1. Define raw staging schemas to ingest {{counter_standard_version}} monthly usage reports.
2. Standardize ISSN, eISSN, and DOI matching keys to resolve multi-publisher title discrepancies.
3. Ingest institutional procurement contract records filtered for {{subscription_year}} and {{journal_consortium_tier}}.
4. Implement {{cost_allocation_method}} logic to distribute bundled package costs across academic departments.
5. Aggregate total unique item investigations and requests per publication title by fiscal quarter.
6. Calculate cost-per-use ratios using safe division logic to prevent runtime division-by-zero errors.
7. Formulate SQL analytics views in {{target_rdbms}} that flag subscriptions exceeding cost-efficiency thresholds.
8. Author validation queries comparing year-over-year expenditure efficiency across faculties.

Constraints

• MUST implement NULLIF and CASE constructs to guard against division-by-zero during metric calculations.
• MUST utilize dialect-specific optimizations native to {{target_rdbms}} for large usage log scans.
• MUST NOT drop unmapped journal records without routing them to an unresolved staging quarantine table.
• Aggregations MUST retain departmental and consortium split granularity as specified.

Output format

1. Metric Specification & Data Pipeline Overview (Summary text and input/output inventory)
2. Source Ingestion & Entity Resolution Schema (DDL with constraint keys)
3. Cost-Per-Use Calculation SQL Query Views (Documented SQL code blocks with inline logic notes)
4. Data Integrity & Verification Query Suite (3-4 diagnostic SQL validation scripts)

Self-review

• Confirm presence and proper formatting of all 6 context variables.
• Verify zero-division safety handling in all cost-per-use ratio expressions.
• Check that {{counter_standard_version}} parsing nuances are handled accurately in the staging DDL.