Microsoft Customer Insights

ReachFive integrates seamlessly with Microsoft Customer Insights (CI). By leveraging ReachFive’s export definition feature, you can export user identities from the ReachFive Console as well as export user events using the user events endpoint.

This brief tutorial guides you on exporting user profiles from the ReachFive Console and importing those identities into Microsoft Customer Insights, enabling you to better analyze your customer base by leveraging the power of ReachFive combined with Microsoft Customer Insights.

You can also follow these same steps to export Export user events or Export user consent logs.
There are three sequential steps in this tutorial

  1. Exporting ReachFive data

  2. Importing data to Microsoft Customer Insights

  3. Transforming the data

Typically, you want to have Microsoft CI poll data via an API. Therefore, this tutorial is purely to show you one way you might use ReachFive data with Microsoft CI.

Exporting user profiles

The instructions below guide you step-by-step in exporting your users from the ReachFive Console. The visual below shows you a high-level flow of the process.

export jobs user flow

Prerequisites

  • You must have access to the ReachFive Console.

  • You must have a Developer, Manager, or Administrator role.

  • You must have the Export Jobs feature enabled.

Instructions

The instructions below apply to both creating and editing an export job definition.

If editing an existing export job, be sure to select the edit icon instead of creating a new definition.

  1. Go to Settings  Export definitions  User Profiles.

  2. Select New definition.

  3. Under General, give the export job a name and description. Don’t forget to Enable the job.

  4. Under Destination, choose the protocol you wish to use to export the file.

    • SFTP

    • S3

    • GCS

    1. Specify the Server host for the secure FTP site.

    2. Specify the Server port.

    3. Under Authentication method, choose the authentication method type:

      Username and password:

      1. Enter the Username for the server.

      2. Enter the Password for the server.

      OpenSSH:

      1. Enter the Username for the server.

      2. Enter the OpenSSH private key.

        example
        -----BEGIN ENCRYPTED PRIVATE KEY-----
MIIBpjBABgkqhkiG9w0BBQ0wMzAbBgkqhkiG9w0BBQwwDgQI5yNCu9T5SnsCAggA
MBQGCCqGSIb3DQMHBAhJISTgOAxtYwSCAWDXK/a1lxHIbRZHud1tfRMR4ROqkmr4
kVGAnfqTyGptZUt3ZtBgrYlFAaZ1z0wxnhmhn3KIbqebI4w0cIL/3tmQ6eBD1Ad1
nSEjUxZCuzTkimXQ88wZLzIS9KHc8GhINiUu5rKWbyvWA13Ykc0w65Ot5MSw3cQc
w1LEDJjTculyDcRQgiRfKH5376qTzukileeTrNebNq+wbhY1kEPAHojercB7d10E
+QcbjJX1Tb1Zangom1qH9t/pepmV0Hn4EMzDs6DS2SWTffTddTY4dQzvksmLkP+J
i8hkFIZwUkWpT9/k7MeklgtTiy0lR/Jj9CxAIQVxP8alLWbIqwCNRApleSmqtitt
Z+NdsuNeTm3iUaPGYSw237tjLyVE6pr0EJqLv7VUClvJvBnH2qhQEtWYB9gvE1dS
BioGu40pXVfjiLqhEKVVVEoHpI32oMkojhCGJs8Oow4bAxkzQFCtuWB1
-----END ENCRYPTED PRIVATE KEY-----

    1. Specify the Path where the import file is located.

      For example

      <serverhost>/path-to-file/file.csv.

      Example w/variables

      <serverhost>/path-to-file/file-{{datetime}}.csv

      See Export path variables for more details.

    1. Specify the URL for the S3 bucket.

    2. Specify the name Bucket.

    3. Enter the Region for the server.

    4. Enter the Access key for AWS.

    5. Enter the Secret key for AWS.

    6. Specify the Path where the export file is to be sent.

      For example

      <serverhost>/path-to-file/file.csv.

      Example w/variables

      <serverhost>/path-to-file/file-{{datetime}}.csv

      See Export path variables for more details.

    1. Specify the Project ID for the Google Cloud Storage.

    2. Specify the App name.

    3. Enter the User name for the server.

    4. Specify the name Bucket.

    5. Enter the Credentials in JSON format.

    6. Specify the Path where the export file is to be sent.

      For example

      <serverhost>/path-to-file/file.csv.

      Example w/variables

      <serverhost>/path-to-file/file-{{datetime}}.csv

      See Export path variables for more details.

  5. Still under Destination, if desired, click Overwrite existing file.

    What happens if I do that?

    overwrite existing file

    • When you check the box, the existing file is replaced by the new file. Data in the existing file is completely replaced.

    • If left unchecked, the new data is added to the existing file.

  6. Under Schedule, if desired, use a cron expression for scheduling the job.

  7. Under File format, select the file format type you wish to export. This will be either JSONL or CSV.

    For the purposes of this tutorial, choose CSV as your file format.

    • JSONL

    • CSV

    1. Define the fields to include in the export. You can include all attributes of the user profile data model including custom fields and consents.

    2. Choose the Encoding standard for your JSONL file. This is either UTF-8 or ISO-8859-1.

      If using ISO-8859-1, note that there are limitations around characters that it encodes. It is primarily for Western European languages. It is not intended for non-Western European languages and also has limitations for certain digital characters and symbols. When a character is unrecognized, it is replaced with a question mark, ? in the export. If in doubt, choose UTF-8.

    All fields of the user profile data model will be exported, including consents and custom_fields.

    1. Define the columns to include in the export. You can include all attributes of the user profile data model including custom fields and consents.

      You can drag and drop the fields to sort the order in which you want the columns displayed as part of the export job.

    2. Choose the Encoding standard for your CSV file. This is either UTF-8 or ISO-8859-1.

      If using ISO-8859-1, note that there are limitations around characters that it encodes. It is primarily for Western European languages. It is not intended for non-Western European languages and also has limitations for certain digital characters and symbols. When a character is unrecognized, it is replaced with a question mark, ? in the export. If in doubt, choose UTF-8.

    3. Enter your Delimiter. The default is ;.

    4. Enter your Quote char. The default is ".

    5. Enter your Escape character. The default is \.

    The data for custom fields that contain the multiple type (such as tags) are separated by the same Delimiter that is configured in the job definition.

