How to integrate Shopify sales into existing CRM

Why do you need Shopify in the first place?

Shopify is a good solution if you want your own website for sales and if you want to go to production quickly. It has all the instruments to manage an online store, customize it, localize and go online right away. Of course you will need to spend some time to manage all this, but it will take a lot less time and money than creating a website from scratch. 

One of the problems you can encounter is when you already have a working sales channel, and you need to integrate it with Shopify sales in one place. We needed exactly this: we already had a fully working CRM with an app for selling and Shopify sales should have gone to CRM, so we could manage everything in one place.

Choose Shopify API

Shopify has two API’s for communication – REST API and GraphQl API. You can choose whatever is more suitable for you, or use both  API’s depending on the task, but I will list the limitations that we faced during development. 

First we chose REST API – .Net and REST are very friendly, it felt natural to use exactly this API. But it seems that Shopify supports some features only in GraphQL, such as:

  • Translations
  • Discounts
  • Inventory update limitation(I will describe it below)

These are only the limitations that we run into, there are sure a lot more of them. Also it’s a lot easier to get linked resources in one request via GraphQL, you should also consider it when choosing the API. Primarily we’re using REST API and ShopifySharp .Net SDK for all managements, so in this article we will rely on this approach most of the time.

To use both of these API’s you would need to create a private app in your Shopify store, to get API password. The authorization implementation is pretty easy, you can read about it in Shopify docs.

Map concepts

Stores

There are several ways you can keep your store info in your system. You can keep sensitive data like url and password in your config. But to support multi currency we’ve created different Shopify stores for each currency. This makes things a little bit more complicated, but it’s all solved by creating a ShopifyStore table in our CRM where we keep passwords, currency settings etc. It’s gonna look something like this:

public class ShopifyStore
{
    public int ID { get; set; }

    public string Name { get; set; }

    public string StoreUrl { get; set; }

    public string APIKey { get; set; }

    public string APIPassword { get; set; }

    public string MultipassSecret { get; set; }
}

ShopifyStore table

Products

To make two systems understand each other we need to find some way to map Shopify concepts of entities into our system. If we’re looking on the standard online store, those concepts are: 

  • Products and inventory
  • Orders 
  • Customers

Of course, there’s a lot more things, that you can manage, and that you might need to sync, but these are the key components.

The main goal was to sync all sales from Shopify into our CRM in order to see the stats and to proceed orders. To do that you should know what exact product has been ordered. So let’s start with our products and how we manage them vs how Shopify stores and manage them. Below is a structure of a Shopify product, I will show only those fields which are important for now in our mapping intentions, more detailed product structure you can see in Shopify REST API Docs.

{
  "product": {
    "id": 632910392,
    "title": "IPod Nano - 8GB",
    "variants": [
      {
        "id": 808950810,
        "product_id": 632910392,
        "title": "Pink",
        "option1": "Pink",
        "option2": null,
        "option3": null,
        "inventory_item_id": 808950810,
        "inventory_quantity": 10
      },
      {
        "id": 49148385,
        "product_id": 632910392,
        "title": "Red",
        "option1": "Red",
        "option2": null,
        "option3": null,
        "inventory_item_id": 49148385,
        "inventory_quantity": 20
      }
    ],
    "options": [
      {
        "id": 594680422,
        "product_id": 632910392,
        "name": "Color",
        "position": 1,
        "values": [
          "Pink",
          "Red"
        ]
      }
    ]
  }
}

Shopify product structure

Just a quick explanation on what we’re selling so we can be on the same page. Our company does printing on different kinds of personal items, such as  cases, photo books, mugs and so on. We can print predefined images or your own personal photos. 

With those being said, if we look at our db model, we have several tables to describe products:

  • Items – actual and final products (e.g. iPhone X White case)
  • Variations – group of similar products (e.g. iPhone cases)
  • Themes – predefined images to print on products

This is how it looks like all together:

Products db scheme

On the other hand if you will look at products in Shopify, it has different meaning and structure: while in our system product is the end product that customers will receive, in Shopify the end product is actually a product variant. To map these concepts we’ve created whole different table:

Shopify products mapping db scheme

