Step-By-Step Guide: Paid and Organic Search Term View

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: 

1. Looking at all channels 
2. Filtered for PLA only
3. Filtered for Text Ads only
4. 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))