Daily Appealsstages Data Transfer

Step 1

Run the SQL below:

select id, owner, email, source_url, source_description, comments, created_at from appealsstages where reviewed = false order by email

This will return all the records that have not yet been processed.

Step 2

Spot check the recrords returned to delete spam, correct typo mistakes and empty submissions

Step 3

Run the SQL below:

update appealsstages set spam = false

This will update all records to indicate that they are NOT spam

Step 4

Run the SQL below:

select max(id) from reviews;
select max(id) from appeals;
select max(id) from audits;
select max(id) from queries;
select count(*) from appealsstages where reviewed = false

Save the results of these queries to be used later.

Step 5

Run the SQL below:

CALL TransferAppealStages(null);

This will run the Transfer Appealsstages Stored Procedure

Step 6

Ignore the error message below:

Error Code : 1329 No data - zero rows fetched, selected, or processed (0 ms taken)

Step 7

Confirm that all records were transfered.
Run the SQL below:

select spam, source_url, created_at from appealsstages where reviewed = false

This query should return 0. If records are returned, they were not processed.

Step 8

If records are returned in step 7, they may have come in after you completed step 3.
To confirm, check the url field in the query above and see if if the url is included in the exported result set in step 5.

Step 9

To perform additional data checks, run the SQL below using the results of the queries in Step 4 above:

select url from reviews where id > *** query result 4.1 *** order by url
- Do you recognize any dupes in Query #1?
select count(*) from appeals where id > *** query result 4.2 ***
select count(*) from audits where id > *** query result 4.3 ***
- Query #2 and #3 should equal each other
select count(*) from queries where id > *** query result 4.4 ***
- Query #2 + Query #4 should equal the amount returned in Step 4, Query #5

Step 10

Problems? IM lleahy right away.

Step 11

Run the SQL below using the results returned in Step 4, Query #2:

SELECT SUBSTRING_INDEX(lower(url), "http://", -1) as url from appeals a, reviews r where a.review_id = r.id AND a.id > *** query result 4.2 *** group by r.url order by r.url, a.created_at

This will create a pretty list of URLs for "new appeals only" to send to Google

Step 12

Export result set to an html file: Appeals<MMDDYYYY>.html

Step 13

Save html file to file server: *****

Step 14

Email the URL list to: ***@*****.com, ***@*****.org and the subject: StopBadware: New Appeals
Please be sure to send these results to both email adresses.

Step 15

You're done!