As you can see in the ShopifyStoreProduct table we have relations to our Items and Themes as well as to Shopify products. Since the end product in Shopify is a product variant we connected product variant id to our product id. Thereby we can always get a specific product in our system if we only have Shopify data. 

Inventory

To track products in our storage we need to manage inventory and sync products sold from Shopify platform into our inventory storage. Here is how Inventory table looks line in our CRM:

public class Inventory
{
    public int ID { get; set; }

    public string ProductName { get; set; }
   
    public string SKU { get; set; }

    public int? Quantity { get; set; }

    public int? ThresholdMin { get; set; }

    public bool IsActive { get; set; }

    #region Relations

    public virtual ICollection<Item> Items { get; set; }

    #endregion
}

Inventory table

Here we have one to many relation with our products (Items property). Since we mapped Items with Shopify products, we don’t need to add any relation to Shopify. The only limitation here is that Shopify stores inventory for different locations so there can be several inventory items for one product variant. In our case we only have one storage and location for all products, so we are just using the fist inventory item. If this is not the case for you, you should check Shopify documentation regarding Shopify inventory levels.

Orders

And now when we’ve done products mapping we can easily create orders in our CRM. Shopify order contains all the information that you need to do that, like customer info, shipping address, price etc. I want to point out how we implemented order lines(products) and price mapping here, because other static content is pretty straightforward. First, let’s have a look on needed info in Shopify order:

{
  "order":{
      "id":450789469,
      "payment_gateway_names":[
        "bogus"
      ],
      "refunds":[
        {
            "id":509562969,
            "order_id":450789469,
            "refund_line_items":[
              {
                  "id":104689539,
                  "quantity":1,
                  "line_item_id":703073504,
                  "location_id":487838322
              }
            ]
        }
      ],
      "line_items":[
        {
            "id":466157049,
            "variant_id":39072856,
            "title":"IPod Nano - 8gb",
            "quantity":1,
            "sku":"IPOD2008GREEN",
            "variant_title":"green",
            "product_id":632910392,
            "grams":200,
            "price":"199.00"
        }
      ],
      "total_tax":"11.94",
      "total_discounts":"10.00",
      "shipping_lines":[
        {
            "id":369256396,
            "title":"Free Shipping",
            "price":"0.00",
            "code":"Free Shipping"
        }
      ]
  }
}

Shopify order

First we need to create order with some basic info:

OrderShopify orderShopify = await _shopifyService.GetOrder(shopifyStoreId, shopifyOrderId);
Order order = new Order
    {
        PaymentType = DetectPaymentType(orderShopify.PaymentGatewayNames.FirstOrDefault()), //you will need to match payment types with your own
        TaxCost = orderShopify.TotalTax,
        Discount = orderShopify.TotalDiscounts,
        ShippingPriceNet = orderShopify.ShippingLines.FirstOrDefault()?.Price.GetValueOrDefault(),
        PaymentDate = orderShopify.ProcessedAt?.UtcDateTime ?? orderShopify.CreatedAt?.UtcDateTime ?? DateTime.UtcNow,
        ShopifyStoreID = shopifyStoreId,
        ShopifyOrderId = orderShopify.Id,//store Shopify ids
        ShopifyOrderName = orderShopify.Name,
        ShopifyOrder = new ShopifyOrderDto
        {
            Raw = JsonConvert.SerializeObject(orderShopify) //save raw data as logs for errors checks etc.
        }
    };

Mapping of order’s main info

Doesn’t look too bad. The important part here, that I want to point out, is storing ShopifyOrderID and raw json order. First one is needed so you can always refer to a specific order in Shopify. The raw data is for checking what went wrong (there is always something wrong) or just to clarify anything you need.

Now let’s take a look on line items mapping part:

