Ta kontakt med oss


A Deep Dive Into Advanced Pivot Tables for PPC


In my previous blog, “How to Pivot Paid Search Data in Excel” I went into great detail about how to perform a pivot table with an example on how to look at that data across multiple channels. Today I am going to dive deeper into how to look at audiences, ad copy, and data over time.

Audience Reporting

The best way to get comfortable with pivot tables is to download data and play with the information. Below is an image of the Audience tab in Google and I downloaded a basic audience report.

google ads audiencesOnce you have the report in Excel, this is a good time to remove and columns you do not want to use in your pivot table to help reduce the number of columns for you to select.

audience-excel-spreadsheetWith the pivot setup, I have the type of audiences selected with basic metrics of impressions, clicks, and conversions to show how the data pull. For this campaign, we are only seeing conversions coming through on our Detailed Demographics.

audience-pivot-setupBy adding another row we can see even more detail about which specific audiences have converted for us.

audience-pivot-setupAfter I have this information, it would be easier for me to see the conversions if they were ranked from the highest conversions to lowest. By selecting the carrot by Row Labels and selecting the Audience field, I can sort by conversions and hit descend. Now my data will sort by conversions for me to quickly see which audiences I should increase by bid modifiers.

pivot-table-sortHere is a close up of how the data looks after our sorting. This client focuses on higher education so increasing our bids for Advanced Degrees and Bachelor’s Degrees makes sense. I can also pull a similar report like this for gender, ages, and income levels to decipher if we should make any bid recommendations based on past performance.

pivot-table-conversions-sortAd Copy Testing

Now that you have more practice by playing around with audience data, you can feel comfortable downloading data with more aspects. The next example starts with pulling an ad report, and I have removed any columns that I do not want to use in my pivot to make it easier to find the data segments I need.

ad-copy-excel-spreadsheetOnce you have the data you need to go ahead and create your pivot. I have selected to look at my Headline 3 ad copy since this was part of our most recent test.

audience-pivot-setupThis shows how each headline performed on its own, but I want to dig further to see how the headlines performed with other parts of the ad. To do so, I am going to drag the Description field down to the row section of the pivot so I can look at performance side by side.

ad-copy-headline-vs-descriptionI can swap out the different descriptions and compare them, but I would rather copy and paste my existing pivot table and edit which description I am looking at so I can compare them side by side. This saves time from having to create a new pivot from scratch but also allows you to save multiple pivots on one Excel sheet for your own analysis.



Lastly, I am going to show you how to work with dates within the pivot table. I have an example of a report pulled directly from Google Ads and the date is in a text format. I can leave the date as is but you will see that this might not be the best want to look at the data.

date-alphabetically-orderBelow is my pivot showing the date as a text, and it pulls the months alphabetically instead of in chronological order.

pivot-alphabeticalI have now gone through and changed the dates to short form and here is how the data now looks in Excel before I pull the same pivot table.

date-text-short-formNow that I have the dates in the new format, the months are coming through in order instead of alphabetically.

pivot-short-form-tableI can now show you one last trick, you can use conditional formatting within the pivot table and we will see there is some seasonality with this account.


With this client, we have our busiest and most successful campaigns in Jan, May, and August and the data reflects this. I have used pivot tables to show seasonality from the past to help predict how much we need to spend in the future. Once you are comfortable formatting your pivot table you will find that you can turn raw data into easily digestible bits of information for you and your clients. To learn more about Excel here is The Complete Guide to Using Excel for PPC.



Google ska betala $391,5 miljoner för uppgörelse över platsspårning, säger statliga AG:er


Google to pay $391.5 million settlement over location tracking, state AGs say

Google has agreed to pay a $391.5 million settlement to 40 states to resolve accusations that it tracked people’s locations in violation of state laws, including snooping on consumers’ whereabouts even after they told the tech behemoth to bug off.

Louisiana Attorney General Jeff Landry said it is time for Big Tech to recognize state laws that limit data collection efforts.

