Missing Ad Cost Troubleshooting Guide
- Tony Browne (Unlicensed)
Summary / TL:DR: If a user has stated that their ad costs are not showing in reporting, you may follow these step by step instructions to diagnose the issue.
Helpful links:
Link to Stitch: Stitch - A Qlik product
Global Support stitch audit pinboard:
https://thoughtspot.14west.us/#/pinboard/7e802ca1-df78-4e14-a793-4f32fb613cb6/
IMPORTANT - PLEASE READ: In order to access an affiliate account for troubleshooting, you will need to login with the credentials that are stored on Secret Server. Secret Server is accessible via OneLogin on the Company screen. Username and password for each account are stored on Secret Server. If you do not have Secret Server on your OneLogin, please submit a ticket to IT requesting access to the Stitch folder.
Global Support - Stitch Integrations Troubleshooting Guide
If a user has stated that their ad costs are not showing in reporting you may use this as a first step to try to diagnose the issue.
Go to the GS audit pinboard (linked above) and filter to the source/promo codes in question (and make sure to adjust the correct date range) by using the ‘Promo Code’ filter at the top of the board & date range filter.
Once you’ve filtered on the source/promo codes you should refer to the column ‘Is Error?’. If the value = ‘True’ then look to the ‘Invalid Fields’ column to see the invalid reason code.
If there the ‘Is Error’ field says ‘False’ and there are ad costs in there please inform the user that they will populate in ad cost reporting (e.g. Marketing Performance data set or Lead Gen data set) later on.
REMINDER: It takes about 2 days for ad costs to catch up, but if the costs in the stitch audit board match the costs that the affiliate has provided, they will make it into the ad cost reports mentioned in section 3a.
Invalid Reason Code Definitions
Integration Name | Invalid Field | Definition |
|---|---|---|
GSheet | Campaign | Ad set name is invalid. Refer to setup instructions for formatting on how to correct |
GSheet | End Date | Their end date is likely not the actual date for the end of month (e.g. 12/30/21) |
Adset Name | Ad set name is invalid. Refer to setup instructions for formatting on how to correct | |
Bing | Ad_Group_Name | Ad set name is invalid. Refer to setup instructions for formatting on how to correct |
AdGroup | Ad set name is invalid. Refer to setup instructions for formatting on how to correct |
5. If the promo code is not coming up when you filter to it, it is likely an indicator that the stitch connection is broken and not setup. You will then need to follow the troubleshooting steps to figure out why it is broken.
6. Check to see if the account is setup in Stitch by looking to see if the integration is setup (please refer to the affiliate stitch credential sheet linked above to login to stitch and check their accounts). You will need to know if it is a Google/Bing/Facebook or Google Sheet integration.
7. If it is setup, is the account returning any errors? You can find errors on the account by looking at the status of the account on the main screen after logging in. You will see ‘Error’ as the status, click into the account then click on ‘Extractions. Once you’ve clicked on extractions, go back 1 or 2 days to see what the most recent error message is.
a. Often times, Facebook will return an error due to a password being updated in Facebook (e.g. SingerConfigurationError GET: 400 Message: Error validating access token: The session has been invalidated because the user changed their password or Facebook has changed the session for security reasons.). If this is the case only the user can fix this on their end. They will need to click into the account, then hit settings. Here is where they will need hit the ‘re-authorize’ button and login again to resync the account.
8. If the account was not returning any errors, does it follow the correct naming convention that was originally outlined in the ad cost integration setup?
a. Facebook = {Affiliate Name/Abbreviation}_facebook_{identifier}
b. Google Ad Words = {Affiliate Name/Abbreviation}_google_{identifier}
c. Google Sheet = {Affiliate Name/Abbreviation}_google_{identifier}
10. If yes, are the correct tables checked for Tables to Replicate?
a. Facebook: ads_insights
b. Google Ad Words: AD_PERFORMANCE_REPORT
c. Bing: ad_group_performance_report
d. Google Sheet: Default
11. If the correct tables are checked, are the correct fields checked for tracking?
a. Facebook: table name should be ads_insight
i. adGroup
ii. clicks
iii. cost
iv. Day
v. impressions
b. Google: table name should be AD_PERFORMANCE_REPORT
i. Ad Network
ii. Campaign
iii. Clicks
iv. Cost
v. End Date
vi. Start Date
vii. Impressions
c. Bing: table name should be ad_group_performance_report
i. Report_datetime (default),
ii. AccountID (default),
iii. AdGroupName,
iv. Clicks,
v. Impressions,
vi. Spend
vii. TimePeriod (default)
d. Google Sheet: table name should be default
i. Ad Network
ii. Campaign
iii. Clicks
iv. Cost
v. End Date
vi. Impressions
vii. Start Date
12. If all of the above is setup correctly please reach out to the client and ask them to provide an export out of their ad platform of the promo/source codes in question to see if the ad set names are correct and follow the following naming structure. This should be indicated via the Global Support Stitch Audit pinboard in ThoughtSpot.
a. Free Source Codes
i. {Source Code}:{List}:anything else
ii. {Source Code} {List} anything else
iii. {Source Code}|{List}|anything else
Example: X300W100 LIBWEALT This is free form text
b. Paid Promo Codes
i. {Promo Code}:{Primary Item}:anything else
ii. {Promo Code} {Primary Item} anything else
iii. {Promo Code}|{Primary Item}|anything else
Example: POXFV100 OXF This is free form text
13. If the ad set names are not in the above format you will need to have them correct the ad set names and then the ad spend should pull in on the next run.
14. If the next run does not balance out the costs you will need to ask the client to do a daily export out of their ad platform (Facebook/Google/Bing) which includes the start/end date, the ad name, and the ad spend. They will then need to submit a support ticket which should be escalated to the D&A Team so that they can update the costs tables with the spend provided from their ad platform. PLEASE NOTE: This situation should be rare, but from experience this is what can cause ad costs to not match what was in stitch. They had more than one item or list associated with one code, causing duplicate costs.