Salesforce Marketing Cloud Data Push
Overview
Oracle CrowdTwist integrates with Salesforce Marketing Cloud/ExactTarget using realtime API calls. This allows a client’s loyalty data to flow into their SFMC/ET instance in real-time and be made available for communications.
This is achieved by Oracle CrowdTwist structuring their push feeds to match the API format of SFMC/ET to populate the mutual client’s data extension tables. Authorization is handled via access token for the server-to-server integration and requires OAuth 2.0 credentials (details below).
NOTE: This requires the client to have their own Salesforce Marketing Cloud / ExactTarget instance.
Data Push Feeds
The following types of data feeds can be enabled:
- User Profile: Includes creations of new profiles and updates to existing profiles
- User Activity: Includes data on user activity completions
- User Redemption: Includes data on user reward redemptions
Integration Setup Process
- Client creates three data extension tables in Salesforce Marketing Cloud per instance, following specifications outlined below. Recommendation is to create distinct tables for each CrowdTwist instance.
- The name of each data extension table is configurable by client. Oracle CrowdTwist recommends using the naming format above and listing the push type, for example:
- USER_PROFILE_PROD (for production User Profile endpoint)
- USER_PROFILE_SAND (for sandbox User Profile endpoint)
- Client generates OAuth Credentials for CrowdTwist platform
- Client generates details for data extension table population (hashed client values and table external keys)
- Client provides the configuration details to Oracle CrowdTwist
- Table Details: Names, hashed client values and external keys for all CrowdTwist data tables
- OAuth credentials: Client secret, Client key, MID – for enterprise Salesforce accounts
- SFCM Instance Hashed Client value
- Oracle CrowdTwist configures the Live Push API integration
- Oracle CrowdTwist pushes sample payload to each data extension table
- If successful, Oracle CrowdTwist configures automated process
API Endpoint for Authorization
- https://[hashed-client-value].auth.marketingcloudapis.com/v2/token
API Endpoint for Table Population
- https://[hashed-client-value].rest.marketingcloudapis.com/hub/v1/dataevents/key:{table_key}/rowset
Data Formats
NOTE: All CrowdTwist date formats are converted to MM/DD/YYYY HH:MI:SS. For example: 10/28/2019 17:51:50
User Profile
|
Field Name |
Data Type |
Description |
Length |
Primary Key? |
Nullable? |
|---|---|---|---|---|---|
|
ID |
Number |
Unique identifier for CrowdTwist platform |
Yes |
No |
|
|
FIRST_NAME |
Text |
Users first name |
100 |
|
No |
|
MIDDLE_NAME |
Text |
Users middle name |
100 |
|
Yes |
|
LAST_NAME |
Text |
Users last name |
100 |
|
Yes |
|
DATE_OF_BIRTH |
Date |
User's date of birth |
|
Yes |
|
|
EMAIL_ADDRESS |
EmailAddress |
Email address currently associated with user in CrowdTwist platform |
254 |
No |
|
|
STREET_ADDRESS1 |
Text |
|
150 |
|
Yes |
|
STREET_ADDRESS2 |
Text |
|
150 |
|
Yes |
|
CITY |
Text |
|
200 |
|
Yes |
|
STATE |
Text |
Plain English state name |
200 |
|
Yes |
|
POSTAL_CODE |
Text |
Postal code |
75 |
|
Yes |
|
COUNTRY_CODE |
Text |
2-character code (ISO-3166) |
2 |
|
Yes |
|
REGION |
Text |
U.S. regions only/Plain English US region name |
200 |
|
Yes |
|
GENDER |
Number |
Internal CrowdTwist gender ID value. Use the ID values below to populate this field. |
|
Yes |
|
|
EMAIL_IS_VERIFIED |
Boolean |
True/False |
|
Yes |
|
|
RECEIVE_EMAIL_UPDATES |
Boolean |
True/False - Has user opted to receive email updates from CrowdTwist platform? |
|
Yes |
|
|
RECEIVE_SMS_MESSAGES |
Boolean |
True/False - Flag that indicates permission to send user verification via SMS. 0=No, 1=Yes |
|
Yes |
|
|
TOTAL_POINTS |
Number |
Total points earned by member (ex. 100000) |
|
No |
|
|
REDEEMABLE_POINTS |
Number |
Total points available for redemption of rewards by member (ex. 50000) |
|
No |
|
|
FAN_RANK |
Number |
Current rank of member compared to total membership base (ex. 25)/Absolute user rank with clients |
|
No |
|
|
MOBILE_NUMBER |
Text |
Ex. 2035555555/Users mobile phone number |
65 |
|
Yes |
|
DATE_MOBILE_VERIFIED |
Date |
Date Mobile Number Verified/Will not be present in the initial push to CT but could be present in any future push |
|
Yes |
|
|
DATE_LAST_LOGIN |
Date |
Date member last logged into their account |
|
Yes |
|
|
DATE_LAST_UPDATE |
Date |
Date member last updated their account information |
|
No |
|
|
DATE_CREATED |
Date |
Date membership was created |
|
No |
|
|
SIGN_UP_TYPE |
Text |
How the member registered (i.e. Facebook, Twitter, etc.) |
200 |
|
Yes |
|
IS_ACTIVE |
Number |
True/False - Is account active in CT platform |
|
Yes |
|
|
USERNAME |
Text |
CrowdTwist system ID associated with user |
150 |
Yes |
|
|
PASSWORD |
Text |
User password |
150 |
|
Yes |
|
HASH_ALGORITHM |
Text |
The hash algorithm used to hash the user's password |
200 |
|
Yes |
|
SALT |
Text |
The arbitrary salt value used to hash the user's password |
115 |
|
Yes |
|
PASSWORD_GENERATED |
Number |
|
|
Yes |
|
|
ACTIVITY_STREAM_OPTIN |
Number |
True/False |
|
Yes |
|
|
IP_ADDRESS |
Text |
User's IP address at time of registration/IP address user last accessed the CrowdTwist platform |
90 |
|
Yes |
|
THIRD_PARTY_ID |
Text |
Client Generated ID |
150 |
|
Yes |
|
FACEBOOK_USER_ID |
Text |
Facebook ID associated with |
150 |
|
Yes |
|
FSQ_USER_ID |
Text |
Foursquare ID associated with user in CrowdTwist platform |
150 |
|
Yes |
|
GOOGLE_USER_ID |
Text |
|
150 |
|
Yes |
|
INST_USER_ID |
Text |
|
150 |
|
Yes |
|
NUM_FACEBOOK_FRIENDS |
Number |
Number of Facebook friends |
|
Yes |
|
|
NUM_TWITTER_FOLLOWERS |
Number |
Number of Twitter followers |
|
Yes |
|
|
SIGN_UP_CAMPAIGN |
Text |
Arbitrary campaign that user signed up through; typically passed on User Create API |
300 |
|
Yes |
|
TWITTER_USER_ID |
Text |
Twitter ID associated with user in CrowdTwist platform |
150 |
|
Yes |
|
YAHOO_USER_ID |
Text |
|
150 |
|
Yes |
|
WEEKLY_POINTS |
Number |
Points accumulated by user in previous 7 days |
|
Yes |
|
|
MONTHLY_POINTS |
Number |
Points accumulated by user in previous 30 days |
|
No |
|
|
FAN_LEVEL_NAME |
Text |
There is a maximum of 6 levels within the platform |
100 |
|
No |
|
CLIENT_BRANDING_ID |
Number |
|
|
Yes |
|
|
LANG_PREF |
Text |
|
20 |
|
Yes |
|
SIGN_UP_CHANNEL_ID |
Text |
Channel of program member enrollment or registration.
|
150 |
|
Yes |
|
TIER_TIMEFRAME_SCORE |
Number |
Cumulative score the member has earned in the current timeframe since the member last expired. For fixed it is calculated from start date to start date, i.e. for an annual timeframe it calculates the total points earned between 1/1/19-12/31/19. This score will reset to 0 at the end of the reset date. For rolling, it is the cumulative score since they last expired, i.e. if they joined on 2/2/19 and jumped on 3/2/19 to gold, the timeframe score will be calculated until 3/2/20. For lifetime, it is the same as "lifetime points". |
|
|
Yes |
|
TIER_SCORE |
Number |
The points earned while the member is in a tier |
|
|
Yes |
|
CURRENT_TIER_LEVEL |
Text |
Name of the user's current tier (this is the same as fan_level) |
200 |
|
Yes |
|
NEXT_TIER_LEVEL |
Text |
Name of the user's next tier level |
200 |
|
Yes |
|
DATE_TIER_ASSIGNED |
Date |
Date the member is assigned current tier. This changes for all event types (start, jump, maintain, drop and reset) |
|
|
Yes |
|
TIER_EXPIRATION_DATE |
Date |
Date a member’s tier will expire if they do not maintain. This is both fixed and rolling. |
|
|
Yes |
|
TIER_RESET_DATE |
Date |
Date a member resets to 0 and enters maintenance. Only fixed timeframe. |
|
|
Yes |
|
SCORE_NEEDED_TO_NEXT_TIER |
Number |
Number of points needed for a member to jump to the next tier |
|
|
Yes |
|
SCORE_NEEDED_TO_MAINTAIN_TIER |
Number |
Number of points needed to maintain current tier |
|
|
Yes |
|
DAYS_UNTIL_TIER_EXPIRATION |
Number |
Number of days until a member’s tier expires if they do not maintain |
|
|
Yes |
|
DAYS_UNTIL_TIER_RESET |
Number |
Number of days until a member resets. Only for fixed timeframe. |
|
|
Yes |
|
DATE_REACHED |
Date |
Date the member reached their current tier. This only changes when a member drops or jumps a tier. |
|
|
Yes |
|
ASSIGNED_BY_EVENT |
Text |
The event that occurred when a member is assigned a tier (i.e. start, jump, maintain, drop, reset) |
150 |
|
Yes |
|
C_EMPLOYEE_ID |
Text |
Custom Data |
200 |
|
Yes |
|
C_CASHIER_ID |
Text |
Custom Data |
200 |
|
Yes |
|
C_REGISTER_ID |
Text |
Custom Data |
200 |
|
Yes |
|
C_STORE_LOC |
Text |
Custom Data |
200 |
|
Yes |
|
C_PLCC |
Text |
Custom Data |
200 |
|
Yes |
|
C_CARDHOLDER_STATUS |
Text |
Custom Data |
200 |
|
Yes |
|
C_TIER |
Text |
Custom Data |
200 |
|
Yes |
|
C_USER_OPERATION |
Text |
Custom Data |
200 |
|
Yes |
User Activity
|
Field Name |
Data Type |
Description |
Length |
Primary Key? |
Nullable? |
|---|---|---|---|---|---|
|
ID |
Text |
Unique identifier for CrowdTwist platform |
32 |
|
No |
|
ACTIVITY_ID |
Text |
CT Activity ID - will be provided when activity propagates in the CT system |
38 |
|
No |
|
USER_ACTIVITY_ID |
Text |
CT Internal User Activity ID |
38 |
Yes |
No |
|
ACTIVITY_NAME |
Text |
Activity description |
100 |
|
No |
|
NUM_POINTS |
Number |
Points earned for activity, positive number for added activity, negative number for deleted activity; also negative in some other cases (i.e. rewards redeemed) |
|
|
No |
|
NOTES |
Text |
Internal Notes |
150 |
|
Yes |
|
THIRD_PARTY_ID |
Text |
Unique identifier of the account between BCUS and CrowdTwist |
50 |
|
Yes |
|
EMAIL_ADDRESS |
EmailAddress |
User's email address |
254 |
|
No |
|
MOBILE_PHONE_NUMBER |
Text |
User's mobile phone number |
65 |
|
Yes |
|
DATE_CREATED |
Date |
Date/time activity occurred |
|
|
No |
|
EMPLOYEE_ID |
Text |
Custom data attribute |
150 |
|
Yes |
|
CASHIER_ID |
Text |
Custom data attribute |
150 |
|
Yes |
|
REGISTER_ID |
Text |
Custom data attribute |
150 |
|
Yes |
|
STORE_LOC |
Text |
Custom data attribute |
150 |
|
Yes |
|
PLCC |
Boolean |
Custom data attribute |
|
|
Yes |
|
CARDHOLDER_STATUS |
Text |
Custom data attribute |
150 |
|
Yes |
User Redemption
|
Field Name |
Data Type |
Description |
Length |
Primary Key? |
Nullable? |
|---|---|---|---|---|---|
|
ORDER_DATE |
Date |
Date order is processed in CrowdTwist's system |
|
|
No |
|
ORDER_ID |
Text |
The CT Internal redemption order ID |
150 |
Yes |
No |
|
TOTAL_POINTS_REDEEMED |
Number |
Total points redeemed in this order |
|
|
Yes |
|
CATEGORY_NAME |
Text |
The category name of the reward redeemed |
150 |
|
Yes |
|
REWARD_TITLE |
Text |
The name of the reward redeemed |
150 |
|
No |
|
QUANTITY |
Text |
The number of rewards redeemed in the redemption order |
150 |
|
Yes |
|
COLOR |
Text |
Color of item redeemed (if applicable) |
150 |
|
Yes |
|
SIZE_NAME |
Text |
Size of item redeemed (if applicable) |
150 |
|
Yes |
|
REFERENCE_ID |
Text |
The reward reference ID (entered into CrowdTwist’s system by the client) |
150 |
|
Yes |
|
FIRST_NAME |
Text |
|
100 |
|
No |
|
LAST_NAME |
Text |
|
100 |
|
Yes |
|
EMAIL_ADDRESS |
EmailAddress |
|
254 |
|
No |
|
USERNAME |
Text |
User name created |
150 |
|
Yes |
|
THIRD_PARTY_ID |
Text |
The user's third party ID |
150 |
|
Yes |
|
SHIPPING_FIRST_NAME |
Text |
|
100 |
|
Yes |
|
SHIPPING_LAST_NAME |
Text |
|
100 |
|
Yes |
|
SHIPPING_STREET_ADDRESS_1 |
Text |
|
150 |
|
Yes |
|
SHIPPING_STREET_ADDRESS_2 |
Text |
|
150 |
|
Yes |
|
SHIPPING_COUNTRY |
Text |
2-character code (ISO-3166) |
2 |
|
Yes |
|
SHIPPING_STATE |
Text |
Plain English state name |
200 |
|
Yes |
|
SHIPPING_CITY |
Text |
|
200 |
|
Yes |
|
SHIPPING_POSTAL_CODE |
Text |
Postal Code |
75 |
|
Yes |
|
SHIPPING_PHONE_NUMBER |
Text |
Ex. 2035555555 |
65 |
|
Yes |
|
DATE_FULFILLED |
Date |
Date the order is shipped |
|
|
Yes |
|
TRACKING_NUMBER |
Text |
Tracking number associated with the order |
150 |
|
Yes |
|
NOTES |
Text |
Notes added by the client |
150 |
|
Yes |
|
COUPON_CODE |
Text |
Coupon code provided to the client |
150 |
|
Yes |
|
USER_ID |
Text |
|
32 |
|
Yes |