I have a csv file of companies that visit my website every week and a csv file of companies that are in my target account list, the data is raw and needs a pivot table to consolidate data (ie. company visited 3 webpages so it shows up 3 times in the data).
I need to see which website visitors are in the target account list. A simple Vlookup typically. But I would like to automate this process.
How I imagine it would look would be in steps:
Add new worksheet to a google sheet with the raw csv file of web visitors for the week (triggers the automation)
Pivot table the worksheet to get to a list of companies and the total # of web visits + the unique web pages they visited
Vlookup on this pivot table data against the list of target companies to see which website visitors are target accounts
Push results to slack
Very curious how Bardeen would help solve this. Currently i’m looking at Zapier but i’m a huge fan of Bardeen so wanted to try this here too.
Thanks for the inquiry. I’ve checked with our product team and unfortunately Bardeen is not able to work with pivot tables at the moment and will add that as a feature request. In the meantime, can we help you get other automations set up?
Cheers,
Lucy
Customer Support - bardeen.ai
Explore | @bardeenai | Bardeen Community
This is something beyond Bardeen’s capabilities - and quite franqly - beyond Zapier’s capabilities too, since this requires some data manipulation that automation tools don’t usually provide.
Here's what I researched with Zapier:
For these, we might need to use a combination of tools, with Zapier orchestrating the workflow between them.
Adding a new worksheet with the raw CSV file of web visitors for the week: Zapier can automate the process of adding a new worksheet to a Google Sheet. You can use a trigger like receiving an email with the CSV file attachment or having the CSV file added to a cloud storage folder (like Dropbox or Google Drive) to automatically upload its contents to a new Google Sheets worksheet.
Creating a pivot table for list of companies and their web visits: Currently, Zapier doesn’t directly support creating pivot tables within Google Sheets through its automated processes. For this step, you might need to pre-configure a Google Sheet with a pivot table that automatically updates when new data is added to a certain range, or consider using Google Apps Script to dynamically create pivot tables based on triggers.
Performing a VLOOKUP against the list of target companies: Like the creation of pivot tables, VLOOKUPs or similar data lookups are not directly handled by Zapier. This step would likely require a custom script within Google Sheets or an integration with a database or service that can perform this lookup.
Pushing results to Slack: Once the desired data (i.e., the list of target account visits) is compiled in Google Sheets, Zapier can easily automate the process of pushing these results to Slack. You can set up a Zap to watch for updates in a specific Google Sheets range or sheet and then send a message to a Slack channel with the information about the target accounts that visited your website.
While Zapier might not directly automate the data manipulation steps (steps 2 and 3), it is very effective at moving data between services (step 1 and 4) and triggering actions based on certain conditions. For the data manipulation steps, you might want to explore integrating Google Sheets’ built-in functions, Google Apps Script, or another tool specialized in data analysis and manipulation.
I would recommend Google Apps Script or Parabola for such a workflow.
What is the workflow you’re trying to achieve? If it’s high impact for your company - I might be able to help.
Thanks Ivan, not super high impact. Was hoping it would be something simple but it looks like much more trouble than its worth. Was going to be a fun side project because it takes me roughly 30 mins to do this every week and was hoping to automate it away.
