arrow-left arrow-right brightness-2 chevron-left chevron-right circle-half-full facebook-box facebook loader magnify menu-down rss-box star twitter-box twitter white-balance-sunny window-close
Day 8: Feeding in sales data
8 min read

Day 8: Feeding in sales data

Knowing where visitors come from and the different attribution models (first touch, last touch,...) is not that useful if not linked with sales data.

  • Did the visitor join a demo?
  • What was the qualification of the visitor (PQL, MQL,...)?
  • Did they end up getting a proposal? How much?
  • Which of those visitors ended up as a customer? (deal won)

At Prezly we're using Hubspot as a sales CRM together with segment. Now Segment and Hubspot don't really get a long too well. Hubspot has their own way of doing things (visitor identification, attribution modelling,...) and the data isn't easily exchangeable.

So in our case:

All integrations are using the data we track through Analytics.js
Hubspot is doing their own tracking through their own JS snippet.

In the post below I will look up the sales data from Hubspot CRM and feed it back to Segment as user attributes.

Ideal Payload

Let's use the same approach as we did with the attribution where we leverage Customer.IO to trigger a lambda call and fill in the missing data.

Here is what I want the identify() payload to look like:

{
  "active": false,
  "messageId": "",
  "receivedAt": "2020-05-18T09:03:02.272Z",
  "traits": {
    "company": "Company Name",
    "created_at": "1570110286549",
    "demo_by": 'Gijs',
    "is_marketing_qualified": true,
    "is_sales_qualified": true,
    "lastSyncedSma": "2020-05-18T09:03:01.820Z",
    "name": "John Doe",
    "lead_status": "new",
    "lifecycle_stage": "new",
    "meeting_status": "planned",
    "priority": "medium",
    "last_deal_amount": 7200, 
    "last_deal_currency": "eur",
    "last_deal_stage": "open"
  },
  "type": "identify",
  "userId": "[userID]"
}

Note I stripped out some other common identify properties (context, ip, writeKey...).

The important properties are:

  • is_sales_qualified: Leads are manually classified as SQLs in Hubspot. If you don't know what that means read about it here.
  • is_marketing_qualified: Leads are manually classified as MQLs in Hubspot. If you don't know what that means read about it here.
  • lead_status: This a status that highlights if we can connect to the prospect. New, connected....
  • meeting_status: Did we have a meeting/demo yet? Scheduled, Not Scheduled, Done, Noshow
  • demo_by: Who did the demo ?
  • priority: Manual prioritisation of the lead (low, medium, high)
  • last_deal_amount: Dealsize of last deal
  • last_deal_currency: Currency of last deal
  • last_deal_stage: Status of deal which can be open, closed and won

By feeding that data back to Mixpanel and Customer.io we can do more reporting around the challenges we said out in the original problem statement.

Mapping User Ids

In our marketing website we hash an email address and recycle that as a unique user identified. I am not sure why we made that choice exactly but it comes with some pros and cons:

  • Pro: Easier to do multi device attribution (hashing email can be done from everywhere)
  • Con: user ids can not be carried through to the app (where we use the user_id)
  • Con: hashing string (specifically the hash we use) will ultimately lead to conflicts. The hashing function might result in duplicates.

All the forms we have on the website submit the data to hubspot form API's while the Hubspot JS snippet is loaded. That combination will allow hubspot to identify and link preexisting page trackg with a user that submits the form. Very similar to how segment does reassociation, but their own Hubspot way.

In this case the id we assign our visitors does not flow into Hubspot so for the data association we can only use the email address which luckily is known and stored by Customer.io

Show me the code

Let's create a new endpoint first where we'll accept an email address:

//add this to src/handlers/segment/segment.serverless.yml

trackHubspot:
  handler: src/handlers/segment/trackHubspot.handler
  events:
    - http: 'GET /segment/track/sales/user/{id}/email/{email}'

The (endpoint) naming is terrible, low inspiration mode today. I am out of coffee too.

Now let's install the hubspot node SDK and add our hubspot api key to .env

npm install hubspot --save

echo 'HUBSPOT_API_KEY=fill_in_your_key' > .env

