/https://assets.v3.snowfirehub.com/images/120651/21_blogbrain-logo.png){kind=logo}
Steering the Company's Cost of Sale with automated sheet
/https://assets.v3.snowfirehub.com/images/120651/33_group-2.jpg)
Time is precious, do spend time automating as much of your reoccurring tasks as possible. It will give you joy and happiness as your brain then can learn and solve other problems, instead of doing the same thing over and over again.
You cannot export data in Excel format from Power Bi Desktop created reports and have to then build the report in the old Power Bi Report Builder.
Automate so you get the email attachment into your Google Drive in an editable format. Here I used Zapier.
Import that data into your sheet, format it, and then import it to the COS Sheet Template. Customize the template however you want it and make it your own!
I’m responsible so that our team, the paid growth team, follows the planned COS targets the company has. I started that responsibility right before Black Week in November, where it became apparent that we needed to follow the COS targets (that haven’t been reached since April 2019). Before, the strategy was to grow as quickly as possible, as long as we didn’t exceed our margins of course! But now, one week before Black Week, we as a team needed to steer by it, and that drastically. So it became my responsibility to steer the COS.
Yes, you are right. It is almost like being a cost controller or an accountant (to be a very light one) as you're adding all the daily costs and all the daily sales. Every day. But it is so important to know as a marketer how you're doing cost-wise against your revenues. It is the foundation of your understanding of the company you work for, to learn the company by the economic numbers is a vital step that connects you to the business value bottom line. Also, as an analyst within marketing, or any decent marketer for that sake, should to some extent be aware of how they’re doing in economic terms. You have to make sure that your marketing isn’t bursting the margins and that you’re making a profit of the orders you get through your marketing.
Before I built this automated sheet, I had a sheet I manually filled in. During Black Friday I even filled it in by the hour so I could know exactly how we were doing. But on normal days I usually fill it in once per day. That is roughly one hour per day. 20h per month, which equals half a week! Yikes, that is not anything I want to continue to do. And neither should you. Plus you get the amazing reward of actually saving the company you work for half a week of your precious time, so that you, being so smart that you are, can solve other interesting tasks. I wish I could get half a week of holiday for that per month...
It’s a business analytics tool by Microsoft. Very, very powerful!
"Power BI is a business analytics service by Microsoft. It aims to provide interactive visualizations and business intelligence capabilities with an interface simple enough for end users to create their own reports and dashboards." - Wikipedia article about Power Bi
When you subscribe to a report, all you get in your email is a PNG-file of the report (!) and a link to the report. You still have to manually export it, which sucks!
I talked to my colleague that is our Power Bi wiz (to be using a trendy word, but oh so true) and he told me that there was a way to actually get data out from Power Bi, and also to get an automated send-out with that data in whatever format you want to have it in. Great! This is exactly what we need.
The work-around lies within Power Bi Report Builder, which is a super old, but very powerful, tool. It has more freedom than it’s modern variant, the Power Bi Desktop plus it can export more than just useless PNGs.
Now if all goes well you have the data you want, in a little sub-dataset. Why having a sub-dataset though? Because you usually have built measures (metrics that is) within Power Bi already that you want to use. Instead of re-building them, you can just copy the dataset they create, and use that into your visual in Power Bi Report builder, where you want to export data from.
Now, as you have built a report that refreshes daily/hourly/whatever your Power Bi people have sent your dataset to refresh, you, of course, want to get it out from Power Bi universe. That’s why we started this whole thing in the old Report Builder interface right.
When you subscribe to a report made in Power Bi Desktop, you only get PNGs and a link to the report sent to you. However, when you subscribe to a report built in the old Power Bi Report Builder, then you can get the data in a CSV, Excel, PDF or another version that you want. This is what we’re going for.
Now you have actually gotten the data out from not only Power Bi, but also the Microsoft universe! Time for the next step!
This is how I, after hours of trial-and-error, got it to work exactly how I wanted it to!
Settings in Zapier
1. Choose Gmail, and App and Event and step 2, choose your account of course.
4. Specifying the file to be replaced
TAAA-DAAA! The file. Still in XLSX format <3
In your little drive, you should now find your file. Mine looks like this
Building the COS Sheet
Now you see that we’re in this example, with these randomized numbers, are trending at 16.44% COS, even though our target is 10%. Somebody needs to take control of this! ;-)
The COS Template is available for you to create a copy of here
Time is precious, do feel satisfied with the work you've done to automate as much of your job as possible. Nobody likes re-occuring, tedious, boring tasks. By following this guide, you've now made the (almos) impossible and got data out from Power Bi in an automated fashion!
Also you've smoothed out your workflow with Zapier, meaning you dont even have to lift your pinky.
:upscale()/https://assets.v3.snowfirehub.com/images/120651/30_cos-google-sheet-template.png)
