Q1 2022 Reporting Needs

opensupplyhub / open-apparel-registry

An application for searching, matching, uploading factories.

MIT License

32 stars 13 forks source link

Q1 2022 Reporting Needs #1787

Closed hlennett closed 2 years ago

hlennett commented 2 years ago

A few reporting items we need to complete our Q1 2022 reporting:

Manual Pulls: Please pull the following data for Jan, Feb, and March 2022 (as discrete time periods):

Average Affiliations and Percent Facilities with Multiple Matches (reports that crash the site)
- Look up the SQL in the codebase and run it via psql on production
Platform % Uptime (just Jan and Feb)
- From panopta
'(# open confirm/rejects for month) / (total confirm/rejects for month)
- Added blocked tag because we need to clarity how to get this information retroactively.
'# of uploaded facilities w/errors
- Count of list items in an error state by month.
Average number of potential match addresses fed to a user during confirm/reject
- Per month count of the number of match rows for each list item when the match was not automatic.

For the new SQL statements, if they are fast enough, commit them as report SQL files.

vrwOAR commented 2 years ago

@hlennett does the Percent Facilities with Multiple Matches report also provide a listing of those facilities & the corresponding OAR IDs? If not, can that be added to the report to facilitate the moderation scripts?

hlennett commented 2 years ago

@vrwOAR The current report literally just gives a percentage of facilities with more than one contributor attached to it (a measure of quality of our facility profiles). This isn't the report about how many potential duplicates are fed to users during confirm/reject. That would be a new report to build (as well as adding the list of facilities that are attached to it), but please do add that request to the ticket, as it sounds like that's a report we'd like to have.

vrwOAR commented 2 years ago

Thanks for the clarification @hlennett. @mariel-oar can we add a report of the OAR IDs and facility names for the potential matches given to users during confirm/reject.

mariel-oar commented 2 years ago

@hlennett @vrwOAR, i'm going to leave off the report of facility IDs for potential matches in this ticket unless you have an immediate need for it (it sounds more like a nice to have above). Once we have the layer that we can use to write our own queries, then I think we can pull that ourselves.

jwalgran commented 2 years ago

@mariel-oar We added the blocked tag because we need additional detail on how to get this report:

'(# open confirm/rejects for month) / (total confirm/rejects for month

Specifically we need to discuss what "open" means when looking several months into the past.

mariel-oar commented 2 years ago

@jwalgran, maybe "pending" or "outstanding" would be better descriptors than "open"?

we are asking for the [# of pending confirm/rejects] / [# of total confirm/rejects pending and completed]

(we want to understand how many users actually go through confirm reject)

mariel-oar commented 2 years ago

Confirming that we want this number as a non-cumulative number, monthly basis, give us both numbers separately adn we will make %s.

also acknowleding that this number may change over time if pending confirm/rejects are addressed by users

jwalgran commented 2 years ago

To run the SQL reports, I ran psql

Set the separator to ,

\f ','

Set the output format to unaligned

\a

Set the output to a file

\o /tmp/average_affiliations_2022-05-19.csv

Run the content of average_affiliations.sql
Set the output to a file

\o /tmp/percent_facilities_with_multiple_matches_2022-05-19.csv

Run the content of percent_facilities_with_multiple_matche.sql
Reset the alignment and output

\a
\o

scp the files and remove the last line (row count) from each