Wiki Model 3 Order Tracking Spreadsheet

[Waiting4M3]

My 2 cents:

I think what is of the highest value is a single database that is open to the community.

I would also advocate a separation of front end visualization vs the database. I hope it can reduce the workload for whoever is maintaining the database, and also prevent someone from walling off the database by owning the front end. I also think multiple front ends with different people doing their own separate analysis and visualization could also be a valuable thing to have.

 

[Dogwhistle]

Having two separate databases makes both of your projects pretty much useless, unless entries are funneled between each other.

 

[mwu]

My 2 cents:

I think what is of the highest value is a single database that is open to the community.

I would also advocate a separation of front end visualization vs the database. I hope it can reduce the workload for whoever is maintaining the database, and also prevent someone from walling off the database by owning the front end. I also think multiple front ends with different people doing their own separate analysis and visualization could also be a valuable thing to have.

I like these ideas and I am happy to provide my expertise if it is desired. As an alternative to a spreadsheet, I built out a simple Mongo database for my site. Sorry to get technical here (also sorry if I am dumbing it down too much for anyone -- I don't know who knows what), but here is what the data in my Mongo database looks like:

> db.reservation.findOne({username: "mwu"})
{
    "_id" : ObjectId("5aeca7d7b9420f22ad737093"),
    "username" : "mwu",
    "location" : "Charlotte",
    "state" : "NC",
    "country" : "USA",
    "owner" : false,
    "date" : ISODate("2016-03-31T05:30:00Z"),
    "inStore" : true,
    "inviteDate" : ISODate("2018-03-22T00:00:00Z"),
    "configurationDate" : ISODate("2018-03-30T00:00:00Z"),
    "vinDirty" : null,
    "vin" : null,
    "vinDate" : ISODate("2018-05-02T00:00:00Z"),
    "deliveryDate" : null,
    "color" : "Solid Black",
    "wheels" : "18 inch Aero",
    "autopilot" : "None",
    "firstProductionDeliveryEstimate" : "Mar 2018 - May 2018",
    "shortRangeDeliveryEstimate" : "Late 2018",
    "awdDeliveryEstimate" : "Late 2018",
    "powertrain" : "RWD",
    "interior" : "Black seats",
    "trim" : "Premium Upgrades Package",
    "battery" : "Long range battery",
    "comments" : null,
    "lastUpdate" : ISODate("2018-05-02T00:00:00Z"),
    "calculatedDeliveryDate" : ISODate("2018-05-02T00:00:00Z"),
    "status" : "Delivered",
    "configurationToDelivery" : null,
    "configurationToVin" : "33 days",
    "vinToDelivery" : null
}

I also put indexes on a few fields which is what helps querying the data faster (and a part of what lends speed to my site). You can think of an index as a pre-calculated (at the time the data is added to the database) sort for that column:

      coll.createIndex({ username: 1 }, { unique: false }),
      coll.createIndex({ state: 1 }, { unique: false }),
      coll.createIndex({ date: 1 }, { unique: false }),
      coll.createIndex({ inviteDate: 1 }, { unique: false }),
      coll.createIndex({ vin: 1 }, { unique: false }),
      coll.createIndex({ vinDate: 1 }, { unique: false }),
      coll.createIndex({ deliveryDate: 1 }, { unique: false }),
      coll.createIndex({ color: 1 }, { unique: false }),
      coll.createIndex({ wheels: 1 }, { unique: false })

MongoDB is a schema-less database which means you don't have to tell it in advance what data fields and data types there will be before adding data. That has its benefits and its drawbacks.

I could design a traditonal Relational SQL database if desired, but I don't think that the overhead of that type of database is necessary for this.

 

[Troy]

I created this chart recently and I want to explain what it shows. The highlighted point represents somebody who configured on 15th April and received his VIN 16 days later. The wait time from configuration to VIN was slightly longer before that but generally speaking, most recently it was between 16-26 days for this color and wheel combination.

If you are waiting for a VIN, the easiest way to access these charts is to open the published spreadsheet here and scroll to the right. This link opens directly to the Production tab. The published version works much faster than the other 4 spreadsheets but it doesn't show the All Entries tab and doesn't support filtering.

Filtering is an important feature of Google Sheets. Here is a video that explains how you can filter and sort the data. You can then try it out here. I'm focused on creating the best experience for the users. The filter views in Google sheets is pretty advanced and works well in my opinion but of course, I'm trying to make things run faster.

I'm always trying to improve the tables and charts. I'm working on some new ones. I have been responding to feature requests since early January. If you check out the Feedback tab here, you can see that dozens of people wanted new features and I added many of those. If you want something new, you can use the feedback tab on the spreadsheet. If you want to talk about something else with me, send me a personal message.

By the way, the VIN chart I created in January has improved a lot over time. Initially, it was a simpler chart with a red trendline. Then I combined it with the NHTSA VINs chart and added the numbers released by Tesla. Today, Tesla registered some new VINs. I added those to the chart as well. I think It looks rather cool. The easiest way to view a live version of this chart is to open this page.

 

[Troy]

If you are waiting for an invite, I recommend checking out this chart I created a long time ago. It didn't always look like this. Initially, it didn't have the red and green bars. I experimented with different ideas until I came up with this version. Splitting the first day into In-Store and Online reservations was suggested by one of the users. I thought that was an excellent suggestion so I added that. I removed some other charts about invites because they were not visual enough and duplicated what this chart shows. You can see the live version of this chart here. It might take 30 seconds to load when you first open it but then it opens faster.

 

[FloridaJohn]

FYI, this morning I was looking at the data and noticed that the two May invites are listed at May 18th and 19th. Since today is only May 4th, I assume those dates are incorrect.

I'm not sure which database needs to be updated now, but that should probably be fixed.

 

[Wandylee]

Reserved on 4/1/2016. Invited on 4/18/2018 to configure. How long does it take to get the vin?

 

[hobbes]

That's a shame, as it means that the value of the project will drop precipitously due to partial/less representative data in each. As it sounds like @chojn1's first desire would be to not split the data, I hope you guys can work it out.

I don't want to 'take sides' as I'm an outsider in this case, but I will say that I hope that you learned something from the brouhaha raised by you, @Troy, in the Disagree Battle of 2018, and that that behavior does not reflect what's going on behind the scenes in this case. We're all friends here.

I agree that it would be better to have a common data base. That said, @Troy has done an tremendous amount of work for the community here, see for example the European registration stats wiki and corresponding google sheet linked in my footer. I always find it really annoying when I put a lot of effort into a free project and all you hear is criticisms and things people don´t like about it, as if people paid for a service. Maybe he is a bit sensitive about disagrees for example - but you´re not going to fix this by disagreeing again (with a post that very matter-of-factly stated his point of view - noone really has the knowledge of what went on between the two, so no point in judging anyway). What we have is an unfortunate problem between two users that they will hopefully work out - and if not, nothing we can do about it. Rant over. Just my two cents.

 

[Troy]

I didn't want to write this message because this is not the place but many details were misrepresented and it creates the wrong impression about me if I don't respond to CJ's message here.

(1) "Troy came along a few weeks later and asked to join."
I didn't ask to join. I was helping out unofficially without any expectation. CJ suggested that I should join. I enjoy helping people with spreadsheet issues because I like Google Sheets. I volunteered to help hundreds of people. Here is my profile on Google product forums.

(2) "But once in, Troy did not want anyone else involved"
Not correct. In January I asked @schonelucht if he wanted to come back. He had started the project but the file became inaccessible when he was away. I thought it would be a nice gesture to invite him back. I don't think @schonelucht would mind if I share a part of our communication back then. Check out my message HERE. It demonstrates what I was doing and how I approached things.

(3) "he wants my owner password to the spreadsheet."
Not correct. In January CJ created a Gmail account for this project and sent me the password. Here is a screenshot. I created many files for this project and then transferred ownership of these files to that shared Gmail account we both had access to. After an argument, CJ changed the password. I asked him to restore it so I could continue managing my own files and supporting his website. He didn't do that. It caused major disruption.

(4) "The last straw came when he cut off the data feed to the website yesterday."
It was the opposite. One minute both the website and the spreadsheets were working fine, the other minute I opened one of the spreadsheets I had created and realized that I couldn't edit it anymore. This caused lots of problems for hundreds of users because there was no link or explanation on those files. For example, THIS is one of the display files I had created. I created the file and about 95% of the tables and charts on that file and all the backend functionality that made things work. I also arranged a link to be added to Reddit's sidebar. I'm very active on Reddit. Many people know me there. After the sidebar link, the project took off.

 

[mrkymarc]

This seriously needs to be resolved. Is there a trusted user or Mod that could be some sort of arbitrator?

I think we are coming to a point where we will have a lower sampling of the data as day 2 and beyond reservation holders start getting invites, VINs, and deliveries and these people are less likely to be the extreme fans that are around these forums. Adding confusion about which which data source to use will only make it worse.

Also, with the coming tax credit phaseout, new options, etc. the data will be extremely valuable to those of us who are making purchasing and timing decisions based on the data.

Please, let’s figure this out. We appreciate the work of everyone who has contributed to the project and we want to see it continue on.

 

[Troy]

Hi, @mrkymarc. The spreadsheet project has moved on and everything works correctly. There is no problem with data entries. Hundreds of new users are entering their data. The link on the Reddit sidebar was updated when the problem started. I'm getting lots of entries from Reddit. Also on the other Tesla forum, there is a sticky thread here that is popular. I created that in mid-January, two weeks after this thread was started. A small number of people might get confused and might not enter their data into the spreadsheet. That's not an important problem because the high number of new entries makes it irrelevant.

Recently, I added a new question about vehicle condition and delivery experience. This is about panel gaps, paint issues etc. Data started to come in. In the next few days, I will add charts about that.

I'm working on Federal tax credits calculations. I have detailed calculations about that on a different spreadsheet. Check out my message here where I explained the situation with data sources. I'm working on an update.

If you want to check out a specific topic, I recommend using filter views. Watch this video that explains how you can filter and sort. Then open one of the 4 numbered spreadsheets below and try it out yourself. Advanced filtering and sorting is an excellent feature of Google Sheets. Here are some recent examples that demonstrate how people are using these features:

• Model 3 Invites Spreadsheet #1 << To edit your entry, open one of these 4 files and click Edit next to your username
• Model 3 Invites Spreadsheet #2
• Model 3 Invites Spreadsheet #3
• Model 3 Invites Spreadsheet #4
• Model 3 Invites Spreadsheet Published Web Version (Loads faster but no 'All Entries' tab and no filtering)
• Google form to submit a new entry

 

[Dogwhistle]

Hi, @mrkymarc. The spreadsheet project has moved on and everything works correctly. There is no problem with data entries. Hundreds of new users are entering their data. The link on the Reddit sidebar was updated when the problem started. I'm getting lots of entries from Reddit. Also on the other Tesla forum, there is a sticky thread here that is popular. I created that in mid-January, two weeks after this thread was started. A small number of people might get confused and might not enter their data into the spreadsheet. That's not an important problem because the high number of new entries makes it irrelevant.

That’s great and all, but you and Chojin have fragmented the data. Take VIN reports for 5/5 for instance. Your database shows 6 VINs assigned that day, his shows 9. Are your 6 included in his 9? Are they entirely different entries, so that each of you all only collecting and reporting half the data? Will ~50% of the configurations on your sheet never show VIN resolution since they are only reported on a separate database? Again, one of you two needs to jump on the grenade and delete your project, sorry. Or figure out a way to sync databases again.

 

[chojn1]

@mwu,

First are you really sure you want to take on this project?
It is really a thankless job. And the time, labor, and money you spend on this project will never be compensated.

If you still want to proceed . . .
How about setting up a third better faster database.
You can have mine.
You can also pull from Troy's.
And we can convince everyone to contribute only to your database.
Ownership should remain with the community.
Eventually the original databases will become obsolete.

What do you think?
CJ

 

[mwu]

@mwu,

First are you really sure you want to take on this project?
It is really a thankless job. And the time, labor, and money you spend on this project will never be compensated.

If you still want to proceed . . .
How about setting up a third better faster database.
You can have mine.
You can also pull from Troy's.
And we can convince everyone to contribute only to your database.
Ownership should remain with the community.
Eventually the original databases will become obsolete.

What do you think?
CJ

I don't really want to manage maintaining and improving on a bunch of html and charts. I had three charts I felt were useful to me and I got them working mostly well (though they did have some bugs occasionally) and I felt ok letting that be the finished state with the exception of some bug fixes as I found problems. Besides, while I can do html and css work, I am not a UI / UX designer -- that part is better left to someone else.

However I don't mind maintaining a database and a set of APIs. The below is my suggestion -- open to constructive criticism.

I can build and maintain a database and a set of APIs to pull data from it for charts. Those would be small and limited initially, but would grow over time as people contribute or request new APIs. I can create some web component elements that create charts and tables that pull from the database so anyone can plug them into a website.

I can also help maintain a github repository that holds code for a public website that I host. I can set it up so that any commits to that github repository automatically get deployed to the hosted site. This would be run like most open source projects -- someone would fork the code, make changes they desire, then request those changes be pulled into the main codebase. I and a few other administrators would be able to accept changes or reject them. I personally would only reject things inappropriate. Reviews are mostly for code that is buggy or has quality issues in which case comments are made and the changes would be approved when the code up to par. This way anyone can contribute and have their work on this hosted site. The site's code and the database would remain public.

Finally, I know that not everyone can program, so I would create a way to connect the database to a google spreadsheet so that the spreadsheet data remains up to date with any data changes through the form. I don't want too many of those auto update spreadsheets because it would become expensive (time and bandwidth) to update a lot of spreadsheets. I would provide a way to request a constantly updated spreadsheet (even if it's just to send me a PM or email).

