The Operations team currently relies on PowerBI for data that fuels the monthly Digital Marketing Growth Project meeting. Data is exported for both ARI and Dealer Spike. It is cleaned up and sent over to each director and the digital marketing sales manager for analysis. Here are the steps to exporting and cleaning up data:
This process should be done after the last day of the previous month to get a complete view of the data.
Click here to go to PowerBI: Cancellation Request Overview
At the top of the screen, adjust the date criteria to the last 1 calendar month. This will automatically change the date to the first and the last days of the previous month.
Then you will adjust the Brand Selector, Product Selector, Change Request Save Status as follows for churn (for cancellations, deselect “Lost” in the Change Request Save Status):
You may find that the buttons are often finicky. When this happens, choose Select all and deselect what you don’t want. Dark button means the it is selected.
What is the difference between cancellation and churn
Cancellations = All cancellation requests. (Think of it as the number of unhappy customers)
Churn = Cancellation requests minus saved accounts. (Money we actually lost).
To export one brand at a time, choose a single brand. The above image has Dealer Spike selected.
Click the ⚫ ⚫ ⚫ below the yellow Word Wrap Toggle button for more options.
If you do not see the dots, hover your mouse over the table below the button to make it appear.
On the menu that pops up, click Export data.
In the next window, select the Data with current layout and click Export.
Find the data that was exported and open the file.
If your spreadsheet is in Protected View, click Enable Editing.
Now that you have the data you need, you will clean it up. It is up to you if you would like to delete columns or copy and paste the relevant columns to a new sheet.
You will need to create 1 Excel workbook with 2 different tabs, one for each company. Here is an example of what your finished product will look like: 06_June.xlsx
In its unedited state, these are the columns you will need to keep and rename to the new sheet.
|
|
| |
|---|---|---|---|
E | Account | A | Client |
N | Primary Reason | G | Reason |
O | Primary Detail | H | Details |
S | Billing Change Notes | I | Notes |
AK | DS_Website_Cancel | B | Website |
AO | DS_SEM_Cancel | C | SEM |
AQ | DS_SEO_Cancel | D | SEO |
AS | DS_TDA_Cancel | E | TDA |
AU | DS_DM_Cancel | F | AEM |
Old Column
|
|
| |
|---|---|---|---|
E | Account | A | Client |
N | Primary Reason | G | Reason |
O | Primary Detail | H | Details |
S | Billing Change Notes | I | Notes |
AJ | Website Cancel ARI | B | Website |
AN | ARI_SEM_Cancel | C | SEM |
AP | DS_SEO_Cancel | D | SEO |
AR | ARI_TDA_Cancel | E | TDA |
AT | ARI_DM_Cancel | F | AEM |
Now it’s time to make the spreadsheet neat and legible!
Start by right-clicking column I and setting the Column Width to 100.
Then, while the column is still highlighted, click Wrap Text.
Here are the rest of the column widths:
Column | Column Width (Default Unit) | Column Width (Pixels) |
|---|---|---|
A | Double click to auto-expand. | Double click to auto-expand. |
B, C, D, E, F | 10 | 75 |
G, H | 25 | 180 |
I | 100 | 705 |
Highlight columns A through H and set the alignments to Middle Align and Center.
To make the Notes in column I easier to read, set the alignment to Left.
The column headers (A1-I1) should be bold.
The headers for Dealer Spike sheet should have an orange (#F6941E) background.
The ARI ones will receive a green (#52B54A) background.
Changing the background color of cells in an Excel worksheet will change its borders. Adjust borders accordingly to ensure readability. Using “All Borders” is recommended for the header row.
Make the sheets easier to read by freezing the first (header) row by highlighting row 1 and going to View > Freeze Panes > Freeze Top Row.
Finally, insert a filter by highlighting columns A through I and going to Data > Sort & Filter > Filter.
You may also add a color to each tab by right-clicking each tab and choosing a color.
Now that you have created and cleaned up the document, place it in the correct year’s folder on OneDrive and follow the naming convention as follows:
mm_month.xlsx
Example:
01_January.xlsx
06_June.xlsx
12_December.xlsx
It might seem repetitious, but the numerical system helps organize the files, and the month spelled out helps directors recognize which month the file belongs to.
You are done! You can now share your data with the VP of Digital Services, Directors, and the DigMS Sales Manager.
Add Comment