Mirror and use model-based data

Log in to Google BigQuery.

Select BigQuery > Studio.

Select your project, dataset and table. In the Schema tab, you see an overview of the schema for the event data.

Select Query.

Run a sample query in the query editor, where project is the name of your project and datasets is the name of your datasets:

SELECT * FROM `project.datasets.eventdata` LIMIT 100

Run the following SQL statement in the query editor to check the setting, where project is the name of your project and datasets is the name of your datasets:

SELECT
   table_name,
   MAX(CASE WHEN option_name = 'enable_change_history' THEN option_value END) AS enable_change_history
FROM `project.datasets.INFORMATION_SCHEMA.TABLE_OPTIONS`
WHERE table_name = 'eventdata'
GROUP BY table_name
ORDER BY table_name;

If the result is not TRUE, use the following SQL statement to enable change history, where project is the name of your project and datasets is the name of your datasets:

ALTER TABLE `project.datasets.eventdata`
SET OPTIONS (enable_change_history = TRUE);

In the Adobe Experience Platform UI, in the left rail, select Schemas within Data Management.

Select Create schema.

From the drop-down menu, select Model-based.

If you see a popup with the option to select between Create manually or Upload a DDL file:

1. Select select Create manually.

   

2. Select Next.

In the Schemas > Create model-based schema interface:

1. Enter a Schema display name. For example: Sample Event Feed Schema.

2. Enter a Description. For example: Sample event feed schema for a model-based schema.

3. Select Time series as the Schema behavior. You select Time series for time-series based data and Record for record based data. The behavior defines the structure of the schema and the properties that are included.

   Experience Platform Data Mirror for Customer Journey Analytics is mostly used for time series data (for example, event data).

   

4. Select Finish.

In the Schemas > Sample Event Feed Schema interface, you see a warning that model-based schemas support ingestion as change rows.

Ingestion as change rows is also known as change data capture (CDC). To support change data capture, the schema requires:

• Primary key.
• Version descriptor.
• Timestamp descriptor for time-series data.

Select next to Sample Event Feed Schema to start to add fields to the schema. Add the following fields with data type and additional attributes to the schema.

table 0-row-4 1-row-4 2-row-4 3-row-4 4-row-4 5-row-4 6-row-4 7-row-4
Field name	Display name	Type	Additional attributes
id	Id	Integer	Version descriptor
orders	Orders	Integer
pagename	Page Name	String
personid	Person Id	String	Primary key Identity Select CRMID for Identity namespace.
revenueamount	Revenue Amount	Double
timestamp	Timestamp	DateTime	Timestamp descriptor
trackingcode	Tracking Code	String

• The id field is configured as Version descriptor.

  

• The personid field is configured, together with timestamp as the Primary key. Select Create composite primary key to create a composite key.

  

  The personid field is also configured as an Identity, with CRMID as the Identity namespace.

  

• The timestamp field is configured, together with personid field as the Primary key. The timestamp field is also configured as Timestamp descriptor. You only need to define a field as Timestamp descriptor for time series model-based data.

  

If you have defined Primary key, Version descriptor and Timestamp descriptor correctly, the warning on top of the schema definition disappears.

Select Save to save your schema.

Select the table from the list of tables. For example: eventdata.

You see a sample of the data displayed for verification.

Select Next to continue.

Select Enable change data capture. A Change data capture requirement information box displays with more information.

Select New dataset for Target dataset to create a new dataset that contains the mirrored data.

Enter an Output dataset name. For example: event-data-mirror.

Select the model-based schema that you created earlier from the Schema drop-down menu. For example: Sample Event Feed Schema.

Specify other details.

Select Next.

Map the fields. From the schema in Google BigQuery (Source data) to the fields in the schema that you have defined in Experience Platform (Target fields).

If all fields are mapped correctly, select Next to continue.

Specify the Frequency and Interval to schedule the synchronization of the mirrored data.

Specify the Start time for the schedule.

Select Next to continue.

Review the configuration for the source connector.

Select Finish. You are directed to the configured dataflow.

Select Connections from the Data Management menu.

Select Create new connection.

Specify the required Connection name, Sandbox, Average number of daily evens, and other optional parameters.

Select Add datasets.

1. In the Select datasets step of Add datasets:

   1. Select the dataset that contains the mirrored data. For example: event-data-mirror. The dataset has Model as the Dataset type.

      

   2. Add any additional datasets that are relevant for the connection.

   3. Select Next.

2. In the Dataset settings step of Add datasets:

   For the event-data-mirror model-based dataset

   1. Select Event as the Dataset type.

   2. Select the PersonId field as the Person ID.

   3. Timestamp is automatically populated as the Timestamp.

   4. Select Other as the Data source type.

   5. Enter Google BigQuery Event Data as the Data source description.

   6. Specify other details, like Import all new data and Backfill all existing data.

      

   Optionally, specify details for other datasets.

3. Select Add datasets.

Select Save.

In the Customer Journey Analytics interface, select Data views, optionally from Data management, in the top menu.

Select Create new data view.

In the Configure step:

1. Select your connection from the Connection list.

2. Name and (optionally) describe your connection.

3. Select Save and continue.

In the Components step:

1. Add any schema field and/or standard component that you want to include to the METRICS or DIMENSIONS component boxes. Ensure you add relevant fields from the dataset that contains the mirrored data. To access those fields:

   1. Select Event datasets.

   2. Select Adhoc & Model-based fields.

   3. Drag and drop fields from the model-based schemas onto METRICS or DIMENSIONS.

      

2. Define derived fields for fields that do not have the proper type, are not in the proper format, or you want to modify for other reasons. For example, for Revenue Amount.

   1. Select Create derived field.

   2. In the derived field editor:

      1. Define a new Revenue Amount (Numeric) field, like below.

         

      2. Select Save.

   3. Drag the new Revenue Amount (Numeric) derived field and drop the field in METRICS.

      

3. Select Save and continue.

In the Settings step:

Leave the settings as they are and select Save and finish.

In the Customer Journey Analytics interface, select Workspace in the top menu.

Select Projects in the left navigation.

Select Create project. In the popup:

1. Select Blank Workspace project.

2. Select Create.

In the New project workspace, ensure that your data view is selected. That data view links to the connection that contains the mirrored data.

To create your first report, drag and drop dimensions and metrics on the Freeform table in the Freeform panel. For example, drag Revenue Amount (Numeric) onto Drag a metric here. And drag PersonId and drop the field on the first column header. Make some other adjustments as you see fit.

The final result is an overview of profiles and their revenues based on mirrored data originating from a Google BigQuery table.