怎么用GraphQL API获取Shopify订单归因并导出(colab+shopify)

之前的文章我们已经聊了shopify的密钥获取，GraphQL API调取，colab的http请求，colab如何导出数据，本期说下如何用colab和shopify token调用GraphQL API获取shopify的订单和订单最终归因数据并导出到google sheet中。

GraphQL语句

之前用admin GraphQL API调取orders的query

query{
orders(first:10){
  nodes{
    id
    name
  }
}
}

现在我们加入新的查询维度customerJourneySummary用于查询整体用户路径，同时，加入lastVisit来查询最终归因，这里我们想看最终点击归因的campaign，content，source，term

加入新维度的query代码

query{
orders(first:10){
  nodes{
    id
    name
    customerJourneySummary{
      lastVisit{
        utmParameters{
          campaign
          content
          source
          term
        }
      }
    }
  }
}
}

为了方便确认，这里把最早的10个订单改为最后10个订单，因此在条件筛选的括号里加入reverse

最终的query代码

query{
orders(first:10,reverse:true){
  nodes{
    id
    name
    customerJourneySummary{
      lastVisit{
        utmParameters{
          campaign
          content
          source
          term
        }
      }
    }
  }
}
}

colab代码

导入库，用token,http,query获取数据

access_token = 'XXXXXXXXXXXXXXXXX'
request_url = 'https://XXXXXXXX.myshopify.com/admin/api/2021-07/graphql.json/'
from google.colab import auth
auth.authenticate_user()

import gspread
from google.auth import default
creds, _ = default()
gc = gspread.authorize(creds)

import pandas as pd
import requests

query = """
query{
    orders(first: 10, reverse: true){
      nodes{
            id
            name
        customerJourneySummary {
                daysToConversion
        lastVisit {
              utmParameters{
                        campaign
                        content
                        medium
                        source
                        term
                    }
                }
            }
        }
    }
}
"""


datalist = []
headers = {
        'X-Shopify-Access-Token': access_token,
        'Content-Type': 'application/json'
    }

payload = {
        "query": query
    }
response = requests.post(request_url, headers=headers, json=payload)
response_data = response.json()

数据处理和导出

接下来开始处理数据，并导出到google sheet，首先把需要的nodes从源数据中取出并导入到空列表中，然后把空列表变成python的dataframe准备导出

处理成df的代码

orders = response_data['data']['orders']['nodes']
for order in orders:
 datalist.append(order)
df = pd.json_normalize(datalist)
df = df.astype(str)

最后导出到google sheet

sheet_url = 'XXXXXXXX'
sheetname = 'XXXXXX'
gsheets = gc.open_by_url(sheet_url)
sheets = gsheets.worksheet(sheetname)
sheets.clear()
sheets.update([df.columns.values.tolist()] + df.values.tolist())

最终完整代码

access_token = 'XXXXXXXXXXXXXXXXX'
request_url = 'https://XXXXXXXX.myshopify.com/admin/api/2021-07/graphql.json/'
from google.colab import auth
auth.authenticate_user()

import gspread
from google.auth import default
creds, _ = default()
gc = gspread.authorize(creds)

import pandas as pd
import requests

query = """
query{
    orders(first: 10, reverse: true){
      nodes{
            id
            name
        customerJourneySummary {
                daysToConversion
        lastVisit {
              utmParameters{
                        campaign
                        content
                        medium
                        source
                        term
                    }
                }
            }
        }
    }
}
"""


datalist = []
headers = {
        'X-Shopify-Access-Token': access_token,
        'Content-Type': 'application/json'
    }

payload = {
        "query": query
    }
response = requests.post(request_url, headers=headers, json=payload)
response_data = response.json()
orders = response_data['data']['orders']['nodes']
for order in orders:
 datalist.append(order)
df = pd.json_normalize(datalist)
df = df.astype(str)
sheet_url = 'XXXXXXXX'
sheetname = 'XXXXXX'
gsheets = gc.open_by_url(sheet_url)
sheets = gsheets.worksheet(sheetname)
sheets.clear()
sheets.update([df.columns.values.tolist()] + df.values.tolist())