foreach (LineItem shopifyLineItem in orderShopify.LineItems)
{
    int refundQty = orderShopify.Refunds.SelectMany(r => r.RefundLineItems)
.Where(c => c.LineItemId == shopifyLineItem.Id)
.Sum(c => c.Quantity.GetValueOrDefault()); //detect quantity of current line item from refunded items list

    int qty = (shopifyLineItem.Quantity ?? 1) - refundQty; //quantity of line item includes refunded items, we don't need them

    OrderItem orderItem = new OrderItem
    {
        Order = order,
        Quantity = qty > 0 ? qty : shopifyLineItem.Quantity ?? 1,
        Status = qty > 0 ? OrderItemStatus.Uploading : OrderItemStatus.Cancelled,// use quantity to detect if item is not refunded completely
        IsDeleted = qty <= 0,
        ShopifyOrderItemId = shopifyLineItem.Id, //store Shopify ids
        ShopifyOrderItem = new ShopifyOrderItemDto
        {
            SKU = shopifyLineItem.SKU,
            ProductId = shopifyLineItem.ProductId,
            VariantId = shopifyLineItem.VariantId,
            Raw = JsonConvert.SerializeObject(shopifyLineItem) //save raw data as logs, for errors checks etc.
        }
    };

    ShopifyStoreProduct shopifyProduct = _uow.ShopifyStoreProductRepository.GetByShopifyOrderItem(shopifyStoreId, orderItem.ShopifyOrderItem.ProductId, orderItem.ShopifyOrderItem.VariantId, ShopifyStoreProductIncludeType.AdminApi);// getting needed product from ShopifyStoreProduct table

    orderItem.Item = shopifyProduct.Item;
    orderItem.Price = shopifyLineItem.Price.GetValueOrDefault();

    decimal weight = (orderItem.Item?.ShippingWeight ?? shopifyLineItem.Grams.GetValueOrDefault() / GrammInKG) * orderItem.Quantity;//due to items mapping we can obtain any info for our product
    order.TotalWeight += weight;

    if (!orderItem.IsDeleted)
    {
        order.ItemsPrice += (orderItem.Price * orderItem.Quantity).Round(order.Currency);
        order.Quantity += orderItem.Quantity;
    }
}

Mapping of order’s line items

This part is more messy. What I want to highlight here: 

  1. As with order, we should add ShopifyOrderItemId to our order item, along with shopifyLineItem.ProductId  and  shopifyLineItem.VariantId:
  ShopifyOrderItemId = shopifyLineItem.Id, //store Shopify ids
  ShopifyOrderItem = new ShopifyOrderItemDto
  {
      SKU = shopifyLineItem.SKU,
      ProductId = shopifyLineItem.ProductId,
      VariantId = shopifyLineItem.VariantId,
      Raw = JsonConvert.SerializeObject(shopifyLineItem) //save raw data as logs, for errors checks etc.
  }

Line item id mapping

2. Shopify returns all line items, even if they are no longer applicable(e.g. refunded), so we need to remove refunded items from the line items list. Also there is no status for line items, and you also need to look up if the item is in the refunded items list to make sure it’s valid:

    int refundQty = orderShopify.Refunds.SelectMany(r => r.RefundLineItems)
.Where(c => c.LineItemId == shopifyLineItem.Id)
.Sum(c => c.Quantity.GetValueOrDefault()); //detect quantity of current line item from refunded items list

    int qty = (shopifyLineItem.Quantity ?? 1) - refundQty; //quantity of line item includes refunded items, we don't need them

And then: 