“I have been ringing the alarm bell on big tech for years, and this is why,” Mr. Landry, a Republican, said in a statement Monday. “Citizens must be able to make informed decisions about what information they release to big tech.”

The attorneys general said the investigation resulted in the largest-ever multistate privacy settlement. Connecticut Attorney General William Tong, a Democrat, said Google’s penalty is a “historic win for consumers.”

“Location data is among the most sensitive and valuable personal information Google collects, and there are so many reasons why a consumer may opt out of tracking,” Mr. Tong said. “Our investigation found that Google continued to collect this personal information even after consumers told them not to. That is an unacceptable invasion of consumer privacy, and a violation of state law.”

Location tracking can help tech companies sell digital ads to marketers looking to connect with consumers within their vicinity. It’s another tool in a data-gathering toolkit that generates more than $200 billion in annual ad revenue for Google, accounting for most of the profits pouring into the coffers of its corporate parent, Alphabet, which has a market value of $1.2 trillion.

The settlement is part of a series of legal challenges to Big Tech in the U.S. and around the world, which include consumer protection and antitrust lawsuits.

Though Google, based in Mountain View, California, said it fixed the problems several years ago, the company’s critics remained skeptical. State attorneys general who also have tussled with Google have questioned whether the tech company will follow through on its commitments.

The states aren’t dialing back their scrutiny of Google’s empire.

Last month, Texas Attorney General Ken Paxton said he was filing a lawsuit over reports that Google unlawfully collected millions of Texans’ biometric data such as “voiceprints and records of face geometry.”

The states began investigating Google’s location tracking after The Associated Press reported in 2018 that Android devices and iPhones were storing location data despite the activation of privacy settings intended to prevent the company from following along.

Arizona Attorney General Mark Brnovich went after the company in May 2020. The state’s lawsuit charged that the company had defrauded its users by misleading them into believing they could keep their whereabouts private by turning off location tracking in the settings of their software.

Arizona settled its case with Google for $85 million last month. By then, attorneys general in several other states and the District of Columbia had pounced with their own lawsuits seeking to hold Google accountable.

Along with the hefty penalty, the state attorneys general said, Google must not hide key information about location tracking, must give users detailed information about the types of location tracking information Google collects, and must show additional information to people when users turn location-related account settings to “off.”

States will receive differing sums from the settlement. Mr. Landry’s office said Louisiana would receive more than $12.7 million, and Mr. Tong’s office said Connecticut would collect more than $6.5 million.

The financial penalty will not cripple Google’s business. The company raked in $69 billion in revenue for the third quarter of 2022, according to reports, yielding about $13.9 billion in profit.

Google downplayed its location-tracking tools Monday and said it changed the products at issue long ago.

“Consistent with improvements we’ve made in recent years, we have settled this investigation which was based on outdated product policies that we changed years ago,” Google spokesman Jose Castaneda said in a statement.

Google product managers Marlo McGriff and David Monsees defended their company’s Search and Maps products’ usage of location information.

“Location information lets us offer you a more helpful experience when you use our products,” the two men wrote on Google’s blog. “From Google Maps’ driving directions that show you how to avoid traffic to Google Search surfacing local restaurants and letting you know how busy they are, location information helps connect experiences across Google to what’s most relevant and useful.”

The blog post touted transparency tools and auto-delete controls that Google has developed in recent years and said the private browsing Incognito mode prevents Google Maps from saving an account’s search history.

Mr. McGriff and Mr. Monsees said Google would make changes to its products as part of the settlement. The changes include simplifying the process for deleting location data, updating the method to set up an account and revamping information hubs.

“We’ll provide a new control that allows users to easily turn off their Location History and Web & App Activity settings and delete their past data in one simple flow,” Mr. McGriff and Mr. Monsees wrote. “We’ll also continue deleting Location History data for users who have not recently contributed new Location History data to their account.”

• This article is based in part on wire service reports.


Fortsätt läsa

Prenumerera på vårt nyhetsbrev
Vi lovar att inte spamma dig. Avsluta prenumerationen när som helst.
Ogiltig e-postadress