How to Find # Customers with X Miles

Data_Devon

Hi Team,

I'm hoping to find the number of customers within a given radius of a given business location. For example, how can I find how many of our customers are within 50 miles? What about within 100 miles? In order to capture ##% of our customers, how many miles do we need to travel?

These are the types of questions I'm trying to answer. All the research I've done suggests that Domo isn't the right tool for this job - but I beg to differ :)

Open to any ideas!

brycec

I like your attitude!

I found this article on how to calculate distance in Excel: How to Calculate Distance Between Two Addresses in Excel. Beast Mode SQL doesn't support the necessary functions, but Magic does. To calculate straight line distance in miles, you'd just use this formula in Magic:

3959 * ACOS(COS(RADIANS(90 - `Latitude1`)) * COS(RADIANS(90 - `Latitude2`)) + SIN(RADIANS(90 - `Latitude1`)) * SIN(RADIANS(90 - `Latitude2`)) * COS(RADIANS(`Longitude1` - `Longitude2`)))

If you're looking for driving distance, that same article talks about using the Google Maps API to do so. Implementing it would certainly be easier with the "coming soon" feature of API requests in the Python tile, but you should still be able to achieve it today with Jupyter Workspaces or Code Engine.

MarkSnodgrass

I have a video that walks you through how to calculate distances between cities in Magic ETL. Once you calculate the distance, you can then use a filter to show which cities are within a certain range.

https://youtu.be/xzi-fhEmN-Y

brycec

I like your attitude!

I found this article on how to calculate distance in Excel: How to Calculate Distance Between Two Addresses in Excel. Beast Mode SQL doesn't support the necessary functions, but Magic does. To calculate straight line distance in miles, you'd just use this formula in Magic:

3959 * ACOS(COS(RADIANS(90 - `Latitude1`)) * COS(RADIANS(90 - `Latitude2`)) + SIN(RADIANS(90 - `Latitude1`)) * SIN(RADIANS(90 - `Latitude2`)) * COS(RADIANS(`Longitude1` - `Longitude2`)))

If you're looking for driving distance, that same article talks about using the Google Maps API to do so. Implementing it would certainly be easier with the "coming soon" feature of API requests in the Python tile, but you should still be able to achieve it today with Jupyter Workspaces or Code Engine.

MarkSnodgrass

I have a video that walks you through how to calculate distances between cities in Magic ETL. Once you calculate the distance, you can then use a filter to show which cities are within a certain range.

https://youtu.be/xzi-fhEmN-Y

Data_Devon

Thank you both! Mark - I love the video. That was super helpful to see you walk through it.

I think the Distance() formula will be my best bet and can get me close. Thank you!

Data_Devon

@MarkSnodgrass your video was super helpful. Thanks to your help, I've got a follow up question :)

The next question is:

What distance would capture 90% of our clients? At X distance, Y% of our clients are accounted for?

This is unrelated to the original question but wanted to open back up for any insight.