The Square Data Model
Authenticating
Pulling Transaction Data
Spend by Customer Group
Issues with the APIsCannot Request Specific Fields
Cannot Update Customer Groups Programmatically
Customer ID Not On Transactions
The Square Data Model
Whether you own your own business or consult for a business using Square to capture payment data, Square can offer some amazing opportunities to gain insights by leveraging their Connect v1 &amp; v2 APIs. The Square data backend operates much like a CRM system that holds information about transactions that customers make when purchasing items offered at a location. Naturally, there are API endpoints for each of the object types italicized above (Locations, Customers, Transactions, and Items) and many more endpoints for administrative tasks (employees, roles, timecards, refunds, etc.).
Authenticating
While working with Square data I decided to develop an R package squareupr that makes it easier to retrieve Square data from R so that you can focus on the analysis. After installing the squareupr package you must authenticate by supplying a personal access token (PAT) or using an OAuth 2.0 flow. You can find your PAT by logging into the Square Dashboard -__ Apps -__ My Apps. Create an app or click “Manage App” if you’ve already created one and there you should see your personal access token:
# The squareupr package is not yet available on CRAN so you must install from GitHub
# install.packages(&quot;devtools&quot;)
devtools::install_github(&quot;StevenMMortimer/squareupr&quot;)
library(tidyverse)
library(squareupr)
# authenticate using your Personal Access Token (PAT)
sq_auth(personal_access_token = &quot;sq-Th1s1sMyPers0nalAcessT0ken&quot;)
The package also offers OAuth 2.0 authentication. More information is available here.
Pulling Transaction Data
As mentioned above there are endpoints for every major type of data stored by Square. The API documentation does a very good job at laying out how requests should be made to each of those endpoints. One thing to note is that it is important to first pull down the location details for your business because the location is often required when searching for things like transactions and items.
our_locations # A tibble: 5 x 7
#__ id name address timezone capabilities status created_at
#__ #__ 1 46FYN9N9RQS54 {HIDDEN} 2 DRDCJ2X8E2PMV {HIDDEN} 3 8T1TYXE840S00 {HIDDEN} 4 1AWPRVVVFWGQF {HIDDEN} 5 50X1GNAWEC8V0 {HIDDEN} id location_id created_at tenders product client_id #__ #__ 1 bUjFGVjBvN… DRDCJ2X8E2P… 2018-05-12T0… 2 5PZP31N5Zs… DRDCJ2X8E2P… 2018-05-11T2… 3 BTrGydD6he… DRDCJ2X8E2P… 2018-05-11T2… 4 XsqOAHl68z… DRDCJ2X8E2P… 2018-05-11T2… 5 vmLRzrwByS… DRDCJ2X8E2P… 2018-05-11T2… 6 pTbzQApZW7… DRDCJ2X8E2P… 2018-05-11T2… 7 lnE20zklpP… DRDCJ2X8E2P… 2018-05-11T2… 8 DSumrqQW0L… DRDCJ2X8E2P… 2018-05-11T2… 9 tPwFXetIwe… DRDCJ2X8E2P… 2018-05-11T2… 10 bqUuFrzH71… DRDCJ2X8E2P… 2018-05-11T2… # ... with 235 more rows
At first glance there does not appear to be very much detail on the transaction record. However, the tender field represents a method of payment used in a Square transaction so it contains information regarding the amount of money paid in total, in Square fees, and tip. The tender field even contains information regarding the customer_id and credit card information. In the following I will loop through the transactions in April 2018 and determine the total spend for each customer in the dataset. Note: The call to sq_list_transactions may take a couple minutes to complete if you are pulling thousands of transactions.
april_transactions % I get the data from each transaction stacked into a single tbl_df that’s ready to analyze.
# create a function that will extract out just the customer id and money spent
extract_cust_info_func %
# pull out just the information we want from each transaction
map_df(extract_cust_info_func) %__%
group_by(customer_id) %__%
summarize(total_spend = sum(money_spent, na.rm=TRUE))
april_customer_spend
#__ # A tibble: 208 x 2
#__ customer_id total_spend
#__ #__ 1 064HFDQG0N52AHDBSG00C1BAC8 1700
#__ 2 07VNWH1V4S6W4W2EJ4AN7SEJNR 0
#__ 3 08M453QNJ97BCT97SM09TN7QK4 0
#__ 4 08XE43X8FS0MPX8P2W4N0DEQY0 350
#__ 5 0CZ78CVRW12V7AZET8S3S82GGW 675
#__ 6 0G1J81148H42GGMTMQKRWSJHGC 0
#__ 7 0V1Y1BX23WYRK889ERVBE2T0KM 900
#__ 8 13HHFBFZTD33RX0RSJNAZQKV5M 0
#__ 9 1BFCHB9MK91GQ39HTD7QK6R7ZR 0
#__ 10 1DR0AK5GKX57H9ER9SG1JF01P0 2900
#__ # ... with 198 more rows
The amounts in april_customer_spend may seem large, but the Square APIs return money as integers that represent whole cents. If you divide by 100, then you will have the money amounts in dollars.
Spend by Customer Group
Square has this concept of “groups” that customers belong to. These groups can be fashioned to do marketing campaigns complete with email blasts. In our analysis let’s further determine which groups these customers belong to. The Square API has an endpoint to retrieve one customer at a time; however, with large lists you may get subjected to rate limiting. Rate limiting is errors on your requests because too many are coming from the same application or access token. I would recommend pulling down the entire list of customers with sq_list_customers() and then matching them up in R.
cust_groups %
select(id, groups) %__%
sq_extract_cust_groups() %__%
# filter to only the groups designated automatically by Square
filter(grepl(&quot;^CQ689YH4KCJMY&quot;, groups.id))
cust_groups
#__ # A tibble: 13,444 x 3
#__ id groups.id groups.name #__ #__ 1 M1RBDFRK7S1Q1EP6EZFJFV3CBW CQ689YH4KCJMY.LOYALTY_ALL Loyalty Participa…
#__ 2 58MK9F1HQ5447D1QZDX60NHTP4 CQ689YH4KCJMY.CHURN_RISK Lapsed #__ 3 58MK9F1HQ5447D1QZDX60NHTP4 CQ689YH4KCJMY.REACHABLE Reachable #__ 4 MBSJA4QV4WX6N2XV8WV9VJJTG8 CQ689YH4KCJMY.LOYALTY_ALL Loyalty Participa…
#__ 5 MBSJA4QV4WX6N2XV8WV9VJJTG8 CQ689YH4KCJMY.REACHABLE Reachable #__ 6 ZCBZJ234217KTV812WX4DP2404 CQ689YH4KCJMY.REACHABLE Reachable #__ 7 FKEMR8KZCN3BH98RV78PKHKQ1R CQ689YH4KCJMY.LOYALTY_ALL Loyalty Participa…
#__ 8 FKEMR8KZCN3BH98RV78PKHKQ1R CQ689YH4KCJMY.LOYAL Regulars #__ 9 78VMJPJNK959AHH0ZQPXDXEG3C CQ689YH4KCJMY.LOYALTY_ALL Loyalty Participa…
#__ 10 QASM1G54VX0QN2S15YS6KHEFCC CQ689YH4KCJMY.LOYAL Regulars #__ # ... with 13,434 more rows
Now that we know the customer memberships, let’s join back with the transaction data to determine the average total spend across the different membership groups.
cust_groups %__% # bring in the spend data
inner_join(., april_customer_spend, by=c('id'='customer_id')) %__%
# group by the customer groups to find average spend per group
group_by(groups.name) %__%
summarize(avg_spend = mean(total_spend / 100, na.rm=TRUE))
#__ # A tibble: 6 x 2
#__ groups.name avg_spend
#__ #__ 1 Cards on File 569. #__ 2 Lapsed 20.3
#__ 3 Loyalty Participants 13.5
#__ 4 Nearing a Loyalty Reward 15.3
#__ 5 Reachable 51.0
#__ 6 Regulars 27.4
Issues with the APIs
What is great about this analysis is that we can use the Square APIs to quickly and reliably pull down transaction data, match it to customer information and see how certain customer groupings or campaigns are performing. However, I did find some quirks and drawbacks that I wish the Square team would consider:
Cannot Request Specific Fields
First, it would be nice to implement, as part of the query parameters, the ability to only return certain fields (e.g. the id and groups fields from the Customer object). This would help for two reasons: 1) It would improve the speed since only the required data would be passed across and 2) For analysts who do not want to deal with personally identifiable information (even in RAM) you would not be forced to pull information like phone number, email, names, and even credit card information.
Cannot Update Customer Groups Programmatically
Second, the API will not allow you to update the customer groups programmatically. This means that you are stuck using the dashboard to create and assign customers to the groups you want to analyze. It is a drag when you want to create a reproducible research workflow to assign customer groups but the API hinders your ability to do so.
Customer ID Not On Transactions
Finally, it appears that the customer ID associated to a transaction is not reliably captured on every transaction’s tender object. This means that you cannot get a complete picture of all the transactions. I believe part of this is an issue with the merchant capturing the customer information at the point of sale, but also partly Square’s fault since they seem to be able to piece everything together in their dashboard. However, this is not the case with the APIs. Overall, the Square APIs are a rich data resource for helping run a business and they should only get better with time as development progresses.