Create the handler code at src/handlers/segment/trackHubspot.js

// /src/handlers/segment/trackUser.js
const { withStatusCode } = require('../../utils/response.util');
const ok = withStatusCode(200, JSON.stringify);
const nok = withStatusCode(500, JSON.stringify);

const {SegmentTracking} = require('../../SegmentTracking');
const SegmentTracker = new SegmentTracking();

const Hubspot = require('hubspot');
const hubspot = new Hubspot({apiKey: process.env.HUBSPOT_API_KEY});

exports.handler = async (event) => {
    const { id, email } = event.pathParameters;

    try {
        let contact = await hubspot.contacts.getByEmail(email);
        contact = await hubspot.contacts.getByEmail(email);

        let user_properties = {
            first_name: contact.properties.firstname.value,
            last_name: contact.properties.lastname.value,
            created_at: contact.properties.createdate.value,
            company: (contact.properties.company && contact.properties.company.value) ? contact.properties.company.value : null,
            lead_status: (contact.properties.hs_lead_status && contact.properties.hs_lead_status.value) ? contact.properties.hs_lead_status.value : null,
            lifecycle_stage: (contact.properties.lifecyclestage && contact.properties.lifecyclestage.value) ? contact.properties.lifecyclestage.value : null,
            priority: (contact.properties.priority && contact.properties.priority.value) ? contact.properties.priority.value : null,
            meeting_status: (contact.properties.meeting_status && contact.properties.meeting_status.value) ? contact.properties.meeting_status.value : null,
            demo_by: (contact.properties.demo_done_by && contact.properties.demo_done_by.value) ? contact.properties.demo_done_by.value : null,
            //demo done by
            //priority
        };

        let lifecycle_changes = [];

        let user_is_sales_qualified = false;
        let user_is_marketing_qualified = false;

        if (contact.properties.lifecyclestage && contact.properties.lifecyclestage.versions) {
            contact.properties.lifecyclestage.versions.forEach((lifecycle_stage) => {
                if (lifecycle_stage.value === 'salesqualifiedlead') {
                    user_is_sales_qualified = true;
                }

                if (lifecycle_stage.value === 'marketingqualifiedlead') {
                    user_is_marketing_qualified = true;
                }
            });
        }

        let timeline = await hubspot.


        let deals = await getDeals(contact.vid);
        const [ last_deal ] = [...deals ].reverse(); //to not modify the original

        if (last_deal) {
            user_properties.last_deal_amount = last_deal.amount;
            user_properties.last_deal_currency = last_deal.currency;
            user_properties.last_deal_stage = last_deal.dealstage;
        }

        user_properties.is_sales_qualified = user_is_sales_qualified;
        user_properties.is_marketing_qualified = user_is_marketing_qualified;

        const user_identify_payload = {
            type: "identify",
            userId: id,
            active: false,
            ip: null,
            context: {
                active:false
            },
            traits: {
                lastSyncedSma: new Date(),
                ...user_properties
            },
        };

        await SegmentTracker.callSegment([ user_identify_payload ]);

    } catch (error) {

        if (error.statusCode === 404) {
            console.log('contact not found');
            return ok('contact not found');
        }

        console.log(error.message);
        return nok(error.message);
    }

    return ok('Track Completed');
};

