This guide will provide support on how you can visualize search term data broken down by channel, like below:
Data Sources
One data source is required - Adthena Search Term Detail API endpoint. It will need to be connected 4 times, in different configurations:
- Looking at all channels
- Filtered for PLA only
- Filtered for Text Ads only
- Filtered for Organic only
If you don't have PLA (Shopping) data, you can skip any references to PLA and just connect organic, text ads and all channels as datasets.
Each connected source, apart from the all channels one, should also be filtered by your domain in the dataset parameter. The connection should look like the below:
Repeat connecting the datasets until you have all of them - the configuration should stay the same apart from the Ad Type being changed each time as outlined in the data sources section, and when you're connecting the all channels dataset remove your domain from the competitor filter.
Implementation
Now that our data is connected, we can pull it all together to create our paid vs organic view, starting by connecting the data as below. We will be blending data sources using our connected data:
We need to select a full outer join, using search term as the join condition, for each data source:
Then, we just need to add a table to the Looker report, and use the below dimensions/metrics - feel free to swap out the metrics to get a configuration that works best for the data view you'd like:
Channels is a custom dimension that uses the below formula that can be copied and pasted, as long as your data sources are named as pictured in the data blend view (All, Organic, PLA, Text):
CASE
WHEN Estimated Impressions (Text) IS NULL AND Estimated Impressions (PLA) IS NULL AND Estimated Impressions (Organic) IS NULL
THEN 'None'
WHEN Estimated Impressions (Text) IS NULL AND Estimated Impressions (PLA) IS NULL AND Estimated Impressions (Organic) IS NOT NULL
THEN 'Organic Only'
WHEN Estimated Impressions (Text) IS NULL AND Estimated Impressions (PLA) IS NOT NULL AND Estimated Impressions (Organic) IS NULL
THEN 'PLA Only'
WHEN Estimated Impressions (Text) IS NOT NULL AND Estimated Impressions (PLA) IS NULL AND Estimated Impressions (Organic) IS NULL
THEN 'Text Ads Only'
WHEN Estimated Impressions (Text) IS NOT NULL AND Estimated Impressions (PLA) IS NOT NULL AND Estimated Impressions (Organic) IS NOT NULL
THEN 'All'
WHEN Estimated Impressions (Text) IS NULL AND Estimated Impressions (PLA) IS NOT NULL AND Estimated Impressions (Organic) IS NOT NULL
THEN 'Organic and PLA'
WHEN Estimated Impressions (Text) IS NOT NULL AND Estimated Impressions (PLA) IS NULL AND Estimated Impressions (Organic) IS NOT NULL
THEN 'Organic and Text Ads'
WHEN Estimated Impressions (Text) IS NOT NULL AND Estimated Impressions (PLA) IS NOT NULL AND Estimated Impressions (Organic) IS NULL
THEN 'Text Ads and PLA'
ELSE 'Other'
END
And done! Your table should now show a breakdown of search term coverage and metrics by channel.
The filtered text ad, organic, and PLA data sets will provide you data on your performance per search term per channel, whereas the all channels view will provide you data on the total performance for this term (for all competitors).
Optional step: create a total view for your metrics across all the channels
You can use a calculated field to sum est. clicks and est. impressions for each channel, using the already connected datasets for each channel), if you'd like to view your total performance by channel:
Full formula for est. clicks across all channels for the domain only:
SUM(IFNULL(Estimated Clicks (Organic),0))+SUM(IFNULL(Estimated Clicks (PLA),0))+SUM(IFNULL(Estimated Clicks (Text),0))