I think this would be enough to:

• Keep my workload manageable
• Allow others to contribute
• Keep the data and work public
• Allow for anyone working from spreadsheet data to continue as before
• Keep everyone accessing the same data set (no matter how many visualizations there are, there is one authoritative data set)

Anyone wanting to query directly from the original database (which will likely be the fastest) would need to use either the public APIs (requires some programming knowledge) or use the public web component elements (need to be able to add some HTML tags to a website). API access would be read-only for charting / tables / other analysis or visualization.

The main supported website would need some people who can do design and or HTML work to get it off the ground. I would prefer for the design to be responsive (automatically mobile friendly without having to create special copies of pages for mobile devices).

 

[mrkymarc]

Honestly I think the websites are overkill. The google sheets with the published web version is plenty fast, and the google form for data entry seems to work pretty well.

New plots and visualizations could be useful, and I’d hope @Troy would be ok with people pulling data to provide those, but honestly I think we’re getting diminishing returns from new charts. Keeping a single source of truth seems much more valuable.

 

[elasalle]

Which form are we supposed to use to enter new data?

My 2c. Asking question - how many more months/quarters will the spreadsheet/chart/website remain useful? Once ramp is confirmed, I think there will be less and less interest - or more and more data?
Hope the foundations are there so that in future it can be quickly used for Model Y ramp (Roadster/Semi - i don't think will have enough data points). Best of Luck. Cheers & thanks.

 

[Troy]

I created a new chart for VIN waiters. I wanted something that gives buyers a better idea of the size of the queue and their place. It took me a while to come up with this idea but I think it turned out pretty well. In case you find the technical part interesting, here is a screenshot that shows how it works. I created a dynamically adjusting 6 column matrix and then attached the usernames to the numbers using vlookup based on their place in the queue.

Click here for the live version. This link opens directly to the new VIN Queue tab which shows all 12 color and wheel combinations. This is the published version of the Google Sheet and it supports heavy traffic. Click here for details about entering and updating your data.

 

[ebmcs03]

Very nice job Troy. Sad to see I’m still so far away from getting a vin though.

 

[TT97]

I created a new chart for VIN waiters. I wanted something that gives buyers a better idea of the size of the queue and their place. It took me a while to come up with this idea but I think it turned out pretty well. In case you find the technical part interesting, here is a screenshot that shows how it works. I created a dynamically adjusting 6 column matrix and then attached the usernames to the numbers using vlookup based on their place in the queue.

Click here for the live version. This link opens directly to the new VIN Queue tab which shows all 12 color and wheel combinations. This is the published version of the Google Sheet and it supports heavy traffic. Click here for details about entering and updating your data.

Great chart. Is the U.S. and Canada orders on the same chart? If so, I would suggest separating as they seem to have different queues. Thanks.

 

[bgbolton]

I created a new chart for VIN waiters. I wanted something that gives buyers a better idea of the size of the queue and their place. It took me a while to come up with this idea but I think it turned out pretty well. In case you find the technical part interesting, here is a screenshot that shows how it works. I created a dynamically adjusting 6 column matrix and then attached the usernames to the numbers using vlookup based on their place in the queue.

Click here for the live version. This link opens directly to the new VIN Queue tab which shows all 12 color and wheel combinations. This is the published version of the Google Sheet and it supports heavy traffic. Click here for details about entering and updating your data.

Can you add flag emoji before names?