async function getDeals(contact_id){
    let deals = await hubspot.deals.getAssociated("contact", contact_id);
    let all_deals = [];
    for (const deal of deals.deals) {
        let dealio = await hubspot.deals.getById(deal.dealId);
        all_deals.push({
            "name": dealio.properties.dealname.value,
            "amount": dealio.properties.amount.value,
            "currency": dealio.properties.deal_currency_code.value,
            "created_at": dealio.properties.createdate.value,
            "is_closed": dealio.properties.hs_is_closed.value,
            "dealstage": getDealStage(dealio.properties.dealstage.value),
            "closedate": dealio.properties.closedate.value,
            "dealtype": dealio.properties.dealtype.value
        });
    }
    return all_deals;
}
function getDealStage(id){
    let dealstage = [];
    dealstage['8e04c403-2a45-48f9-8f43-bd4969f13cff'] = 'Engaged';
    dealstage['adab8a2d-f5e2-4517-9f70-dc642e09fad1'] = 'Disco';
    dealstage['51ceaf87-44ec-45a7-bfb7-e4bdef0bd623'] = 'Demo';
    dealstage['825982'] = 'Trial';
    dealstage['1872965'] = 'On hold';
    dealstage['12c0c06b-3be0-4e51-bcd8-00e885109b95'] = 'Proposal';
    dealstage['87301417-2822-4b01-91b4-2ba524ee9a21'] = 'Negotiation';
    dealstage['a71e4111-aa77-4ca0-bfef-5f58de61c0cf'] = 'Procurement/IT/Legal';
    dealstage['7c536ff8-bc72-4540-96ed-43d4bcd732ba'] = 'Closed Won';
    dealstage['2c36415d-1268-4ce2-ac72-788d8e6a8b48'] = 'Closed Lost';
    dealstage['29ca8173-a62d-4516-9bd6-998dc6aeb053'] = 'Last try';

    return dealstage[id];
}

Yeah i know this code is messy. Will clean it up later but it does the job.

Hit a quick sls deploy to deploy the new endpoint. Here is what the code does:

  • Search contact by email (passed in URL)
  • Get all the deals associated with that email
  • Fire an identify() call with the original id (hashed) and the new properties extracted from the deals/hubspot data

Triggering it (using Customer.io)

I have created some segments in Customer.io to look if certain properties are available:

For example the SMA - Is Marketing Qualified is a segment of customers where is_marketing_qualified is marked as true.

Creating this segment before triggering the new endpoint will create an empty segment. To fix this we're creating a customer.io campaign that triggers every time a new user is identified that lacks those traits:

Criteria for the Campaign

Workflow steps for that campaign:

Create the workflow

Make sure that the webhook url has the right endpoint and passes in the user email as this is the identifier we'll use to extract Hubspot data.

On the last step you need to specify if you want to match current users only or future additions too

In our case I am triggering it for all people in the campaign and future additions only which will make sure that data is synced nightly for people that don't have the sales data yet.

After you have enabled this campaign Customer.io will trigger a ton of webhook calls. The lambda will fail on hubspot thottle limits (too many requests at the same time) and will be retried by Customer.io automatically? (not sure about this)

Triggered webhooks

Reporting (in mixpanel)

Once that is done you can use Mixpanel to create some reports. Let's start by creating some new User Cohorts with that new data:

Using these Cohorts you can now see a what typical behavior of a Marketing Qualified lead is:

Or let's try to find out the different sources (see Day 7) that ultimately flow to a Marketing Qualified Lead:

Show only Source identified events

Now we're getting somewhere. In the Insights > Users we can now aggregate the deal size and group it by the first channel the user came from:

Pipeline generated by source_first_referrer_type

The same data but looking at the last source:

Pipeline generated grouped by source_last_referrer_type

Still early to say that this excercise is finished but let's go back to the original problem statement in my original post:

Here is a list of things that would help understand and improve our marketing campaigns when it comes to attribution:
  • ✅ Seeing the different sources a visitor comes from
  • Understanding which touch points contributed to a conversion.
  • ✅ Look at more than just the last touchpoint that contributed to a conversion
  • ✅ Link different sessions / multiple devices to a single user
  • ✅ Be able to rewrite history / look back in time. PQLs that ultimately end up buying are worth more than other PQLs.

I will now ask the Marketing team what they think of this and how useful this extra information is. When comparing this to our sales data (reports in Hubspot) i found some issues:

  • User ids change. Conflict between website and app cookies with .prezly.com as domain. Wrote about this here.
  • Not all users are known by Segment. The leads reported in segment are 12% lower than what is in our CRM. This is related to ad/tracking cookies being blocked. Will be looking how to solve this tomorrow
  • Funnels are unusable until we trigger events for Deal/Lead changes. Events such as Proposal Created or Demo Took Place are important to get a good understanding on the speed of the funnel
  • Hubspot changes are not automatically propagated. Will be looking into using webhooks to trigger updates to users.