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:

1. Click Connections in the left nav bar of your Cloud Connect account and go to Sample BigQuery Connection by Lytics.

2. Take a look at the Explore Tab in the Connection page to see the database tables available in the Connection along with schema details

3. Click Create Audience in the upper left of the Connection page

4. 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.

   1. 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:

      1. lyticsio-bq-sample.sample.customers

          

      2. lyticsio-bq-sample.sample.purchases

          

      3. lyticsio-bq-sample.sample.subscriptions

          

      4. lyticsio-bq-sample.sample.transactions

          

5. Click Run Test Query to ensure your query works

6. Configure the rest of the fields below the Query Builder and the click Create Audience in the lower right of the screen

7. Click Run in the audience page to initiate Audience queries

8. You’ll see the audience summary page and the audience will grow from 0 based on your chosen Query Frequency

9. 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;