Importing data to Microsoft Customer Insights

Now that you have exported the user data from ReachFive in the first step, you can import the CSV file into Microsoft Customer Insights account.

Prerequisites

  • You must have a Microsoft Customer Insights account.

  • You must have a 25 account.

  • You must have exported your user identities from the ReachFive Console as a CSV file.

Instructions

With your freshly-exported CSV file to hand…​

Prepare the file

For the sake of this tutorial, we are using a Google Spreadsheet to import into Customer Insights. However, as noted in the introduction, you will likely poll data via an API. This is for display purposes only.

  1. Open Google Sheets.

  2. Go to File  Import and import your CSV file.

  3. Update the share settings to ensure that anyone with the link has Editing privileges.

    See the Google help article for assistance if needed.

  4. Copy the URL of your Google Sheet.

  5. Replace edit#gid=<gid> with export?format=csv&gid=<gid>.

Your final URL before importing to Microsoft CI should be:
Next Import the file


Import the file

  1. Log in to your Microsoft Customer Insights account.

    microsoft ci welcome

  2. Go to Data  Data sources.

  3. Select Add data source.

  4. Choose Import data.

    1. Enter the name of your data source. Click Next.

  5. Choose the Web API option.

    1. Paste the URL from your spreadsheet.

      microsoft ci import data

  6. Make sure the encoding and delimiter match those from your exported ReachFive CSV file in the file preview.

  7. Click Transform data.

Transforming the data

If your CSV file has certain characters such as underscores, hyphens, or full-stops (periods) between column names, you may need to clean those columns in order to best leverage Customer Insights.

Prerequisites

Instructions

  1. Create a new Get data  Blank query.

    microsoft ci new data query

  2. Copy the following code into that query:

    let
  CleanColumns = (TableColumnFix as table) as table=>
    let
    ListObjectWithCleaningLogic =
    let
    Source = TableColumnFix,
    DemoteHeaders = Table.DemoteHeaders(Source){0},
    CreateTableFromRecord = Record.ToTable(DemoteHeaders),
    DuplicateOldColumnNames = Table.DuplicateColumn(CreateTableFromRecord, "Value", "Value - Copy"),
    RenameColumns = Table.RenameColumns(DuplicateOldColumnNames,{{"Value", "OldColumnName"}, {"Value - Copy", "NewColumnName"}}),
    TrimmedText = Table.TransformColumns(RenameColumns,{{"NewColumnName", Text.Trim, type text}}),
    ReplacePoints0 = Table.ReplaceValue(TrimmedText, ".", "", Replacer.ReplaceText, {"NewColumnName"}),
    ReplacePoints1 = Table.ReplaceValue(ReplacePoints0, ",", "", Replacer.ReplaceText, {"NewColumnName"}),
    ReplacePoints = Table.ReplaceValue(ReplacePoints1, "_", "", Replacer.ReplaceText, {"NewColumnName"}),
    CleanText = Table.TransformColumns(ReplacePoints, {{"NewColumnName", each Text.Clean(_), type text}}),
    #"Added Custom" = Table.AddColumn(CleanText, "NestedLists", (_)=> {_[OldColumnName],_[NewColumnName]})[NestedLists]
in
    #"Added Custom",
    CleanColumnNames = Table.RenameColumns(TableColumnFix,ListObjectWithCleaningLogic)
in
    CleanColumnNames
in
  CleanColumns

  3. Click Next to validate the query.

  4. Copy the name of your newly-created function. This is typically found on the right of the screen under the Name label.

  5. Paste the following into the textarea of your original query.

    let
    Source = Csv.Document(Web.Contents("YOUR_GOOGLE_SHEET_URL"), (1)
    [Delimiter = ";", Columns = 244, QuoteStyle = QuoteStyle.None, Encoding = 65001]),
    #"NAME" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]), (2)
    result = CleanColumns(#"NAME") (2) (3)
in
    result
    1 Source must match your source CSV/Google Sheet import.
    2 #"NAME" should match the name of your file.
    3 CleanColumns must match the name of your newly-created function.
Why did we do that?

We removed special characters in column names to better integrate/clean the data and prepare for data unification within Microsoft CI.

For more on data unification with Microsoft CI, see their article on Data unification.

Export path variables

In some cases, you might want to add meta information to the path of an export file for identification and analysis purposes. With export path variables, you can do just that. Wherever you would like a variable in the path, simply add it to the name of the file as shown here:

<serverhost>/path-to-file/{{datetime}}-file-{{job_execution_id}}.csv (1)
1 In this example, {datetime} and {job_execution_id} are inserted as part of the file name as part of the export.
The export path variable feature is available for all export types including exporting profiles, user events, and consents.

Available export path variables

The following variables are available for you to use as part of your export.

  • {{datetime}} is the job execution date in the format of yyyy-MM-dd HH-mm-ss.

  • {{date}} is the job execution date in the format of yyyy-MM-dd.

  • {{time}} is the job execution time in the format of HH-mm-ss.

  • {{*datePattern}} lets you set the date pattern as shown here.

  • {{account}} is the name of the account where the export occurred.

  • {{job_execution_id}} is the specific id of the export being executed.