Documentation / Product / Integrations / Google / Google BigQuery

Google BigQuery: Export Users

Export user profiles including user fields and audience memberships from Lytics to Google BigQuery.

Integration Details

  • Implementation Type: Server-side.
  • Implementation Technique: REST API.
  • Frequency: Batch.
  • Resulting data: User fields will be exported to Google BigQuery as either a flat table or multiple tables with join keys for non scalar fields (maps and sets).
    • Field names in BigQuery will not be an exact match of field names in Lytics. Names will be formatted to fit BigQuery's field naming schema.
    • If two field names are transformed to BigQuery format and conflict, one will get an integer appended to the end of the field name, e.g. if both first_name and First Name exist in Lytics and are exported, they'll be written to first_name and first_name1 in BigQuery.
    • If non-scalar fields are exported, each field will be written to a separate table, with a unique identifier id field that can be used to join the main user table to the fields tables.
    • This id is not an identifier that exists in Lytics, but rather it's a hash of all the user unique identifier fields. This is calculated for the express purpose of providing a way to join the non-scalar field tables to the main user table. This field's value may change between exports.

This integration utilizes the Google BigQuery's APIs to send user data. Once the export is started the workflow will:

  1. Check if dataset exists, if not create it in Google BigQuery.
  2. Generate a CSV for each table to be created or updated in BigQuery by:
    1. Scan the selected Audience.
    2. Generate CSV row(s) for each scanned user.
    3. Write CSV row to Google Cloud Storage.
  3. Upload the CSV(s) to BigQuery.

There are a few things to know when running a BigQuery user export:

  • The user export scans the complete Lytics audience and exports all entries into a BigQuery table.
  • The export will run at most once a day, but large audiences may take more than a day to complete.
  • For large audiences (bigger than a million users), users will first be written into a dated user table user_ + audience slug + _ + date as the table builds in BigQuery. As more batches of users are exported, they will be appended to the dated table. Once the complete audience has been sent to BigQuery, the table will be renamed with the date removed.
  • If continuous export is selected, the work will start running again on the next day at the time specified. A new temporary dated audience table will be created in BigQuery alongside the completed non-dated table.
  • If an audience is large enough to take over a day to export and continuous export is selected, the work will not run again until after the previous export completes.

Fields

The fields exported to the Google BigQuery will depend on the Export Non-Scalar Fields and the Export Scalar Fields options in the workflow's configuration. Any user field in your Lytics account may be available for export.

Configuration

Follow these steps to set up and configure an export of users from Lytics to Google BigQuery.

  1. Navigate to Google in the Integrations section of Lytics. google
  2. Select Workflows from the menu on the left.
  3. Select Export Users from the list of workflows.
  4. Select the authorization you would like to use.
  5. From the BigQuery Project input, select the Google BigQuery Project you want to export data to.
  6. From the BigQuery Dataset input, select the Google BigQuery Dataset you want to export data to.
  7. From the Audience input, select the audience you would like to export to BigQuery.
  8. From the Maximum numeric field, select the number of users to be exported. If left blank, all users will be exported.
  9. Select the Audience Membership checkbox to export the audiences each user is a member of. gbq-export-users-config-1
  10. From the Export Non-Scalar Fields input, select a list of non-scalar fields to export. NOTE: these will be created in a separate table.
  11. From the Export Scalar Fields input, select a list of scalar fields to export. NOTE: these will be created in the same table. If none are selected, all fields are exported.
  12. Click on the Show Advanced Options tab to expand the advanced configuration. gbq-export-users-config-2
  13. Select the Keep Updated checkbox to continuously run this export.
  14. Select the Keep Old checkbox to keep daily exports in dated tables.
  15. From the Time of Day input, select the time of day to start export each day.
  16. From the Timezone input, select the timezone for the time of day.
  17. Click Start Export. gbq-export-users-config-3

Exporting table data to BigQuery

Please see the Google BigQuery documentation on Exporting Table Data for options and instructions for exporting data to BigQuery.