For those working in eCommerce and digital, enhancing omnichannel functionality to your physical store/online offering is seen as a natural progression. Breaking barriers to interact with customers and to better customer experience is the ultimate unified retail goal both for pure-play and online/offline retailers.

There are digital transformations happening all around us in every business vertical. The biggest challenge is not identifying suitable technologies, it’s the internal battles – those less so technologically savvy gatekeepers in our organisations. Rejection of eCommerce and digital interaction is the first and probably the last nail in your business’ coffin. Complex data is driving the world’s most successful businesses. The more data points your business creates and measures, the more your business will thrive in the future.

Your eCommerce and digital teams should be pushing the data revolution within your business. Almost all interaction with your business online is measurable and feeding back to relevant internal offline teams is the olive branch digital teams need to succeed in this revolution.

Convincing Store Managers and Store Operations to run a data first store

The oldest-newest argument for store managers with the growth in online sales is: “our website is taking all of our store’s sales”. The shift certainly gives that impression on paper and there is some truth to the argument. Arguing does not solve how customers want to interact with our businesses. This shift is something your whole business must conform to or online and offline business will suffer. You need unity within your ranks. With the growth of online trade, the data says that physical stores work as mini-marketing hubs – influencing the online trade-in their vicinity. Here’s how we can create a common goal by measuring physical stores’ influence on online sales: Measuring Online customer value by proximity to their local stores:

Data you will need:
Customer Sales Data
  • Unique customer identifier (Customer number)
  • Number of orders
  • Customer Value
  • Shipping Address
Store Data
  • Store Name
  • Store Address
  1. Convert shipping address and store addresses to coordinates in Google Sheets using the add-on ‘Geocode by Awesome Table’.
  2. Measure the distance between customer and all stores. How your sheet should look…
distance between customer and all stores

Formula to measure distance (KM): =2*6371000*ASIN(SQRT((SIN((*(3.14159/180)-*(3.14159/180))/2))^2+COS(*(3.14159/180))*COS(*(3.14159/180))*SIN(((*(3.14159/180)-*(3.14159/180))/2))^2))/1000 *Ensure you have added ‘$’ to relevant cell reference: for customer: Before cell reference; for customer: middle of cell reference.

3. Once you apply the formula to all customers: Identify the shortest distance between stores and customers using the ‘Min’ formula.
4. Use the shortest distance as a key to identify the name of the customer’s closest store. Use Index and match formulas. 
5. Now that you have matched customers to their nearest store, create groups based on distance and weight the groups by influence. Use the ‘if’ formula to apply the group labels to relevant distances:

=IF(<Shortest Distance><=10,”A:0-10KM”,IF(AND(<Shortest Distance>>10,<Shortest Distance><=20),”B:10-20KM”,IF(AND(<Shortest Distance>>20,<Shortest Distance><=30),”C:20-30KM”,IF(AND(<Shortest Distance>>30,<Shortest Distance><=40),”D:30-40KM”,”E:40KM+”))))

6. Pivot table the data on the right and begin reporting stores’ influence sales.


This is just one example of how geographically mapping your online sales can unite your online and offline teams. If anyone has found this useful, I can write another article on how the same data could help you predict where to open your next physical store or how it can be used to enhance your email marketing segmentation to drive more physical store visits.