Drop Site Sheet Update

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

Setting the Stage: Chester and Montgomery County Toys for Tots as of Oct 2024

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

Drop Sites: Starting Population

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

Getting access to the current site list

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

Drop Site Tracking in general

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.

Updating the File / System

Step One: Refreshing Drop Site file from the LCO site

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

Get the file

- 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

Update the Google Doc

- 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

Copying 2023 sites to 2024

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

Get the previous year's file

- 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

Finding our matches

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

Updating Drop Sites to set them as Active

Identify records to update

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

Get the Export file

- 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

Locate and Modify Relevant Records

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

Page updated

Google Sites

Report abuse