How to tell how many orders were delivered before tracking was uploaded?

Payments + Shipping & Fulfilment
,
1

Hi folks,

Dealing with a pretty unique situation I believe and trying to get a better idea of how many orders are affected by it. We are a marketplace where we don’t control fulfillment or shipping. Companies who fulfill the products we sell manually provide exports to us with fulfillment information and tracking information. We’ve been finding more cases where packages are arriving to customers BEFORE tracking information is uploaded and therefor although a shipping confirmation goes out, a delivery confirmation email is not triggered with webhook. This is clearly shown in the order timeline.

Can any of you brilliant shopify minds think of how to create a report, or pull data into sheets and filter so that I can determine:
In X month, how many orders were delivered Prior to the date tracking was uploaded?

If we can base it upon events in the timeline, that’s one way we could potentially approach it. Like "How many orders do not have “Delivery Confirmation Email Event” If we can base it upon data within the order like “date of fullfillment” vs “date of delivery” that could work.

Any assistance regarding this would be appreciated!

1 Like
2

Thank you for the AI summary ideas, still looking for anyone real with a clever solution however.

3

That’s a tricky situation but you’re thinking about it the right way. Since Shopify’s native reports won’t give you a direct comparison between “delivery date” and “tracking upload date”, you’ll probably need to export and reconcile that data yourself. 2 approaches that could work:

  • Order timeline events export: Pull the order history into a sheet and filter for cases where the delivery scan happened before the tracking number was attached. You’d need to match up the fulfillment date vs. delivery confirmation date.

  • Tracking/reporting app: An app like ParcelPanel Order Tracking can give you more visibility into tracking events, including when tracking was added vs. when carriers marked it delivered. That can save a lot of manual reconciliation and help you identify the affected orders more quickly.

If this gave you something useful, feel free to mark it as a solution so others can find it too :blush:

4

Hi @Forrest260 ,

It sounds like you’re dealing with a tricky situation regarding fulfillment and tracking updates. To generate a report that helps you track orders delivered prior to the tracking information being uploaded, here’s a potential approach you can try using Shopify’s data and order timeline events:

1. Export Order Data

First, you’ll need to export your order data to analyze it. You can do this by going to the Orders section of your Shopify admin panel and using the export feature to pull a CSV file. Make sure to include fields like:

  • Fulfillment date (the date when the product was marked as fulfilled)

  • Delivery date (or shipping confirmation, depending on your Shopify settings)

  • Tracking number and tracking date (if available)

2. Using Shopify Timeline Data (Event Tracking)

The timeline data could be an effective way to track when the tracking information was uploaded and when delivery was confirmed. If you have access to timeline events via Shopify’s API, you can pull data on order events, such as the “Fulfillment Event” and “Delivery Confirmation Event”. This data would allow you to check if a delivery confirmation email was sent and whether the tracking was uploaded before or after delivery.

If your system supports webhooks for delivery confirmation, you can use the webhook data to correlate when delivery confirmation emails were triggered and compare that with the order fulfillment timeline.

3. Custom Reports in Shopify

Shopify Plus users can create custom reports directly from the Shopify admin. You can create a custom report with filters based on the fulfillment date and delivery date, and then check if the “Delivery Confirmation” email was triggered. You might need to integrate a third-party app if you’re using a basic Shopify plan, such as Data Export Reports or Report Pundit, which can help you build more advanced reports.

4. Google Sheets or Excel Analysis

After exporting your data, you can import it into Google Sheets or Excel. Then, you can create a formula to calculate the difference between the date of fulfillment and the date the tracking was uploaded. You can filter by rows where the date of delivery is earlier than the tracking upload date.

For example, you could use an IF statement to filter and count orders where:

  • IF (Date of Delivery < Date of Tracking Upload, 1, 0)

Then, you can aggregate these results to get a total count for the month.

5. Using Apps for Custom Reporting

There are apps available in the Shopify App Store like Advanced Reports, Data Export, or Better Reports that allow you to pull more granular data and create custom reports. You might find an app that already includes the functionality you’re looking for to automate the process and save you time.

6. Considerations for Automation

If you need to automate this process for future orders, you could consider setting up a system where a custom webhook is triggered every time an order is fulfilled and tracking is uploaded, and then another webhook for when the delivery confirmation email is sent. This can help you keep track of discrepancies moving forward.

I hope this helps! With this approach, you should be able to filter orders by delivery date and tracking upload date. Let me know if you need more help or run into any specific roadblocks.

Best regards,

Felix

1 Like