Dashboard Data Feed & Website Hosting Handoff

Hey Garrett. Um, okay. So we were just saying, I don't think we've met. Um, like we may have talked once or twice in passing, but I don't think we've actually gone on a call together like this.

Oh, no, thanks.

So yeah. Um, so I, I think I gave you a little bit of background in the email that I sent over, but, uh, I think just to kind of restate what the goal is here. So we have a dashboard that we have the companies feeding reservation data into kind of to, so we have like a, um, view for management to see what's going on on a daily basis. Um, we also have a dispatch dashboard. So as things are scheduled to go out for the day, we can kind of see the status, um, whether they've been filled or not. But this, I'm kind of just focused on what is being quoted and what like kind of business is coming in on a daily basis. And I know that everything kind of lives on that, um, server that you guys have and everything is kind of processed through Excel. So one of the workarounds that I came up with on our side, because we have a DOS-based system for the vehicle rental, is I get it to spit out an email report that basically every time something updates or every time we get a new reservation, it'll give me an email saying here's the additional reservation and it'll add it to kind of the, uh, running list of reservations for the day. And then I have the dashboard able to pull that information out and kind of update it. So I can share it with you really quickly.

If that reservation changes though, you're not getting that info, you're just getting the first one, right?

Sorry, I can't really hear you.

Switch my mic. Better?

Yep. Yeah, it sounds better.

For some reason it defaults to a bad microphone, so I have to switch it. Um, so the reservation— let's say the dates on that reservation change though, like a couple hours later. You're not getting that email, you're just getting the original.

I am getting that email. Um, so yeah, I can show you kind of what the— you can see this. Um, so if I go to all mail I get an email. It's not actually every time a reservation uploads, it's every 10 minutes. Um, but it just— the system will ping me every 10 minutes with an email with all of the updated information about the reservations that have been made for the day. So you can see for Avon— oh, sorry, this is for the, um, this is for the dispatch dashboard, but it's kind of the same idea. So I just get this Excel sheet. Um, so I didn't know what the capabilities were from what you guys have built, so that was my initial thought. I can probably work around with some other options. Just the goal would be whenever something new is generated, um, I'm able to get an update on that somehow, whether it's through an email or something else, um, with the information so that I can pull it into this dashboard.

So what mechanisms do you have for ingesting that data? Because the ideal type of scenario would be pulling directly from the, the quote, saved file, because you are on the network, we could pull from there. I don't know where— if this is hosted externally, or there's a link. But the way that we could get these over, if you wanted them on a regular email, you wanted on a cadence that has a report, would you want all, or would you want just what's changed?

So Garrett showed me a report that you had, I can pull that up as well.

The one that I emailed to you.

Yeah. Let me, let me pull that up.

By the way, if we, yeah, if you just, if you have a VPN to our network, then you can just have that in real time whenever you want.

Yeah.

That might be the easiest way to do it. Um, I don't have that right now, so maybe that's the, the best way to go about it.

Okay.

But yeah, so this dashboard, are you just looking for, you're looking for number of reservations, number of quotes. And the monetary value of it, right?

No, I'm looking for the quotes. So I'm looking— I can pull up my email— is the create date and time of the quote. So I know what day to associate with it. This is when the first time we heard from the customer, they reached out to create a quote. Like, to me, that is the most important data point because we can see the volume of activity going on a day-to-day basis, whether they revise it 22 times. I know that's the unique aspect of it, but I want to know when are we actually getting that outbound contact from the customer? So I think that's the only thing missing from the query right now. So if we can get the date, either the create date or just a timestamp activity, whether it's a date or, I know I think you said a time would be difficult. Something that just indicates what day we should allocate that initial outreach to.

So based on this, what you can do, you can actually ascertain that already based on looking at version 1 of every quote ID. If you're looking for the first version of each and when it was created and don't care about revisions, we just look for version— the lowest number for each quote ID. And I could write a query for it that basically pulls it out into an additional sheet even so that you just have a running list of all of the lowest versions for each quote ID.

So where on this sheet does it show when the quote was created, like what day the quote was created?

Oh, that's a good point. So that would be on the— sorry, go ahead, Garrett.

For reference, Devin, he's looking at the, the quote report you created for me that only shows the latest version. He's not looking at the master.

Okay, so I would create a new version that sends out the—

okay, well, so I'm actually— I'm looking at the raw data right now, Garrett.

Yeah, but you're looking at that raw data. I believe the one you're doing it. So let's say we click save 5 times on a quote, right?

Yeah.

There is a column, I think it's column E, it says version, correct? It's a little small, I can't see it.

Yeah, I'm gonna pull it up on Excel so we can take a look at it.

I can just open it on my end too.

Okay. So, this— can we see this?

