Order Tracking E-Mail Notifications

[victimofareload]

Hello, Like many of you. I've been refreshing my order about fifty times a day hoping to see updates on it. This grew tiresome and my wife didn't like the groans every time I refreshed with no change in status.

Well. Modern problems require modern solutions.

I wrote a program that checks my order via an "API". This is the same API that ford's official order tracker seems to reference as well as all the other various trackers. My program checks the order once an hour, compares the dates to the last check and if they're different it emails me. Last night I rewrote this to allow adding additional orders to check. I'd like to extend my services, for free to anyone else here that would like email notifications when their order changes.

I'm not a programmer, and this program is held together with duct tape and rubber bands. Further, I have a day job and do fun projects like this in my spare time. I do this for free with no guarantees on functionality or performance.

If you would like to be added to the system PM me your VIN, Order Number, and email address. I'll get them all inserted as I have the time.

I first brought this to the forum in the 8/2 build thread. Feel free to look over there about more details on it.

Here's what one of the emails looks like.

There has been an update to your F150 order. Here is the current status.

Username: victimofareload
Order Date: 2021-06-16
Release Date:
ETA Start Date: 2021-08-22
ETA End Date: 2021-08-28
Production Date: 2021-08-03
Ship Date:
Transit Date:
Status:
Delivered Date:
Primary Status: In Production
Last Changed: Sun Aug 22 2021 22:04:11 GMT-0400 (Eastern Daylight Time)

Sponsored

 

[dhrandy]

Are you using Python to pull the information? I'd be interested in the coding part of it (I don't know a tone about coding except for some html and javascript). It would be awesome if it could be hosted in a docker container.

 

[victimofareload]

Are you using Python to pull the information? I'd be interested in the coding part of it (I don't know a tone about coding except for some html and javascript). It would be awesome if it could be hosted in a docker container.

No Python here. I wish I knew more about programming. I'm a telecom engineer. My job normally ends at moving packets haha.

