Google Display vs Search Network Targeting

Pre-requisites:

• To leverage the SQL in this alert, your client must have access to both {client}_core.search_campaign and {client}_core.display_campaign core data views, or the cross channel view {client}_core.crosschannel_campaign_w_dsp

• Below you will find 2 SQL options; both options give the same results. We recommend the simplified Option #1, but you can utilize either of the two options listed.

Instructions: Alli Data

1. Create your SQL

   1. Login to Alli Data and select the client you want to create an query for

   2. Navigate to Reports → Explorer and select New Data Report

      

   3. Give your report a Name and update the Report Type to Custom Redshift

      

   4. Copy and paste the SQL Example in from below and update anything within and including { } to match your client settings

   5. Once your SQL is updated, click Publish to update the query

   6. Click Preview to ensure the output is as expected

SQL Example: Option 2 (Using Alli Data Library Cross Channel Views)

select account_id, account_name, campaign_id, campaign_name
from CLIENTHERE_core.crosschannel_campaign_w_dsp
where platform = 'Google Adwords'
and channel = 'Search'
and publisher_platform like 'Display%'
and date = current_date - 1
group by account_id, account_name, campaign_id, campaign_name
having sum(cost) > 0

SQL Example: Option 2 (Using _core.display_campaign & _core.search_campaign datasources

Context:

• CLIENTHERE should correspond to the redshift name of the client in Alli Data. Ie Six Flags → six_flags, Planet Fitness → planet_fitness, Midas → midas, etc

SELECT DISTINCT account_id,
                account_name,
                campaign_id,
                campaign_name
FROM CLIENTHERE_core.search_campaign
WHERE campaign_id IN
    (SELECT campaign_id
     FROM CLIENTHERE_core.search_campaign
     WHERE ENGINE = 'Google Adwords'
       AND date = CURRENT_DATE - 1
     GROUP BY 1
     HAVING sum(cost) > 0 
     
     INTERSECT 
     
     SELECT campaign_id
     FROM CLIENTHERE_core.display_ad
     WHERE platform = 'Google Adwords'
       AND date = CURRENT_DATE - 1
     GROUP BY 1
     HAVING sum(cost) > 0)
ORDER BY campaign_name

Instructions: Setting Up Your Custom Alert

1. Navigate to the Marketplace within Alli and select ‘Discover’

2. Either using the Search feature, or scrolling through the available options, select Alli Custom Alert

   

3. The Set-Up instructions drop down will also contain instructions and tips for setup that mirror this page and can be used as a reference. Once you are confident, click Install App

   

4. Name your pallet and and place your email (or the Custom Alerts owner) in the Notification Emails section on the left. If there is an error with your app, this will be who Alli Marketplace emails, so don’t forget this step!

   

5. Use the + button under Schedules to set the schedule of when your Custom Alert will run

6. Fill out your template variables. Explanations of each are provided beneath the name within the app, and also below

   1. View Name

      • The full name of the Alli Data view or Bigquery view (EX: test.datasource) OR

      • Your full SQL query, copy and pasted, surrounded by parentheses EX: (select * from test.datasource)

   2. Alert_when_results

      • Do you want to be alerted (via slack) when your query (the view you created) returns results? - this will most likely be true, if so, leave as ‘Yes’

      • If you would like to be alerted when your result doesn’t return results, select ‘No’.

   3. File_name

      • The name you'd like the file to be downloaded as when you receive the slack or email message

   4. Client

      • What client is this for? (Should typically match the client you choose in Marketplace, but it’s up to you)

   5. Slack_channel

      • The slack channel to send the message to, without the # (ie test, not #test)

      • Alli Alerts (@Alli Insights) must be invited, and if you are sending to a private channel, you must also invite @pmgadmin.

      • For Moroch users, you will be utilizing email communication at the moment, so you can leave this blank or place troubleshooting in the box

   6. Slack_users

      • the pmg emails of the users you’d like tagged EX) jamie.reinhard@pmg; will tag Jamie EX) jamie.reinhard@pmg.com,garrett@pmg.com; will tag both Jamie & Garrett

      • For Moroch users, either leave this blank or group:support-allialerts in the box

      • a user group you’d like tagged EX) group:alli-alert-support; will tag the @alli-alert-support user group. Ensure you prepend group: before your user group name

      • a combination of both EX) jamie.reinhard@pmg.com,group:alli-alert-support

      • To tag multiple, comma separate without spaces the emails or user group.

   7. Message

      • What message do you want to see when this issue happens? Be descriptive: this will automatically include the tagged users above at the front, and the downloadable link at the end.

   8. Origin

      • is this view (or SQL) in BigQuery or Redshift (aka Alli Data)

   9. Communication Type

      • How would you like the alert delivered? Alerts can be delivered: slack only, email only, or slack & email

      • For Moroch users, select email only

      **At this time, if your alert is bundled and you select either email only or slack & email, the email alert will not be bundled, it will be sent immediately

   10. Service

       • Classify what channel or business this is alert is for: social, search, display, seo, analytics, or other

   11. Slack bundling

       • Bundles are sent at 11 am & 3 pm (Central)

       • Select “yes, include slack messages in bundle” for you alerts to be sent in the next bundle.

       • Select “no, do not include slack messages in bundle” if you want your alert to be sent immediately.

       • Moroch users cannot opt into message bundling at the moment, select no, do not include slack messages in bundle

       • *Note: if your alert is set to run after the last bundle (after 3pm) it will be included in the next bundle at 11 am the next day

   12. Priority

       • What is the priority of this alert? low, medium, high

   13. Email Subject

       • If you’ve selected email only or email & slack as your communication type, this will be the subject of the email alerts

   14. Email Recipients

       • Comma separated list of email recipients of email alerts (if selected as communication type)

   15. Email Body

       1. Body/message of the email alert (if selected as communication type)

7. To finish your Custom alert, select Save or Save & Run if you want to run your alert manually