This may have the lowest version in it.

Yeah. So, that's what I thought is if I go and I want to look at quote, this one has 15 versions. I got to enable editing. But I think I should be able to see all 15 versions. And that's great.

If you sort by quote ID, we'll be able to tell right now, actually. If you see multiple versions for the same quote ID right there, we'll know for sure.

I'm pretty sure you're not because you, Devin, you built this quote report to only show me the latest because I don't care about the history. The history only shows in the master.

So this is the raw data. Is the raw data not have it? Have we showed you my quote ID?

Sorry.

Okay, no, it only does show the version 15. It doesn't show—

then I need to get a new one.

You're looking at like a junior master version that I had Devin built. The master master that pulls the information to get into here, and then this eliminates previous versions because I don't need this to be what we're talking about. But there is a version of what you're asking for where version 1, version 2, version 3, they all show up in the raw data. That's just a different file I'm not currently sending you. We can use that one. It's just a million times bigger.

Well, I don't need, I guess I don't need any sort of like report. I just need the raw data and I can have the dashboard build, basically build what I need out of that. I'm going to pull the data because I don't—

do you want the raw data to constantly only give you the latest version then, or do you want version 1, version 2, version 3 every single time?

So I need version 1 to know when a quote was generated. What day, and then from there I just need the latest version. But I can have it just say like, look at all the reservations that have come in today, pick out what the latest version is and give me that information. Outside of that, ignore it.

Like, wouldn't this report give him what he needs besides the timestamp?

It does not have the timestamp and I don't think it has version 1.

So because like I'm looking at 1/1/21, it would have version 1 the very first time it was created.

Yeah.

Yeah.

If there's a version of this that has all 15, then that would have what I need. I just need the date stamp so I know what the first date that was created.

So we just need to make another sheet, I think, for that, Garrett. We'll just duplicate the raw data, change the query from max to min, and basically just have it pull the lowest version for each, and that'll be your start date. And then we can have the other one be latest revision. Or do you want all the intermediates?

I was gonna— it doesn't even have to be that, um, it doesn't even have to be that complex. I think if, if there's just a raw data sheet where everything is coming in as something gets built, if I just get access to that with a created date stamp, or I can get read access to that, then I will have what I need because then I can just— I can sort on my end from there.

Do you need the timestamp though? Because that was the problem we were getting with using the referential date that we have. We're using— it comes as a format. So when you use a formula to pull over a value from cell to cell in Excel, it copies the formatting as well in a way.

So I don't think I need the time. The time is a nice to have. The date is the mandatory thing.

Okay, so the date— we have that then. So that's good. Yeah. Okay, perfect. So then it sounds like I just need to make another sheet that has the Or no, the raw. We just need to send the raw then and we're good.

Yeah.

So I'm going to pull up the raw right now.

Yeah. Pull, if you can share that, because I also, I think if we're sending all of the raw data, like sending it in an email is probably going to not be the most efficient way.

Sorry, JD. Is there a way to create a copy of the raw so it doesn't affect opening the raw? Because you know, when we have it open, then things are not being updated.

Yeah, yeah, yeah, yeah. Depends how— if we're going to do it via email, we would have to— we would be making a copy and sending that copy so that we're not having in-use issues. If we went the route of a VPN, we could make a shortcut that opens it up in read-only always, so you would never have to worry about write conflicts.

Right. What's easier on your end?

Is there a way for me to open it right now and read-only so we can look at it for more than 30 seconds without affecting anything?

Yeah, see here.

Sorry, JD, if we open it up because it's on the network, if we open it, anybody who currently clicks save, it won't get uploaded because it makes sense. Yeah, so I don't want to— every time I do that for more than like 60 seconds, I end up —by one quote.

So there's a— I wonder how that would work in practice. We can protect the workbook to always open it in read-only where you have to manually go undo it. And as long as I went and did the macros properly, we could theoretically do that because all I have to do is unprotect and then reprotect the sheet. If you wanted something to have opened constantly here.

Yeah, I mean, I don't care. This is more for JD because you built me the other reports that I already send JD, which gives me all the info I need.

Well, then what I would do if we were going to go the VPN route, that would— I would just have something make a copy of that file on a regular basis. And he always only opens the copy. That way, if he has that open, he just doesn't get his updated one if there's a sync and he'll just get it on the next one. And if we have one running every 10 minutes, I don't think that would be too much of an issue, but I'd leave that to your decision.

Yeah, no, he would never be more than 10 minutes behind at that point. Exactly.

So that would be— that would work, um, for technically speaking. I don't know if it works for your requirements.

