Back to main Coord help page: Coord Stuff
Back to Assistant Page: Assistant
This page is geared specifically for Chester and Montgomery County Toys for Tots. While links to templates may be added or referenced, I'm not going to genericize it as much as some of the other walkthroughs on this site
Chester County Toys for Tots was stood up in 2020. There was a previous Toys for Tots campaign in Chester County, but it ceased operations in 2018. There was no Toys for Tots in Chester County in 2019.
While I originally took on responsibility for Chester County, I soon discovered that Montgomery County was not covered by a single campaign, but by many - and there were gaps in coverage.
We have gone from this
to this in 2023. But then the Harleysville campaign (in yellow) closed. None of my peers were in a position to take responsibility for the area. So we will be
This is the county for 2024. We are responsible for everything in yellow. That includes Norristown, which has... a lot of need. But it also includes King of Prussia and Blue Bell, where we can source a lot of toys.
From a Toys for Tots perspective, there are now two campaigns: a Chester County campaign, and an entirely separate and distinct Montgomery County campaign, covering the areas in yellow. Although it should be noted that *technically* the 'new' campaign is a rebrand of the Harleysville campaign, with the areas previously covered by Chester County rolled in. That's why the campaign code is PAHRLY rather than PAMNTG
I just happen to be the Coordinator for both now
But we will be treating them separately from a data perspective, both so I can get a better feel for how the needs differ, and so if I can find someone to eventually take over Montgomery County, everything is turnkey for them
Our starting outreach was to sites that were part of "Chester County:" last year, including the ones in Montgomery, as well as those that were served by Harleysville
Any 'new' sites are going to be coming in through the standard process and don't need special handling
Look here: https://www.pachst.com/assistant#h.f8vafavn7m0
You will get the user name and passwords in a separate email. Note that because there are two separate campaigns, there will be two sets of files to update
This is an expanded version of what can be seen here; this is the version you will want to review if you are *not* from our campaign
https://www.pachst.com/Coord#h.lzdfb83hhhxr
I have a Google Sheet that I use to keep track of my sites.
Chester / Montco assistants: You will be getting a link to the 'live' file for the campaign, do not update the 'template' file associated with the link above
This is the sheet that has the most changes during the campaign, as what I 'care' about will change during the campaign. Initially I need to know who has signed up, then I need to know which sites are ready to be set as visible on the search screen, then I need to know if there are sites that need to be picked up
The 2024 sheet has the follow tabs:
Drop Site To Do. Ignore this. These are note for myself for follow-up stuff that I can't delegate or is work in progress
2024 PACHST. This is a subset of the information from the Chester County Drop Dump tab. The first column will copy over the Request ID, the others will have Lookup formulas to pull the data I consider of interest. This can be modified to pull more or less as needed. Tied into Drop Dump PACHST
2024 PAHRLY. Same for Montco
Drop Dump PACHST This is the current Drop Site export for Chester County.
Drop Dump HRLY Same thing for Montgomery County
2023 PACHST. 2023 tracking sheet for Chester County
2023 Harleysville, 2023 Harleysville sites. Different format
Survey Response. These are the responses I get from a "Do you want me to clone your info" survey that goes to Drop Sites. We'll go into more detail later
2023 Data Raw data for the matching we need to do, slightly modified
In the past I've tried to track significantly more information - how many boxes each site has, who is retaining them until next year, which Consolidation Site they plan to us - but I determined I wasn't actually *using* that information. At this point I set up processes to keep the sites updated with information but don't count on most of them telling me what's going on - I don't have the bandwidth to track them that closely.
The first step that is needed will be to refresh the data in the Drop Dump tabs and update the appropriate 2024 tabs. This will give us a baseline for the other work we will be doing
Log into the LCO site for the relevant campaign
Manage Online Forms and Request > Become A Drop Site > Review Form Submissions
Set year for current year
Hit Export to Excel button
Open Export File
Copy all values
Open Google Sheet
Go to Drop Dump [Campaign]
Select Cell C1 (we need to leave the formulas in column A and B intact)
Paste all to overwrite current values
Copy down the formula in column A and B for the new rows
A: This tells us if the specific record is already on the tracking sheet
B: This copies the Request ID so VLookups work for the next tab
Where the 'Logged' field is N/A, copy the ID column
Go to the appropriate 2024 tab
Go to the bottom of Column A, Request ID
Paste the *values* you copied from the previous tab
Copy down the formulas starting on column B to populate the relevant values
The Survey Response tab can be used to identify sites that have participated in the past and wish to do so again, without having to re-enter their information.
Here is the process to follow
Log into the LCO site for the relevant campaign
Manage Online Forms and Request > Become A Drop Site > Review Form Submissions
Set year for previous year
Hit Export to Excel button
Open the file in Excel, and add a new column to A
Create a new tab in the extract file
From the Survey Response tab of the Drop Site sheet, copy the email addresses from Column D, Email Address where we don't already have a match in columns A or B. Over time, this will be towards the end of the file as matches are added to the system
Paste the values in column A of the new extract file
On the Dashboard tab, in cell A2, type the following: "=MATCH(M2,Sheet1!A:A,0)"
This is going to match the email addresses from the 2023 extract with those in the survey
Copy the formula down to all rows on the sheet, then sort the column A to Z
This indicates the columns we will want to upload
Delete all over rows on the sheet. Then delete column A, as we no longer need the matching
Delete the other tab with the email addresses
Makes the following changes on the sheet:
Column A: Request Date - set as today's date
Column B: Status - Set as pending (lower case only)
Column V: Start Collect Date - Set at 10/1/2024
Column W: Date Pickup - Set at 12/1/2024
Column AC: Comments - Clear any values, but make sure not to clear the header
Column AK: Request ID - Set at 0 (zero, not o)
Save as Excel Workbook
The upload process can be found here: LCO Page Bulk Updates
Go to the Drop Site Tracking Sheet
Most of the "ready to go live" notifications will have come via email. Between batches, updates will have been made in the tracking sheet. Find those by setting the 'Public?' dropdown to show those updates by unchecking the 'blank' box
We're going to need to do some research on the sites that didn't give us business hours to close that gap
Log into the LCO site for the relevant campaign
Manage Online Forms and Request > Become A Drop Site > Review Form Submissions
Set year for current year
Hit Export to Excel button
Open Extract File; create a new tab
Copy and paste the selection with the Request ID and Hours info the tab
Go to the dashboard tab
Create a new column to the left of Column A
Type the following into cell A2: =MATCH(AL2,Sheet1!A:A,0)
This is going to help us find just the records that we want to update
Copy the formula down all of column A; sort smallest to largest
Delete all the rows except those with a number next to them
Delete the now redundant column A
Because we have a 1:1 match between tabs, we don't need to do any extra processing
Go to the 'sheet1' tab, copy the values starting at cell C2 down - *not* the header
Go back to the dashboard tab; paste special values into column X, hours of operation
Because we have some nonpublic sites, sort column X A to Z to group the nonpublic
Hmph. Not perfect, but good enough for now
Delete Sheet1; we no longer need that information
Go to Column B, Status. Change from pending to approved (lower case)
Go to Column N, Make Public. Change from FALSE to TRUE only on public sites. So, in the case of the above, we will keep FALSE on three of the last four records
Make the same changes on Column Y, Show on Toy Drop Map
Save the file as Excel Workbook
Follow the upload process, here: LCO Page Bulk Updates
Follow the "Refreshing File" process from here
You should see the Status has been updated to reflect that they're good to go