Analytics-Mastery Pty Ltd

Contact Us

Abandon Cart Customer Identification Solution

4–6 minutes

Abandon Cart Email from SFMC for AU Website 

This forms the part of the third usecase that has been developed for Bunnings wherein an abandon cart email is sent to all the subscribers that have items in the cart and have not made a purchase in last 24 hours.

GA data is currently used to identify Abandon cart.

Steps

StepsProcess Definition
1.In order to stitch a customer in SFMC to a cart on the AU website- a url parameter SFMCID=SubscriberKey is configured in SFMC so that all email links will have this paramter appended to them.
2.When a customer clicks on a link from any of the marketing email and lands on the AU website of Bunnings and adds some products to the cart but does not make a purchase.
3.After 24 hours this becomes an abandon cart data and is pulled by the BigQuery Report to Azure and is then sent to SFMC SFTP as a CSV file.
4.The CSV file is imported into a Data Extension (overwrite) via an triggered automation that listens to the Abandon Cart folder in SFTP.
5.The CSV file can have multiple rows of data for a single SFMCID as there could be multiple products present in the cart for a single customer and each row of data in CSV has SFMCID(CustomerID in SFMC)and the product data like – product SKU,product name,price,delivery method.
6.Customer emailaddress is then extracted form the Customer_Master_ANZ based on the CustomerID (SFMCID) present in the Data Extension which gets the abandon data imported and the records aregrouped by CustomerID so that we get a final data set that can be appended to a data extension which has customers to whom the Abandon cart email has to be sent.This DE is a sendable DE andis the enrty source to a Journey that sends Abandon cart email.
7.An additional Data Extension is created to which the records from the DE that gets the import data from CSV file are appended everyday.This data extension servers as the source from which the productdata lookup is done in the Abandon cart email.(based on CustomerID and Importdatetime).
SELECT country, time_stamp, ga_user_id1 as ga_user_id, sfmcid, cart_id1 as cart_id, product_sku, product_name, product_price, product_category, delivery_type, purchase_step FROM (
  SELECT abandon_set.ga_user_id as ga_user_id1, abandon_set.latest_cart, abandon_set.cart_id as cart_id1 FROM (
    SELECT max(latest_cart) AS latest_cart, ga_user_id FROM (
               SELECT * FROM (
                SELECT
                 hits.time AS latest_cart,
                 clientId AS ga_user_id,
                 CASE WHEN customDimensions.index = 112 THEN customDimensions.value END AS cart_id,
                 eCommerceAction.action_type AS purchase_step
                 FROM
                 `fine-citadel-90303.183059133.ga_sessions_*`, -- GA table ID for AU site
                 UNNEST(hits) AS hits,
                 UNNEST (hits.customDimensions) AS customDimensions
                 WHERE
                 eCommerceAction.step IN (2, 3) -- checkout and payment step
                 AND
                 _table_suffix BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE("Australia/Melbourne"), INTERVAL 1 DAY))
                 AND FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE("Australia/Melbourne"), INTERVAL 1 DAY)) -- date range to extract data
                 ) 
               WHERE cart_id is not null
               )
          GROUP BY ga_user_id
          ) latest_set
  INNER JOIN (
    SELECT
               hits.time AS latest_cart,
               clientId AS ga_user_id,
               CASE WHEN customDimensions.index = 112 THEN customDimensions.value END AS cart_id,
               eCommerceAction.action_type AS purchase_step
          FROM
               `fine-citadel-90303.183059133.ga_sessions_*`, -- GA table ID for AU site
               UNNEST(hits) AS hits,
               UNNEST (hits.customDimensions) AS customDimensions
    WHERE
               eCommerceAction.step IN (2, 3) -- checkout and payment step
               AND
               _table_suffix BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE("Australia/Melbourne"), INTERVAL 1 DAY))
               AND FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE("Australia/Melbourne"), INTERVAL 1 DAY)) -- date range to extract data
  ) abandon_set
  ON abandon_set.ga_user_id = latest_set.ga_user_id and abandon_set.latest_cart = latest_set.latest_cart
  WHERE cart_id is not null
  ) latest_cart --get only the latest abandon cart per user
