Onboarding Alli Budget Consolidation

GPT Assistant

Alli Budget Consolidation Onboarding GPT

We have created a CustomGPT to help collect the information for each step for you. While it won’t be able to assist with the step by step, using it ensures you have all the details at the end for submitting your onboarding ticket. It will always link you to this document. It also helps to format the ticket so you’re not spending a lot of time creating it. This is the recommended route. However, if ChatGPT isn’t working for whatever reason, all the steps are listed here too.

Google Sheet

Make a copy of this google sheet: Alli Budget Consolidation Template
Make sure to keep the same Share settings. You can change the PMG group to Editor
Update the Sheet name but do not edit tab names or headers
Share Sheet ID and Link with the chat bot or note the Sheet ID and Link

Populate IDs tab

Step 1: Create a Report in Alli

Use the following SQL query to create a report in Alli. You will need to make sure it has your client’s core table name where it says client_name_core. The GPT can update this SQL for you if you provide your client’s Core table name or just find and replace in a text editor.

You may want to limit the amount of data you bring in either by only bringing in certain campaigns, dates, or a hard limit (LIMIT 2000) per platform. It is client dependent. The max recommended rows to have in the IDs column is 10,000 since that is google’s limit for what it can read/write

SQL

WITH tab AS (
  -- Meta data
  SELECT
    source,
    id,
    account_name,
    account_id AS meta_account_id,
    campaign_id AS meta_campaign_id,
    campaign_name AS meta_campaign_name,
    adgroup_id AS meta_adgroup_id,
    adgroup_name AS meta_adgroup_name,
    ad_id AS meta_ad_id,
    ad_name AS meta_ad_name,
    NULL AS pinterest_account_id,
    NULL AS pinterest_campaign_id,
    NULL AS pinterest_campaign_name,
    NULL AS pinterest_adgroup_id,
    NULL AS pinterest_adgroup_name,
    NULL AS pinterest_ad_id,
    NULL AS pinterest_ad_name,
    NULL AS tiktok_account_id,
    NULL AS tiktok_campaign_id,
    NULL AS tiktok_campaign_name,
    NULL AS tiktok_adgroup_id,
    NULL AS tiktok_adgroup_name,
    NULL AS tiktok_ad_id,
    NULL AS tiktok_ad_name,
    NULL AS snapchat_account_id,
    NULL AS snapchat_campaign_id,
    NULL AS snapchat_campaign_name,
    NULL AS snapchat_adgroup_id,
    NULL AS snapchat_adgroup_name,
    NULL AS snapchat_ad_id,
    NULL AS snapchat_ad_name
  FROM
    client_name_core.ad_performance
  WHERE
    platform = 'Facebook Ads'
    AND date >= DATEADD(day, -30, GETDATE())
  UNION ALL
  -- Pinterest data
  SELECT
    source,
    id,
    account_name,
    NULL AS meta_account_id,
    NULL AS meta_campaign_id,
    NULL AS meta_campaign_name,
    NULL AS meta_adgroup_id,
    NULL AS meta_adgroup_name,
    NULL AS meta_ad_id,
    NULL AS meta_ad_name,
    account_id AS pinterest_account_id,
    campaign_id AS pinterest_campaign_id,
    campaign_name AS pinterest_campaign_name,
    adgroup_id AS pinterest_adgroup_id,
    adgroup_name AS pinterest_adgroup_name,
    ad_id AS pinterest_ad_id,
    ad_name AS pinterest_ad_name,
    NULL AS tiktok_account_id,
    NULL AS tiktok_campaign_id,
    NULL AS tiktok_campaign_name,
    NULL AS tiktok_adgroup_id,
    NULL AS tiktok_adgroup_name,
    NULL AS tiktok_ad_id,
    NULL AS tiktok_ad_name,
    NULL AS snapchat_account_id,
    NULL AS snapchat_campaign_id,
    NULL AS snapchat_campaign_name,
    NULL AS snapchat_adgroup_id,
    NULL AS snapchat_adgroup_name,
    NULL AS snapchat_ad_id,
    NULL AS snapchat_ad_name
  FROM
    client_name_core.ad_performance
  WHERE
    platform = 'Pinterest Ads'
    AND date >= DATEADD(day, -30, GETDATE())
  UNION ALL
  -- TikTok data
  SELECT
    source,
    id,
    account_name,
    NULL AS meta_account_id,
    NULL AS meta_campaign_id,
    NULL AS meta_campaign_name,
    NULL AS meta_adgroup_id,
    NULL AS meta_adgroup_name,
    NULL AS meta_ad_id,
    NULL AS meta_ad_name,
    NULL AS pinterest_account_id,
    NULL AS pinterest_campaign_id,
    NULL AS pinterest_campaign_name,
    NULL AS pinterest_adgroup_id,
    NULL AS pinterest_adgroup_name,
    NULL AS pinterest_ad_id,
    NULL AS pinterest_ad_name,
    account_id AS tiktok_account_id,
    campaign_id AS tiktok_campaign_id,
    campaign_name AS tiktok_campaign_name,
    adgroup_id AS tiktok_adgroup_id,
    adgroup_name AS tiktok_adgroup_name,
    ad_id AS tiktok_ad_id,
    ad_name AS tiktok_ad_name,
    NULL AS snapchat_account_id,
    NULL AS snapchat_campaign_id,
    NULL AS snapchat_campaign_name,
    NULL AS snapchat_adgroup_id,
    NULL AS snapchat_adgroup_name,
    NULL AS snapchat_ad_id,
    NULL AS snapchat_ad_name
  FROM
    client_name_core.ad_performance
  WHERE
    platform = 'TikTok Ads'
    AND date >= DATEADD(day, -30, GETDATE())
  UNION ALL
  -- Snapchat data
  SELECT
    source,
    id,
    account_name,
    NULL AS meta_account_id,
    NULL AS meta_campaign_id,
    NULL AS meta_campaign_name,
    NULL AS meta_adgroup_id,
    NULL AS meta_adgroup_name,
    NULL AS meta_ad_id,
    NULL AS meta_ad_name,
    NULL AS pinterest_account_id,
    NULL AS pinterest_campaign_id,
    NULL AS pinterest_campaign_name,
    NULL AS pinterest_adgroup_id,
    NULL AS pinterest_adgroup_name,
    NULL AS pinterest_ad_id,
    NULL AS pinterest_ad_name,
    NULL AS tiktok_account_id,
    NULL AS tiktok_campaign_id,
    NULL AS tiktok_campaign_name,
    NULL AS tiktok_adgroup_id,
    NULL AS tiktok_adgroup_name,
    NULL AS tiktok_ad_id,
    NULL AS tiktok_ad_name,
    account_id AS snapchat_account_id,
    campaign_id AS snapchat_campaign_id,
    campaign_name AS snapchat_campaign_name,
    adgroup_id AS snapchat_adgroup_id,
    adgroup_name AS snapchat_adgroup_name,
    ad_id AS snapchat_ad_id,
    ad_name AS snapchat_ad_name
  FROM
    client_name_core.ad_performance
  WHERE
    platform = 'Snapchat Ads'
    AND date >= DATEADD(day, -30, GETDATE())
)
SELECT * 
FROM tab
ORDER BY source ASC;

