Step-by-step guide: Calculating the geographic center to launch a last-mile delivery partnership campaign

Overview

Tapping into a partnership with last-mile delivery companies with micro fulfillment centers around the United States means enabling customers to get products in 60 minutes or less. With consumers more impatient than ever, it’s an opportunity to fulfill people’s need for instant gratification.

‍

TLDR; Cannascale took 35,000+ rows of unstructured data and turned it into 28 unique segments by cleaning the data, calculating the geographic center of each location, then calculating the mileage radius to include all the associated zip codes.

‍

Our client came to us with the desire to set up a new campaign which would allow us to target those contacts who had a micro fulfillment center in their area. We didn’t know exactly what to expect, but we got an unstructured data set with 35,000+ rows, that looked like this (except many more zip codes and location names):

‍

The Challenge: Unstructured Data and Inconsistent Location Information

The data set provided by the client was quite large and we were scratching our heads on how we go about making sense of it. First, we needed to understand our client’s Klaviyo implementation and what data we had access to in contact profiles. What we found was that for purchasers we had a full address including a 5-digit zip code, and for non-purchasers we only had city and state. The client wanted to be able to target everyone in those delivery zip codes, regardless of whether they had purchased. So, that meant our solution would need to account for that.

Within Klaviyo and its segmentation rules, we had the choice of segmenting based on a profile trait such as city/state or by proximity to a zip code. The city/state approach would be challenging since the zip codes didn’t just represent a single city, they were zip codes of smaller cities around a bigger city. So we decided we needed to create our segments using the proximity by zip code. But this presented the next challenge, which is how to determine the proximity (in miles or kilometers) around a zip code which would include all the zip codes we had per location name.

Once we knew what the solution needed to look like, we were able to begin tackling the data analysis portion of the project.

Of our 35,000+ rows, we had zip codes in a ZIP+4 format, then a Location Name. Initially, that format threw us off, but we we started by doing a simple split of the zip codes to get us a clean list of five-digit codes:

‍

‍

We then wanted to get an idea of the volume of unique data we were working with. After pivoting the zip codes we came up with around 800 unique zip codes. From there, we wanted to figure out how many locations we were working with. It came out to around 58, but it did appear like there was some overlap in locations (micro-fulfillment, so that makes sense), which meant we might be able to bundle some locations together.

‍

‍

There are formulas to be able to calculate the geographic center for each city cluster, so we began running our data through those formulas for each of the locations (tip: we used ChatGPT’s o1 model to run through our zip codes and do the math for us).

We’ll skip the complicated math behind all of this, but we’ll break down the basics behind how this works:

Finding the geographic center of an array of zip codes

Goal: Given an array of zip codes, identify the geographic center.

• Take the latitude and longitude for each zip code in the cluster.
• For each zip code’s latitude and longitude, convert to 3D Cartesian coordinates (using cos() and sin() functions, this maps each point onto a 3d sphere
• Calculate the mean of the X, Y, and Z values across all zip codes, which gives a central point in 3d space
• Convert the averages Cartesian coordinates back to geographic latitude and longitude
• Voila! Your geographic center!

Calculating the radius

Goal: Given an array of zip codes, and a known geographic center, calculate the radius that would encompass all the zip codes.

• Calculate the distance of each zip code from the geographic center
• The maximum distance among these was selected as the radius, this ensured that every zip code was within the calculated radius.

‍

Implementing in Klaviyo

Once we figured out the approach we ran through all the locations, which then allowed us to narrow down and create radius-based segments for each of the major locations for this campaign. 

‍

‍

Consideration with this approach

While this approach provides a high degree of accuracy, there are some limitations to be aware of:

• ‍Potential for Over-Inclusion: Since the radius is calculated using the maximum distance, some zip codes that fall within the radius might not actually be within the service delivery area.
• ‍Acceptable Margin of Error: For this project, the margin of error was considered acceptable because the goal was to broadly promote the last-mile delivery partnership.

‍Did we do the actual math? Hell no! Look at that equation below…use the resources available to you, ChatGPT is your best friend.

a = sin²(Δφ/2) + cos(φ₁) * cos(φ₂) * sin²(Δλ/2) c = 2 * atan2(√a, √(1−a)) d = R * c

Where:

• Δφ is the difference in latitude
• Δλ is the difference in longitude
• φ₁ and φ₂ are the latitudes of the geographic center and the zip code
• R is the Earth’s radius (~3,959 miles or 6,371 km)
• d is the great-circle distance between the geographic center and the zip code

‍