One of the features of Agency MVP includes its unique reports. This article will cover the marketing report, which helps you track your ROI so that you can figure out which campaigns are working and which ones aren’t.
Check out the video here.
To get to your reports, click your name in the top right hand corner and click “reports.”
You select a time range and then click apply and it will generate your report.
Once ready, click download and open the spreadsheet. There are a couple of things we’ll want to do to format it.
-
Click the tab right above the first row and to the left of the first column
-
Double click on the line between columns A and B to expand the information.
This report takes every marketing source - or tag - that you’ve used in your agency and breaks down some data.
-
Column B tells you the number of leads that you’ve entered for each tag. This is helpful because as you determine your ROI, it’s important to know how many leads you have for each tag.
-
The next three columns contain the number of households quoted, the number of households sold, and the closing ratio.
-
Column F gives you the total premiums sold. This information allows you to determine which marketing campaigns have a pretty high closing ratio as well as which campaigns have sold the most premium.
But to really track your ROI, we can add a few formulas to the spreadsheet to get you even more data.
For example, in cell G1, type:
-
“Quote to Lead Ratio.”
-
In cell G2, type equals sign [=]
-
open parenthesis [ “(“ ]
-
Click on cell C2
-
Type forward slash [ “/”]
-
Click in cell B2
-
Type a closed parenthesis [ “)”].
-
Click enter, and it will calculate the result.
-
Click on column G, making it highlight the entire column and click the percentage symbol [%] to format it correctly. This is helpful to be able to see the number of leads that have had quotes completed for them.
Pro Tip: click in cell G2 and hover your mouse in the bottom right corner until it turns into a small black plus sign. From there, you can drag it down, making formula copies in each row.
Next, let’s look at cost. Click cell H1:
-
Type “Cost.”
-
Click in cell H2
-
Type the equals sign [=]
-
Type an open parenthesis [“(“].
-
Click in cell B2
-
Type the asterisk [*] to multiply.
-
Here, you’ll need to type your cost per lead. For example, we’ll say these leads cost a dollar-twenty-five.
-
You can also click on column H, then click the dollar sign [$] to format it accordingly.
Now, you’ll have to do this individually for each row as not every lead costs the same. And, for those that didn’t cost anything, you will type “0” instead. Once you’ve completed that, you can use the next column to track commission.
-
Click in cell I-1
-
Type “Commission.”
-
In cell I-2, type the equals sign [=]
-
Type the open parenthesis [“(“]
-
Click cell F2
-
Type the asterisk symbol for multiplication [*]
-
Then type the percentage amount.
NOTE: Make sure you type this out as a decimal. So, for example, if it’s 5%, you would type .05. If it’s 10%, then you would type .10. And so on. Make sure to include a closing parenthesis [“)”] to finish off the formula and click “enter.”
Now that we have calculated the cost and commission on each tag, we can type the formula for determining the ROI. In cell J1:
-
Type “ROI.”
-
Click in cell H2.
-
Type the equals sign [=]
-
Type an open parenthesis [“(“].
-
Click cell I-2
-
Type a dash. In this case, it will be a minus symbol [-].
-
Click Cell H2
-
Type a closing parenthesis [“)”]
Pro Tip: you can click and drag the formula down, like you did for the quote to lead ratio so that it calculates this for each tag. Now you have a way to track your ROI and figure out which campaigns are working for your agency!