# Excel PowerQuery for "Surge Chaser" Local Surge History



## AVLien (Mar 4, 2017)

Hi all,

I have created a spreadsheet-complete with data queries that digest Surge Chaser CSV output files-for anybody who wants to use them. Surge Chaser only recently began offering an "Export to Excel" functionality. I was (originally) trying to parse the raw Android notification logs, so big-ups to Michael Siedlecki, the developer of Surge Chaser for saving me a pretty big headache!

This Excel Spreadsheet, the accompanying CSV files, & the queries contained therein will give you an idea of how to adapt this to your locale. I am located in Asheville, NC, so (obviously) the data will not be pertinent to anyone from outside of Asheville. All the same, I wanted to give an example of how the queries digest the raw data.

I will append further instructions to this post when I am able. For now, you're on your own (apart from the image instructions in the .7z file). At the very least, you will need to open the queries & correct the file paths. Excel does not (easily) allow for relative file paths, so the simple fact that you will be unzipping it to a different folder structure than I was using it in will break the queries. One *big hint* I will give you for this is: _*LEAVE THE "DAYS" QUERIES ALONE! ONLY EDIT THE "AREAS" QUERIES (unless you really know what you are doing) *_because the "Days" tables are aggregated from the "Areas".

Hope this will help some people! The Surge multiplier column gets darker the higher the surge (much like the heat-map in the Partner App), & the time column fills as blue on yellow for daytime (8 a.m.-8 p.m.) & yellow on blue for nighttime (8 p.m.-8 a.m.).

I am not great with the charting features in Excel, so if someone who possesses that knowledge wanted to contribute to this little project, it would be quite helpful. I will at some point, but not likely very soon. Also, I will try to port it over to Google Sheets soon as well.

Cheers!

-The AVLien

Link to Uber-SurgeChaser Spreadsheet v0.1


----------



## SurgePredictor (May 17, 2017)

Awesomeness!
Thank you, thank you. I'm on the same wave length though my approach is different, I knew I couldn't be the only driver/developer out there...I'm gonna PM you.


----------



## naptownlux (Dec 1, 2016)

I suppose I'll stick my head out from the shadows instead of just lurking on these type of threads. Heavily into the analytics side here. Above average excel knowledge, but wouldn't call it anywhere near mastery. Been trying to self-educate myself on developing, studying xCode right now for a driving tool idea that I have. Still in very beginning learning stages of the process though. I have loads of relevant data and large sample sizes of trip data from certain scenarios/locations/times/etc. 7 months (and counting) of SurgeChaser data for ~40-50 pins. 

Happy to network and discuss experiences/analysis. Open to collaboration also. PMs welcomed!


----------



## SurgePredictor (May 17, 2017)

naptownlux said:


> I suppose I'll stick my head out from the shadows instead of just lurking on these type of threads. Heavily into the analytics side here. Above average excel knowledge, but wouldn't call it anywhere near mastery. Been trying to self-educate myself on developing, studying xCode right now for a driving tool idea that I have. Still in very beginning learning stages of the process though. I have loads of relevant data and large sample sizes of trip data from certain scenarios/locations/times/etc. 7 months (and counting) of SurgeChaser data for ~40-50 pins.
> 
> Happy to network and discuss experiences/analysis. Open to collaboration also. PMs welcomed!


Awesome! Glad to see there are other devs, even up and coming ones, that are driving. I've only been doing Uber for two months now but I'm a veteran front end web developer. Sending you a PM, would love to connect and figure out how to maximize $$$ with our other skills.


----------



## Erica Fresquez (Jun 29, 2017)

This is great! I'm new to rideshare, but love excel! Just learned how to export my surge data into excel but was looking to twerk it a bit .. is there a chance you could email me your document? I tried opening it up but it's giving me an error.

I can private mssg you my email if that's possible! Thanks in advance!


----------



## Thisthisguyguy (Jun 22, 2017)

Is this feature only available on android?

I can't figure out how to do the trend analysis on iphone.


----------



## UberGeo (Jan 16, 2017)

On the export, can you export more than one checkpoint at a time.


----------



## aflexdriver (Jul 15, 2016)

Very interesting! I was looking for something similar then I found this topic. I've downloaded the spreadsheet and started playing with the data but I couldn't find how to export my SurgeChaser data to csv - I tried both android and iPhone apps but couldn't find any option to export data besides the "Export Checkpoints" option. Does anybody know how to get that CSV data?

Edit - SOLVED: It's necessary to purchase at least one month to get access to the export functions.


----------



## aflexdriver (Jul 15, 2016)

I played a little with AVLien data and could find some trends. There's one screenshot of a chart that I made:








It's filtered for weekdays and shows the count of how many times the surge appeared.

Conclusion: "Home" seems to be a pretty busy area with a lot of commuters and drunk people.


----------



## surlywynch (Jun 22, 2017)

Thanks for this info. Are you combining the Lyft PT chaser too?


----------



## aflexdriver (Jul 15, 2016)

surlywynch said:


> Thanks for this info. Are you combining the Lyft PT chaser too?


You're probably asking for AVLien but I've noticed that at least in my area they have to be analyzed separeted - of course there are some convergences. I mean, each one has a different audience that can give different hotspots.


----------



## UberGeo (Jan 16, 2017)

aflexdriver said:


> I played a little with AVLien data and could find some trends. There's one screenshot of a chart that I made:
> 
> 
> 
> ...


Pick up home right of the bat. Is your data inclusive? If so, that is great.

Does this mean that home surged a lot of times during that time span or does this mean that home surged really high (1.x+) during that time span.

Got my answer... It is filtered on the number.


----------



## aflexdriver (Jul 15, 2016)

UberGeo said:


> Pick up home right of the bat. Is your data inclusive? If so, that is great.
> 
> Does this mean that home surged a lot of times during that time span or does this mean that home surged really high (1.x+) during that time span.
> 
> Got my answer... It is filtered on the number.


The way the data comes on the exported file is hard to say if there was a single peak or it was a surge that lasted for a long time bc it doesn't show the zeros - shows only when the surge appeared, not when it ends.
So if there was for example a surge of 2.2x for 2 minutes only in a single day, the average is gonna be higher than a spot that has 1.6x surge frequently. The only way to spot those false signals is checking the frequency of the surges.


----------



## Gingerpithicus_Frisii (Sep 2, 2017)

aflexdriver said:


> I played a little with AVLien data and could find some trends. There's one screenshot of a chart that I made:
> 
> 
> 
> ...


What app are you using to create this graph?


----------



## UberGeo (Jan 16, 2017)

Is there a way to format the data and pull into Excel. Hitting every link from my email is very time consuming. 

First I have to put the URL in my browser or click the link, the I have to say open, then save, then import into Excel.


----------



## aflexdriver (Jul 15, 2016)

Gingerpithicus_Frisii said:


> What app are you using to create this graph?


Excel



UberGeo said:


> Is there a way to format the data and pull into Excel. Hitting every link from my email is very time consuming.
> 
> First I have to put the URL in my browser or click the link, the I have to say open, then save, then import into Excel.


I do it once a week. I do have to hit each link but then I created a macro to import all the files into one.


----------

