Connect with us


Excel For Marketers: Make Your PPC Dashboards Dynamic With Data Validation



PPC dashboards are great for account managers, but they’re even better if they’re dynamic! What do I mean by dynamic, though?

A dashboard is dynamic when you can easily change inputs and review the corresponding output.

For example, in the dashboard below, I can select any of my campaign labels and the data in the dashboard will adjust according to which label I’ve chosen.

completed dynamic excel dashboard

In this post, I’ll walk you through the necessary steps to create this kind of dynamic dashboard!

What is Data Validation?

First, we need to review data validation in Excel. Data validation only allows data inputs within a specified range in the chosen cells. In the example below, I’ve used data validation to limit this cell to only the list of months in the dropdown menu.

Data validation doesn’t have to be a list. It can be a number of different data types, including:

  • Whole Number
  • Decimal
  • List
  • Date
  • Time
  • Text Length
  • Custom

The benefits of limiting a cell to a specific set of values include:

  • It prevents data entry errors
  • It’s less manual and thus more efficient

In this PPC use case, we’re making our dashboard more efficient by enabling the dashboard to change with the campaign label inputs, versus creating one separate dashboard for every campaign label in your paid search account. We’ll use data validation to create a dropdown list of all labels in our PPC account. Finally, we’ll set up the dashboard data so that it’s dynamically pulled from the raw data sheet based on which label you’ve chosen from the data validation dropdown list.

See also  IronSource launches new ad platform for app-focused mobile campaigns

How To Set Up Data Validation

For this example, we want to set up data validation in the Label cell.

dashboard for ppc account

First, go to your raw data sheet, which contains the data you exported from your PPC campaigns. Determine which data field you want your dashboard to be based on. In this example, we want the dashboard data to dynamically change based on the Label of the campaign. We highlight that column in our raw data and copy it.


ppc data highlight labels

On another worksheet, paste that column and title it “LabelList”. Then, with the labels still highlighted, remove the duplicates with the Remove Duplicates button on the Data tab.

Once you’ve removed the duplicates and have a concise list of labels, you should name the list. This isn’t required, but it helps eliminate potential mistakes later on. Highlight your list, including the header, and then use the Create From Selection button in the Formulas tab. Create the list name based on the top row, where the header is located.

Once you have a list, you’re ready to add data validation to your dashboard. Click the cell where you want to place your dropdown list.

Then, go to the Data tab and select “Data Validation”. The pop-up below should appear.

data validation helper in excel

For Validation criteria, choose List.

data validation options in excel

For Source, use “=LabelList”.

data validation source in excel

And that’s it! You should now have a dropdown that’s based on your LabelList.

excel dashboard with data validation

From there, you need to connect the PPC metric cells to the raw data and the new data validation cell. In my raw data, I’ve used Excel’s Create From Selection feature to name every field that I exported from my PPC account. That’s why in the formula below, you’ll see “Label” and “Impr.”. Those are the named ranges I created for my labels and impressions column in my raw data. With those, you can easily write the formula below to tell Excel to sum the respective metric from the raw data IF the label column contains whatever label you’ve selected in E4.


excel dashboard with dynamic data

And voila! You now have a dynamic PPC dashboard.

See also  A Quick Guide to Conditional Formatting in Excel

If you want to take your dashboards one step further, you can link either your Excel workbook or maybe your Google sheet to the actual data source, like Google Ads. You can create triggers that pull Google data into your workbook on a regular basis so that your dashboard is always using the most updated data.

I hope this post helps you get one step closer to automating your dashboards and PPC reporting! Data validation has been a huge time-saver for me and my team, so I believe you can benefit too.

If you want more hands-on Excel tips, I’ll be running an Excel workshop at Hero Conf Austin this year. You can get more information on that workshop here.


How is the Blockchain Shaping the Digital Marketing Automation Tools?



How is the Blockchain Shaping the Digital Marketing Automation Tools?

It is no doubt that most of us are aware of what blockchain is and how it has shaped the future of digital marketing automation. If you are reading this article, then you might be wondering what exactly is the difference between blockchain and digital marketing automation?

What Exactly Is Blockchain? The blockchain is a decentralized ledger system that records transactions between two parties. A block stores information about previous transactions, so each block contains a link to its previous transaction’s record to prevent double-spending.

The users will have their copy of the record on their device so they can keep track of all the changes made in that particular chain by any member of the network.

The Blockchain Is Creating a New World

