How to Use Customer Insights in Square: Step-by-Step Tutorial

Introduction to Customer Insights in Square

Understanding who your best customers are is fundamental to growing your business. Every merchant asks the same critical questions: Who spends the most? Who visits most frequently? How can I encourage more repeat business? Square's own reports can answer part of this, and an export lets you answer the rest yourself.

This tutorial works from a transactions export and shows, with pandas, how to find your top customers, measure repeat business, estimate lifetime value and see where customers are. Every figure in the outputs comes from a synthetic data set generated for the tutorial, not from a real business, so yours will differ.

The same calculations apply to a cafe, a shop or a service business, as long as your payments carry a customer identifier.

Prerequisites and Data Requirements

• A Square account with permission to export transactions from the Dashboard
• A customer identifier on the payments. Customer-level analysis only works for payments tied to a customer (a customer profile, an email or a phone number). Payments without one cannot be counted per customer
• History. Repeat behavior needs time. With only a few weeks of data most customers have one visit, so the repeat rate says little yet
• Python with pandas, or a spreadsheet pivot table for the same totals

Step 1: Export Your Transactions and Load Them

Per Square's help article on viewing and searching transactions: "click Export to download transaction details as a CSV file in your Square Dashboard." That article does not list the columns of the export or say whether it includes customer information, so open your file and look for a customer column, a date and an amount. If there is no customer column, a transactions file cannot give per-customer figures; look in Square's help center for an export that includes customers. Rename the columns you find to the layout used below (customer_id, postal_code, date, amount).

The code builds a synthetic file in that layout: 400 customers and a 12-month window.

import numpy as np
import pandas as pd

# Synthetic transactions made up for this tutorial; they do not come from any business.
# One row per payment that carries a customer, over a 12-month window.
rng = np.random.default_rng(7)
n_customers = 400
zips = rng.choice(["94102", "94110", "94103", "94114"], size=n_customers, p=[0.4, 0.3, 0.2, 0.1])
visits = 1 + rng.poisson(rng.gamma(1.2, 1.0, size=n_customers))
rows = []
for cid in range(n_customers):
    for _ in range(visits[cid]):
        rows.append((f"C{cid:03d}", zips[cid], pd.Timestamp("2025-01-01") + pd.to_timedelta(int(rng.integers(0, 365)), unit="D"),
                     round(float(rng.gamma(4, 7)), 2)))
tx = pd.DataFrame(rows, columns=["customer_id", "postal_code", "date", "amount"])
print("Transactions:", len(tx), "| customers:", tx["customer_id"].nunique(), "| revenue:", round(tx["amount"].sum(), 2))

Output:

Transactions: 826 | customers: 400 | revenue: 23115.32

Step 2: How Many Repeat Customers Do I Have?

Repeat customers are those with two or more visits in the window. Count visits per customer, then summarize. The average visits per customer is total transactions divided by customers, so it follows from the same table.

per = tx.groupby("customer_id").agg(visits=("amount", "size"), spend=("amount", "sum"), postal_code=("postal_code", "first"))

bins = [0, 1, 2, 3, 6, np.inf]
labels = ["1 visit", "2 visits", "3 visits", "4-6 visits", "7+ visits"]
freq = pd.cut(per["visits"], bins=bins, labels=labels).value_counts().reindex(labels)
table = pd.DataFrame({"customers": freq, "pct": (100 * freq / len(per)).round(1)})
print(table.to_string())
print("Total customers:", len(per))
print("Repeat customer rate (2+ visits):", round(100 * (per["visits"] >= 2).mean(), 1), "%")
print("Average visits per customer:", round(per["visits"].mean(), 2), "(", len(tx), "/", len(per), ")")

Output:

customers   pct
visits                     
1 visit           192  48.0
2 visits          101  25.2
3 visits           60  15.0
4-6 visits         36   9.0
7+ visits          11   2.8
Total customers: 400
Repeat customer rate (2+ visits): 52.0 %
Average visits per customer: 2.06 ( 826 / 400 )

The five groups add up to the 400 customers. Customers with 2 or more visits are 101 + 60 + 36 + 11 = 208, which is 52.0%. The average of 2.06 visits is consistent with the table (the minimum implied by the groups is 192 + 202 + 180 + 144 + 77 = 795 visits, and the actual total is 826).

Two limits: the repeat rate depends on the window you choose (a longer window gives customers more chances to return), so compare rates only for equal windows. And it counts only customers Square could identify.

For retention, define the cohort and the period explicitly: of the customers who visited in one period, the share who visited again in the next. For more on customer behavior methods see AI-first data analysis pipelines.

Step 3: Who Are the Top Spenders, and What Is a Customer Worth?

Sort customers by total spend and measure how much of the revenue the top 20% bring in. Measure it, do not assume a rule such as 80/20 holds: in this example the top 20% account for well under half of revenue.

