Solved

Need to find "Date" column from the "Total Sales Report", using data loaded from Fivetran

mrohwetter
Visitor
2 0 2

Found this post that suggested that the "date" column from the "Total Sales Report" corresponds to the "processed_at" field from the API here: https://community.shopify.com/c/shopify-apis-and-sdks/could-not-find-any-corresponding-property-of-t...

We are loading Shopify data using Fivetran, and I'm struggling to find that "processed_at" date within that data--Fivetran ERD is here: https://docs.google.com/presentation/d/1wSWI7SbY4NMtyRLWdg2Z4LW3-SRCF8K7McN0VzLjh3w/edit#slide=id.g1...

I have a particular order that was created, and then later had some line items refunded / new line items added. The "date" column in the "Total Sales Report" accurately captures this, but I'm unable to figure out how to represent this in the data I have access to in our Snowflake instance.

Accepted Solution (1)

CloudlabSam
Shopify Partner
539 43 88

This is an accepted solution.

You use the processed_at date on the order to represent the created date of each sale. For edits to the order (line items added, exchanged) the date can only be retrieved from deep in the GraphQL of the order. So it isn't represented in your Fivetran model as this just seems to be the REST API objects.

View solution in original post

Replies 3 (3)

CloudlabSam
Shopify Partner
539 43 88

This is an accepted solution.

You use the processed_at date on the order to represent the created date of each sale. For edits to the order (line items added, exchanged) the date can only be retrieved from deep in the GraphQL of the order. So it isn't represented in your Fivetran model as this just seems to be the REST API objects.

mrohwetter
Visitor
2 0 2

Would you mind helping me find the processed_at field in the GraphQL docs? 

 

Thanks for the help!

CloudlabSam
Shopify Partner
539 43 88

You can use this query to see adjustments. Tt will be the happenedAt date for the ORDER_EDIT types.

 

{
  orders(first: 1, query: "name:#259600") {
    edges {
      node {
        edited
        agreements(first: 10) {
          edges {
            node {
              id
              happenedAt
              reason
              sales(first: 10) {
                edges {
                  node {
                    lineType
                    actionType                    
                    quantity                    
                    totalAmount {
                      shopMoney {
                        amount
                      }
                    }
                    ... on ProductSale {
                      
                      lineItem {
                        id
                        name
                        quantity
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}