It sounds like it would. I, um, so I would have access to a file that updated every 10 minutes with the raw data in read-only mode, but it's not on your guys' main, like, server or whatever. So it wouldn't— it wouldn't hold up what you guys are doing, basically. It wouldn't prevent you guys from being able to update your side because I don't have it open. This is a copy of it.

Yeah, half yes, half no. Um, it would be on our server still, but yes to the other portion of not affecting anything. Yeah, okay.

So I would have to have a VPN Couldn't you just at that point, once the copy is created, just email it to him every 10 minutes?

Yes. The— yes.

Could do it that way. You never even have to— you don't have to VPN. Yeah.

Do you want to do that?

That would be the easiest if I just had a scheduled email coming through.

Then we'll just change the schedule that we have there and we'll send over the raw data instead. Yeah.

Okay. That should work. So if it's the raw data with a created date timestamp, or not a timestamp, sorry, a created date stamp. That should give me everything I need.

Oh crud, yes, let me mark that here.

Make schedule pass. How do I open and read only right now just so I can look at it?

Is there a way to do that? You have to, yeah, you have to protect the sheet. You go to File, Info, and then Protect Workbook, and then there's Always Open as Read Only.

You're gonna have to open it first.

Yeah, you have to open it and then you go to the File menu and then there's an Info button.

I just didn't remember if you had the creation date on there or not. If not, we just need to extract that.

The creation date, I think, is something that we added most recently. Okay. Yeah.

Okay, so I think we have a plan. Yeah. Devin's going to confirm we have the creation date in the master, master master, not the one I'm sending you, which is a mini master. Yep. And then he's going to create a copy and email it to you every 10 minutes.

That'd be, yeah, that sounds perfect. And then, uh, just for, uh, my own edification, how much information will be sent? Like how does that, is that everything going back for the history of the system or is it going to like cut off by a certain date?

No, it's, uh, it does include some 2025 in there. But the, the quotes that are reporting to this didn't— we didn't build this until late 2025. Okay. It's really basically just this year. Right now the, the file sits at 1,638 kilobytes. Okay. So by the end of this year, that'll probably be a couple of gigs. Well, actually, no, we're a couple months in. Yeah.

It'll be maybe like 10, 11 megabytes by the end of the year, something like that. Yeah. But that's not unwieldy. Okay.

All right.

That sounds good. Yeah, Devin, if you have any other questions or anything run into, like, anything you want to run by me, let me know. But this sounds like it's going to work.

Yeah, no, should be pretty straightforward. I'll get this going today. Perfect. All right.

I also do have another question while I have both of you. The website hosting for HDR, you said you should— did that get transferred over or are you still doing that? Devin?

I technically still have it. Okay.

Is there, um, because we want to build a landing page for the bathroom trailer division. Um, so I would probably need the ability to put that landing page on whatever is being hosted. Um, do we have analytics or anything for that page as well where we're tracking like, um, like Google, um, the Google Analytics, the I forget what it's called, like GA4. Yeah, GA4 and that kind of stuff. No, no.

Uh, we have the native, like, uh, there's stuff through InMotion that has like the regular metrics. We can get the raw requests that come through, but nothing that has any sort of like user agent tracking or verifying that it's not any automated traffic or that kind of thing.

Okay. I might give you a, um, like a, uh, something just to put on the website so we can track, cuz I want to use the, I want to try to track the ROI. If we do end up putting some money into Google ad spend for the bathroom trailers. So I'd be curious to see if anything flows through to HDR as well, um, just so we can do some customer acquisition cost analysis.

Who currently handles this for us, like on the Avon side? Is that Jay or somebody else?

Jay handles it.

Um, so would this not be easier if we just turn the— give the credentials of the hosting over to Jay and let him either keep it on InMotion and do his thing or move it over to wherever you guys want?

I'm happy to bring it over to Jay, just so Jay has both of those. But we already have all that set up. It's all set up for Avon right now. So we'd have to set the GA4 stuff for HDR and HSS. But do you want to transfer it over to Jay so we just have one centralized piece for all the websites?

Yeah, he's already doing it and he knows what you want and everything. And I have confidence that Devin can do it. It's just not really his scope of— he always does it for me as my nephew. Like, of course I'll take care of it for you. It's not really part of his job.

Okay, then yeah, if it's not something that you're actively doing, why don't we transfer it so it can get off your plate so you don't have to worry about it? Sounds good.

Okay.

Um, I'll put you in touch with Jay. Have you— you talked to Jay in the past, I'm sure. Oh yeah.

Yeah.

Okay. I'll put you guys on an email and ask if he can set up getting the transfer, um, for the hosting of the HDR website. Sounds good. Cool. Um, okay. That was what I had. You guys have anything else?

Yeah, sweet.

I think I'm okay. Great.

Thank you, Devin.

Great to meet you.

Talk to you guys later. It seems okay.