Google BigQuery: Export Audiences

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

• 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 job 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.

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.

Follow these steps to set up and configure an export of users from Lytics to Google BigQuery. If you are new to creating jobs in Lytics, see the Jobs Dashboard documentation for more information.

1. Select Google Cloud from the list of providers.
2. Select the Export Audiences job type from the list.
3. Select the Authorization you would like to use or create a new one.
4. Enter a Label to identify this job you are creating in Lytics.
5. (Optional) Enter a Description for further context on your job.
6. From the BigQuery Project input, select the Google BigQuery Project you want to export data to.
7. From the BigQuery Dataset input, select the Google BigQuery Dataset you want to export data to.
8. From the Audience input, select the audience you would like to export to BigQuery.
9. From the Maximum numeric field, select the number of users to be exported. If left blank, all users will be exported.
10. Select the Audience Membership checkbox to export the audiences each user is a member of.
11. 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.
12. 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.
13. Click on the Show Advanced Options tab to expand the advanced configuration.
14. Select the Keep Updated checkbox to continuously run this export.
15. Select the Keep Old checkbox to keep daily exports in dated tables.
16. From the Time of Day input, select the time of day to start export each day.
17. From the Timezone input, select the timezone for the time of day.
18. Click Start Export.

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