Tableau Viz: Customers within N miles radius

This blog post will help you to know how many customers live within N miles of any location. If your data contains a zip code, latitude, longitude and email or customer ID information you will easily answer this question using such a brilliant tool as Tableau.

Calculated Fields

First, you will need to create two parameters:
a) Distance Selector
b) Zip Code Selector

• Distance Selector:
Type – Integer
Values – All

• Zip Code Selector:
Type – String
Values – Add from Field > zip code

To link the newly created parameters with the existing data you will need to create some calculated fields:
a) Latitude & Longitude of a Selected Location
b) Distance
c) Filter for a Selected Distance
d) City Title
e) Number of Customers

• Latitude & Longitude of a Selected Location

Let’s create a new calculated field Selected Latitude:

WINDOW_AVG(ATTR(IF [Zipcode] = [Zip Code Selector] THEN [latitude] END))

Note that it is necessary to repeat this exercise but replacing longitude with latitude in the formula and calling it Selected Longitude:

WINDOW_AVG(ATTR(IF [Zipcode] = [Zip Code Selector] THEN [longitude] END))

• Distance

Let’s create a new calculated field Distance:

3959 * ACOS(COS((PI()/180) * AVG([latitude])) * COS((PI()/180) * AVG([longitude])) * COS((PI()/180) * [Selected Latitude]) * COS((PI()/180) * [Selected Longitude]) + COS((PI()/180) * AVG([latitude])) * SIN((PI()/180) * AVG([longitude])) * COS((PI()/180) * [Selected Latitude]) * SIN((PI()/180) * [Selected Longitude]) + SIN((PI()/180) * AVG([latitude])) * SIN((PI()/180) * [Selected Latitude]) )

• Filter for a Selected Distance

The next step is to create a filter, called Distance Filter, which will be filtering only zip codes within a selected distance:

IF [Distance] <= [Distance Selector] THEN “Show” ELSE “Hide” END

• City Title

Then you need to create a calculated field which will highlight the city of a selected zip code on the map, calling it City Title:

Attr(IF [Zipcode] = [Zip Code Selector] THEN [City] END)

• Number of Customers

The final step is to create a formula, which will be displaying the number of customers. You need to do this exercise for Size on the view:

COUNTD([Customer ID])

Tableau View

When the necessary fields have been created you can now start dragging them to the view:
• [latitude] to Rows
• [longitude] to Columns
• [Zipcode] to Detail on the Marks card
• [Selected Latitude] to Detail
• [Selected Longitude] to Detail
• [Number of customers] to Size
• [Distance] to Color
• [City Title] to Lable

On the Color mark display the drop-down menu for Distance and select Edit Table Calculation:

Step 1
In the Table Calculation dialog box complete the following steps:
1. For Calculated Field, select Selected Latitude.
2. For Compute using, select Advanced.
Step 2
In the Advanced dialog box complete the following steps:
1. In the Advanced dialog box select the first field under Partitioning and click the right-arrow to move it to Addressing. Repeat this for each subsequent field until all of them appear under Addressing in their original order from top to bottom: zip code, latitude, longitude.
2. Under Sort select Field, select Number of customers for the field and then choose Ascending.

3. Click OK to return to the Table Calculation dialog box.
Step 3
With the Table Calculation dialog box still open, select Selected Longitude for Field and repeat the steps under Step 1 and Step 2 to configure the advanced settings for this field. When you complete the advanced settings and return to the Table Calculation dialog box click OK to return to the view.

The next action is to filter unnecessary data from the map:

1. From the Measures pane, drag Distance Filter to Filters.
2. In the Filters dialog box that appears, select Show and then click OK.

On the Filters shelf display the drop-down menu for Distance Filter and select Edit Table Calculation. Repeat the steps under Step 1 and Step 2 earlier in this section to configure the advanced settings for the Selected Latitude and Selected Longitude fields. After you are finished and click OK on the Table Calculation dialog box, the Filters dialog box appears again. Select Show and then click OK.

Last preparation

I would recommend that you add one more tab that will display the percentage of all customers who live within N miles of a selected zip code. It is necessary to create several calculated fields:

a) Percent
b) First Filter

• Percent

The formula below calculates the number of customers within a selected radius divided by the number of customers with valid distance (i.e. all available customers):

WINDOW_SUM(IF [Distance] < [Distance Selector] THEN 1 ELSE 0 END] / WINDOW_SUM(IF [Distance] > 0 THEN 1 ELSE 0 END)

• First Filter

This field will filter only the first record from the list, therefore showing only the percentage of customers who live within N miles of a selected zip code:

First() == 0

When the necessary fields have been created you can start dragging them to the view:
• [Customer ID] to Rows
• [First Filter] to Filter
• [Percent] to Lable

On the Label mark display the drop-down menu for Percent and select Edit Table Calculation:

Step 1
In the Table Calculation dialog box, complete the following steps:
a) For Calculated Field, select Percent.
b) For Compute using, select Advanced.
Step 2
In the Advanced dialog box, complete the following steps:
a) In the Advanced dialog box select customer ID from the first field under Partitioning and click the right-arrow to move it to Addressing.
b) Under Sort select Field, select Number of customers for the field and then choose Ascending.

c) Click OK to return to the Table Calculation dialog box.

Step 3
With the Table Calculation dialog box still open, choose Selected Longitude for Field, repeat this exercise for Selected Latitude and follow the steps under Step 1 and Step 2 to configure the advanced settings for this field. When you complete the advanced settings and return to the Table Calculation dialog box click OK to return to the view.

The next action is to filter unnecessary data on the table:

In the Filter shelf right click on First Filter and select Edit Table Calculation. In the Table Calculation dialog box select compute using Advance and repeat the Step 2 as earlier in this section to configure the advanced settings. After you are finished and click OK on the Table Calculation dialog box, the Filters dialog box appears again. Select True and then click OK.

You can hide the customer ID field to display only the percentage. Drag this sheet to the view. The final dashboard should look as the image below:

You are able to select any zip code as well as radius within it, and look where your customers are located. In this case, the size of the dots indicates the number of customers who live in this particular area. The percentage figure at the top of the image displays the percentage of all customers living within N miles of a selected zip code.

You are done, congrats!

Eugene

Posted under Tableau, Vizualization by Eugene Shiman on 27th April, 2015

3 Comments
Send to a friend

3 comments + Add a comment

Andrey
April 27, 2015 at 1:19 pm

GJ, Eugene! It seems that this approach can be used to calc % of customers, that leave from client’s retail store and etc..

Greg Sobiech
April 27, 2015 at 1:40 pm

Eugene, can one use this approach to for instance identify customers who live x miles from a store – and then report on how these customers respond to marketing messages that are of the ‘drive to store’ type?

Eugene
April 28, 2015 at 8:40 am

Greg, absolutely! In this case we just need to have one more source that will contain customer ID/email address field as well as “customer responce” data and connect it to the initial source using customer ID/email address as a key.

Have your say

Thanks for your contribution.

You can use Gravatar to upload an avatar that will appear next to your comment.

Please be polite and respectful to others.

<