OrderItem orderItem = new OrderItem
    {
        ...
        IsDeleted = qty <= 0, // use quantity to detect if item is not refunded completely

3. With above being said it is better to calculate subtotal by yourself to get correct price and quantity:

order.ItemsPrice += (orderItem.Price * orderItem.Quantity)       .Round(order.Currency);
order.Quantity += orderItem.Quantity;

I hope I’ve covered all the main info and problems that I’ve faced while mapping orders and products. The next step would be to make things work. Let’s go.

Use webhooks

Orders creation

The last thing left, after mapping is done,  is to sync sales from Shopify into our system, preferably in real time. Thankfully we have webhooks for that. Our CRM is fully functional so all we need to do is create order in our system at the time when the order was placed so we can proceed working with it. You can read about webhook authorization and main concepts here

To create an order we need orders/create event. Here is how we implemented the webhooks endpoint:

[Route("event/{storeID:int}/{eventType:int}")]
[HttpPost]
public HttpResponseMessage Event(int storeID, ShopifyEventType eventType)
{
    ShopifyStore store = _uow.ShopifyStoreRepository.GetIn(storeID, EmptyIncludeType.None);
    HttpRequestHeaders requestHeaders = Request.Headers;
    Stream reqStream = AsyncHelper.RunSync(() => Request.Content.ReadAsStreamAsync());
    string content = reqStream.ReadString();

    if (AuthorizationService.IsAuthenticWebhook(requestHeaders, content, store.MultipassSecret))
    {
        _logger.Log($"Request is not authentic. storeID {storeID}, eventType {eventType}, data {content}", Constants.EventType.Error);

        return Request.CreateResponse(HttpStatusCode.Unauthorized);
    }

    _shopifyWebhookManager.EventEnqueue(storeID, eventType, content);

    return Request.CreateResponse(HttpStatusCode.OK);
}

Shopify webhook endpoint

ShopifySharp allows you to authorize webhook. On creating a webhook we need to hardcode our inner store id and event type, so we can get the necessary info. To create webhooks you can use Shopify REST API or you can do it from the admin site. Using API for webhooks creation is a more flexible approach.

We’ve created an endpoint with parameters, where storeID is id of a store and  eventType is our custom enum, so we can easily operate with Shopify event types.

As you can see from the example content is newly created order (in case if it’s orders/create event). All we need next is to pass it into the method we created in previous chapters.

Inventory tracking

As I pointed out earlier, inventory tracking can be tricky. I will show here the diagram from the Shopify documentation on how inventory is stored there:

Inventory level structure

To get the quantity of some product variant you need to know the specific location and inventory level. Our Inventory doesn’t rely on locations, so we can use the first inventory level for specific product variants. Unfortunately Shopify REST API doesn’t allow us to get this data from the product variant. So we were forced to use GraphQL API. 

First, create inventory_levels/update webhook to update inventory quantity when it’s changed in Shopify(order was placed, etc.)

Second, use GraphQL query to get the product variant id  by inventory item id in your webhook endpoint:

public async Task<long?> GetProductVariantID(int storeID, long? inventoryItemID)
{
    if (!inventoryItemID.HasValue)
    {
        return null;
    }

    GraphService graphService = GetGraphService(storeID);
    const string InventoryItemVariantIDGraphQlQuery = @"query {{ inventoryItem(id: ""gid://shopify/InventoryItem/{0}"") {{ id variant {{ id }} }} }}";
    JToken response = await graphService.PostAsync(string.Format(InventoryItemVariantIDGraphQlQuery, inventoryItemID));
    return response.SelectToken(InventoryItemVariantIDGraphQlPath).ToString().ParseGraphQlID();
}

Use GraphQL to get variant id

Third, update inventory level in Shopify if it was changed in your main system.

public async Task SetVariantQty(ShopifyStore store, long productVariantID, int qty)
{
    InventoryLevelService inventoryLevelService = new InventoryLevelService(store.StoreUrl, store.APIPassword);
    ProductVariantService productVariantService = new ProductVariantService(store.StoreUrl, store.APIPassword);

    ProductVariant variant = await productVariantService.GetAsync(productVariantID);
    if (!variant.InventoryItemId.HasValue)
    {
        return;
    }

    ListResult<InventoryLevel> inventoryLevels = await inventoryLevelService.ListAsync(
        new InventoryLevelListFilter
        {
            InventoryItemIds = new[] { variant.InventoryItemId.Value }
        });

    if (variant.InventoryManagement == null)
    {
        await productVariantService.UpdateAsync(productVariantID, new ProductVariant { InventoryManagement = "shopify" });
// product can be non trackable, so we change it
    }

    foreach (InventoryLevel inventoryLevel in inventoryLevels.Items)
    {
        await inventoryLevelService.SetAsync(new InventoryLevel
        {
            LocationId = inventoryLevel.LocationId,
            InventoryItemId = inventoryLevel.InventoryItemId,
            Available = qty
        });
    }
}

Inventory update

That way inventory will be updated in Shopify and in our CRM if order was created in any of the sales channels – Shopify or our app.

Conclusions

This is the basics that needed to integrate two sales channels together and now our system stores all orders and inventories in one place. It is obvious that every system has different db and model structure, and you will need to map those contents differently, but maybe our example will help you in understanding how to do so. The next step would be creating and updating products from one place, content management, discount sync, etc. But for now our goal is achieved.

Related

Get In Touch

Name

Phone or Email

Message