Pull All Tracking For A POGO Form

Pulling all tracking for a POGO form

Jeremy Kushner - 17 Feb 2021

This procedure explains how to get a list of all tracking numbers for a POGO form. This can be helpful if a list is requested by a customer or if you need to confirm that all shipments have arrived successfully.

How to pull tracking:

In Shipstation, figure out the date the form was shipped. You can usually do this by searching for the POGO form number under “Orders”, sorting by “Ship Date”, and then seeing what days the ship dates fall on. Note, most forms will ship in one day, but there were multiple shipments on different days this may require a little digging.

Now get the Shipping Manifest for the date or dates the form shipped. Go to “Insights” at the top, then “Reports” on the left, and then click the “Shipping Manifest” link.
Select the date you need and click “Export to Excel”.

Import the shipping manifest into Google sheets. If you’re pulling multiple shipping manifests for multiple days, you may need to combine them into one sheet.
The manifest will look like this:
Now, export the POGO csv from the form. Go to the form in the POGO backend, go to the “Orders” tab, and click “Download Order List CSV”.
Import the POGO csv as a new sheet in the same document as the shipping manifest. You can use the default import settings, except use “Insert new sheet(s)”.
Now you have two sheets in the document, one with the shipping manifest and one with the POGO csv.
So that it matches up with formulas used in the following steps, name the sheet containing the shipping manifest “Shipping Manifest” and name the sheet containing the POGO CSV “POGO CSV”.
Add a third, blank sheet and name it “Shipping By Order #”
In your new blank sheet, add a list of all unique order numbers from the POGO CSV. To do this, go to the top cell of the first column of your blank sheet and paste this formula without including the double quotes: “=unique('POGO CSV'!N:N)”.
1. Note, if any of the formats change, or if anything is named differently, this formula won’t work exactly as written. For reference, “'POGO CSV'!N:N” refers to the column containing the order numbers in the POGO CSV sheet.
Now in the next column to the right, you’ll add the tracking from the shipping manifest. The very first order number at the top will be in cell “A2”. To the right of that, in cell B2, paste this formula without the quotes: “=transpose(filter('Shipping Manifest'!J:J,'Shipping Manifest'!M:M=A2))”.
1. “'Shipping Manifest'!J:J” refers to the column in the shipping manifest containing the tracking numbers. “
2. 'Shipping Manifest'!M:M” refers to the column in the shipping manifest containing the order numbers.
3. “A2” refers to the cell containing the order number you’re looking up.
This will bring up all tracking numbers in the shipping manifest associated with the order number in cell “A2”.
Now, just drag down the lower right corner cell “B2” to get the tracking numbers for all other orders on the form. Your sheet will look something like this. All tracking for an order number will be shown to the right of that order number:
If any reshipments were made and were given different order numbers (such as if they ended in a “-1”), those may need to be added manually to the list.
If you’d like to add tracking URLs to the sheet, you can copy and paste this formula to the right of the others, where “B2” refers to the first cell with tracking on it:
1. =hyperlink(concat(if(left(B2, 5)="1Z35W", "https://www.ups.com/track?loc=null&tracknum=", "https://tools.usps.com/go/TrackConfirmAction?tRef=fullpage&tLc=26&text28777=&tLabels="), B2), B2)
2. Drag-copy it down like usual to get links for all the tracking numbers.
3. If there are multiple columns of tracking numbers, drag the link formula to the right as well to add links for the other columns.
Protip: if you need to check statuses on the shipments, you can easily search multiple shipments at once by copying tracking from the columns and pasting them into the shipping providers’ websites. UPS allows you to paste 25 at a time, and USPS allows up to 35.