It's actually a few different pieces. Mostly written around bash, nodejs, and SQL. First I have a bit of a point-and-click interface called "node-red". Its basically a GUI for nodejs. All the logic i've assembled there (mostly "functions" I copy-pasted or otherwise BS'd together).

I have bash scripts for each user that 1. Uses CURL to fetch the data from the ford API. 2. Turns around and uses CURL to HTTP POST that to node-red. This Bash script runs on a cron job once an hour per user.

Node red then parses the incoming data (It's in JSON format). It compares the incoming data to data stored in a MySQL database that is sorted by VIN. If any of the data doesn't match, it updates the database with the new incoming data. If an update is done, it marks a "send me" flag on the record.

Third and finally, There is a second node-red process that comes and queries the database for all "send me" flagged records and then sends them to the email on file.

There's lots of little safe guards built in along the way (Probably not enough, but it hasn't broken yet). And any activity it has, either new updates or it sending emails to people gets pushed to me in a Telegram notification (Android/IOS messaging app). So worst case if it breaks and starts sending someone email over and over again (5 min interval) I see the notifications and fix it ASAP. Worst would be if it happened overnight.

It was a fun little project and the guys in the 8/2 build week thread wanted in. So here we are

 

[dhrandy]

That's pretty impressive. You know more than I do and I took a 6 month course. I'm better at the html and design side of things. The only thing I used node-red for was when I was messing with home automation and my Wink smart home stuff, I've since switched to SmartThings. We did code some stuff in React JS, but I have coded anything with it since then.

 

[CO-Bronco]

No Python here. I wish I knew more about programming. I'm a telecom engineer. My job normally ends at moving packets haha.

It's actually a few different pieces. Mostly written around bash, nodejs, and SQL. First I have a bit of a point-and-click interface called "node-red". Its basically a GUI for nodejs. All the logic i've assembled there (mostly "functions" I copy-pasted or otherwise BS'd together).

I have bash scripts for each user that 1. Uses CURL to fetch the data from the ford API. 2. Turns around and uses CURL to HTTP POST that to node-red. This Bash script runs on a cron job once an hour per user.

Node red then parses the incoming data (It's in JSON format). It compares the incoming data to data stored in a MySQL database that is sorted by VIN. If any of the data doesn't match, it updates the database with the new incoming data. If an update is done, it marks a "send me" flag on the record.

Third and finally, There is a second node-red process that comes and queries the database for all "send me" flagged records and then sends them to the email on file.

There's lots of little safe guards built in along the way (Probably not enough, but it hasn't broken yet). And any activity it has, either new updates or it sending emails to people gets pushed to me in a Telegram notification (Android/IOS messaging app). So worst case if it breaks and starts sending someone email over and over again (5 min interval) I see the notifications and fix it ASAP. Worst would be if it happened overnight.

It was a fun little project and the guys in the 8/2 build week thread wanted in. So here we are

This ?. LOVE IT.
Just goes to show there are many different ways to get the job done.

Over in the Bronco forum we were trying to use Google Apps Script to pull the API JSON via XMLHttpRequest within a Google Sheet. Unfortunately XMLHttpRequest() isn't supported in the Google-verse.

The next effort was URLFetchApp() but sadly Fords content security policy blocks access.

Your way is very ingenious, and can handle large volumes of members status updates, and for free(?).

After realizing the GoogleSheet tracker was a no go I was thinking of doing something similar via an third party like this:
https://visualping.io/

Regardless though, AWESOME WORK. It's people like you that bring value to forums like this!

 

[victimofareload]

This ?. LOVE IT.
Just goes to show there are many different ways to get the job done.

Over in the Bronco forum we were trying to use Google Apps Script to pull the API JSON via XMLHttpRequest within a Google Sheet. Unfortunately XMLHttpRequest() isn't supported in the Google-verse.

The next effort was URLFetchApp() but sadly Fords content security policy blocks access.

Your way is very ingenious, and can handle large volumes of members status updates, and for free(?).

After realizing the GoogleSheet tracker was a no go I was thinking of doing something similar via an third party like this:
https://visualping.io/

Regardless though, AWESOME WORK. It's people like you that bring value to forums like this!

Is the bronco tracking using the same ford tracking page?

This could likely be retrofitted to work with the bronco tracking VERY easily. Assuming it's mostly the same. Happy to discuss and maybe try some stuff if it's helpful.

"Free" is relative. I'm not looking to make any money. This runs on a Virtual Machine at my home. All the pieces were already in use with other stuff I had going on (Mostly home automation). Only thing I've really sunk here is my time.

I had originally planned to use Node-Red/NodeJS's HTTP request function. Which would've eliminated the need for the curl/cron portion. But the API doesn't respond to me at all. I'm guessing the same security policies. It responds to CURL though. So that's how I proceeded.

 

[CO-Bronco]

Is the bronco tracking using the same ford tracking page?

I would assume they are the same. The only info needed to be inputted for the Bronco is their VIN and Order#. Is this the same for F150s?

https://shop.ford.com/aemservices/s...BP2_.*&vin=1FMEE5DH6MLA77590&orderNumber=5209

 

[victimofareload]

I would assume they are the same. The only info needed to be inputted for the Bronco is their VIN and Order#. Is this the same for F150s?

https://shop.ford.com/aemservices/s...BP2_.*&vin=1FMEE5DH6MLA77590&orderNumber=5209

Yup. This is the same API. Or at least the returned JSON is the same. I should be able to add this order to my notification system exactly as I do the F-150's with no modification.

If this is your order and you want to be a guinea pig I'm happy to add you. Just need your email address (Feel free to PM me).

 

[UGADawg96]

I'd think this could be done easily with PowerShell or Python too.

 

[victimofareload]

I'd think this could be done easily with PowerShell or Python too.

Yup, Absolutely could. The logic is actually quite simple and it could be retrofitted into many different languages.

 

[CO-Bronco]

Over at B6G Forum we were trying to get the data into GoogleSheets to help community members automatically track the production status of our collective Bronco's were during Ford's radio silence on why ~6000 built rigs were being held at "Dirt Mountain".

The idea of pulling the from the API came after we realized our user generated spreadsheet failed due to the "Human Factor" (ie. people being to lazy to return to the form and updating their current Production Status).

https://www.bronco6g.com/forum/threads/updated-graphs-in-production-broncos-tracker.20531/

Ford auto-emails us Bronco reservation holders once the status is updated (albeit sometimes with a few hours delay) so we were more looking to set up a data analysis tool. Should be as simple as pulling the data with XMLHttpRequest(), storing it in an MySQL database and then importing the data to Sheets to create pretty trends.

 

[victimofareload]

Over at B6G Forum we were trying to get the data into GoogleSheets to help community members automatically track the production status of our collective Bronco's were during Ford's radio silence on why ~6000 built rigs were being held at "Dirt Mountain".

The idea of pulling the from the API came after we realized our user generated spreadsheet failed due to the "Human Factor" (ie. people being to lazy to return to the form and updating their current Production Status).

https://www.bronco6g.com/forum/threads/updated-graphs-in-production-broncos-tracker.20531/

Ford auto-emails us Bronco reservation holders once the status is updated (albeit sometimes with a few hours delay) so we were more looking to set up a data analysis tool. Should be as simple as pulling the data with XMLHttpRequest(), storing it in an MySQL database and then importing the data to Sheets to create pretty trends.

Ahh. Understood. Well I'm certainly happy to assist when I have time. I've got some ideas (like using a middle-man API to allow the use of URLfetchapp. Or something like that. If you wanted to explore some further options.

 

[CO-Bronco]

Over at B6G Forum we were trying to get the data into GoogleSheets to help community members automatically track the production status of our collective Bronco's were during Ford's radio silence on why ~6000 built rigs were being held at "Dirt Mountain".

The idea of pulling the from the API came after we realized our user generated spreadsheet failed due to the "Human Factor" (ie. people being to lazy to return to the form and updating their current Production Status).

https://www.bronco6g.com/forum/threads/updated-graphs-in-production-broncos-tracker.20531/

Ford auto-emails us Bronco reservation holders once the status is updated (albeit sometimes with a few hours delay) so we were more looking to set up a data analysis tool. Should be as simple as pulling the data with XMLHttpRequest(), storing it in an MySQL database and then importing the data to Sheets to create pretty trends.

Oh wow - I just realized @427CobraJet is a member from this Forum!?!? The legend himself. There's a good 23,000 members from B6G (and tens of thousands from the other Ford forums) that want to shake your hand for creating the original VIN tracking tool. Huge props to you man!

Ever think about modifying your tool to allow for a User Accepted input field where their Production Status data (ie. Scheduled/InProduction/Built/Shipped/Delivered) would be stored in a database and then used for Analytics? VINs could be blocked if privacy needed to be maintained, with only Production Status displayed.

I think there would be a huge demand for this on the Bronco side of things, but could also see it useful elsewhere to judge when stored vehicles (ie. With semiconductor chip constraints) get parked and start being shipped again).

Thoughts?

Again, you're a champ!

 

[victimofareload]

Oh wow - I just realized @427CobraJet is a member from this Forum!?!? The legend himself. There's a good 23,000 members from B6G (and tens of thousands from the other Ford forums) that want to shake your hand for creating the original VIN tracking tool. Huge props to you man!

Ever think about modifying your tool to allow for a User Accepted input field where their Production Status data (ie. Scheduled/InProduction/Built/Shipped/Delivered) would be stored in a database and then used for Analytics? VINs could be blocked if privacy needed to be maintained, with only Production Status displayed.

I think there would be a huge demand for this on the Bronco side of things, but could also see it useful elsewhere to judge when stored vehicles (ie. With semiconductor chip constraints) get parked and start being shipped again).

Thoughts?

Again, you're a champ!

I'd like to discuss this more via PM. I'll send you one.

 

[Bbass5]

Oh wow - I just realized @427CobraJet is a member from this Forum!?!? The legend himself. There's a good 23,000 members from B6G (and tens of thousands from the other Ford forums) that want to shake your hand for creating the original VIN tracking tool. Huge props to you man!

Ever think about modifying your tool to allow for a User Accepted input field where their Production Status data (ie. Scheduled/InProduction/Built/Shipped/Delivered) would be stored in a database and then used for Analytics? VINs could be blocked if privacy needed to be maintained, with only Production Status displayed.

I think there would be a huge demand for this on the Bronco side of things, but could also see it useful elsewhere to judge when stored vehicles (ie. With semiconductor chip constraints) get parked and start being shipped again).

Thoughts?

Again, you're a champ!

As far as analytics go, you could break down the VIN numbers based on this VIN guide and really get into the details. Of course that might be digging up too much Ford dirt applying the right analytics. Could compare across Bronco, F150, mustangs, etc and narrow down which trims, build locations, engine models, etc have faster or longer lead times. Lots of interesting data there.

Sponsored