Install squareupr Library

# This package is experimental and not available on CRAN. 
# You can install directly from GitHub using the devtools package
# install.packages("devtools")
devtools::install_github("StevenMMortimer/squareupr")

Authentication

First, load the squareupr package and authenticate. There are two ways to authenticate:

1. Personal Access Token
2. OAuth 2.0

library(tidyverse)
library(squareupr)

# Using Personal Access Token (PAT)
sq_auth(personal_access_token = "sq-Th1s1sMyPers0nalAcessT0ken")

# Using OAuth 2.0 authentication
sq_auth()

NOTE: Before using OAuth 2.0 authentication it is necessary that you set up your own Connected App in the Square dashboard. An App ID and App Secret will be provided, then you will be able to plug into your script like so:

options(squareupr.app_id = "sq0-99-thisisatest99connected33app22id")
options(squareupr.app_secret = "sq0-Th1s1sMyAppS3cr3t")
sq_auth()

OAuth 2.0 credentials will be cached locally in a file entitled ".httr-oauth-squareupr" in the current working directory so that a new token is not needed each session.

Determining Business Locations

The first step, after authenticating, is determining the unique IDs associated with each of your Square business locations. The API data is often organized or pulled by location, so it is important to know which ID is associated with each location you may have, even if you only have one location. In the squareupr packge you can use the sq_list_locations() function to retrieve the details of each location. The ID to take note of is listed in the ID column shown in the data below.

# list all locations
our_locations <- sq_list_locations()
our_locations$name <- "{HIDDEN}"
our_locations$phone_number <- "{HIDDEN}"
our_locations %>% 
  select(id, name, address, phone_number, status)
#> # A tibble: 5 x 5
#>   id            name     address    phone_number status
#>   <chr>         <chr>    <list>     <chr>        <chr> 
#> 1 46FYN9N9RQS54 {HIDDEN} <list [5]> {HIDDEN}     ACTIVE
#> 2 DRDCJ2X8E2PMV {HIDDEN} <list [6]> {HIDDEN}     ACTIVE
#> 3 8T1TYXE840S00 {HIDDEN} <list [5]> {HIDDEN}     ACTIVE
#> 4 1AWPRVVVFWGQF {HIDDEN} <list [5]> {HIDDEN}     ACTIVE
#> 5 50X1GNAWEC8V0 {HIDDEN} <list [6]> {HIDDEN}     ACTIVE

Pulling Transactions

Transactions are organized by location. With with the sq_list_transactions() function you can provide the location and timeframe to search. The function defaults to pulling transactions from the previous day using Sys.Date() - 1 if no dates are specified.

# list all locations
our_locations <- sq_list_locations()
our_transactions <- sq_list_transactions(location = our_locations$id[2], 
                                         begin_time = as.Date('2018-05-11'), 
                                         end_time = as.Date('2018-05-12'))
our_transactions
#> # A tibble: 245 x 6
#>    id          location_id  created_at    tenders product client_id       
#>    <chr>       <chr>        <chr>         <list>  <chr>   <chr>           
#>  1 bUjFGVjBvN… DRDCJ2X8E2P… 2018-05-12T0… <list … REGIST… D5528FBA-E5DE-4…
#>  2 5PZP31N5Zs… DRDCJ2X8E2P… 2018-05-11T2… <list … REGIST… A3A1FF51-325A-4…
#>  3 BTrGydD6he… DRDCJ2X8E2P… 2018-05-11T2… <list … REGIST… 2B3D32EB-8E58-4…
#>  4 XsqOAHl68z… DRDCJ2X8E2P… 2018-05-11T2… <list … REGIST… C50AF3D7-BE32-4…
#>  5 vmLRzrwByS… DRDCJ2X8E2P… 2018-05-11T2… <list … REGIST… 52E40E1B-2333-4…
#>  6 pTbzQApZW7… DRDCJ2X8E2P… 2018-05-11T2… <list … REGIST… 962766FF-1436-4…
#>  7 lnE20zklpP… DRDCJ2X8E2P… 2018-05-11T2… <list … REGIST… A02191CC-9AC9-4…
#>  8 DSumrqQW0L… DRDCJ2X8E2P… 2018-05-11T2… <list … REGIST… 1135FF4F-9B89-4…
#>  9 tPwFXetIwe… DRDCJ2X8E2P… 2018-05-11T2… <list … REGIST… 0D95E79D-B44C-4…
#> 10 bqUuFrzH71… DRDCJ2X8E2P… 2018-05-11T2… <list … REGIST… 48FD6A49-80A9-4…
#> # ... 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.

Helper Functions

In Square, customers can be assigned to groups. Those groups can be helpful for studies on a particular set of customers. The squareupr package has a convenience function called sq_extract_cust_groups() that will take data retrieved from the Customer API endpoint and extract the group membership of each customer into a tbl_df that is one row per customer group membership (i.e. customers may be repeated in rows if they belong to more than one group). Note: Square may create some loyalty and churn risk groups without you having to manually create them.

# list all customers
our_customers <- sq_list_customers()
our_customers$given_name <- "{HIDDEN}"
our_customers$family_name <- "{HIDDEN}"
our_customers %>% select(id, given_name, family_name, preferences, groups)
#> # A tibble: 11,939 x 5
#>    id                         given_name family_name preferences groups   
#>    <chr>                      <chr>      <chr>       <list>      <list>   
#>  1 M1RBDFRK7S1Q1EP6EZFJFV3CBW {HIDDEN}   {HIDDEN}    <list [1]>  <list [1…
#>  2 56EB9YV54D5W1BYE9P0BXNRC7C {HIDDEN}   {HIDDEN}    <list [1]>  <NULL>   
#>  3 Z2HYX7GT2160V7P9PQM1BK03HG {HIDDEN}   {HIDDEN}    <list [1]>  <NULL>   
#>  4 017CX5SWZX0MQ5A5GTPKDS6Y2M {HIDDEN}   {HIDDEN}    <list [1]>  <NULL>   
#>  5 58MK9F1HQ5447D1QZDX60NHTP4 {HIDDEN}   {HIDDEN}    <list [1]>  <list [2…
#>  6 T5HXZFS1RH6SN7N2JG9GTS9WGG {HIDDEN}   {HIDDEN}    <list [1]>  <NULL>   
#>  7 MBSJA4QV4WX6N2XV8WV9VJJTG8 {HIDDEN}   {HIDDEN}    <list [1]>  <list [2…
#>  8 ECVG5QMV99390S6PXJF9KWNN84 {HIDDEN}   {HIDDEN}    <list [1]>  <list [1…
#>  9 H8BZA910D96BGRRQDMBF08ABH4 {HIDDEN}   {HIDDEN}    <list [1]>  <list [1…
#> 10 ZCBZJ234217KTV812WX4DP2404 {HIDDEN}   {HIDDEN}    <list [1]>  <list [2…
#> # ... with 11,929 more rows

# show the groups that each customer belongs to
# filter to the groups designated automatically by Square
sq_extract_cust_groups(our_customers) %>%
  filter(grepl("^CQ689YH4KCJMY", groups.id))
#> # A tibble: 13,607 x 3
#>    id                         groups.id                 groups.name       
#>    <chr>                      <chr>                     <chr>             
#>  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,597 more rows