Save and Publish the Report:

Save the report and publish it in Alli.

Set Export to Google Sheets:
- First make sure you share the sheet with your Alli Client Google Service Account found in the Client Settings tab.
- Click the three dots on the top right of the report in Alli.
- Choose to export the report to Google Sheets.
- If you are having issues or have questions about limitations you can review the How to connect data to Google Sheets using Data Explorerdocument
- You will need to provide the Google Sheet ID, which can be found between /d/ and /edit in the Google Sheet URL.
  - For example:
    - https://docs.google.com/spreadsheets/d/1o1ex3c124m7UE9-f4FmgzZmkqalgxhCw1ANjzsqwIo8/edit?gid=960898496#gid=960898496
    - sheet id here is 1o1ex3c124m7UE9-f4FmgzZmkqalgxhCw1ANjzsqwIo8
- Set the tab name to IDs in the Google Sheet where the data will be exported.
- If you are having issues check with the
Verify Data Export:
- Ensure the data is being exported to the IDs tab as scheduled (daily or hourly).
- Feel free to delete the Copy of IDs sheet that is created.
Test Dropdown and Auto-Populate Functionality:
- In the "Budget Sheet" and "Ad Status" tabs, select a platform and level to change.
- Verify that the dynamic dropdown for names appears correctly.
- Ensure that the corresponding ID is auto-populated based on the selected name.

How to connect data to Google Sheets using Data Explorer

If you have issues with this step you can note that in the ticket you will submit later but please try first or it will lead to a delay.

Follow the Alli Data document linked here:

https://agencypmg.atlassian.net/wiki/x/JoCMyg

Marketplace Application

Navigate to the Social Consolidation app in your client on Alli. (Marketplace > Apps > Search “Social Consolidation”)

Hit the + symbol to install the application
Follow the set up steps noted in the application
1. Add Environment Variable SHEET_ID -
  1. This is your Google sheet ID. This is the only variable your team needs to set up. Hit the eye icon to make sure it matches your Google Sheet ID.
2. Click the checkbox for Test_Run and the platforms you plan to use.
3. Hit SAVE. If you hit run, it’s fine but it’s not going to work at this point.
Note the Marketplace Link. If you are using the GPT you will share it with it to keep track for you.

Slack

Go to the slack channel you want error messages in
Open channel setting and select Integrations tab.
Select Add Apps. Search for “Alli Budget Consolidation” and hit Add.
Note the slack channel and the emails of who should be notified of errors. If you are using the GPT you will share it with it to keep track for you.

Submit Onboarding Ticket

Use the CustomGPT to ensure all the info you collected + more is captured. The GPT will format and guide you through the process. Using the GPT will ensure you have it all so it is recommended and preferred route

Submission Details if GPT is not working:

Go to the Alli Service Desk (https://agencypmg.atlassian.net/servicedesk/customer/portal/1/group/3 )
Select > General Support Help
Use the following for the fields:
1. Priority: Low
2. Feature: Marketplace App
3. Summary: Onboarding Alli Budget Consolidation Account Details - <Client Name>
4. Fill out the below with the required details:

Tool Onboarding Request For: Alli Budget Consolidation

Client: <insert client name>

Point of Contact: <insert point of contact>

Account Information:

Meta:

Managed by: <PMG or Client?>
Account ID: <insert account ID>
Account Name: <insert account name>

TikTok:

Managed by: <PMG or Client?>
Account ID: <insert account ID>
Account Name: <insert account name>

Snapchat:

Managed by: <PMG or Client?>
Account ID: <insert account ID>
Account Name: <insert account name>

Pinterest:

Managed by: <PMG or Client?>
Account ID: <insert account ID>
Account Name: <insert account name>

Slack Channel Details:

channel: <slack channel>

tagged users: <emails of users tagged>

Ready By Date:<insert ready by date>

Link to Marketplace App: <insert marketplace link>

Link to Google Sheet: <insert google sheet link>

Any Issues: <put down any details with issues may have encountered>

After Submitting Ticket

Once your onboard ticket is submitted, you can follow our User Guide for setting up the google sheet. Keep in mind, you won’t be able to use the tool until your onboarding ticket is marked as done by the Alli Solutions team
Alli Budget Consolidation User Guide