The blockchain is a new technology. It’s based on the idea of decentralization and peer-to-peer networks, which makes it different from traditional centralized systems. Blockchain technology uses a distributed ledger to store information, making it impossible for a hacker to modify the data stored on the network.

The blockchain can get used in many ways, including cryptocurrencies like Bitcoin or Ethereum. But it also has applications in other industries such as digital marketing automation tools where marketers need fast access to information about their customers or prospects without having to go through an intermediary (like Google Analytics).

Some companies have started using this technology as part of their product offerings by providing users with access to their data through APIs that they can use however they wish (for example: visualizing trends over time).


How Digital Marketing Automation Tools Work

Digital marketing automation tools work by task automation like sending emails, creating landing pages, and tracking the performance of your marketing campaigns. These tools can help you save time and money by automating tasks.

See also  How to Use the Weighted Average Formula in Excel

For example, let’s say you want to reach out to more leads on LinkedIn but you don’t have time to send them an email every day. With a digital marketing automation tool like HubSpot or Marketo, you can set up an autoresponder sequence that will send out emails automatically so all you have to do is check-in once in a while and make sure everything’s running smoothly.

Prepare for the Future of the Blockchain Marketing

As technology continues to evolve, the blockchain will play a pivotal role in digital marketing. This is because it offers a secure and reliable platform that marketers can use to communicate with their audience. In addition, it also provides better transparency than traditional marketing methods because of its decentralized nature.

Below are some of the ways that marketers can benefit from using blockchain:

  • Security: The blockchain offers greater security than traditional methods of digital marketing since it doesn’t rely on central servers or third parties.
  • Efficiency: Using blockchain technology means there’s no need for middlemen or intermediaries when conducting transactions online. This improves efficiency by cutting out unnecessary steps between two parties looking to transact business with one another; this saves both time and money for both parties involved!

Secure Digital Marketing Tool Designed on Blockchain

A digital marketing tool designed on blockchain will be more robust and secure. Blockchain technology is a decentralized, distributed ledger technology that helps in recording transactions between two parties efficiently.

It also ensures that the records cannot be tampered with once they get recorded. This means that if you buy a product from an e-commerce website and pay for it using cryptocurrency (a form of digital currency), then no one can hack into the system and change your transaction record to avoid paying you the money.

See also  A Quick Guide to Conditional Formatting in Excel

In general, blockchain offers marketers several benefits:

  • It allows for better data sharing and storage than traditional databases
  • It offers enhanced security features because there is no single point of failure
  • It can help companies boost productivity by integrating both humans and machines

Blockchain Protocol That Ensures Safety, Security

Don’t worry, you can still use digital advertising. You just need to trust the blockchain protocol that runs it.

Ad-blocking software has made digital advertising ineffective already and will soon make all digital advertising completely ineffective. You see, if you are using ad-blocking software on your computer or mobile device, then you’re not seeing any of the ads being served to people who aren’t using ad blockers.

This means that advertisers must pay for each impression (or view) of their ads, even when those impressions do not get seen by anyone at all!


By adopting a blockchain protocol for their digital advertising campaigns, marketers and publishers can verify which impressions are being seen by humans and prove whether or not they were delivered effectively before paying for them.

It is worth mentioning that the benefits can be multifold as is already visible from the other spaces like the cryptocurrency markets. Numerous traders are gaining from the rising cryptocurrency prices. So, digital markets too must watch out for this technology.

The entire industry benefits because everyone is working together instead of trying to scam each other through fake views on fake websites with fake content created solely for financial gain without any real value-added back into society whatsoever!

See also  19 Easy Excel Tips, Tricks, & Shortcuts

Digital Technology Is Changing Everything

You probably already know that digital technology is changing everything. But what you may not realize is that blockchain is one of the biggest and most important digital technologies to come out in years.

Blockchain can be a difficult concept to grasp, but it’s worth learning about because it’s changing the way we do business, marketing, and advertising—even as we speak! The best place to start learning is by understanding what blockchain means. It’s essentially a decentralized database that allows information (such as financial transactions) to be stored on multiple computers rather than in one location. This makes it more secure than traditional databases which are usually stored on just one server or computer system.


As you can see, the blockchain is changing the way we interact with technology. It’s also changing how we experience marketing automation tools, and how they work. The future of digital marketing will be shaped by this new technology, which means marketers need to start exploring how it can be applied to their jobs now.

Source link

Continue Reading

Subscribe To our Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address