Statistics - Articles Report Data Missing or Incorrect Data

Hello,
We are currently working in OJS 3.3.0.6

I do an export of the Articles Report from the Reports section in the dashboard to calculate various internal statistics for reporting, including the time from submission to first decision (either to decline the submission or to send it to review).

I have just noted that I have a chunk of around 25 published articles for which the export shows the first decision being “send to production” and that particular date. (they are not in a continuous time frame).

I also have a chunk of a little over 50 articles that have no data recorded for the first decision or after - no date or decision.

If I compare these submissions to the dashboard Activity Log, all of the correct stages and dates are recorded.

And, our IT expert has found that in the MySqul database, the data is recorded correctly. So there’s some glitch in the way the data is exporting through the dashboard tool.

I’m wondering if anyone else has noticed this? Has this been addressed somewhere? And how can we fix it so that we know our data export is reliable to work with?

Thanks!

Hi @hxo82,

Curious if you’ve upgraded recently? And, if so, from what version? There are some troubleshooting tips found here: https://docs.pkp.sfu.ca/admin-guide/en/statistics#frequently-encountered-statistics-problems - and a lot of them stem from upgrades from OJS 2 (not sure if that is your situation or not?), but this might be helpful to pass along to your administrator so we can rule out some things there.

-Roger
PKP Team

We upgraded about 2 years ago. The troubleshooting link seems to be for data with article views and counts, but I’m seeing issues with the tracked data related to the activity log - the data tracked in each submission’s log isn’t outputting accurately in the article report spreadsheet. I am missing logged dates for various decision steps. Is there anything specific to that? Thank you for your guidance. I’ll share this info with our admin!

Hi @hxo82,

Thanks for clarifying. I’m not sure about specific help for your scenario, but I’ll see if one of our team can weigh in.

-Roger
PKP Team

Thank you @rcgillis! I appreciate it!

Hi Everyone, I am the IT personal helping hxo82 with this, and would like to provide some insight towards what I think the issue may be.

Issue: Using the Articles export tool, data is incorrectly exported to CSV. Export module is: OJS Admin->Statistics->Reports->Articles Report

Evidence: The CSV file is supposed to show each article including columns corresponding to each of their accompanying editor decisions. These are displayed in a single row per articleID, with columns for each decision.

The decision columns are often populated with inaccurate data or with missing data. For instance:

  • In some cases, Decision 1 may be “Send To Production”, with no other decisions populated.
  • In some cases, the decisions are recorded, but in the wrong order, so the dates are not chronological and the decisions don’t make sense as they are in the wrong column.

Also, I have verified that the decisions are correct in the raw tables, and that this data issue only shows in the CSV export. I have verified this by directly querying the MySQL tables to determine if the decisions, dates, and order of the decision of record we have identified to have issues. The query I used to assess this was:

SELECT u.username, e.* FROM edit_decisions e left join users u on e.editor_id = u.user_id

This resulting in everything in rows, not of course not pivoted to columns. But, I can use thise query to confirm that the data in the tables is correct. So, again, leading me to believe there is some issue in the coding of this Export feature within the OJS platform.

Root Cause: Unsure. But, it seems like this export function may use something akin to a PIVOT function, rolling up all related decision rows into a single row across multiple columns for each article. If s, perhaps there is a problem in the PIVOT statement, causing data to be inaccurately displayed in the resulting CSV file.

Hi Roger,

Just curious if there was any insight form your team about our export issue?

Thanks!

Hi @guitman444,

I’ve asked our team to have a look at this again - hopefully you’ll hear back on it soon.

-Roger
PKP Team

Hi,

Upgraded to 3.3.0.15 a few weeks ago. Still seem to have same issue.

Curious if this is being looked at, or perhaps even addressed in 3.4?

Thank you

@bozana - would you have any insights on this?

-Roger

Hi all, yes, let me take a look into the plugin code first… will then come back…

Hi @guitman444,

Hmmm… I cannot see nothing wrong in the code… and it works well for me…

Can you test this SQL:

SELECT	edit_decision_id, editor_id, decision, date_decided, review_round_id, stage_id, round
FROM	edit_decisions
WHERE	submission_id = <your_submission_id>
ORDER BY date_decided ASC

using a submission ID for which the report does not work properly.
This SQL is used in the report and it returns everything correctly, and correctly ordered.

The further code, how we calculate max number of editors and decisions per editor is also correct… and thus how it is flatten for the CSV…

Hmmm…

Ah, you know what, it’s our team that misunderstood the export. We found what we were looking for, just not in a way we expected.

We did not previously understand that the columns are formatted first by Editor, then by Decision. So, we were just looking at Decision1, 2, 3, etc. We did not realize it was Editor 1’s decisions only. If we scroll over, we see Editor 2’s decision, then Editor 3’s decisions, and so on.

Now that we understand this, we will have to come up with a way to visualize and run calculations on these decisions.

Thank you for your time, turns out OJS was indeed working just fine…

@guitman444, great, glad to hear that… :slight_smile:
Thanks!