LEFT JOIN (
  SELECT country, time_stamp, ga_user_id, sfmcid2 as sfmcid, cart_id, product_sku, product_name, product_price, product_category, delivery_type, purchase_step  FROM (
   SELECT DISTINCT country, time_stamp, ga_user_id, sfmcid, cart_id, product_sku, product_name, product_price, product_category, delivery_type, purchase_step FROM (      
    SELECT * FROM ( -- join with products
      SELECT * FROM ( --join with purchase_users (yesterday purchase users)
      SELECT DISTINCT
      'AU' AS country,
      FORMAT_TIMESTAMP("%d/%m/%Y %H:%M:%S",TIMESTAMP_SECONDS(visitStartTime), "Australia/Melbourne") AS time_stamp,
      clientId AS ga_user_id,
      CASE WHEN customDimensions.index = 3 THEN customDimensions.value END AS sfmcid,
      CASE WHEN customDimensions.index = 112 THEN customDimensions.value END AS cart_id,
      eCommerceAction.action_type AS purchase_step
      FROM
      `fine-citadel-90303.183059133.ga_sessions_*`, -- GA table ID for AU site
      UNNEST(hits) AS hits,
      UNNEST (hits.customDimensions) AS customDimensions
      WHERE
        eCommerceAction.step IN (2, 3) -- checkout and payment step
        AND
      _table_suffix BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE("Australia/Melbourne"), INTERVAL 1 DAY))
      AND FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE("Australia/Melbourne"), INTERVAL 1 DAY)) -- date range to extract data
      ORDER BY sfmcid DESC, cart_id
    ) abandon_users
    LEFT OUTER JOIN (
      SELECT DISTINCT
      FORMAT_TIMESTAMP("%d/%m/%Y %H:%M:%S",TIMESTAMP_SECONDS(visitStartTime), "Australia/Melbourne") AS time_stamp2,
      clientId AS ga_user_id2,
      CASE WHEN customDimensions.index = 3 THEN customDimensions.value END AS sfmcid2,
      CASE WHEN customDimensions.index = 112 THEN customDimensions.value END AS cart_id2,
      eCommerceAction.action_type AS purchase_step2
      FROM
      `fine-citadel-90303.183059133.ga_sessions_*`, -- GA table ID for AU site
      UNNEST(hits) AS hits,
      UNNEST (hits.customDimensions) AS customDimensions
      WHERE
       eCommerceAction.action_type = '6'-- purchase complete action
       AND
      _table_suffix BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE("Australia/Melbourne"), INTERVAL 1 DAY))
      AND FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE("Australia/Melbourne"), INTERVAL 1 DAY)) -- date range to extract data      
        UNION DISTINCT
        --intraday purchases
        SELECT DISTINCT
      FORMAT_TIMESTAMP("%d/%m/%Y %H:%M:%S",TIMESTAMP_SECONDS(visitStartTime), "Australia/Melbourne") AS time_stamp2,
      clientId AS ga_user_id2,
      CASE WHEN customDimensions.index = 3 THEN customDimensions.value END AS sfmcid2,
      CASE WHEN customDimensions.index = 112 THEN customDimensions.value END AS cart_id2,
      eCommerceAction.action_type AS purchase_step2
      FROM
      `fine-citadel-90303.183059133.ga_sessions_intraday_*`, -- GA table ID for AU site
      UNNEST(hits) AS hits,
      UNNEST (hits.customDimensions) AS customDimensions
      WHERE
       eCommerceAction.action_type = '6'-- purchase complete action
       AND
      _table_suffix BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE("Australia/Melbourne"), INTERVAL 0 DAY))
      AND FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE("Australia/Melbourne"), INTERVAL 0 DAY)) -- date range to extract data
           
      ) purchase_users
      ON (abandon_users.ga_user_id = purchase_users.ga_user_id2)
     ) WHERE purchase_step2 is null
   ) abandon_users2
   --JOIN with Products
  LEFT JOIN (
    SELECT
      clientId AS ga_user_id2,
      product.productSKU AS product_sku,
      product.v2ProductName AS product_name,
      product.v2ProductCategory AS product_category,
      product.productPrice / 1000000 AS product_price,
      CASE WHEN productCustomDimensions.index = 55 THEN productCustomDimensions.value END AS delivery_type
    FROM
      `fine-citadel-90303.183059133.ga_sessions_*`, -- GA table ID for AU site
      UNNEST(hits) AS hits,
      UNNEST (hits.product) AS product,
      UNNEST (product.customDimensions) AS ProductCustomDimensions
    WHERE  
      _table_suffix BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE("Australia/Melbourne"), INTERVAL 1 DAY))
      AND FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE("Australia/Melbourne"), INTERVAL 1 DAY)) -- date range to extract data
    ) abandon_products
    ON (abandon_users2.ga_user_id = abandon_products.ga_user_id2)
    WHERE delivery_type is not null
    ) abandon
   --JOIN with SFMCID
   LEFT JOIN (
    SELECT DISTINCT
      clientId AS ga_user_id2,
      CASE WHEN customDimensions.index = 3 THEN customDimensions.value END AS sfmcid2,
      CASE WHEN customDimensions.index = 112 THEN customDimensions.value END AS cart_id2
      FROM
      `fine-citadel-90303.183059133.ga_sessions_*`, -- GA table ID for AU site
      UNNEST(hits) AS hits,
      UNNEST (hits.customDimensions) AS customDimensions
      WHERE
      _table_suffix BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE("Australia/Melbourne"), INTERVAL 1 DAY))
      AND FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE("Australia/Melbourne"), INTERVAL 1 DAY)) -- date range to extract data
      ) smcid
   ON (abandon.ga_user_id = smcid.ga_user_id2)
  WHERE sfmcid2 is not null AND cart_id is not null
  ORDER BY time_stamp ASC, sfmcid2
) abandon_cart
ON (latest_cart.ga_user_id1 = abandon_cart.ga_user_id AND latest_cart.cart_id1 = abandon_cart.cart_id)
WHERE ga_user_id is not null