GraphQL API repo for first order per customer

[bbuhmann]

I'm trying to use a graphQL query to report on first time vs. repeat customers.  In order to do this, I'm trying to get each customers' first order so that I can compare if the order id of the order in the report matches the customer's first order.  When I tried to include this in the bulkQuery for orders it returned a FAILED response.  Unfortunately, this is my preferred approach, as it would get my answer in one step, but perhaps going orders, to customer, and back to orders was too much.  I was using this query:

mutation {
  bulkOperationRunQuery(
    query:"""{
    orders(query:"created_at:>=2022-08-22T00:00:00-04:00 AND created_at:<=2022-08-23T00:00:00-04:00") { 
         edges { 
            node { 
                id 
                createdAt
                customer {
                    id
                    numberOfOrders
                    orders(first:1 reverse: true){
                        edges{
                            node{
                                id
                                
                            }
                        }
                    }
                }
                displayFinancialStatus
                currentTotalPriceSet{
                    shopMoney{
                        amount
                        currencyCode
                    }
                }
                cartDiscountAmountSet{
                    shopMoney{
                        amount
                        currencyCode
                    }
                }
                    agreements {
                edges {
                    node {
                    id
                    happenedAt
                    ... on RefundAgreement {
                        refund {
                        id
                        }
                    }
                    sales {
                        edges {
                        node {
                            id
                            actionType
                            lineType
                            quantity
                            ... on ProductSale {
                            lineItem {
                                id
                                product {
                                            id
                                            title
                                            productType
                                        }
                                        originalUnitPriceSet {
                                            shopMoney {
                                                amount
                                                currencyCode
                                            }
                                        }
                            }
                            }
                            ... on GiftCardSale {
                            lineItem {
                                id
                            }
                            }
                            totalAmount {
                            shopMoney {
                                amount
                            }
                            }
                            totalTaxAmount {
                            shopMoney {
                                amount
                            }
                            }
                            totalDiscountAmountBeforeTaxes {
                            shopMoney {
                                amount
                            }
                            }
                            totalDiscountAmountAfterTaxes {
                            shopMoney {
                                amount
                            }
                            }
                        }
                        cursor
                        }
                        pageInfo {
                        hasNextPage
                        }
                    }
                    }
                }
                }
            }
        }
    }
    }"""
  ) {
    bulkOperation {
      id
      status
    }
    userErrors {
      field
      message
    }
  }
}

My second approach is to split this data and then recombine it.  I have a query to get the first order of every customer created during a specific time window, corresponding to the timeframe I'm requesting orders across. I've run into 2 issues here.  I'm filtering by "customer_date", but I'm not returning customers created in the window I've requested.  For example, when I request just this customer: gid://shopify/Customer/5947421130799, I see a createdAt value of 2022-07-02T14:16:11Z.  But when I run this query for "customer_date:>=2022-07-01T00:00:00-04:00 AND customer_date:<=2022-07-03T00:00:00-04:00", this customer is not returned.

The second issue I have is that I am unable to return only the first order for a customer in a bulk query.  When I request an individual customer, it returns just the first order, but when I request from customers, with a connection to Orders, it returns all orders, regardless of how many I request.

mutation {
  bulkOperationRunQuery(
    query:"""{
    customers(customer_date:>=2022-07-01T00:00:00-04:00 AND customer_date:<=2022-07-03T00:00:00-04:00 AND query:"orders_count:>0") { 
        edges { 
            node { 
                id 
                numberOfOrders
                createdAt
                orders(first:1){
                    edges{
                        node{
                            id
                            createdAt
                            updatedAt 
                        }
                    }
                }
                
            }
        }
    }
    }"""
  ) {
    bulkOperation {
      id
      status
    }
    userErrors {
      field
      message
    }
  }
}

[Funnel]

We are experiencing the same issues as you. We have tried to use CustomerJourney.customerOrderIndex via a orders query as well, but this field is returned as empty in many situations and isn't as reliable as checking the customer's orders unfortunately. We also had a related issue that caused INTERNAL_SERVER_ERROR when querying orders{customer{orders{}}}: https://community.shopify.com/c/shopify-apis-and-sdks/internal-server-error/td-p/1704682