This blog post is for all the marketing folks out there using numerous hours every week building and maintaining Excel sheets. Today I will show you how to setup a business intelligence dashboard giving you the full overview of your Facebook and Google marketing activities. There are many business intelligence tools out there some of them are very enterprise concerned and require deep knowledge of programming. QuickSight from Amazon Web Services (AWS) is very easy to use and requires only minimal tech knowledge so do not be frightened by leaving your Excel sheets for a while.

Transitioning from spreadsheets to dashboard tools requires patience but it will for sure be benefitial. Some of the benefits of transitioning from spreadsheets to QuickSight are: Email reports, reuse of visualizations, embeed dashboard, drag and drop graphs and most importantly automaticly data update.

Imagine being anaffiliate marketer

Lets pretend for the rest of this article that your are working as an affiliate publisher. You are the happy owner of a comparison website, comparing danish phone company products. In order to generate leads from the website you have decided to buy traffic from both Facebook and Google Ads (former Adwords). All links on your website is redirected through an affiliate network where you earn a fixed lead price per campaign.

The diagram above shows the user flow from start to finish. We assume that you are running a CPM campaign on Facebook and CPC campaign on Google Ads. Your are only earning money if users decide to switch telecompany and signs a contract with one of the companies listed on your website.

In order to analyze on the scenario described above, I have generated a dataset with daily marketing numbers on fictive Facebook Ad-sets and Google Ads adgroups. You can download the dataset in the bottom of this article. I must stress that numbers are randomly generated and does not relate to an actual campaign. Running a setup like this is not riskless and you can loose money buying traffic not generating any leads … thereby no revenue.

What is AWS QuickSight

AWS QuickSight is a business intelligence tool developed by Amazon Web Services. When I tried QuickSight some years ago I was actually really disappointed. QuickSight did not have as much features as other alternatives such as Tableau and Qlik and it seemed like AWS did not prioritise the development – I was wrong. Since then QuickSight has developed into a fullblown BI tool with predictive capabilities able to utilize Machine Learning products from the AWS stack of products.

Pros

  • Developed by AWS which stands for quality and stability – especially stability. QuickSight is a browser based application able to handle millions of rows of data.
  • QuickSight is really connected to the AWS product catalog. If your company is already utilizing AWS products using QuickSight is a nobrainer.
  • SPICE is data engine letting you import millions of observations without worrying about speed or scalability. SPICE automaticly indexes your observations. The maximum capacity of SPICE is 25 GB per dataset.

Cons

  • No application for Mac or Windows. QuickSight is mainly browser based but apps for iOS and Android exists.
  • It is not possible to blend data sources together like in Tableau. You must blend data before importing.
  • Simple trend lines is not possible to make in QuickSight.
  • Only support for the 4 biggest currencies in the world. Not really a big problem since you can always construct dataformat yourself.

Datastructure

The real benefit of using daashboard tools is when data is automaticly updated from a database or similar. Since the purpose of this article is just to inpsire you to create a dashboard we will just upload a simple CSV file. Maybe I will write a blog post about importing data from a SQL database. Stay tuned !

The most important columns of the dataset. Every row correspond to a unik date and ad-id. Revenue is added through the use of sub-id tracking which is possible with most serious affiliate networks out there.

It is now time to import data in QuickSight. The process is simple an illustrated on the picture below. First pick the file on your computer. You can also import files from S3 or other sources. I recommend you to export your Excel sheet to CSV. QuickSight is not super at interpreting Excel formulas.

When the file is uploaded hit the “edit” button. Your are now able to change data types, add calculated field and dimensions and remove cols not needed. If you follow along then please add a calculated field called profit = commission – cost.

Picture 3 shows a summary of the data set. Data is 700 kb and contains 2191 rows/observations. You can always come back later and edit the data set. Be careful to not delete any fields used in a dashboard.

Time to build and analyze

Data is now imported into QuickSight and we are ready to build a dashboard. Let’s look at the final result and then describe every six parts of it.

Visualization 1) Profit + Forecast

Figure 1 shows the profit depicted over time. As we see some days are good and some days are bad. Overall we are making a small profit as table 6 shows. The orange span at the end of the graph is the forecasted profit the next 14 days. Forecast is a relatively new feature in QuickSight and requires you to subscribe to the enterprise plan.

Unofrtunatly our little affiliate business is not making money in the future, according to predictions.

Figure 2) Cost distribution

Just a simple pie chart showing the media spend distribution. Many marketing people have to stay withing a certain media budget. This graph tells you if you are spending money according to your initial plan.

Figure 3) CPC and EPC

Earnings per Click (EPC) = Commission / Clicks, is a really important KPI for affiliate marketers. EPC tells you how much money you are generating for every user visiting your website. EPC is combined metric including both conversion rate and lead price. If the EPC is not higher than your CPC it is impossible to earn any money.

As you can see the average EPC for facebook traffic is 0.5 dkk and 7,87 dkk for Google traffic. What a difference !

Cost per Click (CPC) = Cost / Clicks is how much you pay for a click on average. Eventhough the EPC is significantly higher for Google traffic the CPC is likewise very high. Therefore Google is not a profitable channel to buy traffic from in this particular case.

Figure 4) EPC over time

The EPC plotted over time will show if the conversion rate is decreasing or the lead price is decreasing. An important KPI to look out for when doing affiliate marketing.

Figure 5) Where is the volume ?

Your comparison website is linking to 6 different tele companies. This simple pie chart shows where your are delivering the most volume. YouSee telecompany are getting nearly 60% of all your leads – maybe it is time to negotiate some better prices…

Figure 6) The grand table

The big table at the bottom of the dashboard is the most basic information you need. If your are not willing to create any fancy charts then please at a minimum make a table like this. The most important KPI’s in the table are:

CTR%: Click Through Rate. The number of users clicking on your banner.

Conv%: Conversion rate. How many of the users on your website is converting into leads.

Contribution Margin: As you see some ad-sets are making more profit than others. In order to answer why some adsets are not making money please refer to the EPC,CPC, Conv% and CTR% metrics.

Conclusion

Our little fictional affiliate business is now transparent thanks to AWS QuickSight. The dashboards shows us that Google Ads traffic is not profitable and it is necesssary to decrease the CPC to earn money.

The Facebook traffick on the other hand looks promising and 3 out of 6 ad sets are very profitable.

Hopefully this article will give you inspiration on how to build your own dashboard for your business. Other dashboard tools exists on the market and I know that Tableau and Klipfolio both integrate with Google Ads and Facebook.