For lifetime value, the code multiplies revenue per customer per year by a lifespan. The window is exactly 12 months, so revenue per customer in the window is already per year. The lifespan (3 years here) is an assumption you choose; this data cannot measure it. The result is revenue, not profit: multiply by your gross margin before comparing it with what you spend to acquire a customer.

per = per.sort_values("spend", ascending=False)
top_n = int(round(0.2 * len(per)))
top_share = 100 * per["spend"].head(top_n).sum() / per["spend"].sum()
print(f"Top 20% of customers ({top_n}) account for {top_share:.1f}% of revenue in this window")

avg_purchase = tx["amount"].mean()
visits_per_year = len(tx) / len(per)          # the window is exactly 12 months
annual_value = avg_purchase * visits_per_year  # = revenue per customer per year
assumed_lifespan_years = 3                     # an assumption you choose, not something measured here
print(f"Average purchase: {avg_purchase:.2f} | visits per customer per year: {visits_per_year:.2f}")
print(f"Revenue per customer per year: {annual_value:.2f}")
print(f"Revenue-based lifetime value if customers stay {assumed_lifespan_years} years: {annual_value * assumed_lifespan_years:.2f}")

Output:

Top 20% of customers (80) account for 44.8% of revenue in this window
Average purchase: 27.98 | visits per customer per year: 2.06
Revenue per customer per year: 57.79
Revenue-based lifetime value if customers stay 3 years: 173.36

Revenue per customer per year is average purchase times visits per year (27.98 x 2.065 is about 57.8, using the unrounded 2.065 visits), and the lifetime figure is that times the assumed 3 years. Use one VIP threshold throughout your own work (here the top 20%) and say what it is.

Step 4: Where Are My Customers Located?

If your file has a postal code per customer, group by it. The table shows customers, spend per customer and each postal code's share of revenue. The distance from your store is not in the file; add it from a map if you need it.

by_zip = per.groupby("postal_code").agg(customers=("spend", "size"), total_spend=("spend", "sum"))
by_zip["avg_spend"] = by_zip["total_spend"] / by_zip["customers"]
by_zip["revenue_pct"] = 100 * by_zip["total_spend"] / by_zip["total_spend"].sum()
print(by_zip.sort_values("total_spend", ascending=False).round(1).to_string())

Output:

customers  total_spend  avg_spend  revenue_pct
postal_code                                                
94102              162       8437.5       52.1         36.5
94110              111       6741.6       60.7         29.2
94103               88       5483.0       62.3         23.7
94114               39       2453.3       62.9         10.6

The four shares add up to 100%. In this example 94102 has the most customers (162) but the lowest spend per customer (52.1), which is why a count of customers and a share of revenue can rank areas differently. Small postal codes with few customers give noisy averages.

Interpreting Your Customer Insights Results

Customer Segments

Segments are rules you define from the tables above. One possible set, with thresholds you should adjust to your own distribution:

• VIP customers: top 20% by spend in the window
• Loyal customers: 4 or more visits
• Promising customers: 2 to 3 visits
• One-time customers: 1 visit
• Lapsed customers: no visit in the last 60 days

A customer can fall in more than one segment (a VIP is usually also loyal), so decide the order in which the rules apply before counting.

Metrics to Track

• Repeat customer rate for a fixed window
• Revenue per customer per year, and the same figure after margin
• Customer acquisition cost: marketing spend per new customer
• Lifetime value compared with acquisition cost: use margin-based value and your own cost of acquisition; there is no ratio that is right for every business
• Average days between visits
• Churn: the share of customers who stop buying, which needs a stated definition of "stopped"

Segment differences you see in one period are descriptions. To learn whether a change, such as a loyalty offer, works, compare against a control group; see A/B testing and statistical significance.

Run It on Your Own Square Export

Export your transactions from Square as a CSV and upload it at MCP Analytics. The analysis runs a customer frequency and spend breakdown on your file and shows the result with the method used. You choose the columns; nothing connects to your Square account.

Try it on your data

Common Issues and Solutions

Issue 1: Many Payments Have No Customer

Count them and report the share next to your results. If it is large, the customer figures describe only the identified customers, who may differ from the rest. Collecting an email or phone number at payment is the only way to identify more of them going forward.

Issue 2: The Same Person Appears Under Two Identifiers

Duplicate profiles lower the repeat rate and inflate the customer count. Merge them on a shared email or phone number before counting.

Issue 3: Totals Do Not Match Square's Reports

• Check the date range and the time zone.
• Check how refunds, voids and tips are treated in your file and in the report you compare with.
• Check that the same locations are included.

Issue 4: Lifetime Value Looks Unreliable

It rests on the lifespan you assume and on a short window. Try several lifespans and report the range, and recalculate when you have more history.

Conclusion

A transactions export with a customer identifier gives you visit counts, repeat rate, concentration, a revenue-based value per customer and a location breakdown, each of which reconciles to the totals above. Treat thresholds and the lifespan as choices you state, and test changes instead of assuming they work.