Hello World! Test Cloud Connect with the Sample BigQuery Connection by Lytics
Interested in testing Cloud Connect with sample data? You’re in luck! The Sample BigQuery Connection by Lytics is available for creating test audiences before connecting to your own data warehouse. Read on for details on creating your first Cloud Connect Hello World Audience!
The Sample BigQuery Connection includes four tables with sample data for customers, purchases, transactions, and subscriptions. These types of data represent a few of the many use cases where Cloud Connect can help activate your warehouse data.
Steps to Get Started
Here are step by step instructions to create your first audience:
Click Connections in the left nav bar of your Cloud Connect account and go to Sample BigQuery Connection by Lytics.
Take a look at the Explore Tab in the Connection page to see the database tables available in the Connection along with schema details
Click Create Audience in the upper left of the Connection page
Give your Audience a Name and Description then start building your SQL query in the Query Builder. You can use a SQL query from one of the Sample Use Cases below or feel free to experiment with your own queries. The Schema Explorer next to the Query Builder provides details to help construct your queries.
Note: Table names must follow exact syntax used in the data warehouse, BigQuery in this case. For the sample tables use the following table names:
Click Run Test Query to ensure your query works
Configure the rest of the fields below the Query Builder and the click Create Audience in the lower right of the screen
Click Run in the audience page to initiate Audience queries
You’ll see the audience summary page and the audience will grow from 0 based on your chosen Query Frequency
Connect to your own data warehouse and start activating your data to all of the Destinations available in the Cloud Connect
Happy Audience Building!
Sample Use Cases & SQL
Below are a few sample use cases along with SQL queries you can use to build test audiences.
Select all users who have made a purchase
This query returns users and purchase details from the purchases table. This could be used to support an engagement strategy with all existing customers, supported by multiple marketing Cloud Connect Destinations.
SELECT DISTINCT email, first_name, last_name, purchase_date FROM `lyticsio-bq-sample.sample.purchases`;
Select users who’ve purchased in the last year but don’t have a subscription
This query joins the purchases and subscriptions table and returns users who have made a purchase in the last year but do not have a subscription (b.account_id is NULL). This is an example where you might run a campaign to upsell recent purchasers to a subscription product via an email nurture campaign in HubSpot.
SELECT DISTINCT a.email AS email, a.first_name, a.last_name, a.accountid, a.purchase_date, a.product_name, b.subscription_status FROM `lyticsio-bq-sample.sample.purchases` a LEFT OUTER JOIN `lyticsio-bq-sample.sample.subscriptions` b ON a.accountid = b.account_id WHERE b.account_id is NULL AND date_diff(current_date(),a.purchase_date, day) < 365 GROUP BY a.email, a.accountid, a.purchase_date, a.product_name, b.subscription_status, a.first_name, a.last_name;
Select users who have not made a purchase
This query returns users where information has been collected but the customer has not made a purchase. This could be an opportunity for a first time buyer promotion via a social media ad with Facebook.
SELECT DISTINCT email, first_name, last_name, customer_type, haspastpurchases FROM `lyticsio-bq-sample.sample.customers` WHERE haspastpurchases = FALSE;
Select users with an average annual revenue greater than $500
This query returns a list of high LTV customers. This list might support a loyalty monthly newsletter campaign supported by MailChimp.
SELECT DISTINCT email, first_name, last_name, accountid, avgannualrev FROM `lyticsio-bq-sample.sample.customers` WHERE avgannualrev >= 500;
Select users who have not purchased in the last 180 days
This query uses a nested select statement with date filtering to return a list of users who have not purchased in the last 180 days. This list could be used to support an ad campaign in Google Ads.
SELECT DISTINCT email, first_name, last_name, MAX (purchase_date) FROM `lyticsio-bq-sample.sample.purchases` WHERE email NOT IN ( SELECT email FROM `lyticsio-bq-sample.sample.purchases` WHERE date_diff(current_date(),purchase_date, day) < 180) GROUP BY email, first_name, last_name;