Overview
Each customer gets their own, dedicated SQL database at AWS where Digital Touchpoint Capture data is stored. All databases are fully encrypted while at rest using AES-256 algorithms. The data can be made available to customers upon request. Contact your Full Circle account manager to discuss details.
Schema
Visit Table
The Visit table stores the following data related to a page view or touch. DST uses the terms visit, page view, and touch interchangeably.
| Field Name | Description |
|---|---|
| Id | Internal unique identifier for the record |
| ExternalVisitId | GUID used to uniquely identify the record to external systems. Stored on CampaignMember in Salesforce when a Visit is sync'd to a CampaignMember record. |
| DateCreated | Date/time the record was inserted |
| DateUpdated | Date/time the record was last updated |
| UrlPath | The URL slug. For a full URL of "https://myco.com/about", the UrlPath (or slug) would be "/about" |
| Origin | The URL domain. For a full URL of "https://myco.com/about", the Origin (or domain) would be "myco.com" |
| UtmContent | Stores the value from the utm_content URL parameter that was present when the page was viewed |
| UtmTerm | Stores the value from the utm_term URL parameter that was present when the page was viewed |
| UtmCampaign | Stores the value from the utm_campaign URL parameter that was present when the page was viewed |
| UtmMedium | Stores the value from the utm_medium URL parameter that was present when the page was viewed |
| UtmSource | Stores the value from the utm_source URL parameter that was present when the page was viewed |
| ReferrerPage | Stores the URL of the referring page from the page view. Does not include URL parameters that were present |
| ReferrerPageRaw | Stores the URL of the referring page from the page view. Includes URL parameters that were present |
| VisitDateTime | The UTC date/time that the visitor viewed the page |
| VisitorId | Foreign key to the Visitor table |
| SourceIP | The IP address of the visitor that viewed the page |
| FirstTouch |
1 - This was the first page view 0 - This was not the first page view |
| LeadCreation |
1 - A lead was created from this page view 0 - A lead was not created from this page view |
| Form |
1 - User submitted a form from this page view 0 - User did not submit a form from this page view |
| If the user submitted a form, the email address that was provided by the user when submitting the form | |
| UserAgent | The raw user agent information that was present in the request when the page view was saved to the DST API |
Visitor Table
The Visitor table stores the following data related to a person that visits a web property.
| Field Name | Description |
|---|---|
| Id | Internal unique identifier for the record |
| ExternalVisitId | GUID used to uniquely identify the record to external systems |
| DateCreated | Date/time the record was inserted |
| DateUpdated | Date/time the record was last updated |
| SourceIP | The IP address of the visitor that viewed the page |
| If the user submitted a form, the email address that was provided by the user when submitting the form | |
| FirstName | Not currently captured |
| LastName | Not currently captured |
| ParentId | If populated, the parent visitor record. When there is a parent visitor record, all visit records are related to the parent, not the child. |
| ForgottenDate | If populated, the date/time that the visitor opted to not be tracked. This is sync'd from the Individual object in Salesforce. |
AWS to Salesforce Field Mappings
| AWS Field Name | Salesforce Field Name |
|---|---|
| ExternalVisitId | FCDSC_VisitId__c |
| UrlPath | FCDSC_Property_Location__c |
| Origin | FCDSC_Property_Domain__c |
| UtmContent | FCDSC_UTM_Content__c |
| UtmTerm | FCDSC_UTM_Term__c |
| UtmCampaign | FCDSC_UTM_Campaign__c |
| UtmMedium | FCDSC_UTM_Medium__c |
| UtmSource | FCDSC_UTM_Source__c |
| ReferrerPage | FCDSC_Referrer_Page__c |
| ReferrerPageRaw | FCDSC_Referrer_Page_Raw__c |
| VisitDateTime | FCDSC_Visit_DateTime__c |
| FirstTouch | FCDSC_First_Digital_Touch__c |
| UrlRaw | FCDSC_URL_Raw__c |
The session fields all have equivalent mappings, except they map to the first visit in the database for that particular session, as determined by the SessionStartTime field (e.g. ReferrerPage maps to FCDSC_Session_Referrer_Page__c).
Email Field Mapping
DST determines which Visit data to sync to the Campaign Member based on matching the associated Visitor record's email address with the associated Lead's email address. A single Visitor record can be associated with multiple Visit records (one-to-many relationship). At least one of these Visit records will be associated with a form fill (Form = 1) and contain a value in the Email field. However, there might be Visit records without a value in the Email field and with Form = 0. These represent visits to the site that occurred without a form fill. For example, it might be an anonymous touch before the visitor filled out a form, or it might represent another site visit after the initial form fill where the visitor didn't fill out another form. We are still able to associate these Visit records to an email address in SFDC because we can look up the Email field on the Visitor record associated with those Visit records.
Also, a Visit record can have an Email value that differs from its associated Visitor record's Email value. DST uses cookies to keep track of the same visitor across multiple sessions. If a visitor fills out multiple forms with different email addresses, a single Visitor record will have associated Visit records with different email addresses. The Visitor record's Email field gets set to the Email found on the most recent Visit.
DST looks for a Visitor record with the Email field currently set to the Lead’s email address in SFDC. If there’s a seeming mismatch, that’s because the sync happened when the Visitor record had a certain matching Email value, but since then the same visitor filled out a form with a different email address and that changed the Email field on the Visitor record.
AWS Field Encoding
The following Visit fields are saved to the AWS database in UTF-8 encoding:
- UrlPath
- Origin
- UtmContent
- UtmTerm
- UtmCampaign
- UtmMedium
- UtmSource
- ReferrerPage
- ReferrerPageRaw
- UrlRaw
Their values are decoded when synced to their matching Campaign Member fields in Salesforce. For example, if a Visit record has ReferrerPageRaw set to https%3A%2F%2Fwww.google.com%2F, it will get synced to the Campaign Member's FCDSC_Referrer_Page_Raw__c field as https://www.google.com/. Therefore, one must decode these AWS field values if making a direct comparison to the Salesforce field values.
Integer-to-Boolean Conversion
The AWS FirstTouch field is saved as an integer representing a boolean, but the corresponding FCDSC_First_Digital_Touch__c field in Salesforce is a boolean. Therefore, a FirstTouch value of 0 is saved in FCDSC_First_Digital_Touch__c as false and FirstTouch value of 1 is saved in FCDSC_First_Digital_Touch__c as true.
DateTime Formatting
The AWS database saves DateTime values in the format YYYY-MM-DD hh:mm:ss.SSS. Salesforce saves DateTime values in the format YYYY-MM-DDThh:mm:ss.SSS. Therefore, if the AWS Visit record has a VisitDateTime set to 2021-08-09 10:35:53.053, it will be saved in the Salesforce field FCDSC_Visit_DateTime__c as 2021-08-09T10:35:53.053.
Comments
0 comments
Please sign in to leave a comment.