Ratings Queries
QUALITY CONTROL
As you go along, keep five sets of tallies:
- the number of historical ratings and endorsements (pre-2007) that weren't live but now are
- the number of current ratings and endorsements (2007-2008) that weren't live but now are
- the total number of current endorsements that are live (2007-2008)
- the number of current ratings and endorsements (2007-2008) in admin that can't be released because there is a problem with them. Note this next to the SIG in the excel, as well. Do not count in this ratings that simply haven't finished checks yet.
- the number of current ratings and endorsements (2007-2008) that can't be released because the SIG requested that they be taken down.
- Making sure all SIG's Release Statuses are live that should be:
Any non-blank fields under release_status need to be further investigated. Check your excels and look for notes in the SIG Description field to see if there is a reason we should not release that interest group. If you find info in the admin description, add it to your excel. If there is no reason that you can find as to why it shouldn't be on the live web, release it. Webcheck the issue organization to make sure it appears. Change the value in the query to reflect that SIG's current release status, if changes were made.
- Making sure all Ratings' Release Statuses are live that should be:
- Making sure all Endorsements' Release Statuses are live that should be:
Any non-zero fields under "not_live" means that you will have to go to that SIG, look through all of its ratings, and determine which ratings are not live. Check your excels and look for notes in the SIG Description field to see if there is a reason we should not release that interest group's ratings. If this rating is fairly new and is not done with checks, DO NOT RELEASE IT. Also, if it looks like there's a problem with it and shouldn't be released, ask Kristen about it. If you find info in the admin description, add it to your excel. If there is no reason that you can find as to why the ratings shouldn't be on the live web, release them. Webcheck the ratings and make sure they appear. Check again with the query to make sure that you have investigated all the problematic ratings for that SIG. Change the value in the query to reflect that SIG's ratings release statuses, if changes were made. Keep a count of how many current ratings (2007, 2008, and 2007-2008) are entered but are not ready
Any non-zero fields under "endorsements_not_live" means that you will have to go to that SIG, look through all of its endorsements, and determine which endorsements are not live. Check your excels and look for notes in the SIG Description field to see if there is a reason we should not release that interest group's endorsements. If you find info in the admin description, add it to your excel. If there is no reason that you can find as to why the ratings shouldn't be on the live web, release them. Webcheck the endorsements and make sure they appear. Check again with the query to make sure that you have investigated all the problematic endorsements for that SIG. Change the value in the query to reflect that SIG's endorsements release statuses, if changes were made.
- Making sure all SIG's have a category assigned to them, otherwise they will not show up on the website:
Any blank fields under "has_category" must have a category assigned to their SIG. Look at your excels to make sure the SIG should be live, then check the organization's website or call the organization in order to determine a category. Webcheck the issue organization and make sure it appears. Change the value in the query to reflect that the SIG now has a category, if changes were made.
- Making sure all Ratings have a category, otherwise they will not show up on the website:
Any non-zero fields under "ratings_wo_cat" means that you will have to go to that SIG, look through all of its ratings, and assign a category to those ratings without categories. Look at your excels to make sure the SIG should be live, then find the scorecard (online or in filing cabinet) or call the organization in order to determine a category. Webcheck the ratings and make sure they appear. Check again with the query to make sure that you have investigated all the problematic ratings for that SIG. Change the value in the query to reflect that these certain ratings for the SIG now have categories, if changes were made.
DETERMINING STATUS OF CURRENT (2007, 2007-2008, and 2008) RATINGS
- Make sure you use filters to show only state SIGs or only federal SIGs
- Count the number of ratings and endorsements in your desk that are on admin but are not yet live.
- Add up the number of "t"s in the following columns: has_2007. has_2008, and has_2007-2008. Add tally #3. Subtract the number you got in Step 2. On the Weekly Report, this will equal the "Total Count Completed (ratings pulled and live)"
- Take the "Total Count Completed (ratings pulled and live)" and subtract tally #4 and tally #5. On the Weekly Report, this will equal "Complete and Available to the Public (LIVE)"
- Count the total number of ratings you have on your desk, including those you have yet to enter. Add tally #4 and tally #5. This number equals "In Office - Not Available to the Public" on the Weekly Report.
- At some point before Friday, you will need to calculate how many SIGs you are currently contacting.
CONTACTING SIGS
This query will streamline contacting SIGs.
- using filters, you can create groups based on the last time we received a rating from them, the frequency in which they tend to produce ratings, if we're missing a current rating from them, or if they're still problematic. Once a group is created, mail merges can be used to speed up the contacting process.
- when speaking to them or emailing them, you can quickly reference this document and ee when the last time we received a rating from them was
IDEAL QUERY FOR FUTURE
[Reference: sig_checkup.sql]
Purpose of query: filter SIGs by year to see who we still need to collect from and what, streamline contacting SIGs, correct some errors from the past, and get a better picture of where we're currently at with ratings
-Special Interest Group Name
-State of Coverage
-SIG Release Status
-Phone1
-Phone2
-URL
-Contact Name
-Years of all Ratings in system, or else: # of total ratings, has a 2009 rating, has a 2010 rating, and has a 2009-2010 rating
-Years of Ratings with "use this rating" not selected
-Years of Ratings with release status of not-live
-Years of all Endorsements in system
-Years of Endorsements with release status of not-live
-If the SIG does not have a SIG Category
-The number of ratings the SIG has that do not have a rating category (these will prevent them from showing up on the website as well)
-The number of ratings the SIG has that do not have "Rating Text"
-the number of ratings the SIG has that do not have the standard blurb of "[TITLE] [NAME] supported the interests of the [ORGANIZATION] [RATING] percent in [YEAR]." or "[TITLE] [NAME] supported the interests of [ORGANIZATION] [RATING] percent in [YEAR]." for the "Rating Text"
