6. Anomaly Detection - Yesterday vs Past Week Average

In these instructions, we’ll focus on how to use SQL to identify when there is a large change in metrics comparing yesterday to the past week average.

The date range to compare against can easily be switched from 7 days to any option (past 30, past 3, past 2, etc)

Single out days with huge changes using Alli Custom Alerts in Marketplace. Instructions for how to create a custom alert can be found in the set-up instructions for the app in Marketplace and in the doc below.

Pre-requisites:

To leverage the SQL in this alert, your client must have access to either core datasources for the channel you are alerting for (ie search_campaign, display_campaign , or social_campaign core data views) OR have access to platform data that contains date and at least one column of the metric you want to alert off of (for example. could be clicks, conversions, impressions, orders, etc)

Instructions: Alli Data

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

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
To adjust the comparison date range, adjust the 7 in line 5 to the date range you’d like. Ie if 30 days back: ORDER BY date ROWS BETWEEN 30 PRECEDING AND 1 PRECEDING) AS past_avg,
The examples below leverage conversion columns; to alert on a different metric, switch the metric columnname within sum(conversions) or sum(AllConversions) to the metric you’d like. Ie to alert on clicks, sum(clicks) as total_metric

You can transfer this alert to any datasource + metric simple by adjusting the datasource & metrics in lines 8-11

Example for social

CODE

(SELECT date, sum(impressions) AS total_metric
      FROM CLIENTHERE_core.social_campaign
      WHERE date >= CURRENT_DATE - 30
      GROUP BY date)

Core Data Option

CODE

SELECT *
FROM
  (SELECT date, total_metric,
                avg(total_metric) OVER(
                       ORDER BY date ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING) AS past_avg,
                (ABS(past_avg - total_metric) / ((total_metric+ past_avg)/2)) * 100 AS percent_change
   FROM
     (SELECT date, sum(conversions) AS total_metric
      FROM CLIENTHERE_core.search_campaign
      WHERE date >= CURRENT_DATE - 30
      GROUP BY date)
   ORDER BY DATE DESC)
WHERE percent_change >= 30
and date = current_date -1

Non-Core Data Example (Using Adwords Insights)

CODE

SELECT *
FROM
  (SELECT date, total_metric,
                avg(total_metric) OVER(
                        ORDER BY date ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING) AS past_avg,
                (ABS(past_avg - total_metric) / ((total_metric+ past_avg)/2)) * 100 AS percent_change
   FROM
     (SELECT date, sum(AllConversions) AS total_metric
      from CLIENTHERE.adwords_insights_campaign
      WHERE date >= CURRENT_DATE - 30
      GROUP BY date)
   ORDER BY DATE DESC)
WHERE percent_change >= 30
and date = current_date -1

Instructions: Setting Up Your Custom Alert

Navigate to the Marketplace within Alli and select ‘Discover’
Either using the Search feature, or scrolling through the available options, select Alli Custom Alert
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
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!
Use the + button under Schedules to set the schedule of when your Custom Alert will run
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)
To finish your Custom alert, select Save or Save & Run if you want to run your alert manually